Cost-of-Quality (COQ) Analysis; Spreadsheet Application Use the data in Problem 17-5...

Cost-of-Quality (COQ) Analysis; Spreadsheet Application Use the data in Problem 17-50 and a spreadsheet to complete this problem. Use the spreadsheet functions to carry out all calculations. Do not hard-code or carry out calculations elsewhere and type in the calculated amounts.

1. Determine the information you’ll need to generate a cost-of-quality (COQ) report and set up a spreadsheet for this information. Among the items to be included in the COQ report are proper headings of the report, revenue and cost items (cost items should be in the cost-of-quality category), and cost as a percentage of revenues for each of the two years.

2. Input the data provided in 17-50 into the spreadsheet by COQ category.

3. Enter functions or steps to calculate the total amount for each COQ category and the total COQ. Do not hard-code or type in the amounts.

4. Enter functions or steps to calculate the total cost of each COQ category as a percentage of revenues for each of the years. Use two digits after the decimal point for the percentages. Do the same for the total COQ. Do not hard-code or type in the amounts.

5. Move to another area of the spreadsheet or use a fresh sheet and title the area “Cost of Quality Trend Analysis.” Enter functions or steps for the percentages; do not hard-code or type in the amounts.

6. Create a bar chart to compare the percentages of each of the COQ categories and the total COQ in 2013 and 2014.

7. Do a sensitivity analysis by making the following changes to the 2014 amounts:

• Increase the total sales by 5%.

• Increase total prevention cost by 6%.

• Decrease total internal failure cost by 60%.

• Decrease total external failure cost by 50%.

Required What is the total COQ as a percentage of total sales?

Reference: