Hello! I only need help completing #8. Which is the Budgeted income statement. Thank you in advance.
this is what I have
Use Excel to prepare the following budgets for March 2020: 1. Sales budget 2. Production budget 3. Direct materials purchases budget 4. Direct labor cost budget 5. Factory overhead cost budget 6. Costs of goods sold budget. Work in process at the beginning of March is estimated to be $11,000, and work in process at the ending of March is desired to be $12,500. 7. Selling and administrative expenses budget 8. Budgeted income statement The excel (or google sheet) document should have a different tab for each budget (8 total). Do not use excel as a typewriter. You must use formulas and links between spreadsheet tabs. Formatting should include appropriate headings. See example budgets in the text for format. The videos in the Chapter 21 module, 21.3 Chapter 21 - Learning Objective #4 demonstrate how to link and complete formulas. Please ask questions if you are not sure what I mean about linking and formulas. The Selling/Admin Expenses Budget and the Budgeted Income Statement were not covered in the Chapter 21 module. You should find Exhibit 15 and Exhibit 16 helpful (Page 1068 and 1069 in the paper book and section 21-4g and 21-4h in the eBook). Information for Budgeted Income Statement and Supporting Budgets: The budget director of Athletic Supplies Inc., with the assistance of the controller, treasurer, production manager, and sales manager, has gathered the following data for use in developing the budgeted income statement for March: a. Estimated sales for March: Batting helmet 1,100 units at $42 per unit Football helmet 6,400 units at $146 per unit b. Estimated inventories at March 1: Direct materials: Plastic 55 lbs. Foam lining 65 lbs. Finished products: Batting helmet 75 units at $28 per unit Football helmet 220 units at $75 per unit c. Desired Inventories at March 31: Direct materials: Plastic 70 lbs. Foam lining 74 lbs. Finished products: Batting helmet 50 units at $27 per unit Football helmet 225 units at $72 per unit d. Direct materials used in production: In manufacture of batting helmet: Plastic 1.5 lbs. per unit of product Foam lining 0.75 lb. per unit of product In manufacture of football helmet: Plastic 3.5 lbs. per unit of product Foam lining 1.75 lbs. per unit of product e. Anticipated cost of purchases and beginning and ending inventory of direct materials: Plastic $5.4 per ib. Foam lining $3.6 per lb. f. Direct labor requirements: Batting helmet: Molding Department 0.3 hr. at $21 per hr. Assembly Department 0.6 hr. at $15 per hr. Football helmet: Molding Department 0.8 hr. at $21 per hr. Assembly Department 1.6 hrs. at $15 per hr. g. $75,000 14,000 3,000 3,700 Estimated factory overhead costs for March: Indirect factory wages Depreciation of plant and equipment Power and light Insurance and property tax h. Estimated operating expenses for March: 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 Estimated other income and expense for March: Interest revenue Interest expense 1. Estimated tax rate: 30% $186,200 88,200 33,500 5,000 3,900 1,500 10,000 1,500 2,200 i. 800 875 Note: above information was adapted from Financial and Managerial Accounting (12th edition) Warren, Reeve and Duchac Athletic Supplies Inc., Sales Budget For the month ending March 31, 2020 Product Unit Sales Volume Unit Selling Price Batting Helmet 1,100 $42 Football Helmet 6,400 146 Total 7,500 Total Sales $46,200 934,400 $980,600 Athletic Supplies Inc. Producton Budget For the Month Ending March 31, 2020 Units Batting helmet Football helmet Estimated units to be sold (linked from Sales budget) 1,100 6,400 Desired ending inventory, March 31, 2020 50 Total 1,150 6,625 Less estimated beginning inventory, March 1, 2020 -75 -220 Total units to be produced 1,075 6,405 225 Units Plastic 55 70 Units Foam Lining 65 74 Estimated Inventory, March 1, 2020 Desired Inventory, March 31, 2020 Price per pound Total 0.6 1.6 Athletic Supplies Inc. Direct Labor Cost Budget For the Month Ending March 31, 2020 Molding Assembly Hours required for production: Batting Helmet $322.50 $645.00 Football Helmet $5,124.00 $10.248.00 Total $5,446.50 $10,893.00 Hourly Rate 21 15 Total Direct Labor Cost S114 376.50 $163.395.00 Direct Labor Data Table Molding Assembly Hours required per unit: Batting Helmet 0.3 Football Helmet 0.8 Labor Hourly Rate Molding $21 Assembly $15 Units to be produced (from Production Budget): Batting Helmet 1,075 Football Helmet 8,405 $277.771.50 Athletic Supplies Inc., Factory Overhead Cost Budget For the month ending March 31, 2020 Indirect Factory Wages Power and light Depreciation of plant and equipment Total Factory Overhead Cost $75,000 $3,000 $14,000 92,000 $ 18,600.00 Athletic Supplies Inc. Cost of Goods Sold Budget For the Month Ending March 31, 2020 Finished goods inventory, March 1 Work in process inventory, March 1 $ 11,000.00 Direct materials: Direct materials inventory, March 1 $ 531.00 Direct materials purchases Cost of direct materials available for use Direct materials inventory, March 31 $ (644.40) Cost of direct materials placed in production Direct labor $ 277.771.50 Factory overhead Total manufacturing costs Total work in process during period Work in process inventory, March 31 $ (12,500.00) Cost of goods manufactured Cost of finished goods available for sale Finished goods inventory, March 31 Cost of goods sold $ (17,550.00)
