! Required information Excel Analytics 12-1 (Static) Quality Cost Report (LO12-2] (The following information applies to the questions displayed below.) 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: Cost of testing equipment Customer returns of defective goods Downtime due to quality problems Inspection Net cost of scrap Product recalls Quality engineering Rework labor Statistical process control Supplies used in testing Systems development Warranty repairs Costs (in thousands) Quarter 1 Quarter 2 $ 270 $ 390 $ 3,200 $ 200 $ 600 $ 1,100 $ 1,700 $ 2,770 $ 800 $ 1,300 $3,500 $ 600 $ 1,080 $ 1,650 $ 1,400 $ 1,600 $ $ 270 $ 30 $ 40 $ 120 $ 680 $ 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. 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 bars-one for each quarter. Each bar should quantify that quarter's 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 bars-one for each quarter. Each bar should quantify that quarter's total quality costs as a percent of that quarter's 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. Complete this question by entering your answers in the tabs below. Req 3B Req 3C Using PivotChart, create a stacked bar chart that includes a total of two bars-one for each quarter. Each bar should quantify that quarter's 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). Based on a review of your bar chart, answer the following questions: (Enter your answers in thousands.) Show less b-1 by b-2 From quarter 1 to quarter 2, the external failure costs From quarter 1 to quarter 2, the prevention costs From quarter 1 to quarter 2, the internal failure costs by b-3 by Complete this question by entering your answers in the tabs below. Req 3B Req 30 Using PivotChart again, create a stacked bar chart that includes a total of two bars-one for each quarter. Each bar should quantify that quarter's total quality costs as a percent of that quarter's 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. Based on a review of your bar chart, answer the following questions: (Round your answers to 2 decimal places.) Show less A C-1 by % C-2 From quarter 1 to quarter 2, the appraisal costs as a percent of sales From quarter 1 to quarter 2, the external failure costs as a percent of sales From quarter 1 to quarter 2, the prevention costs as a percent of sales % by by C-3 % A B C D E F G H L M N P 0 R S U VLOOKUP : 1 2 3 4 5 a VLOOKUP is a function in Microsoft Excel that easily allows you to search tables or sheets within Excel workbooks and return specified corresponding values. Below is a description of the Excel formula and an example of how to use it 1. The first step is to understand the VLOOKUP function itself. The VLOOKUP Function has the following arguments: 6 2. =VLOOKUP(lookup_value, table_array, col_index_num, (range_lookup] 7 7 The "lookup_value is the cell that contains the value you want to search for in another table 8 9 b. The "table_array" is the table where you want to look for the "lookuo_value" 10 11 It is very important that the leftmost column in the "table_array" is the column that would contain the lookup_value" you are looking for " 12 C. The "enl_index_num, is the column number that contains the corresponding value you want to return, for example if you input 1, it will give you the column you are searching in for the "lookup_value", if you choose it will 13 give you the column directly to the right of the column that contains the "lookup value and so on. 14 15 d. "range_lookup" has to options "True" or "False". If you want an exact match (which you typically do choose "False" if you are looking only for an approximate match, choose "True". 16 17 18 19 20 VLOOKUP 21 3. Lets do the VLOOKUP(lookup_value, table_array, col_index_num, (range_lookup) to have a lookup function where you can type in any state and have it return the sales for that state. You have a simple table that lists the states your 22 compan > where in you want to type the state name into Cell E2 and have Cell F2 generate the sales for that state: 23 24 25 26 77 N 0 0 R 5 T U D F F G H J M 25 26 27 20 A B D F 29 1 1 State Sales Sales 30 2 Alabama $ $ 169,511 State Lookup Virginia 31 3 Georgia $ 128,652 32 4 North Carolina $ 244,164 33 5 South Carolina S 285,084 24 6 Virginia S 205,626 35 7 West Virginia $ 196,359 36 Ic 37 38 b) The "table_array" is $A$1:$E$7 as this is the table where we want to search for the "lookup_value" (State) 39 c) The "col_index_num is 2 as we are searching the first column but want to return the value found in the 2nd column (Sales) 40 41 d) "range_lookup" is set to "False" as we want to match the state exactly 42 43 44 45 46 47 48 49 D E G H 1 1 Sales Sales 2 Alabama $ 169,511 State Lookup Virginia - VLOOKUP(E2.$A$1:$$7.2.FALSE) 513 Georgia $ 128,652 VLOOKUP(lookup_value table_array, col_index_num, Irange_lookup , , 52 4 North Carolina $ 244,164 53 5 South Carolina $ 285,084 54 6 Virginia $ 205,626 55 7 West Virginia $ 196,359 2 56 57 A 50 State Paste - Insert Delete Format Sei BIVA Merge Center $ -% Conditional Format as Cell Formatting Table Styles Styles clear Sort & Find & Filter Select Analyze Data Clipboard Font Alignment Number Cells Editing Analysis Se B6 X A B E F . K 1 2 3 4 Quarter 2 Q2% of Total Quality Cost - Classification Q1 % of Sales 02 Amount 02 % of Sales D Harvey Company Quality Cost Report (in thousands) Quarter 1 91% of Total 01 Amount Quality Cost- 270 $ 3,200 $ 600 $ 1,700 S 800 $ 3,500 $ 1,080 $ 1,400 $ $ 30 $ 120 $ 3,300 $ 16,000 0% Cost Description 5 Cost of testing equipment 7 Customer returns of defective goods 8 Downtime due to quality problems 9 Inspection 10 Net cost of scrap 11 Product recalls 12 Quality engineering 13 Rework labor 14 Statistical process control 15 Supplies used in testing 16 Systems development 17 Warranty repairs 18 19 20 21 22 23 24 25 26 27 28 29 $ $ $ $ S $ S $ $ $ $ S $ $ 0.0% $ 390 200 1,100 2,770 1,300 600 1,650 1,600 270 40 680 2,800 13,400 Total 0% 0.0% Qi Sales S Q2 Sales 5 95,000 100,000 Classification 1. Prevention 2. Appraisal 3. Internal Failure 4. External Failure 31 - X AA Calibri = AutoSum WA a Wrap Text Custorn SEX Insart Dalate Fomat 47 0 LE ill Paste BJU Sensitivity = = = YS Analyza Data + + Merge & Center $% 999 Conditional Format as Cell Formatting Tolole Styles 5.yles Clear Sort & Find & Filter Select [citing Clipboard Tunt Alignment Number Analysis Sensitivity B6 x A B E F G H ) K M 1 2 3 4 Classification 91 % of Sales Q2 Amount 92% of Sales 1 5 Cost Description 6 Cost of testing equipment 7 Customer retums of defective goods 8 Downtime due to quality problems 9 Inspection 10 Net cost of scrap 11 Product recalls 12 Quality engineering 13 Rework labor 14 Statistical process control 15 Supplies used in testing 16 Systems development 17 Warranty repairs 18 19 20 21 22 23 24 25 26 C D Harvey Company Quality Cost Report (in thousands) Quarter 1 Q1% of Total Q1 Amount - Quality Cost S 270 0.0% $ 3.200 0.094 $ 600 0.0% $ 1.700 0.0% $ 800 0.0% S 3,500 0.0% $ 1,080 0.0% $ 1,400 0,0% $ 0.0% $ 30 0.0% $ 120 0.0% $ 3,300 0.0% 0.0% $ 0.0% $ 0.0% $ 0.0% $ 0.0% $ 0.0% 0.0% $ 0.0% $ 0.0% $ 0.0%S 0.0% $ 0.0% $ 390 200 1,100 2.770 1,300 600 1,650 1,600 270 40 680 2,800 Quarter 2 92% of Total Quality Cosi 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% ! Required information Excel Analytics 12-1 (Static) Quality Cost Report (LO12-2] (The following information applies to the questions displayed below.) 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: Cost of testing equipment Customer returns of defective goods Downtime due to quality problems Inspection Net cost of scrap Product recalls Quality engineering Rework labor Statistical process control Supplies used in testing Systems development Warranty repairs Costs (in thousands) Quarter 1 Quarter 2 $ 270 $ 390 $ 3,200 $ 200 $ 600 $ 1,100 $ 1,700 $ 2,770 $ 800 $ 1,300 $3,500 $ 600 $ 1,080 $ 1,650 $ 1,400 $ 1,600 $ $ 270 $ 30 $ 40 $ 120 $ 680 $ 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. 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 bars-one for each quarter. Each bar should quantify that quarter's 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 bars-one for each quarter. Each bar should quantify that quarter's total quality costs as a percent of that quarter's 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. Complete this question by entering your answers in the tabs below. Req 3B Req 3C Using PivotChart, create a stacked bar chart that includes a total of two bars-one for each quarter. Each bar should quantify that quarter's 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). Based on a review of your bar chart, answer the following questions: (Enter your answers in thousands.) Show less b-1 by b-2 From quarter 1 to quarter 2, the external failure costs From quarter 1 to quarter 2, the prevention costs From quarter 1 to quarter 2, the internal failure costs by b-3 by Complete this question by entering your answers in the tabs below. Req 3B Req 30 Using PivotChart again, create a stacked bar chart that includes a total of two bars-one for each quarter. Each bar should quantify that quarter's total quality costs as a percent of that quarter's 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. Based on a review of your bar chart, answer the following questions: (Round your answers to 2 decimal places.) Show less A C-1 by % C-2 From quarter 1 to quarter 2, the appraisal costs as a percent of sales From quarter 1 to quarter 2, the external failure costs as a percent of sales From quarter 1 to quarter 2, the prevention costs as a percent of sales % by by C-3 % A B C D E F G H L M N P 0 R S U VLOOKUP : 1 2 3 4 5 a VLOOKUP is a function in Microsoft Excel that easily allows you to search tables or sheets within Excel workbooks and return specified corresponding values. Below is a description of the Excel formula and an example of how to use it 1. The first step is to understand the VLOOKUP function itself. The VLOOKUP Function has the following arguments: 6 2. =VLOOKUP(lookup_value, table_array, col_index_num, (range_lookup] 7 7 The "lookup_value is the cell that contains the value you want to search for in another table 8 9 b. The "table_array" is the table where you want to look for the "lookuo_value" 10 11 It is very important that the leftmost column in the "table_array" is the column that would contain the lookup_value" you are looking for " 12 C. The "enl_index_num, is the column number that contains the corresponding value you want to return, for example if you input 1, it will give you the column you are searching in for the "lookup_value", if you choose it will 13 give you the column directly to the right of the column that contains the "lookup value and so on. 14 15 d. "range_lookup" has to options "True" or "False". If you want an exact match (which you typically do choose "False" if you are looking only for an approximate match, choose "True". 16 17 18 19 20 VLOOKUP 21 3. Lets do the VLOOKUP(lookup_value, table_array, col_index_num, (range_lookup) to have a lookup function where you can type in any state and have it return the sales for that state. You have a simple table that lists the states your 22 compan > where in you want to type the state name into Cell E2 and have Cell F2 generate the sales for that state: 23 24 25 26 77 N 0 0 R 5 T U D F F G H J M 25 26 27 20 A B D F 29 1 1 State Sales Sales 30 2 Alabama $ $ 169,511 State Lookup Virginia 31 3 Georgia $ 128,652 32 4 North Carolina $ 244,164 33 5 South Carolina S 285,084 24 6 Virginia S 205,626 35 7 West Virginia $ 196,359 36 Ic 37 38 b) The "table_array" is $A$1:$E$7 as this is the table where we want to search for the "lookup_value" (State) 39 c) The "col_index_num is 2 as we are searching the first column but want to return the value found in the 2nd column (Sales) 40 41 d) "range_lookup" is set to "False" as we want to match the state exactly 42 43 44 45 46 47 48 49 D E G H 1 1 Sales Sales 2 Alabama $ 169,511 State Lookup Virginia - VLOOKUP(E2.$A$1:$$7.2.FALSE) 513 Georgia $ 128,652 VLOOKUP(lookup_value table_array, col_index_num, Irange_lookup , , 52 4 North Carolina $ 244,164 53 5 South Carolina $ 285,084 54 6 Virginia $ 205,626 55 7 West Virginia $ 196,359 2 56 57 A 50 State Paste - Insert Delete Format Sei BIVA Merge Center $ -% Conditional Format as Cell Formatting Table Styles Styles clear Sort & Find & Filter Select Analyze Data Clipboard Font Alignment Number Cells Editing Analysis Se B6 X A B E F . K 1 2 3 4 Quarter 2 Q2% of Total Quality Cost - Classification Q1 % of Sales 02 Amount 02 % of Sales D Harvey Company Quality Cost Report (in thousands) Quarter 1 91% of Total 01 Amount Quality Cost- 270 $ 3,200 $ 600 $ 1,700 S 800 $ 3,500 $ 1,080 $ 1,400 $ $ 30 $ 120 $ 3,300 $ 16,000 0% Cost Description 5 Cost of testing equipment 7 Customer returns of defective goods 8 Downtime due to quality problems 9 Inspection 10 Net cost of scrap 11 Product recalls 12 Quality engineering 13 Rework labor 14 Statistical process control 15 Supplies used in testing 16 Systems development 17 Warranty repairs 18 19 20 21 22 23 24 25 26 27 28 29 $ $ $ $ S $ S $ $ $ $ S $ $ 0.0% $ 390 200 1,100 2,770 1,300 600 1,650 1,600 270 40 680 2,800 13,400 Total 0% 0.0% Qi Sales S Q2 Sales 5 95,000 100,000 Classification 1. Prevention 2. Appraisal 3. Internal Failure 4. External Failure 31 - X AA Calibri = AutoSum WA a Wrap Text Custorn SEX Insart Dalate Fomat 47 0 LE ill Paste BJU Sensitivity = = = YS Analyza Data + + Merge & Center $% 999 Conditional Format as Cell Formatting Tolole Styles 5.yles Clear Sort & Find & Filter Select [citing Clipboard Tunt Alignment Number Analysis Sensitivity B6 x A B E F G H ) K M 1 2 3 4 Classification 91 % of Sales Q2 Amount 92% of Sales 1 5 Cost Description 6 Cost of testing equipment 7 Customer retums of defective goods 8 Downtime due to quality problems 9 Inspection 10 Net cost of scrap 11 Product recalls 12 Quality engineering 13 Rework labor 14 Statistical process control 15 Supplies used in testing 16 Systems development 17 Warranty repairs 18 19 20 21 22 23 24 25 26 C D Harvey Company Quality Cost Report (in thousands) Quarter 1 Q1% of Total Q1 Amount - Quality Cost S 270 0.0% $ 3.200 0.094 $ 600 0.0% $ 1.700 0.0% $ 800 0.0% S 3,500 0.0% $ 1,080 0.0% $ 1,400 0,0% $ 0.0% $ 30 0.0% $ 120 0.0% $ 3,300 0.0% 0.0% $ 0.0% $ 0.0% $ 0.0% $ 0.0% $ 0.0% 0.0% $ 0.0% $ 0.0% $ 0.0%S 0.0% $ 0.0% $ 390 200 1,100 2.770 1,300 600 1,650 1,600 270 40 680 2,800 Quarter 2 92% of Total Quality Cosi 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0%