Looking for help with analysis of x-bar, and IM charts for course project.

COURSE PROJECT Introduction The student will be provided with a project scenario via an Excel spreadsheet that will require the use of several analytical tools to arrange, display and analyze data. Directions Project You are the Quality Manager for Widgets, Inc. You have just won a contract to produce a specialized widget that the customer requires a very tight specification limit. This widget is to be 5.50\" in length, plus or minus . 50\". This gives the total specification limit of 5.00\" to 6.00\". You have set up the production line to produce a test run of the widgets (Data Set 1). The sample size consists of a total of 20 samples per run, with a total of four (4) runs. A total of 80 samples will be used to determine if the process is capable of producing the widgets within specification. Due to the specifications of the customer, and the setup of the manufacturing processes, the systematic random sampling technique was used to ensure that samples from each run were collected at the same point in time on different days. This provides the basis for the X-bar chart to group the sample from each run into logical subgroups. Requirements: 1 Calculate the subgroup mean for each numbered sample 2 Calculate the grand mean and standard deviation of the subgroup samples 3 Calculate the UCL and LCL of the grouped samples 4 Create the X-bar chart 5 Provide detailed analysis of the X-bar chart To ensure that the process in actually producing individual widgets within specification limits, you have decided to use an Individual Measurement chart that will plot each of the 80 samples against a calculated UCL and LCL, as well as plotting the data against the customer specification limits. Requirements: 1 Stack the sample data (place the run data into a single column. Run 1 first, Run 2 second, etc) 2 Using the Data Analysis Toolpak, create the Descriptive Statistics for the stacked data 3 Using the Descriptive Statistics output, calculate the UCL and LCL 4 Create the IM chart using the calculated UCL and LCL 5 Recopy the stacked data, and enter the customer specification limits 6 Create the IM chart of the sample data against the customer specifications 7 Provide a detailed analysis of both IM charts Answer the questions on the \"Questions DS 1\" tab on the spreadsheet, and follow the instructions. Repeat the steps for Data Set 2. Rubric - 200 points possible The project will be graded with the following criteria: 1 Data properly displayed a 10 pts each Data Set; 20 Total 2 Descriptive Statistics output displayed (Data Sets 1 & 2) a 10 pts each Data Set; 20 Total 3 X-bar charts correctly created and displayed (Data Sets 1 & 2) a 10 pts each Data Set; 20 Total 4 X-bar charts data correctly calculated (Avg, Grand Mean, UCL, LCL) (Data Sets 1 & 2) a 10 pts each Data Set; 20 Total 5 X-bar chart detailed analysis and worded to ensure understanding (Data Sets 1 & 2) a 10 pts each Data Set; 20 Total 6 Questions for Data Set 1 answered correctly a 20 pts 7 IM charts correctly created and displayed (Data Sets 1 & 2) a 10 pts each Data Set; 20 Total 8 IM charts data correctly calculated (Avg, UCL, LCL; Specification Mean, USL, LSL) (Data Sets 1 & 2) a 10 pts each Data Set; 20 Total 9 IM chart analysis of both charts detailed and worded to ensure understanding (Data Sets 1 & 2) a 10 pts each Data Set; 20 Total 10 Questions for Data Set 2 answered correctly a 20 pts 1 2 3 4 5 6 7 Run 1 5.74 5.22 6.44 5.92 5.58 5.31 5.48 Run 2 5.65 6.26 5.23 5.16 5.66 6.29 5.06 Run 3 6.18 6.11 6.15 5.25 5.59 5.14 5.06 Run 4 5.34 5.55 5.96 5.27 6.46 5.17 5.47 Avg 5.73 5.79 5.95 5.40 5.82 5.48 5.27 GrMean 5.66 5.66 5.66 5.66 5.66 5.66 5.66 UCL 6.31 6.31 6.31 6.31 6.31 6.31 6.31 LCL 5.02 5.02 5.02 5.02 5.02 5.02 5.02 Stacked 5.74 5.22 6.44 5.92 5.58 5.31 5.48 8 9 10 11 12 13 14 15 16 17 18 19 20 5.45 6.45 5.48 5.43 6.01 5.39 5.29 5.51 5.05 6.16 5.77 5.93 5.76 5.48 5.26 5.69 6.35 5.34 5.94 6.48 6.48 5.73 5.51 5.14 5.76 5.54 5.15 5.29 5.52 6.35 5.26 6.03 5.90 6.18 5.56 6.35 6.38 5.06 6.36 5.07 5.95 5.12 5.20 5.22 6.40 5.55 5.48 5.86 5.36 5.75 5.46 5.19 5.29 5.74 5.45 5.83 5.46 5.94 5.81 5.91 5.55 5.85 5.76 5.55 5.71 5.66 5.66 5.66 5.66 5.66 5.66 5.66 5.66 5.66 5.66 5.66 5.66 5.66 6.31 6.31 6.31 6.31 6.31 6.31 6.31 6.31 6.31 6.31 6.31 6.31 6.31 5.02 5.02 5.02 5.02 5.02 5.02 5.02 5.02 5.02 5.02 5.02 5.02 5.02 5.45 6.45 5.48 5.43 6.01 5.39 5.29 5.51 5.05 6.16 5.77 5.93 5.76 Grand Mean 5.66 5.65 Std Dev 0.21 6.26 5.23 5.16 5.66 6.29 5.06 5.48 5.26 5.69 6.35 5.34 5.94 6.48 6.48 5.73 5.51 5.14 5.76 5.54 6.18 X-Bar Chart 6.40 6.20 6.00 Place X-bar X-bar 5.80 chart ONLY here 5.60 Format Axis to Max of 6.4; Min of 4.8 5.40 5.20 5.00 4.80 1 2 3 4 5 6 7 Avg 8 9 10 GrMean 11 12 13 UCL 14 15 LCL 16 17 18 19 20 5.00 4.80 1 2 3 4 5 6 7 Avg 8 9 10 GrMean 11 12 13 UCL 14 15 16 17 18 19 20 LCL The X-bar chart illistrates that the process is in control. No points are outside of the control limits. There appears to be common cause variation which is acceptable per specification limits. Points 3, 13, and 15 are near the customer specification level, and should be investigated. 6.11 6.15 5.25 5.59 5.14 5.06 5.15 5.29 5.52 6.35 5.26 6.03 5.90 6.18 5.56 6.35 6.38 5.06 6.36 5.34 5.55 5.96 5.27 6.46 5.17 5.47 5.07 5.95 5.12 5.20 5.22 6.40 5.55 5.48 5.86 5.36 5.75 5.46 5.19 Process Mean 5.66 5.66 5.66 5.66 5.66 5.66 5.66 UCL 6.98 6.98 6.98 6.98 6.98 6.98 6.98 5.66 5.66 5.66 5.66 5.66 5.66 5.66 5.66 5.66 5.66 5.66 5.66 5.66 6.98 6.98 6.98 6.98 6.98 6.98 6.98 6.98 6.98 6.98 6.98 6.98 6.98 4.35 4.35 4.35 4.35 4.35 4.35 4.35 4.35 4.35 4.35 4.35 4.35 4.35 5.45 6.45 5.48 5.43 6.01 5.39 5.29 5.51 5.05 6.16 5.77 5.93 5.76 5.50 5.50 5.50 5.50 5.50 5.50 5.50 5.50 5.50 5.50 5.50 5.50 5.50 6.00 6.00 6.00 6.00 6.00 6.00 6.00 6.00 6.00 6.00 6.00 6.00 6.00 5.00 5.00 5.00 5.00 5.00 5.00 5.00 5.00 5.00 5.00 5.00 5.00 5.00 5.66 6.98 4.35 5.65 5.50 6.00 5.00 5.66 5.66 5.66 5.66 5.66 5.66 5.66 5.66 5.66 5.66 5.66 5.66 5.66 5.66 5.66 5.66 5.66 5.66 5.66 5.66 6.98 6.98 6.98 6.98 6.98 6.98 6.98 6.98 6.98 6.98 6.98 6.98 6.98 6.98 6.98 6.98 6.98 6.98 6.98 6.98 4.35 4.35 4.35 4.35 4.35 4.35 4.35 4.35 4.35 4.35 4.35 4.35 4.35 4.35 4.35 4.35 4.35 4.35 4.35 4.35 6.26 5.23 5.16 5.66 6.29 5.06 5.48 5.26 5.69 6.35 5.34 5.94 6.48 6.48 5.73 5.51 5.14 5.76 5.54 6.18 5.50 5.50 5.50 5.50 5.50 5.50 5.50 5.50 5.50 5.50 5.50 5.50 5.50 5.50 5.50 5.50 5.50 5.50 5.50 5.50 6.00 6.00 6.00 6.00 6.00 6.00 6.00 6.00 6.00 6.00 6.00 6.00 6.00 6.00 6.00 6.00 6.00 6.00 6.00 6.00 5.00 5.00 5.00 5.00 5.00 5.00 5.00 5.00 5.00 5.00 5.00 5.00 5.00 5.00 5.00 5.00 5.00 5.00 5.00 5.00 LCL Stacked 4.35 5.74 4.35 5.22 4.35 6.44 4.35 5.92 4.35 5.58 4.35 5.31 4.35 5.48 Spec Mean Spec USL Spec LSL 5.50 6.00 5.00 5.50 6.00 5.00 5.50 6.00 5.00 5.50 6.00 5.00 5.50 6.00 5.00 5.50 6.00 5.00 5.50 6.00 5.00 IM C 7.00 6.50 6.00 5.50 5.00 4.50 4.00 Stacked Proces The Process appears to be in control. Altho intermittent shifts that may deserve some fu 5.66 5.66 5.66 5.66 5.66 5.66 5.66 5.66 5.66 5.66 5.66 5.66 5.66 5.66 6.98 6.98 6.98 6.98 6.98 6.98 6.98 6.98 6.98 6.98 6.98 6.98 6.98 6.98 4.35 4.35 4.35 4.35 4.35 4.35 4.35 4.35 4.35 4.35 4.35 4.35 4.35 4.35 6.11 6.15 5.25 5.59 5.14 5.06 5.15 5.29 5.52 6.35 5.26 6.03 5.90 6.18 5.50 5.50 5.50 5.50 5.50 5.50 5.50 5.50 5.50 5.50 5.50 5.50 5.50 5.50 6.00 6.00 6.00 6.00 6.00 6.00 6.00 6.00 6.00 6.00 6.00 6.00 6.00 6.00 5.00 5.00 5.00 5.00 5.00 5.00 5.00 5.00 5.00 5.00 5.00 5.00 5.00 5.00 5.66 5.66 5.66 5.66 5.66 5.66 5.66 5.66 5.66 5.66 5.66 5.66 5.66 5.66 5.66 5.66 5.66 5.66 5.66 5.66 5.66 5.66 5.66 5.66 5.66 6.98 6.98 6.98 6.98 6.98 6.98 6.98 6.98 6.98 6.98 6.98 6.98 6.98 6.98 6.98 6.98 6.98 6.98 6.98 6.98 6.98 6.98 6.98 6.98 6.98 4.35 4.35 4.35 4.35 4.35 4.35 4.35 4.35 4.35 4.35 4.35 4.35 4.35 4.35 4.35 4.35 4.35 4.35 4.35 4.35 4.35 4.35 4.35 4.35 4.35 5.56 6.35 6.38 5.06 6.36 5.34 5.55 5.96 5.27 6.46 5.17 5.47 5.07 5.95 5.12 5.20 5.22 6.40 5.55 5.48 5.86 5.36 5.75 5.46 5.19 5.50 5.50 5.50 5.50 5.50 5.50 5.50 5.50 5.50 5.50 5.50 5.50 5.50 5.50 5.50 5.50 5.50 5.50 5.50 5.50 5.50 5.50 5.50 5.50 5.50 6.00 6.00 6.00 6.00 6.00 6.00 6.00 6.00 6.00 6.00 6.00 6.00 6.00 6.00 6.00 6.00 6.00 6.00 6.00 6.00 6.00 6.00 6.00 6.00 6.00 5.00 5.00 5.00 5.00 5.00 When plotted against the customer specificatio out of control as there are multiple points outsi 5.00 points at or near both the UCL, and LCL which w 5.00 and analysis to improve 5.00 5.00 5.00 5.00 5.00 5.00 5.00 5.00 5.00 5.00 5.00 5.00 5.00 5.00 5.00 5.00 5.00 5.00 Customer S 6.80 6.60 6.40 6.20 6.00 5.80 5.60 5.40 5.20 5.00 4.80 Stacked Spec Mean Desc Stats Mean Standard Error Median Mode Standard Deviation Sample Variance 5.6633698021 0.0490635982 5.550592387 #N/A 0.4388381633 0.1925789336 Kurtosis Skewness Range Minimum Maximum Sum Count -1.0361339906 0.4459681872 1.4266879918 5.0517819168 6.4784699086 453.06958417 80 The Mean from Desc Stats MUST be in cell U3 TheStdDev from Desc Stats MUST be in cell U7 IM Chart 7.00 6.50 6.00 5.50 5.00 Place IM chart plotted against process mean/UCL/LCL here Format axis to max of 7.00 and min of 4.00 4.50 4.00 Stacked Process Mean UCL LCL The Process appears to be in control. Although there appears to be some intermittent shifts that may deserve some further investigating, and analysis. Customer Specification 6.80 6.60 6.40 6.20 6.00 5.80 5.60 5.40 5.20 5.00 4.80 Place IM chart using Spec Mean, USL and LSL here Format axis to max of 6.8; min of 4.8; Major unit 0.2 Stacked Spec Mean Spec USL Spec LSL When plotted against the customer specifications the process would appear to be out of control as there are multiple points outside of the UCL. There are also various points at or near both the UCL, and LCL which would warrant further investigation, and analysis to improve the process. Column1 Mean 5.6633698 Standard Erro 0.0490636 Median 5.55059239 Mode #N/A Standard Devi 0.43883816 Sample Varia 0.19257893 Kurtosis -1.03613399 Skewness 0.44596819 Range 1.42668799 Minimum 5.05178192 Maximum 6.47846991 Sum 453.069584 Count 80 You have just completed the first test run of the widget production. Answer the questions below and follow the instructions. What is the customer specification for the widget mean? What is the customer USL for the widget? What is the customer LSL for the widget? 5.50 6.00 5.00 X-bar chart What is the Grand Mean of the test data? What is the calculated UCL of the test data? What is the calculated LCL of the test data? Is there an issue with the grouped data and the customer specifications? (Y/N) 5.66 6.31 5.02 N What is the issue? All the points fall within the customer specification limits although some points warrant further investigation and analysis. IM charts What is the calculated UCL of the test data? What is the calculated LCL of the test data? How many widgets are out of specification? Is there an issue with the IM data and the customer USL and LSL? (Y/N) 6.98 4.35 20.00 Y What is the issue? With 20 points outside of the UCL would indicate that the process needs more evaluation in order to improve the process. After removing the special causes the process should be evaluated again. What is your suggestion to correct the problem? I would suggest also plotting a moving range to measure process variability. Based on your analysis, do you make corrections and do another test sample? (Y/N) Y If no, why? If yes, proceed to Data Set 2, and repeat the steps for Data Set 1 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 Day 1 5.53 5.79 5.12 5.80 5.43 5.53 5.50 5.81 5.64 5.72 5.70 5.58 5.82 5.41 5.33 5.41 5.13 5.28 5.14 5.50 Day 2 5.26 5.38 5.24 5.49 5.33 5.14 5.71 5.29 5.53 5.63 5.28 5.41 5.69 5.52 5.58 5.70 5.49 5.65 5.51 5.78 Day 3 Day 4 5.25 5.74 5.47 5.46 5.20 5.54 5.47 5.18 5.15 5.24 5.62 5.72 5.41 5.59 5.12 5.78 5.74 5.55 5.57 5.29 5.84 5.41 5.48 5.45 5.81 5.65 5.76 5.65 5.75 5.38 5.21 5.33 5.34 5.18 5.81 5.71 5.53 5.48 5.16 5.45 Grand Mean Avg 5.44 5.52 5.28 5.49 5.29 5.50 5.55 5.50 5.62 5.55 5.56 5.48 5.74 5.58 5.51 5.41 5.28 5.61 5.42 5.47 5.49 Std Dev Mean 5.49 5.49 5.49 5.49 5.49 5.49 5.49 5.49 5.49 5.49 5.49 5.49 5.49 5.49 5.49 5.49 5.49 5.49 5.49 5.49 UCL 5.84 5.84 5.84 5.84 5.84 5.84 5.84 5.84 5.84 5.84 5.84 5.84 5.84 5.84 5.84 5.84 5.84 5.84 5.84 5.84 LCL 5.14 5.14 5.14 5.14 5.14 5.14 5.14 5.14 5.14 5.14 5.14 5.14 5.14 5.14 5.14 5.14 5.14 5.14 5.14 5.14 0.12 5.38 5.24 5.49 5.33 5.14 5.71 X-bar Chart 6.00 5.80 Create and place X-bar chart here 5.60 5.29 5.53 5.40 5.20 5.00 4.80 4.60 Stacked 5.53 5.79 5.12 5.80 5.43 5.53 5.50 5.81 5.64 5.72 5.70 5.58 5.82 5.41 5.33 5.41 5.13 5.28 5.14 5.50 5.26 1 2 3 4 5 6 7 Avg 8 9 10 Mean 11 12 UCL 13 14 15 16 17 18 19 20 LCL 5.63 5.28 5.41 5.69 5.52 5.58 5.70 5.49 5.65 5.51 5.78 5.25 After revision the process is in control although I would further investigate data point 13 as it is near the UCL. After revision the process is in control although I would further investigate data point 13 as it is near the UCL. 5.47 5.20 5.47 5.15 5.62 5.41 5.12 5.74 5.57 5.84 5.48 5.81 5.76 5.75 5.21 5.34 5.81 5.53 5.16 5.74 5.46 5.54 5.18 5.24 5.72 5.59 5.78 5.55 5.29 5.41 5.45 5.65 5.65 5.38 5.33 5.18 5.71 5.48 5.45 Process Mean 5.49 5.49 5.49 5.49 5.49 5.49 5.49 5.49 5.49 5.49 5.49 5.49 5.49 5.49 5.49 5.49 5.49 5.49 5.49 5.49 5.49 UCL 6.12 6.12 6.12 6.12 6.12 6.12 6.12 6.12 6.12 6.12 6.12 6.12 6.12 6.12 6.12 6.12 6.12 6.12 6.12 6.12 6.12 5.49 5.49 5.49 5.49 5.49 5.49 6.12 6.12 6.12 6.12 6.12 6.12 4.86 4.86 4.86 4.86 4.86 4.86 5.38 5.24 5.49 5.33 5.14 5.71 5.50 5.50 5.50 5.50 5.50 5.50 6.00 6.00 6.00 6.00 6.00 6.00 5.00 5.00 5.00 5.00 5.00 5.00 5.49 6.12 4.86 5.29 5.50 6.00 5.00 5.49 6.12 4.86 5.53 5.50 6.00 5.00 5.49 5.49 5.49 5.49 5.49 5.49 5.49 5.49 6.12 6.12 6.12 6.12 6.12 6.12 6.12 6.12 4.86 4.86 4.86 4.86 4.86 4.86 4.86 4.86 5.63 5.28 5.41 5.69 5.52 5.58 5.70 5.49 5.50 5.50 5.50 5.50 5.50 5.50 5.50 5.50 6.00 6.00 6.00 6.00 6.00 6.00 6.00 6.00 5.00 5.00 5.00 5.00 5.00 5.00 5.00 5.00 5.49 6.12 4.86 5.65 5.50 6.00 5.00 5.49 6.12 4.86 5.51 5.50 6.00 5.00 5.49 5.49 6.12 6.12 4.86 4.86 5.78 5.25 5.50 5.50 6.00 6.00 5.00 5.00 LCL Stacked 4.86 5.53 4.86 5.79 4.86 5.12 4.86 5.80 4.86 5.43 4.86 5.53 4.86 5.50 4.86 5.81 4.86 5.64 4.86 5.72 4.86 5.70 4.86 5.58 4.86 5.82 4.86 5.41 4.86 5.33 4.86 5.41 4.86 5.13 4.86 5.28 4.86 5.14 4.86 5.50 4.86 5.26 Spec Mean Spec USL Spec LSL 5.50 6.00 5.00 5.50 6.00 5.00 5.50 6.00 5.00 5.50 6.00 5.00 5.50 6.00 5.00 5.50 6.00 5.00 5.50 6.00 5.00 5.50 6.00 5.00 5.50 6.00 5.00 5.50 6.00 5.00 5.50 6.00 5.00 5.50 6.00 5.00 5.50 6.00 5.00 5.50 6.00 5.00 5.50 6.00 5.00 5.50 6.00 5.00 5.50 6.00 5.00 5.50 6.00 5.00 5.50 6.00 5.00 5.50 6.00 5.00 5.50 6.00 5.00 5.49 5.49 5.49 5.49 5.49 5.49 5.49 5.49 5.49 5.49 5.49 5.49 5.49 6.12 6.12 6.12 6.12 6.12 6.12 6.12 6.12 6.12 6.12 6.12 6.12 6.12 4.86 4.86 4.86 4.86 4.86 4.86 4.86 4.86 4.86 4.86 4.86 4.86 4.86 5.47 5.20 5.47 5.15 5.62 5.41 5.12 5.74 5.57 5.84 5.48 5.81 5.76 5.50 5.50 5.50 5.50 5.50 5.50 5.50 5.50 5.50 5.50 5.50 5.50 5.50 6.00 6.00 6.00 6.00 6.00 6.00 6.00 6.00 6.00 6.00 6.00 6.00 6.00 5.00 5.00 5.00 5.00 5.00 5.00 5.00 5.00 5.00 5.00 5.00 5.00 5.00 5.49 5.49 5.49 5.49 5.49 5.49 5.49 5.49 5.49 5.49 5.49 5.49 5.49 5.49 5.49 5.49 5.49 5.49 5.49 5.49 5.49 5.49 5.49 5.49 5.49 5.49 6.12 6.12 6.12 6.12 6.12 6.12 6.12 6.12 6.12 6.12 6.12 6.12 6.12 6.12 6.12 6.12 6.12 6.12 6.12 6.12 6.12 6.12 6.12 6.12 6.12 6.12 4.86 4.86 4.86 4.86 4.86 4.86 4.86 4.86 4.86 4.86 4.86 4.86 4.86 4.86 4.86 4.86 4.86 4.86 4.86 4.86 4.86 4.86 4.86 4.86 4.86 4.86 5.75 5.21 5.34 5.81 5.53 5.16 5.74 5.46 5.54 5.18 5.24 5.72 5.59 5.78 5.55 5.29 5.41 5.45 5.65 5.65 5.38 5.33 5.18 5.71 5.48 5.45 5.50 5.50 5.50 5.50 5.50 5.50 5.50 5.50 5.50 5.50 5.50 5.50 5.50 5.50 5.50 5.50 5.50 5.50 5.50 5.50 5.50 5.50 5.50 5.50 5.50 5.50 6.00 6.00 6.00 6.00 6.00 6.00 6.00 6.00 6.00 6.00 6.00 6.00 6.00 6.00 6.00 6.00 6.00 6.00 6.00 6.00 6.00 6.00 6.00 6.00 6.00 6.00 5.00 5.00 5.00 5.00 5.00 5.00 5.00 5.00 5.00 5.00 5.00 5.00 5.00 5.00 5.00 5.00 5.00 5.00 5.00 5.00 5.00 5.00 5.00 5.00 5.00 5.00 Desc Stats Mean Standard Error Median Mode Standard Deviation Sample Variance Kurtosis Skewness Range Minimum Maximum Sum Count 5.4902861484 0.0234693674 5.4917406128 #N/A 0.2099164033 0.0440648964 -1.0470058632 -0.1185053185 0.7284070968 5.1157649083 5.8441720051 439.22289187 80 The Mean from Desc Stats MUST be in cell U3 TheStdDev from Desc Stats MUST be in cell U7 IM Chart 7.00 6.50 6.00 Create and place IM chart plotted against process mean/UCL/LCL here 5.50 5.00 4.50 4.00 Stacked Process Mean UCL LCL The procees is in control, it appears to be symmetric with about the same number above, and below the center line. Customer Specification 6.80 6.60 6.40 Customer Specification 6.80 6.60 6.40 6.20 6.00 5.80 5.60 Create and place IM chart plotted against spec mean/UCL/LCL here 5.40 5.20 5.00 4.80 Stacked Spec Mean Spec USL Spec LSL After revision the process now appears to be in control, and within customer specification limits. Column1 Mean 5.49028615 Standard Erro 0.02346937 Median 5.49174061 Mode #N/A Standard Devi 0.2099164 Sample Varia 0.0440649 Kurtosis -1.04700586 Skewness -0.11850532 Range 0.7284071 Minimum 5.11576491 Maximum 5.84417201 Sum 439.222892 Count 80 You have just completed the second test run of the widget production. Answer the questions below and follow the instructions. What is the customer specification for the widget mean? What is the customer USL for the widget? What is the customer LSL for the widget? 5.50 6.00 5.00 X-bar chart What is the Grand Mean of the test data? What is the calculated UCL of the test data? What is the calculated LCL of the test data? Is there an issue with the grouped data and the customer specifications? (Y/N) 5.49 5.84 5.14 N Why? All of the data points fall within the customer specified limits. IM charts What is the calculated UCL of the test data? What is the calculated LCL of the test data? How many widgets are out of specification? Is there an issue with the IM data and the customer USL and LSL? (Y/N) 6.12 4.86 0 N Why? The process has a normal distriibution, and meets all specified limits. What is your suggestion to correct the problem, if any? None, the process shows that it will meet customer specifications. Based on your analysis, do you make corrections and do another test sample? (Y/N) N If no, why? The charts show that the process is in control, and will meet customer specification so I would not use more time to continue testing. The basic information for you to complete the project is contained in the Course Project document. This is additional information that you will need to know in order to create the charts and perform the analyses correctly. 1. You are a widget manufacturer. This is for a special production of widgets with extremely tight tolerances. Since you specialize in widget design and production, you can assume that the processes that are similar in nature with regard to the specification limits are valid to base a process capability. 2. The initial run of the widgets should be considered a population. This is due to the fact that you make widgets similar in tolerance, but maybe not as tight as those in the contract. You can assume that the first 4 runs of the prototype widget will meet specification. However, you're more concerned with the tolerances of the machines. The question is whether the current machinery can make these close tolerance widgets. This is why you selected a sample of the population runs (each run * 20 samples) 3. The UCL and LCL are calculated using data set 1 as a population. They will provide a more reliable assessment on the manufacturing process. To calculate UCL and LCL on a population, you simply add and subtract the mean of each subgroup to 3*population std dev. To get the these values, copy the following formulas into the appropriate cells: Column F - Avg: Cell F2 =AVERAGE(B2:E2) Copy this formula to the end of the table Grand Mean: Cell F22 =AVERAGE(F2:F21) Std Dev: Cell F23 =STDEV.S(F2:F21) These formulas will also be used in Data Set 2. 4. Create a line chart using the subgroups averages, mean, UCL and LCL (columns F-I). This will be the X-bar chart for the subgroup data. Follow the format instructions on Data Set 1 tab. 5. The IM charts are columns K-R. You MUST stack the data as instructed on the project document. You must then do descriptive stats on the stacked column and place the results in the area shown. Make sure you follow the placement help for the mean and stddev!! 6. The first IM chart is plotted against the process mean and will use the desc stats for the calculations. Create a line chart using the stacked data, process mean, UCL and LCL just like the x-bar and follow the formatting directions. 7. Copy the stacked data into column \"O\" and enter the SPECIFICATION limits into the appropriate cells. Make sure you copy these data for the entire stacked column length!!! Create another IM chart based on these data. 8. For all charts you must provide a detailed analysis. Be detailed and thorough, but not lengthy. 9. Answer the Questions for data set 1. 10. After the analysis of data set 1, you have determined that process improvements must be made. You have done this and have collected another set of data to see if you are now within the customer specification limits. These data are found in data set 2. 11. Repeat steps 3-9 for data set 2. Submit ONLY the completed spreadsheet via the Project dropbox