Spreadsheet Application Use the data and a spreadsheet to complete this prob- lem. Use the spreadsheet functions to carry out all calculations. Do not hard-code or carry out cal- culations elsewhere and type in the calculated amounts.
|
|
2010
|
2011
|
|
Sales
|
$15,000
|
$18,750
|
|
Materials inspection
|
300
|
60
|
|
Production inspection
|
160
|
125
|
|
Finished product inspection
|
225
|
70
|
|
Preventive equipment maintenance
|
20
|
60
|
|
Scrap (net)
|
500
|
300
|
|
Warranty repairs
|
700
|
400
|
|
Product design engineering
|
150
|
270
|
|
Vendor certification
|
10
|
60
|
|
Direct costs of returned goods
|
250
|
80
|
|
Training of factory workers
|
40
|
140
|
|
Product testing-equipment maintenance
|
60
|
60
|
|
Product testing labor
|
210
|
90
|
|
Field repairs
|
70
|
30
|
|
Rework before shipment
|
240
|
180
|
|
Product-liability settlement
|
360
|
60
|
|
Emergency repair and maintenance
|
190
|
60
|
1. Determine the information you'll need to generate cost-of-quality (COQ) report and set up a spread- sheet 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-67 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 2010 and 2011.
7. Do a sensitivity analysis by making the following changes to the 2011 amounts:
• Increase the total sales by 5 percent.
• Increase total prevention cost by 6 percent.
• Decrease total internal failure cost by 60 percent.
• Decrease total external failure cost by 50 percent.
Required: What is the total COQ as a percentage of total sales?