Question

In: Accounting

The BCJ Company needs a master budget for the three months beginning April 1, 2020. The...

The BCJ Company needs a master budget for the three months beginning April 1, 2020. The company retails widgets. The 2020 budget should be based on the following information. An ending minimum cash balance of $10,000 each month is required. Sales are forecasted at an average selling price of $8 per widget. Merchandise costs are $4 per widget. Currently, the company maintains an ending inventory balance equal to 20% of the next month’s projected cost of goods sold. Purchases during any given month are paid half in the month of purchase and half during the following month. Sales are 20% cash and 80% on credit (payable within 30 days), but experience has shown that 60% of monthly credit sales is collected in the current month, 40% in the next month.

Monthly operating expenses are as follows:

Wages and salaries                                  $15,000

Insurance expired 150

Depreciation                                                 1,200

Utilities    1,000

Advertising   300                          

Miscellaneous 500

Rent 400 per month+ 10% of monthly sales.

All operating expenses are paid as incurred, except insurance, depreciation, and rent. Rent of $400 is paid at the beginning of each month, and the additional 10% of sales is paid in the month following the sales.    The company plans to buy some new equipment for $5,000 cash in June. Cash dividends of $1,500 are to be paid quarterly, beginning April 15. Dividends are declared on the 15th of the last month in the calendar quarter.

BCJ has an established line of credit with its bank, Third Fifth National. Money can be borrowed and repaid in multiples of $1,000, at an interest rate of 6% per annum. Management wants to minimize borrowing and repay rapidly. Interest is computed and paid when the principal is repaid. Assume that borrowing occurs at the beginning and repayments at the end of the months in question. Money is never borrowed at the beginning and repaid at the end of the same month. Compute interest to the nearest dollar.

Balance Sheet

March 31, 2020

Assets                                                                          Liabilities

Cash                                                      $16,300           Accounts payable (inventory)                          $13,750

Accounts receivable (net)                        19,200 Dividends payable                                            1,500      

Inventory                                                   4,000 Rent payable 6,000

Prepaid insurance                                 1,800 Total 21,250

Land, Building, Equipment (net)            75,000

Stockholders' Equity                                                

                                                                                   Capital stock ($1 par value)    54,400

                                                                                   Retained earnings 40,650

Total assets                                   $116,300           Total Liabilities & Stockholders' Equity            $116,300

Recent and forecasted sales:

January         $45,000      February    $50,000                March $60,000               April            $40,000     

May               $50,000      June           $70,000                July            $60,000

Required:            1.    Prepare a master budget, using Excel, and all supporting schedules (including sales) for the months April, 2020 through June, 2020.

2. Prepare the budgeted Income Statement and Statement of Cash Flows for the quarter ended June 30, 2020 and the budgeted Balance Sheet at June 30, 2020.

Solutions

Expert Solution

Please give positive ratings so I can keep answering. It would help me a lot. Please comment if you have any query. Thanks!
Due to character limit I am attaching the image of Balance Sheet
BCJ
Sales Budget April May June Total Note
Budgeted Sales Revenue        40,000.00         50,000.00      70,000.00       160,000.00 C
Cash sale is 20%          8,000.00         10,000.00      14,000.00         32,000.00 D=C*20%
Credit sale is 80%        32,000.00         40,000.00      56,000.00       128,000.00 E=C*80%
Collection Budget April May June Total
Cash sale          8,000.00         10,000.00      14,000.00         32,000.00
60% of Credit sale in the same month        19,200.00         24,000.00      33,600.00         76,800.00
40% of Credit sale in the next month         12,800.00      16,000.00         28,800.00
Collection of December 31, 2019 accounts receivable        19,200.00                        -                      -           19,200.00
Total Scheduled Collections        46,400.00         46,800.00     63,600.00       156,800.00
Monthly merchandise purchases budget April May June Total July Note
Monthly Sales Budget        40,000.00         50,000.00      70,000.00       160,000.00      60,000.00 C
Sell price                  8.00                   8.00                8.00                8.00 A
Monthly Sales units          5,000.00            6,250.00        8,750.00         20,000.00        7,500.00 B=C/A
Add: Closing          1,250.00            1,750.00        1,500.00           1,500.00 M= 20% of A of next month.
Less: Opening          1,000.00            1,250.00        1,750.00           1,000.00 N= 20% of A of same month. For April its $ 4,000/8.
Material Purchase Budget          5,250.00           6,750.00        8,500.00         20,500.00 H
Monthly merchandise payment budget April May June Total Note
Material Purchase Budget          5,250.00           6,750.00        8,500.00 See H
Cost per unit                  4.00                   4.00                4.00 O
Material cost        21,000.00         27,000.00     34,000.00         82,000.00 P=H*O
50% paid in the same month        10,500.00         13,500.00      17,000.00         41,000.00 Q= 50% of P
50% paid in the next month         10,500.00      13,500.00         24,000.00 R= 50% of P of previous month
Payment for December 31, 2019 purchases        13,750.00                        -           13,750.00
Material Payment        24,250.00         24,000.00     30,500.00         78,750.00 S
Operating expense budget April May June Total
Wages and salaries        15,000.00         15,000.00      15,000.00         45,000.00
Utilities          1,000.00            1,000.00        1,000.00           3,000.00
Insurance expired              150.00               150.00           150.00               450.00
Advertising              300.00               300.00           300.00               900.00
Miscellaneous              500.00               500.00           500.00           1,500.00
Rent (Fixed)              400.00               400.00           400.00           1,200.00
Rent (Variable)          4,000.00            5,000.00        7,000.00         16,000.00 T=C*10%
Operating expense budget        21,350.00         22,350.00     24,350.00         68,050.00
Answer 6- Cash budget April May June Total
Beginning Cash Balance        16,300.00         13,750.00      15,350.00   &nbs

