Master Budget

Practice As You Learn

Cost Accounting

Practice Problem 

Use the following information and prepare the comprehensive master budget. Explanations will be provided for each budget.


                                        January     February     March      April      May
Sales in units:                   7,000          8,000       10,000     9,000   8,000
Sales price per unit: $12

Cost sheet to make 1 unit

4 ounces at $0.75 per oz.
.2 labor hours at $10 per hour
$1.20 variable M O/H cost per unit
$1.45 fixed M O/H cost per unit

Total annual fixed manufacturing overhead   $175,000
Total annual fixed selling expenses   $ 85,000
Total annual fixed administrative expenses   $220,000

Sales are collected 20% in the month of the sale, and 80% the month following the sale

Accounts Payable for raw materials is paid 25% in the current month and 75% in the next month after purchase

Finished Goods ending inventory is planned to be 80% of next month’s sales
Raw materials inventory is planned to be 100% of next month’s production requirements

Annual Depreciation expense: $29,000 fixed manufacturing overhead

End of December balance sheet:
Cash $123,075 A/R: $85,000 A/P: $39,042

End of December inventory:
   6,000 finished goods units
  26,000 ounces of raw material

Prepare the comprehensive master budget for the company for January, February and March. Do all parts of the budget noted in Things You Must Know.

Answer

 

   
January
February
March
   
  Sales in units
7,000
8,000
10,000
  x Price per unit
$12
$12
$12
  = Total sales dollars
$84,000
$96,000
$120,000

2) Do the cash collections budget:
Once you have estimated total sales dollars, you can determine when the cash from these credit sales will be received by the company. Cash from sales is expected to be collected: 20% in the month of the sale, and 80% in the month following the sale

Set up the schedule as follows:

 
Amounts Collected In:
 
 
Dollars owed
January
February
March
 
 
 
 
 
 
 
December A/R: 
85,000
all
 
 
 
January Sales
84,000
20%
80%
 
 
February Sales
96,000
 
20%
80%
 
March Sales
120,000
 
 
20%
80%
 
 
 
 
 
 in April

December A/R will be collected the following month

Fill in when the percentage that will be collected for each month’s sales during each month. For example: February sales are all collected in February and March, 20% during the current month, February and 80% during the following month, March.

Now that you know what percent is coming in, take the amount on the left in the row times the % on the same row to get the dollars that will be collected during that month.

 

Amounts Collected In:  
 
Dollars owed  
January
February
March
 
 
 
 
 
December A/R:
85,000
85,000
 
 
January Sales
84,000
16,800
67,200
 
February Sales
96,000
 
19,200
76,800
March Sales
120,000
 ______
_____ 
24,000
Total dollars collected from customers:
$101,800
$86,400
$100,800

3) Prepare the production budget:
in units: Use the sales in units to determine how many units should be produced to support those sales and still leave you with the desired amount of finished goods inventory at the end of each month. What you already have on hand, beginning inventory, you do not have to produce.

Budgeted Sales in Units
+ Desired Ending Inventory in Units
– Beginning Inventory in Units
= Required Production in Units

Begin by dropping in the sales in units from the sales budget:

Then add desired ending inventory. Take the next months units sales x 80%.

Then add beginning inventory, which will always be last months ending inventory.
January beginning is December ending. March beginning is February ending.

Add each column to get the units that must be produced during the month to meet sales targets and ending inventory goals.

   
January
February
March
   
  Budgeted Sales in Units
7,000
8,000
10,000
  + Desired Ending Inventory in Units
6,400
8,000
7,200
  – Beginning Inventory in Units
(6,000)
(6,400)
(8,000)
  = Required Production in Units
7,400
9,600
9,200

4) Direct Materials Budget:
The first product cost to budget is direct materials. First you must determine how much to purchase and then you determine how much it will cost.

   
January
February
March
   
  Required Production in Units
7,400
9,600
9,200
  X Quantity Required Per Unit (yards)
4
4
4
  = Units of Raw Mat. for Production
29,600
38,400
36,800
  + Desired Ending Inventory of Raw Mat.
38,400
36,800
32,800
  – Beginning Inventory of Raw Materials
(26,000)
(38,400)
(36,800)
  = Raw Materials to be Purchased – units
42,000
36,800
32,800

Drop in units to produce from the production budget above.

Look on the cost sheet, or given information for the quantity that it takes to make one unit and put it on the next line.

Multiply units x required for one to get the total quantity (in ounces) required for production for the period.

