Part 2 In 20x3, Spring Company initiated a quality improvement program. At the end of the vear December 31st, the president noted that the defects per unit of product had dropped significantly compared to the pror year. The president also noted the good relationships with suppliers had improved and defective materials had declined The new quality training program was also accepted by employees The president also highlighted the impact of the quality improvements on profitability to help assess the dollar impact of the quality improvements, the actual sales and the actual quality costs for 20x2 and 20x3 are as follows All prevention costs are fixed Assume all other quality costs variable 20x2 20X3 Sales $8,000,000 $10,000,000 Return Allowances 120.000 140,000 Design reviews 2.000 20,000 Packaging inspection 320,000 300 000 Product acceptance 40,000 28.000 Quality circles 4,000 40,000 Quality improvement projects 2,000 100,000 Retesting 200.000 160.000 Customer Complaints 16,000 14,000 Rework 360,000 320,000 Scrap 280,000 240,000 Warranty 400.000 440,000 Yield losses 160,000 100,000 Required: You must use cell referencing to earn credit 1 Prepare a quality cost report for 20X2, classifying costs by category and expressing each category as a percentage of sales. Please review Cornerstone 141 2 Prepare a pie chart showing each quality cost category's contribution to total quality costs for 20X2 in percentage terms. Make sure you provide a legend in your chart. Please review exhibit 14.4 3 Prepare a quality cost report for 20X3, classifying costs by category and expressing each category as a percentage of sales 4. Prepare a pie chart showing each quality cost category's contrbution to total quality costs for 20x3 in percentage terms 5. Calculate the budgeted costs for 20X3, which are determined based on adjusting 20X2 costs to the sales level of 20X3, and prepare a one-year performance report. Please review Cornerstone 14.2 Hint Make sure you take into account the cost behavior of the quality costs You must also designate U or F if there is a variance if there is no variance you can leave the designation blank, since the variance of zero is not U or F You must use the IF Function to designate U or F to earn credit Please remember that variances are not shown in negative terms, so make sure you use absolute value 6 Do you believe the company is moving towards a good balance among the quality cost categones? Why? Explain your response in detail 7 Estimate the additional improvement in profits of Spring Company ultimately reduces its quality costs to 40% of sales revenues of 20X3 Solution Make sure you answer the questions below in order