Answer Questions 5, 6, 7 and 8 using Excel Only.

Portfolio Question Four - Budgeting Spring Manufacturing Company makes two components identified as C12 and D57. Selected budgetary data for 2019 follow: Data Finished Components C12 D57 10 pounds 2 pounds 2 hours 8 pounds 4 pounds 1 pound 3 hours Requirements for each finished component: RM1 RM2 RM3 Direct labor Product information: Sales price Sales units Estimated beginning inventory (units) Desired ending inventory (units) $150 12,000 400 300 $220 9,000 150 200 Cost per pound Estimated beginning inventory in pounds Desired ending inventory in pounds Direct Materials Information RM1RM2 RM3 $2.00 $2.50 $0.50 3,000 1,500 1,000 4,000 1,000 1,500 The firm expects the average wage rate to be $25 per hour in 2019. Spring Manufacturing uses DLHS to apply overhead. Each year the firm determines the overhead application rate for the year based on the budgeted ouput for the year The company maintains negligible WIP inventory and expects the cost per unit for both beginning and ending finished products inventories to be identical Factory Overhead Information Indirect materials-variable Miscellaneous supplies and tools-variable Indirect labor-variable Supervision-fixed Payroll taxes and fringe benefits-variable Maintenance costs-fixed Maintenance costs-variable Depreciation-fixed Heat, light, and power-fixed Heat, light, and power-variable Total $10,000 $5,000 $40,000 $120,000 $250,000 $20,000 $10,080 $71,330 $43,420 $11,000 $580.830 Selling and Administrative Expense Information Advertising Sales salaries Travel and entertainment Depreciation-warehouse Office salaries Executive salaries Supplies Depreciation office Total $60,000 $200,000 $60,000 $5,000 $60,000 $250,000 $4,000 $6,000 $645,000 Income Tax Rate 40% Required Prepare the following schedules or statements for 2019: 1. Sales budget 2. Production budget 3. Direct materials purchases budget (units and dollars) 4. Direct labor budget 5. Factory overhead budget 6. Cost of goods sold and ending inventory budgets 7. Selling and administrative expense budget 8. Budgeted Income Statement, ending with After-tax Operating Income