Harvey Company designs and produces surgical lasers. Its product is brilliantly designed, but the manufacturing process has been plagued by chronic quality control problems. To help address the situation, Harvey formed a cross-functional team of managers that quantified its costs of quality over the last two quarters as follows:

	Costs (in thousands)
	Quarter 1				Quarter 2
Cost of testing equipment	$	270			$	390
Customer returns of defective goods	$	3,200			$	200
Downtime due to quality problems	$	600			$	1,100
Inspection	$	1,700			$	2,770
Net cost of scrap	$	800			$	1,300
Product recalls	$	3,500			$	600
Quality engineering	$	1,080			$	1,650
Rework labor	$	1,400			$	1,600
Statistical process control	$	0			$	270
Supplies used in testing	$	30			$	40
Systems development	$	120			$	680
Warranty repairs	$	3,300			$	2,800

The company’s sales were $95 million in Quarter 1 and $100 million in Quarter 2.

Click here to download the Excel template, which you will use to answer the questions that follow.

Click here for a brief tutorial on PivotTables in Excel.

Click here for a brief tutorial on PivotCharts in Excel.

Click here for a brief tutorial on the Subtotal function in Excel.

Click here for a brief tutorial on VLOOKUP in Excel.

rev: 05_07_2020_QC_CS-210952

Excel Analytics 12-1 (Static) Part 1

Required:
1. Go to the tab titled Template A:

a. For each cost described in cells A6 through A17, choose the correct quality cost classification (i.e., prevention, appraisal, internal failure, or external failure) from the drop-down list that appears when you put the cursor in each of cells B6 through B17.

b. Create formulas in cells D6 through D17 that state each individual cost from Quarter 1 (as shown in cells C6 through C17) as a percent of the first quarter’s total cost of quality (as shown in cell C18). Repeat this process in column G for quarter 2.

c. Create formulas in cells E6 through E17 that state each individual cost from Quarter 1 (as shown in cells C6 through C17) as a percent of the first quarter’s total sales (as shown in cell C20). Repeat this process in column H for quarter 2.

• Req 1A
• Req 1B
• Req 1C

For each of the quality costs shown below, what is the amount of the cost as a percent of the total sales? The first column of answers relates the first quarter and the second column relates to the second quarter. (Round your answers to 1 decimal place.)

For each of the quality costs shown below, what is the amount of the cost as a percent of the total cost of quality? The first column of answers relates the first quarter and the second column relates to the second quarter. (Round your answers to 1 decimal place.)

What is the correct quality cost classification for each of the following quality costs?

Customer returns of defective goods
Net cost of scrap
Quality engineering
Statistical process control
Supplies used in testing
Warranty repairs

	Quarter 1 (%)		Quarter 2 (%)
Cost of testing equipment		%		%
Inspection		%		%
Net cost of scrap		%		%
Product recalls		%		%
Systems development		%		%
Warranty repairs		%		%

	Quarter 1 (%)		Quarter 2 (%)
Customer returns of defective goods		%		%
Downtime due to quality problems		%		%
Quality engineering		%		%
Rework labor		%		%
Statistical process control		%		%
Warranty repairs		%		%