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% 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 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 $ 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. Required information 2. Go to the tab titled lemplate B: a. Use VLOOKUP to identify the proper quality cost classification that should appear in cells B6 through B17. (Hints: Your VLOOKUP formula in cell B6 will refer to Template A to find the cost that matches the description in cell A6 of Template B. Once a match is found your formula will instruct Excel to take the corresponding cost classification from Template A and insert it into cell B6 of Template B. Use absolute references where needed when creating your formula in cell B6 because this will enable you to copy the formula into cells B7 through B17.) b. What quality cost classification should appear in cells B9, B12, and B17? c. Using Exhibit 12-7 as your guide, prepare a quality cost report for Quarters 1 and 2. Your quality cost report should list prevention costs first followed by appraisal, internal failure, and external failure costs. (Hints: First, sort all rows of data by quality cost classification (A-Z). Second, select cells A5 through H17 and use the Excel's subtotal function to create subtotals within columns C through H by classification type. d. What are the first quarter's total prevention, appraisal, internal failure, and external failure costs? What are the same four amounts for the second quarter? e. What are the first quarter's total prevention, appraisal, internal failure, and external failure costs each stated as a percent of the first quarter's total cost of quality? What are the same four percentages for the second quarter? f. What are the first quarter's total prevention, appraisal, internal failure, and external failure costs each stated as a percent of the first quarter's sales? What are the same four percentages for the second quarter? Complete this question by entering your answers in the tabs below. Reg 2B Req 2D Req 2E Req 2F What quality cost classification should appear in cells B9, B12, and B17? Inspection (cell B9) Quality engineering (cell B12) Warranty repairs (cell B17) Appraisal Req 2D > External fallure Internal failure Prevention Complete this question by entering your answers in the tabs below. Req 2B Req 2D Req 2E Req 2F What are the first quarter's total prevention, appraisal, internal failure, and external failure costs? What are the same four amounts for the second quarter? Quarter 1 (in thousands) Quarter 2 (in thousands) Prevention Appraisal Internal failure External failure Complete this question by entering your answers in the tabs below. Req 2B Req 2D Req 2E Req 2F What are the first quarter's total prevention, appraisal, internal failure, and external failure costs each stated as a percent of the first quarter's total cost of quality? What are the same four percentages for the second quarter? (Round your answers to 1 decimal place.) Quarter 1 (%) Quarter 2 (%) Prevention % % Appraisal % % Internal failure % % External failure % % Complete this question by entering your answers in the tabs below. Req 2B Req 2D Reg 2E Req 2F What are the first quarter's total prevention, appraisal, internal failure, and external failure costs each stated as a percent of the first quarter's sales? What are the same four percentages for the second quarter? (Round your answers to 1 decimal place.) Quarter 1 (%) Quarter 2 (%) % Prevention % Appraisal % % Internal failure % % External failure % % 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 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 $ 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. Required information 2. Go to the tab titled lemplate B: a. Use VLOOKUP to identify the proper quality cost classification that should appear in cells B6 through B17. (Hints: Your VLOOKUP formula in cell B6 will refer to Template A to find the cost that matches the description in cell A6 of Template B. Once a match is found your formula will instruct Excel to take the corresponding cost classification from Template A and insert it into cell B6 of Template B. Use absolute references where needed when creating your formula in cell B6 because this will enable you to copy the formula into cells B7 through B17.) b. What quality cost classification should appear in cells B9, B12, and B17? c. Using Exhibit 12-7 as your guide, prepare a quality cost report for Quarters 1 and 2. Your quality cost report should list prevention costs first followed by appraisal, internal failure, and external failure costs. (Hints: First, sort all rows of data by quality cost classification (A-Z). Second, select cells A5 through H17 and use the Excel's subtotal function to create subtotals within columns C through H by classification type. d. What are the first quarter's total prevention, appraisal, internal failure, and external failure costs? What are the same four amounts for the second quarter? e. What are the first quarter's total prevention, appraisal, internal failure, and external failure costs each stated as a percent of the first quarter's total cost of quality? What are the same four percentages for the second quarter? f. What are the first quarter's total prevention, appraisal, internal failure, and external failure costs each stated as a percent of the first quarter's sales? What are the same four percentages for the second quarter? Complete this question by entering your answers in the tabs below. Reg 2B Req 2D Req 2E Req 2F What quality cost classification should appear in cells B9, B12, and B17? Inspection (cell B9) Quality engineering (cell B12) Warranty repairs (cell B17) Appraisal Req 2D > External fallure Internal failure Prevention Complete this question by entering your answers in the tabs below. Req 2B Req 2D Req 2E Req 2F What are the first quarter's total prevention, appraisal, internal failure, and external failure costs? What are the same four amounts for the second quarter? Quarter 1 (in thousands) Quarter 2 (in thousands) Prevention Appraisal Internal failure External failure Complete this question by entering your answers in the tabs below. Req 2B Req 2D Req 2E Req 2F What are the first quarter's total prevention, appraisal, internal failure, and external failure costs each stated as a percent of the first quarter's total cost of quality? What are the same four percentages for the second quarter? (Round your answers to 1 decimal place.) Quarter 1 (%) Quarter 2 (%) Prevention % % Appraisal % % Internal failure % % External failure % % Complete this question by entering your answers in the tabs below. Req 2B Req 2D Reg 2E Req 2F What are the first quarter's total prevention, appraisal, internal failure, and external failure costs each stated as a percent of the first quarter's sales? What are the same four percentages for the second quarter? (Round your answers to 1 decimal place.) Quarter 1 (%) Quarter 2 (%) % Prevention % Appraisal % % Internal failure % % External failure % %