Comprehensive Profit Plan Spring Manufacturing Company makes two components identified as C12 and D57. Selected budgetary data for 2016 followw Finished Components D57 C12 10 pounds 2 pounds Requirements for each finished component RM 1 RM 2 RM 3 Direct labor 8 pounds 4 pounds 1 pound 3 hours 2 hours Product information: S220 9,000 150 200 $150 Sales price Sales (units) Estimated beginning inventory (units) Desired ending inventory (units) 12,000 400 300 Direct Materials Information RM1 2.00 3,000 4,000 RM3 0.50 1,000 1,500 RM2 $ 2.50 1,500 1,000 Cost per pound Estimated beginning inventory in pounds Desired ending inventory in pounds The firm expects the average wage rate to be $25 per hour in 2016. Spring Manufacturing uses direct labor hours to apply overhead. Each year the firm determines the overhead application rate for the year based on the budgeted output for the year. The firm maintains negligible work- in-process 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 10,000 5,000 40,000 120,000 250,000 20,000 10,080 71,330 43,420 11,000 $580,830 Total Advertising Sales salaries Travel and entertainment Depreciation-warehouse Office salaries Executive salaries Supplies Depreciation office Selling and Administrative Expense Information 60,000 200,000 60,000 5,000 60,000 250,000 4,000 6,000 $645,000 Total The effective income tax rate for the company is 40% Required Prepare an Excel spreadsheet that contains the following schedules or statements for 2016 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 finished goods inventory budgets 7. Selling and administrative expense budget 8. Budgeted income statement Comprehensive Profit Plan (Kaizen Budgeting) (Use information in Problem 10-50 for Spring Manufacturing Company.) Spring Manufacturing Company has had a continuous improvement (kaizen) program for the last two years. According to the kaizen program, the firm is expected to manufacture C12 and D57 with the following specifications: C12 9 pounds 1.8 pounds Cost Element D57 Raw material 1 7 pounds 3.6 pounds 0.8 pounds 2 hours Raw material 2 Raw material 3 Direct labor 1.5 hours The company specifies that the variable factory overhead is to decrease by 10% while the fixed factory overhead is to decrease by 5%, except for depreciation expenses. The company does not expect the price of the raw materials to change. However, the hourly wage rate is likely to be $30 Required 1. What is the budgeted after-tax operating income if the company can attain the expected operation level as prescribed by its kaizen program? 2. What are the benefits of Spring Manufacturing Company adopting a continuous-improvement program? What are the limitations