Related Solutions

1. It is April 2019. A US company needs to borrow $100,000,000 for three months starting...
1. It is April 2019. A US company needs to borrow $100,000,000 for three months starting five months from now. The current 3-month LIBOR is 2.5%. The company is afraid that rates may rise during those five months before it obtains the loan. Should the company buy or sell Eurodollar futures? And how many (ignoring the present value of the basis point change)? Which month should the futures settle/expire? Assume that the appropriate Eurodollar future is trading at 97.4. What...
OJ Small Company needs a cash budget for the month of April 2020. The company's controller...
OJ Small Company needs a cash budget for the month of April 2020. The company's controller has provided you with the following information and assumptions: A. The April 1, 2020 cash balance is expected to be $14,000. B. All sales are on account. Credit sales are collected over a 3 month period -- 50 percent in the month of sale, 35 percent in the month following sale, and 15 percent in the second month following sale. Actual sales for Feb....
To prepare a master budget for April, May, and June of 2020, management gathers the following...
To prepare a master budget for April, May, and June of 2020, management gathers the following information: Sales for March total 19,500 units. Forecasted sales in units are as follows: April 19,500 ; May 17,100; June 21,300; July 19,500. Sales of 248,000 units are forecasted for the entire year. The product’s selling price is $30.00 per unit and the total product cost is $24.60 per unit. b. Company policy calls for a given month’s ending finished goods inventory to equal...
Budget Preparation Collins Company is preparing its master budget for April. Use the given estimates to...
Budget Preparation Collins Company is preparing its master budget for April. Use the given estimates to determine the amounts necessary for each of the following requirements. (Estimates may be related to more than one requirement.) a. What should total sales revenue be if territories A and B estimate sales of 10,000 and 12,000 units, respectively, and the unit selling price is $40? $Answer b. If the beginning finished goods inventory is an estimated 2,000 units and the desired ending inventory...
Herald company, a jacket selling company, prepares its master budget for the year 2020 on a...
Herald company, a jacket selling company, prepares its master budget for the year 2020 on a quarterly basis. The budget officer has gathered the following data: 1. Estimated Sales for each quarter is $230,000. All sales are on credit and all sales are collected in the following quarter. 2. Cost of goods sold for each quarter is $100,000. 3. Variable selling and administrative expenses for each jacket are 10% sales commission. 4. Fixed selling and administrative (S&A) expenses for the...
Our company prepared a Master Budget for planning purposes before the beginning of the year. That...
Our company prepared a Master Budget for planning purposes before the beginning of the year. That budget was for unit sales of 32,000 chairs at a budgeted price of $100 per chair. Actual sales for the period were 30,000 units totaling $2,910,000. T What was the total sales budget variance? How much of the sales budget variance is attributable to a difference in sales price (the Sales Price Variance)? How much of the sales budget variance is attributable to the...
A company paid the $1,350 premium on a three-year insurance policy on April 1, 2020. The...
A company paid the $1,350 premium on a three-year insurance policy on April 1, 2020. The policy gave protection beginning on that date. How many dollars of the premium will appear as an expense on the calendar year 2020 income statement assuming the accrual basis of accounting? Assuming the cash basis of accounting?
Master Budget Project Okay Company is preparing to build its master budget. The budget will detail...
Master Budget Project Okay Company is preparing to build its master budget. The budget will detail each quarter’s activity and the activity for the year in total. The master budget will be based on the following information: a. This will be the first year of operation for Okay Company. b. Budgeted unit sales by quarter for 2017 are projected as follows: First quarter 6,300, Second quarter 6,100, Third quarter 6,100 & Fourth quarter 6,450. First and second quarter 2018 budgeted...
Master Budget Project Okay Company is preparing to build its master budget. The budget will detail...
Master Budget Project Okay Company is preparing to build its master budget. The budget will detail each quarter’s activity and the activity for the year in total. The master budget will be based on the following information: This will be the first year of operation for Okay Company. Budgeted unit sales by quarter for 2017 are projected as follows: First quarter 6,300, Second quarter 6,100, Third quarter 6,100 & Fourth quarter 6,450. First and second quarter 2018 budgeted sales units...
ABC Company has the following sales budget for the last six months of 2020:
  Note: Not all sales are always collected. Remember Bad Debt Expense. So don’t worry if the problems do not add up to 100%.   1. ABC Company has the following sales budget for the last six months of 2020:                   July                             $9,000                                          August                      $8,000                                          September                 $11,000                   October                      $9,000                        Historically, the cash collection of sales has been as follows:                                     35% of sales collected in the month of sale,                                     45% of sales collected in the month...
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT