Assignment:
(Using the Below budget sheets)
Prepare an executive summary to the company owner of the Bottled Water Company, Mac Brown.In this summary;
1. State your results of your analysis of this proposed budget for the new product and
2. State how launching the new product would or wouldn't affect net income.
The Bottled Water Company Sales Budget For the Year Ending December 31
1. Calculate the Bottled Water Company's Income for the new product in the coming year by completing the operating budgets and budgeted income statement that follows.
The Bottled Water Company Productivity Budget For the Year Ending December 31
| |
Quarter |
| 1 |
2 |
3 |
4 |
Year |
| Sales in Units |
40,000 |
30,000 |
50,000 |
55,000 |
175,000 |
| Plus Desired Units of Ending Finished Goods Inventory* |
|
|
|
|
|
| 3,000 |
5,000.0 |
5,500.0 |
6,000 |
6,000 |
| Desired Total Units |
43,000 |
35,000.0 |
55,500.0 |
61,000.0 |
181,000.0 |
| Less Desired Units of Beginning Finished goods Inventory+ |
|
|
|
|
|
| 4,000 |
3,000 |
5,000.0 |
5,500.0 |
4,000 |
| Total Production Units |
39,000 |
32,000.0 |
50,500.0 |
55,500.0 |
177,000.0 |
Desired Units of Ending finished goods Inverntory = 10% of next quarter's budgeted sales.+ Desired Units of beginning finished goods inventory = 10% of current quarter's budgeted sales.
The Bottled Water Company Direct Materials Purchase Budget For the Year Ending December 31
| |
Quarter |
| 1 |
2 |
3 |
4 |
Year |
| Total Productin Units |
39,000 |
32,000 |
50,500 |
55,500 |
177,000 |
| X 20 Ounces Per Unit |
$ 20 |
$ 20 |
$ 20 |
$ 20 |
$ 20 |
| Total Production Needs in Ounces |
780,000 |
640,000 |
1,010,000 |
1,110,000 |
3,540,000 |
| Plus Desired Ounces of Ending direct Materials Inventory* |
128,000 |
202,000 |
222,000 |
240,000 |
240,000 |
| |
908,000 |
842,000 |
1,232,000 |
1,350,000 |
3,780,000 |
| Less Desired Ounces of Beginning Direct Materials Inventory+ |
156,000 |
128,000 |
202,000 |
222,000 |
156,000 |
| Total Ounces of Direct Materials to be Purchased |
752,000 |
714,000 |
1,030,000 |
1,128,000 |
3,624,000 |
| X Cost per Ounce |
$ 0.01 |
$ 0.01 |
$ 0.01 |
$ 0.01 |
$ 0.01 |
| Total Cost of Direct Materials Purchases |
$ 752,000 |
$ 7,140.00 |
$ 10,300.00 |
$ 11,280.00 |
$ 36,240.00 |
Desired Ounces of Ending Direct Materials Inventory = 20% OF NEXT QUARTER'S BUDGETED Production Needs in Ounces. + Desired Ounces of Beginning Direct Materials Inventory = 20% of Current Quarter's Budgeted Production Needs in Ounces
The Bottled Water Company Direct Labor Budget For the Year Ending December 31
| |
Quarter |
| 1 |
2 |
3 |
4 |
Year |
| Total Productin Units |
39,000 |
32,000 |
50,500 |
55,500 |
177,000 |
| X Direct Labor Hours Per Unit |
0.001 |
0.001 |
0.001 |
0.001 |
0.001 |
| Total Direct Labor Hours |
39 |
31 |
51 |
56 |
176 |
| X Direct Labor Cost Per Hour |
$ 8 |
$ 8 |
$ 8 |
$ 8 |
$ 8 |
| Total Direct Labor Cost |
312 |
248 |
404 |
444 |
1,408 |
The Bottled Water Company Overhead Budget For the Year Ending December 31
| |
Quarter |
| 1 |
2 |
3 |
4 |
Year |
| Variable Overhead Costs |
|
|
|
|
|
| Factory Supplies ($0.1) |
$ 390.00 |
$ 320.00 |
$ 505.00 |
$ 555.00 |
$ 1,770.00 |
| Employee Benefits ($0.5) |
$ 1,950.00 |
$ 1,600.00 |
$ 2,525.00 |
$ 2,775.00 |
$ 8,850.00 |
| Inspection ($0.1) |
$ 390.00 |
$ 320.00 |
$ 505.00 |
$ 555.00 |
$ 1,770.00 |
| Maintenance & Repair ($0.02) |
$ 780.00 |
$ 640.00 |
$ 1,010.00 |
$ 1,110.00 |
$ 3,540.00 |
| Utilities ($0.01) |
$ 390.00 |
$ 320.00 |
$ 505.00 |
$ 555.00 |
$ 1,770.00 |
| Total Variable Overhead Costs |
$ 3,900.00 |
$ 3,200.00 |
$ 5,050.00 |
$ 5,550.00 |
$ 17,700.00 |
| Total Fixed Overhead Costs |
$ 1,500.00 |
$ 1,280.00 |
$ 2,020.00 |
$ 2,220.00 |
$ 7,080.00 |
| Total Overhead Costs |
$ 5,400.00 |
$ 4,480.00 |
$ 7,070.00 |
$ 7,770.00 |
$ 24,780.00 |
The Bottled Water Company Selling and Administrative Expense Budget For the Year Ending December 31
| |
Quarter |
| 1 |
2 |
3 |
4 |
Year |
| Variable Selling and Administrative Expenses |
(40,000Units) |
(30,000Units) |
(50,000Units) |
(55,000Units) |
(175,000Units) |
| Delivery Expenses ($0.01) |
$ 400 |
$ 300 |
$ 500 |
$ 550 |
$ 1,750 |
| Sales commissions ($0.02) |
$ 800 |
$ 600 |
$ 1,000 |
$ 1,100 |
$ 3,500 |
| Accounting ($0.01) |
$ 400 |
$ 300 |
$ 500 |
$ 550 |
$ 1,750 |
| Other Administrative Expenses ($0.01) |
$ 400 |
$ 300 |
$ 500 |
$ 550 |
$ 1,750 |
| Total Variable Selling and Administrative Expenses |
$ 2,000 |
$ 1,500 |
$ 2,500 |
$ 2,750 |
$ 8,750 |
| Total Fixed Selling and Administrative Expenses |
$ 5,000 |
$ 3,750 |
$ 6,250 |
$ 6,875 |
$ 21,875 |
| Total Selling and Administrative Expenses |
$ 7,000 |
$ 5,250 |
$ 8,750 |
$ 9,625 |
$ 30,625 |
The Bottled Water Company Costs of Goods Manufactured Budget For the Year Ending December 31
| The Bottled Water Company Costs of Goods Manufactured Budget For the Year Ending December 31 |
| Direct Materials Used |
|
|
| Direct Materials Inventory, Beginning |
$ 1,560 |
| Purchases |
$ 36,240 |
| Cost of Direct Materials Available for use. |
$ 37,800 |
| Less Direct Materials Available for use. Ending |
|
$ (2,400) |
| Cost of Direct Materials used. |
$ 35,400 |
| Direct Labor Costs |
$ 1,408 |
| Overhead Costs |
$ 24,780 |
| Total Manufacturing Costs |
$ 61,588 |
| Work in Process Inventory, Beginning* |
$ - |
| Cost of Goods Manufactured |
$ 61,588 |
| Manufactured Cost Per Unit = Cost of Goods |
$ 0.35 |
| Manufactured / Units Produced |
The Bottled Water Company Budgeted Income Statement Budget For the Year Ending December 31
| Sales |
175,000 |
|
| Cost of Goods Sold |
|
|
| Finished Goods Inventory, Beginning |
$ 4,000 |
is total profit /units |
| Cost of Goods Manufactured |
$ 61,588 |
|
| Cost of Finished Goods Available for Sale |
$ 65,588 |
|
| Less Finished Goods Inventory, Ending |
$ (6,000) |
|
| Cost of Goods Sold |
|
$ 59,588 |
| Gross Margin |
|
$ 115,412.00 |
| Selling and Administrative Expenses |
|
$ 30,625 |
| Income From Operations |
|
$ 84,787.00 |
| Income Taxes Expense (30%)* |
|
$ (25,436.10) |
| Net Income |
|
$ 59,350.90 |
The Figure in Parenthese is the Company's Income Tax Rate