• If you haven't done so already (in conjunction with Problem 17-70), prepare in Excel a cost-of-quality (COQ) report for 2019 and 2020. Show subtotals each year for each of the four COQ categories. As well, show category costs each year as a percentage of revenue for the year. Finally, show each year total COQ as a percentage of revenue for the year. and set up a spreadsheet for this information. Round all percentages to 2 decimal places.

	2019	2020
Sales Materials inspection In-process (production) inspection Finished product inspection Preventive equipment maintenance Scrap (net) Warranty repairs Product design engineering Vendor certification Direct costs of returned goods Training of factory workers Product testingequipment maintenance Product testing labor Field repairs Rework before shipment Product-liability settlement Emergency repair and maintenance	$16,000 300 160 250 20 500 700 150 10 275 40 60 210 70 240 360 200	$20,000 60 125 70 60 300 450 270 60 80 140 60 90 40 200 60 75

2. 
   
4. Move to another area of your spreadsheet and title the area "Cost of QualityTrend Analysis." Show, for both 2019 and 2020, COQ spending (both category-level and in total) as a percentage of sales for the year. Show all percentages to 2 decimal places.
6. Provide a bar chart to compare the percentages of each of the COQ categories and the total COQ for 2020 vs. 2019.
8. Complete a sensitivity analysis by making the following (joint) changes to the 2020 amounts:
   2. Increase the total sales by 5%.
   4. Increase total prevention cost by 6%.
   6. Decrease total internal failure cost by 60%.
   8. Decrease total external failure cost by 50%.

For each of the four COQ categories, what is the revised (i.e, 2020) category cost as a percentage of sales, rounded to 2 decimal places?

Answer rating: 100% (QA)

Here is the costofquality COQ report for 2019 and 2020 along with a cost of quality trend analysis a... View the full answer