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 Cost per material Total direct materials to be purchased Bird House Unlimited, Inc. Direct Labor Cost Budget For the Month Ending January 31, 2020 Fabrication Department Assembly Department Total Units to be produced x hours required per unit Hours required for production: Bird House Bird Feeder Total Hourly rate Total direct labor cost Bird House Unlimited, Inc. Manufacturing Overhead Cost Budget For the Month Ending January 31, 2020 Indirect factory wages Depreciation of plant and equipment Power and light Insurance and property tax Total 99 6. Hint: Do not forget you have 2 products and 2 direct materials, so you will need to combine this information below. 102 103 Bird House Unlimited, Inc. Cost of Goods Sold Budget For the Month Ending January 31, 2020 107 108 109 110 111 112 114 Direct materials: Direct materials inventory, January 1, 2020 Direct materials purchases Cost of direct materials available for use Less: Direct materials inventory, January 31, 2020 Cost of direct materials placed in production Direct labor Manufacturing overhead Total manufacturing costs Work in process inventory. January 1, 2020 Subtotal Less work in process inventory, January 31, 2020 Cost of goods manufactured Finished goods inventory, January 1, 2020 Cost of finished goods available for sale Less finished goods inventory, January 31, 2020 Cost of goods sold EFF 119 120 121 122 123 124 125 7. 126 127 128 129 Bird House Unlimited, Inc. Selling and Administrative Expenses Budget For the Month Ending January 31, 2020 Selling expenses: Sales salaries expense Advertising expense Telephone expense - selling Travel expense - selling Total selling expenses Administrative expenses: Office salaries expense Depreciation expense - office equipment Telephone expense - administrative Office supplies expense Miscellaneous administrative expense Total administrative expenses Total operating expenses 136 139 140 141 142 144 Droiect 1Da, Dudat Tomato