Management desires to have raw materials on hand equal to 100% of the next months production requirements, (same thing as units of raw material required for production). 100% means times 1.0, so do the math and drop in the ending inventory requirement.

Calculate April so you can determine March ending inventory.

                                                                                        April                   May
                  Sales                                                           9,000                8,000
                  + FG ending inventory                               6,400   (80% of May)
                  - FG beginning inventory                          (7,200)   March ending
                  = Units to produce                                      8,200
                  x RM quantity required per unit                      4    
                  = RM required for production                   32,800

March ending inventory is 100% of RM required for production for April, or 32,800

Beginning inventory is last month’s ending inventory for raw materials. For January, it is December’s ending inventory.

5) Materials purchased budget:
The next logical step is to determine how much the materials will cost the company each month.

   
January
February
March
   
  Raw Materials to be Purchased – units
42,000
36,800
32,800
  x Cost per unit (in this case a unit = oz.)
$0.75
$0.75
$0.75
  Total cost of raw materials purchased
$31,500
$27,600
$24,600

6) Cash disbursements budget: The next logical step is to determine when the purchase dollars will be paid. The company pays 25% in the current month and 75% in the next month after purchase.

Set up the schedule as follows:

 
Amounts Paid In:
 
 
Dollars owed
January
February
March
 
 
 
 
 
December A/P: 
39,042
all
 
 
January purchases
31,500
25%
75%
 
February purchases
27,600
 
25%
75%
March purchases
24,600
 
 
25%

For the December A/P, the part that is still owed will be paid all the following month

Fill in the % that will be paid when for each month’s purchases for the month it will be paid. For example: February purchases are all paid in February and March; 25% during the current month, February and 75% during the following month, March.

Now that you know what percent is paid when, take the amount on the left in the row times the % on the same row to get the dollars that will be paid during that month.

 

Amounts Paid In:  
 
 
Dollars owed  
January
February
March
 
 
 
 
 
 
 
December A/P::
39,042
39,042
 
 
 
January purchases
31,500
7,875
23,625
 
 
February purchases
27,600
 
6,900
20,700
 
March purchases
24,600
 ______
_____ 
6,150

75%

April

Total paid:

$46,917
$30,525
$26,850
 

7) Direct Labor Budget:
Now that you have finished direct materials, the next product cost, direct labor costs, must be budgeted:

Use this format:

   
January
February
March
   
  Required Production in Units
7,400
9,600
9,200
  x Direct Labor Hours to Make One Unit
.2
.2
.2
  = Total Direct Labor Hours Required
1,480
1,920
1,840
  x Direct Labor Cost Per Hour
$10
$10
$10
  = Total Direct Labor Cost
$14,800
$19,200
$18,400

Go to the production budget and get required units to produce.

Go to the cost sheet information, or given, and put in the direct labor hours to make one unit. Also get the cost per hour from the same place and drop this in.

Do the math and it will give you the cost of direct labor for each period. Direct labor is almost always paid in the month incurred.

8) Manufacturing Overhead budget: Now that you have finished direct labor, the last product cost, manufacturing overhead must be budgeted. Use this format.

   
January
February
March
   
  Required Production in Units
7,400
9,600
9,200
  x Variable Overhead Rate Per Unit 
$1,20
$1,20
$1.20
  = Variable Manufacturing Overhead
$8,880
$11,520
$11,040
  + Fixed Manufacturing Overhead (total)
$14,583
$14,583
$14,583
  = Total Manufacturing Overhead
$23,463
$26,103
$25,623
  – Depreciation (not paid)
($2,417)
($2,417)
($2,417)
  = Cash Paid for Manufacturing O/H
$21,046
$23,686
$23,206

To get fixed manufacturing overhead expense for each month, annual costs must be
divided by 12 months — $175,000 / 12 months = $14,583 (Ignore fixed cost per unit)
To get manufacturing depreciation per month, divide annual costs by 12 months
—- $29,000 / 12 months = $2,417 per month

If variable manufacturing variable costs per is stated in terms of an activity other than units, such as direct labor hours or machine hours, you should take required units x the quantity of activity required per unit and then take the total activity required times the variable rate per activity. Then continue with adding fixed manufacturing overhead.

