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

Bird House Unlimited, Inc.
Budgeted Income Statement
For the Month Ending January 31, 2020
Sales Revenue		3178000
Less: Cost of Goods Sold		1642375
Gross Margin		1535625
Operating Expenses:
Selling Expenses	793000
Administrative Expenses	229900
Total Operating Expenses		1022900
Income from Operations		512725
Other Income:
Interest Revenue	14000
Other Expenses:
Interest Expense	11000
Income before Income Tax		515725
Income Tax Expense	32%	165608
Net Income		350117

Working:-

> Sales: 3178000.

Sales	Units	Rate	Amount
Bird House	34000	42	1428000
Bird Feeder	25000	70	1750000

> Cost of Goods Sold: 1642375

Note: Since no basis of allocation has been provided, Manufacturing Overheads have been taken completely in the Cost of Goods Sold. Actual units produced during the period would be Bird House 34400 units and Bird Feeder 24600 units, given the opening and closing stock of finished goods. But for the purpose of Cost of Goods Sold, it is assumed that all the units sold have been produced in the given period.

Material per unit			Rate	Cost per unit
Bird House
Wood	0.8	feet	6.5	5.2
Plastic	0.5	pound	0.9	0.45
Bird Feeder
Wood	1.2	feet	6.5	7.8
Plastic	0.75	pound	0.9	0.675
Direct Labor per unit			Rate
Bird House
Fabrication	0.25	hrs	14	3.5
Assembly	0.3	hrs	10	3
Bird Feeder
Fabrication	0.45	hrs	14	6.3
Assembly	0.35	hrs	10	3.5

Direct Expenses	Units	Rate	Amount
Bird House Wood	34000	5.2	176800
Bird House Plastic	34000	0.45	15300
Bird Feeder Wood	25000	7.8	195000
Bird Feeder Plastic	25000	0.675	16875
Bird House Fabrication	34000	3.5	119000
Bird House Assembly	34000	3	102000
Bird Feeder Fabrication	25000	6.3	157500
Bird Feeder Assembly	25000	3.5	87500
			869975

Manufacturing Overheads
Indirect Wages			550000
Depreciation			165000
Power			42000
Insurance & Tax			15400
			772400

> Selling Expenses: 793000

Sales Salary			600000
Advertisement			148600
Telephone Sales			5200
Travel Sales			39200
			793000

> Administrative Expenses: 229900

Office Salary			214800
Depreciation Office			4900
Telephone Office			1700
Office Supplies			3500
Misc Admin			5000
			229900