AutoSave OFF り × Assignment 4 Tell me Insert ✓ Σ ✓ Delete ▼ .00 0- Conditional Format Cell Formatting as Table Styles Format Home Insert Draw Page Layout Formulas Data Review View Automate Calibri (Body) ✓ 11 ✓ Aˆ A Wrap Text Number Paste B I U ✓ ✓ Merge & Center $ ✓ % V22 fx A B C D E F G H I J K L M N 0 P Q R S T U W X Y Z AA AB AC AD 吕 AE AF 10 11 123456782222 22222 13 14 This problem has three questions: Q1 Q3. Due to complaints from retailers, West Battery Corp. decided to study the voltage of batteries produced at their upstate New York plant. The manager collected a sample of 18 batteries each hour for 24 hours. The voltage (in volts) of each battery is shown in the table below. The batteries are used for a medium-size electric lawn mower, and a new battery's voltage should be greater than 46 volts but no more than 54 volts to be considered acceptable. The manager would like to classify each battery using grades A, B, C, D, and F according to the new inspection guidelines: -- Grade A (Excellent): Voltage is greater than 49 and up to 52. -- Grade B (Good): Voltage is greater than 52 and up to 53. - Grade C (Average): Voltage is greater than 53 and up to 54. -- Grade D (Marginal): Voltage is greater than 46 and up to 49. Grade F (Fail): Any voltage outside the above ranges. Q1: Create a chart that displays the frequency distribution of these grades. Ensure the frequencies are shown in descending order to highlight which grades are most commonly produced. Additionally, indicate the cumulative percentage of total output for each grade (i.e., the proportion of batteries for that grade and all more frequent grades) on the chart. Your chart should be made by linking to the data on the 15 worksheet, not a picture copied from another source. 16 17 18 Hour Sample of 18 batteries sample 19 taken 1 2 3 4 5 6 7 8 20 1 21 2 3 23 4 24 5 25 6 26 7 27 8 28 9 29 10 30 11 31 12 32 13 33 14 34 15 35 16 36 17 37 18 38 19 39 20 40 21 41 22 42 23 43 24 44 17 18 9 10 11 12 13 14 15 16 50.2 53.7 48.6 49.3 51.1 52.3 47.3 50.1 50.5 50.2 52.3 46.9 50.1 50.5 52.3 49.9 50.6 54.1 50.8 48.8 52.8 48.6 45.4 50.6 49.9 46.1 53.6 46.2 50.6 49.9 56.1 50.2 49.9 47.9 57.3 52.6 50.2 48.3 52.6 46.9 53.3 49.2 47.9 54.1 50.6 52.1 54.0 50.6 48.8 53.8 47.9 53.3 50.6 50.3 49.5 48.8 54.9 53.2 53.6 51.1 52.6 52.6 49.5 51.8 47.8 52.3 48.3 52.6 53.3 53.1 50.5 50.6 52.3 51.7 50.5 50.5 50.5 43.7 45.7 50.6 50.5 50.2 48.3 52.6 48.5 50.2 49.9 53.6 56.1 46.1 50.6 49.9 46.1 53.6 53.6 52.6 50.6 50.9 44.4 51.9 45.7 50.2 51.1 52.3 47.9 52.3 48.8 50.6 46.4 54.1 50.6 49.2 52.3 48.3 45.3 49.9 46.1 53.6 49.9 46.1 50.6 49.9 50.3 52.6 48.3 46.1 52.5 52.6 46.9 50.2 50.6 49.9 49.9 47.9 54.7 50.6 52.6 51.1 49.9 46.1 50.3 53.6 50.6 52.6 51.5 50.3 50.5 46.7 49.2 47.9 48.6 52.6 52.6 46.3 50.2 45.7 56.1 55.3 49.9 52.6 47.8 46.9 49.3 45.1 50.6 48.3 52.6 53.3 52.6 49.6 50.2 52.3 45.7 47.9 54.5 51.2 50.6 47.9 48.3 50.5 49.9 46.1 57.3 53.1 53.2 53.1 55.3 48.3 52.6 53.3 52.6 46.6 50.2 50.6 49.9 49.2 45.7 48.6 48.6 48.9 50.6 49.9 46.1 53.6 52.6 52.6 46.9 51.8 51.5 49.8 50.5 46.7 49.9 51.1 49.9 52.8 45.7 50.5 50.5 46.2 51.1 52.3 47.9 50.9 50.5 50.2 46.7 49.2 47.9 46.7 51.1 43.7 52.6 52.6 47.7 50.2 56.1 50.2 45.1 50.6 49.9 46.1 53.6 46.2 48.3 52.6 53.3 52.3 45.7 52.6 50.2 54.9 48.1 50.8 48.8 53.8 53.3 49.2 47.9 54.0 50.6 52.1 48.1 49.5 50.5 51.1 51.7 48.3 48.3 50.5 52.9 55.3 48.3 52.6 52.6 49.1 50.5 52.1 48.3 52.6 49.9 46.1 53.6 47.8 53.6 49.9 49.9 50.2 48.6 50.3 50.6 49.9 46.1 52.3 48.8 52.2 50.5 49.1 46.4 53.1 49.2 47.9 54.4 46.4 47.9 49.2 51.9 48.5 50.2 52.2 50.1 50.6 49.9 46.1 53.6 52.6 52.5 52.6 44.3 50.8 55.3 52.5 53.3 44.3 49.3 51.1 52.3 49.6 47.9 54.7 50.6 48.1 50.3 50.5 51.1 52.3 45.7 46.1 53.6 46.9 53.1 45.7 49.3 50.6 49.9 50.6 47.7 47.8 52.3 52.7 48.3 48.8 48.9 50.6 49.9 50.6 49.2 50.5 53.6 49.9 50.6 49.9 46.1 50.2 56.1 45.9 49.2 52.3 49.4 48.3 49.9 46.1 53.6 53.6 52.6 53.6 52.3 53.6 49.2 47.9 54.2 53.3 52.6 48.1 50.8 48.8 52.8 46.7 54.6 50.6 49.2 52.3 45.7 50.6 52.6 49.2 49.3 51.1 52.3 45.7 50.8 50.5 43.7 52.8 48.6 52.7 49.9 46.1 52.3 45.7 50.1 50.2 55.3 52.3 54.1 45.7 49.9 50.5 50.2 45.8 50.2 56.1 49.9 56.1 50.1 50.5 46.7 49.2 47.9 51.9 52.6 46.7 45 Eric, the design engineer at West Battery Corp., found the original documentation for the battery design specifications. The nominal voltage is specified as 50.5 volts, with a tolerance of ±5.5 volts. The document 46 also states that the company was aiming for three-sigma performance (i.e., no more than 2.7 out-of-spec units per 1,000 produced) when this battery production was designed. 47 48 Q2: Based on the process capability ratio and/or process capability index, is the current battery production considered a three-sigma capable process? If not, identify which issue (centering or variability) must be 49 addressed to achieve three-sigma capability. Show your calculations and analysis below. Note that the question focuses on the production process output, not just the sample. 50 51 52 53 54 55 56 57 58 UJ Problem 1 Problem 2 Problem 3 Ready Accessibility: Investigate + ✓ ✓ BD Share Comments ✓ ✓ Sort & Filter Find & Select Analyze Data Sensitivity AG 60 AH ΑΙ AJ AK AL AM AN AO AP AQ AR AS + 75% 58 AutoSave OFF り Home Insert Draw Page Layout Formulas Data Review View Automate × Assignment 4 Tell me BD Share Comments Calibri (Body) ✓ 11 ✓ Aˆ A Wrap Text Number Insert ✓ Σ ✓ ✓ ✓ Delete ▼ Paste B I U ✓ ✓ Merge & Center $ ✓ % .00 →0 Conditional Format Cell Formatting as Table Styles Format ✓ ✓ Sort & Filter Find & Select Analyze Data Sensitivity V22 fx A B с D E F G H | J K L M N 0 P 0 R S T U V W X Y Z AA AB AC CO AD AE AF AG AH H=4 AC ΑΙ AJ AK AL AM AN AO AP AP AQ AR AS 59 60 61 62 64 65 66 63 Q3: With the redesigned lawn mower, the battery voltage specifications have been updated. The new nominal voltage is 52.0 volts, with a tolerance of ±6.0 volts. To meet this new design requirement, Eric has collaborated with engineers to implement changes in the battery production process. The table below displays the voltage data from the testing of batteries produced after these changes. Based on the process capability ratio and/or index, has this plan successfully achieved three-sigma capability? If not, identify whether the issue lies with the process centering and/or variability. Provide your answer and justification using the calculated process capability ratio and index, rather than personal opinion. Note that this question refers to the overall production process output, not just the sample. 67 68 69 Voltages of batteries tested 70 53.1 53.6 52.0 52.5 52.0 51.5 71 53.8 52.2 53.0 49.6 47.4 54.7 72 53.0 53.0 52.5 51.2 53.5 52.8 73 52.9 56.4 50.2 55.5 54.0 50.9 74 55.9 50.3 49.8 49.9 51.3 50.9 75 52.6 53.3 51.3 52.1 50.3 51.8 76 54.2 51.7 51.8 53.8 55.1 52.8 77 53.8 50.0 50.0 53.0 51.2 53.8 78 50.5 51.7 51.3 55.1 53.2 51.5 79 54.3 51.6 55.0 52.9 53.0 52.1 80 51.6 50.5 52.4 55.5 53.4 52.0 81 51.4 54.0 53.0 50.8 52.7 53.5 82 52.0 54.7 53.3 50.1 54.4 51.2 83 52.1 52.6 51.8 48.3 53.6 48.5 84 51.6 53.8 52.8 49.9 52.5 52.1 85 52.0 51.9 53.5 51.3 54.4 49.0 86 52.5 49.6 51.7 53.1 52.3 55.7 87 51.7 51.9 55.1 51.9 53.9 52.2 88 51.2 51.1 51.9 46.2 53.5 51.7 89 57.5 51.6 54.7 51.4 54.0 51.0 90 51.3 54.6 51.7 52.9 54.5 50.7 91 52.6 51.0 48.1 55.1 51.3 50.1 92 51.7 50.1 54.7 51.8 53.9 55.0 93 51.7 51.3 50.9 55.2 49.3 52.3 94 54.8 55.5 50.6 53.3 49.7 54.0 95 50.9 55.1 54.6 51.1 53.6 54.1 96 53.5 52.5 52.9 49.3 51.7 53.0 97 51.7 51.1 54.9 52.8 52.4 51.4 98 51.5 52.2 51.8 55.6 51.9 54.2 99 49.1 55.5 51.2 50.7 48.7 47.0 100 53.6 53.4 51.9 51.2 52.5 50.8 101 53.8 51.7 51.2 53.9 52.9 51.2 102 51.0 52.5 49.9 56.8 49.8 51.9 103 54.6 51.8 54.1 52.7 55.8 51.5 104 53.8 52.6 52.7 49.6 52.5 53.5 105 52.3 53.3 51.3 49.7 51.8 51.3 106 51.3 54.3 51.7 50.8 54.5 52.2 107 54.7 51.2 53.2 49.5 53.9 51.2 108 53.5 53.5 51.6 52.0 53.9 50.0 109 51.0 51.2 48.8 51.0 53.9 50.8 110 52.9 50.5 50.3 52.8 52.8 55.4 111 49.7 51.6 52.4 52.3 50.7 51.5 112 52.2 53.5 49.4 51.3 51.3 54.3 113 54.1 52.6 53.6 51.5 53.9 54.5 114 51.3 52.1 54.5 53.9 53.8 50.9 115 53.4 53.7 52.0 51.5 52.4 52.8 116 117 51.2 56.0 50.4 51.6 50.7 53.2 49.0 52.6 57.2 51.2 52.4 52.5 Ready Problem 1 Accessibility: Investigate Problem 2 Problem 3 + + 75%