9) Inventory budget of FG and RM costs:
Now that you have budgeted all product costs, you can budget the cost of finished goods inventory and raw materials inventory that will be in the warehouse at the end of each period and will be reported on the budgeted balance sheet.

   First – determine the cost of producing one unit
                                                                            Per unit cost
                  Direct materials                                     $3.00    (4 oz. x $0.75)
                  Direct labor                                            $2.00    (.2 hrs x $10)
                  Manufacturing overhead                       $2.65          ($1.20 + $1.45)
                           Total cost of one product             $7.65       Variable + Fixed
   
January
February
March
   
Ending FG Inventory Units (production budget)
6,400
8,000
7,200
x Finished Goods Cost Per Unit
$7.65
$7.65
$7.65
= Ending Finished Goods Inventory in Dollars
$48,960
$61,200
$55,080

 

   
January
February
March
   
Ending raw materials (materials budget)
38,400
36,800
32,800
x RM cost per ounce
$0.75
$0.75
$0.75
= Ending raw materials dollars
$28,800
$27,600
$24,600

10) Selling and Administrative expense budget:
The last cost to budget is selling 
and administrative expenses.
This budget is usually developed by department managers and then all department expenses are added together to get total 
annual expenses.

In our example we only have annual information so we will divide the total by 12 months to get a monthly expense. We will assume the expense will be paid in the month incurred since we are not told otherwise. If the expense is not paid in the same month, you need to know when it is paid and set up a schedule similar to the cash disbursements schedule for raw materials.

                  Annual selling expense                            $ 85,000
                  Annual administrative expense              $220,000
                           Total                                                 $305,000 / 12 months = $25,417
                                                                                                                             per month

11) Cash Budget: Now that you have all the pieces, you can prepare the cash budget that projects the cash available at the end of the month or the cash shortage that must be covered by borrowing. Use the standard format and drop in the numbers from all the budgets done above.

 
January
February
March
 
 
 
 
 
 
 
 
Cash Balance, Beginning 
$123,075
$116,695
$104,267
+ Receipts for the Period (Cash Collections)
101,800
86,400
100,800
= Total Cash Available
$224,875
$203,095
$205,067
 
 
 
 
– Disbursements for Material (Cash Disb.)
46,917
30,525
26,850
– Disbursements for Labor
14,800
19,200
18,400
– Disbursements for Manufacturing Overhead
21,046
23,686
23,206
– Disbursements for Selling and Administrative
25,417
25,417
25,417
= Cash Available
$116,695
$104,267
$111,194
 
 
 
 
– Expenditures for Property/Plant/Equipment
0
0
0
– Dividends Paid to Shareholders
0
0
0
= Excess(Deficiency) of cash
$116,695
$104,267
$111,194
+ Borrowings 
0
0
0
– Repayment of Borrowings
0
0
0
– Interest Expense Paid
0
0
0
= Cash Balance, Ending
$116,695
$104,267
$111,194

Our example did not result in a negative cash balance. When a negative cash balance occurs, the company borrows enough to get the minimum desired balance in minimum increments based on terms given in the borrowing arrangement. Interest expense for the month is calculated by multiplying the total amount borrowed times the annual interest rate divided by 12 months. The interest goes on the schedule in the month it is paid, sometimes it is the current month other times it is the following month.

We also had no planned expenditures or dividends during the first three months. If there are any planned, they need to be put in the above budget and subtracted from cash.

12) Balance Sheet and Income Statement: You can use the information calculated above to prepare a budgeted income statement and balance sheet. We will do the budgeted financial statements for the first Quarter ending March 31st.

   
1st Quarter
 
  Sales
$300,000
(25,000 x $12)
  – Cost of Goods Sold
($191,250)
(25,000 x $7.95)
  = Gross Profit
$108,750
 
  – Selling & Admin
($76,250)
(25,417 x 3 months)
  = Operating Income
$32,500
 
  – Tax Expense (30%)
$9,750
 
  = Net Income
$22,750
 

We will not do a full balance sheet because this example problem does not give the full balance sheet for the end of the prior year. We will identify the amounts that would be reported on the projected balance sheet from our budget work above.

                                                                          End of March
         Accounts Receivable                            $96,000 see cash collections schedule
         Inventory – Raw Material                      $24,600 see inventory budget
         Inventory – Finished Goods                 $55,080 see inventory budget
         Accumulated Depreciation                  Prior year balance + $7,251 (3 x $2,417)
         Accounts Payable                                 $18,450 see cash disbursements schedule
         Tax Payable                                           $ 9,750 none of the tax expense was paid
         Retained Earnings                                Prior year balance + income for the first 
                                                                            3 months – $22,750 from above net income