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 companys 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, 01_09_2021_QC_CS-246235
Excel Analytics 12-1 (Static) Part 3
3. Refer back to the Template A tab:
a. Create a PivotTable based on the data in cells A5 through H17.
b. Using PivotChart, create a stacked bar chart that includes a total of two barsone for each quarter. Each bar should quantify that quarters total quality costs as well as the portion of those costs that fall within each of the four quality cost categories (i.e., prevention, appraisal, internal failure, and external failure).
c. Using PivotChart again, create a stacked bar chart that includes a total of two barsone for each quarter. Each bar should quantify that quarters total quality costs as a percent of that quarters sales. It should also depict each of the four quality cost categories (i.e., prevention, appraisal, internal failure, and external failure) as a percent of sales.
Auto Save Off H ExcelAnalytics _QualityCostReport_Template Saved - Search (Alt+Q) A Pierro, Jonathan Paul D X File Home Insert Draw Page Layout Formulas Data Review View Help Comments Share 2 X General V Insert Calibri In 29 AA === Uv UHA ===== = $ % ) DX Delete Paste B 1 Conditional Formatting 2 Format as Table Cell Styles Styles y Analyze Sensitivity Data Format 40 000 Nurnber Undo Clipboard Font Alignment 5 Cells Editing Analysis Sensitivity CS X fx 01 Amount F G H . K 1 M N P 4 Quarter 2 02 % of Total Q2 Amour Quality Co 92 % of Sale A B D F 1 1 Harvey Company 2 Quality Cost Report 3 (in thousands) Quarter 1 01 % of Total Cost Description Classification Q1 Amount 01 k. of Sal Quality Co 6 Cost of testing equipment 2. Appraisal 5 270 1.796 0.3% 5 7 Customer retums of defective goods 4. External Failure S 3,200 20.0% 3.49 $ 8 Downtime due to quality problems 2. Internal Failure $ 600 3.8% 0.6s 9 Inspection 2. Appraisal $ 1.700 10.6% 1.8% 5 10 Net cost of scrap 3. Internal Failure $ 2 800 5.0% 0.8% $ 11 Product recalls 3. Internal Failure S 3.500 21.9% 3.74 $ 12 Quality engineering 1. Prevention $ 1.000 6.8% 1.19 $ 13 Rework labor 3. Internal failure $ 1.100 % 8.8% 1.5% S 14 Statistical process control 1. Prevention S 0.0% 0.0% S 15 Supplies used in testing 2. Appraisal $ 20 0.2% 0.04 S 16 Systerns development 1. Prevention $ 120 0.8% 0.15$ 17 Warranty repairs 4. External Failure S 3.300 20.6% 3.5% 5 18 Total S 16.000 100% 16.856S 19 20 Q1 Sales 95,000 21 02 Sales 100,000 22 23 Pivot Table PivotChart Subtotal VLOOKUP Template A Template B Ready Accessibility: Investigate 390 200 1,100 2.770 1,300 600 1,650 1,600 270 40 680 2.800 13.400 2.9% 1.5% 8.29 20.7% 9.7% 4.59 12.39 11.9% 2.0% 0.39% 5.1% 20.9% 100% 0.4% 0.2% 1.1% 2.BX 1.3% 0.6% 1.7% 1.6% 0.376 0.0% 0.7% 2.8% 13.5% + 75% - 3:03 PM 4/25/2022 Auto Save Off H ExcelAnalytics _QualityCostReport_Template Saved - Search (Alt+Q) A Pierro, Jonathan Paul D X File Home Insert Draw Page Layout Formulas Data Review View Help Comments Share 2 General Calibri V Insert 22 27 Ich LN Paste AA === Uv UHA ===== = $ % ) DX Delete B 1 Conditional Formatting Format as Table Cell Styles Styles y Analyze Sensitivity Data Format 40 000 Nurnber Undo Clipboard Font Alignment 5 Cells Editing Analysis Sensitivity CS fx 01 Amount F H 1 1 K 1 M N P A 200 1,100 2,770 1,200 600 1,650 1.600 270 40 680 2,800 12,400 1.5% B.2% 20.7% 9.79% 4.5% 12.3% 11.9% 2.0% 0.3% 5.1% 20.0% 100% 0.2% 1.1%) 2.846 1.3% 0.6% 1.7% 1.646 % 0.3% 0.0% 0.7% 2.8% 13.5% A D F 7 Customer returns of delective guads 4. External Failure $ 3,200 20.0% 3.45$ 8 Downtime due to quality problems 3. Internal Failure 5 600 3.8% 0.6% 5 9 Inspection 2. Appraisal S 1,700 10.676 1.89 $ 10 Net cast of sarap 2. Internal Failure S 800 5.0% 0.8WS 11 Product recalls 3. Internal Failure S 3,500 21.9% 3.7% 5 12 Quality enpineering 1. Prevention $ 1,080 6.8% 1.1% 5 13 Rework labor 3. Internal Failure S 1.400 8.8% 1.5% S 14 Statistical process control 1. Prevention $ 0.0% 0.0S 15 Supplies used in testing 2. Appraisal $ 30 0.2% 0.0% 5 16 Systems development 1. Prevention S 120 0.8% 0.19 S 17 Warranty repairs 4. External Failure S 3.300 20.6% 3.5% S % 18 Total S 16,000 100% 16.89 $ 19 20 01 Sales 95.000 21 02 Sales 5 100,000 22 23 24 Classification 1. Prevention 25 2. Appraisal 26 3. Internal Failure 27 4. External Failure 20 29 30 31 Pivot Table PivotChart Subtotal VLOOKUP Template A Template B Ready Accessibility: Investigate OneDrive Screenshot saved The screenshot was added to your OneDrive. el 3:04 PM 4/25/2022
