Question

Clipboard Font Styles Ideas X fx Part 2 D E F G H I J K L M L 1 Part 2 2 You should use this worksheet as your data field and only use cell references and formulas in the budgets. I have set up a template budget worksheet for you to develop the budgets 3 Your grade will be based on accuracy of your solution and correct usage of excel. The budget worksheet has formatted budgets for you to complete. 4 The beauty behind excel is that managers can perform what-if analysis just by changing the data, so you do not need to retype the budgets if you 5 have used cell references and fomulas throughout 7 The budget director for Bird House Unlimited, Inc., has gathered the following data for use in developing the budgeted income statement for January 2020. Estimated sales for January Bird House 34,000 units at $42 per unit Bird Feeder 25,000 units at $70 per unit Direct materials Estimated inventories at January 1(beginning) Wood 2600 feet Plastic 3200 pounds Estimated inventories at January 31stlending) Wood 3500 feet Plastic 2800 pounds Direct materials used in production: In the manufacture of a Bird House: Wood Plastic 0.80 feet per unit of product 0.50 pounds per unit of product In the manufacture of a Bird Feeder: Wood Plastic 1.20 feet per unit of product 0.75 pounds per unit of product Estimated cost of direct materials Wood Plastic $6.50 per feet $0.90 per pound Work in process-given in total of the 2 products Estimated inventories at January 1(beginning) Estimated inventories at January 31stiending) $27,000 $32,400 Finished Goods Estimated inventories at January 1(beginning) Bird House Bird Feeder 4900 units 2.500 units at at $25 per unit $35 per unit Estimated inventories at January 31st(ending) Bird House Bird Feeder 5300 units 2,100 units at $24 per unit $36 per unit Direct Labor Requirements: Bird House Fabrication Department Assembly Department 0.25 hour 0.3 hour $14 per hour $10 per hour Bird Feeder Fabrication Department Assembly Department 0.45 hour 0.35 hour at $14 per hour $10 per hour Estimated Manufacturing Overhead Costs for January Indirect Factory Wages Depreciation of Plant and Equipment Power and Light Insurance and property tax $550 000 165,000 42,000 15,400 Estimated operating expenses for January Sales salaries expense Advertising expense Office salaries expense Depreciation expense-office equipment Telephone expense-selling Telephone expense-administrative Travel expense-selling Office supplies expense Miscellaneous Administrative expense $600,000 148,600 214,800 4,900 5,200 1,700 39,200 3,500 5.000 Estimated other income and expense for January Interest Revenue Interest Expense $14.000 11,000 Estimated tax rate 3246

Answer 1

Sales Budget
January
	Birdhouse	Birdfeeder	Total
Unit Sales Volume	34000	25000	59000
Unit Selling Price	$                        42	$                     70
Total Revenue from sales	$          1,428,000	$       1,750,000	$ 3,178,000

Production Budget
January
	Birdhouse	Birdfeeder	Total
Expected Unit Sales	34000	25000	59000
Add : Desired Ending Finished Good Units	5300	2100
Total Required Units	39300	27100	66400
Less : Beginning Finished Good units	4900	2500
Required Production units	34400	24600	59000

Direct Materials Budget
January
	Wood		Plastic		Total
Required Units for production
Birdhouse	27520	=34400*0.8	17200	=34400*0.5	44720
Birdfeeder	29520	=24600*1.2	18450	=24600*0.75	47970
Add : Desired Ending inventory	3500		2800
Total Required Units	60540		38450		98990
Less : Beginning inventory	2600		3200
Total Units to be Purchased	57940		35250		93190
Unit Price	$                     6.50		$            0.90
Total costs for direct materials	$              376,610		$       31,725		$ 408,335

Direct Labor Budget
January
	Fabrication Department		Assembly Department		Total
Hours Required for Production
Birdhouse	8600	=34400*0.25	10320	=34400*0.3	18920
Birdfeeder	11070	=24600*0.45	8610	=24600*0.35	19680
Total hours	19670		18930		38600
Hourly Rate	$                        14		$                10
Total cost of direct labor	$              275,380		$     189,300		$ 464,680

Manufacturing Overhead Cost Budget
January
Indirect Factory Wages	$              550,000
Depreciation of plant and equipment	$              165,000
Power and light	$                42,000
Insurance and Property Tax	$                15,400
Total	$              772,400

Cost of Goods Sold Schedule
January
Direct Materials
Beginning Balance, Raw Material	$                  3,680		=2600*0.8+3200*0.5
Add : Raw Material Purchased	$              408,335
Raw Material Available	$              412,015
Less : Ending Balance, Raw Material	$                  4,200		=3500*0.8+2800*0.5
Raw Material Consumed		$           407,815
Direct labor		$           464,680
Manufacturing Overhead		$           772,400
Total Manufacturing Costs		$       1,644,895
Add : Beginning Balance, Work in Process		$             27,000
Subtotal		$       1,671,895
Less : Ending Balance, Work in Process		$             32,400
Cost of Goods Manufactured		$       1,639,495
Add : Beginning Balance, Finished Goods		$           210,000	=4900*25+2500*35
Cost of Goods Available for Sale		$       1,849,495
Less : Ending Balance, Finished Goods		$           202,800	=5300*24+2100*36
Cost of Goods Sold		$       1,646,695

Selling and Administrative Expense Budget
January
Selling Expenses
Sales Salaries Expense	$              600,000
Advertising Expense	$              148,600
Telephone Expense - Selling	$                  5,200
Travel Expense - Selling	$                39,200
Total Selling Expenses	$              793,000
Administrative expenses :
Office Salaries Expense	$              214,800
Depreciation Expense	$                  4,900
Telephone Expense - Administrative	$                  1,700
Office Supplies Expense	$                  3,500
Miscellaneous administrative exp	$                  5,000
Total Administrative expenses	$              229,900
Total Operating Expenses	$          1,022,900