Complete the attached MS Excel file (both worksheets / tabs) as your assignment this week. No APA formatted report required; just complete the template's questions.
monyuzulu
assignmentone.xlsxPart 1 of 2
| Week Two Assignment | |||||||||||||||||||
| Assignment: Part 1 - Is my manufacturing process in control? (Statistically speaking…) | |||||||||||||||||||
| Background on Statistical Controls in Manufacturing: A state of statistical control is defined as a process with a constant mean and variance that is not changing over time. Generally speaking, the upper and lower control limits are set at ± three standard deviations from the mean. If a normal probability distribution is assumed, these control limits will include 99.74 percent of the random variation observed. | |||||||||||||||||||
| UCL = Average + 3*Standard-Deviation | (Upper Control Limit) | ||||||||||||||||||
| CL = Average | (Center Line) | ||||||||||||||||||
| LCL = Average - 3*Standard-Deviation | (Lower Control Limit) | ||||||||||||||||||
| Activity: For our hypothetical single-port yogurt filing machine (shown below). See the filing results of the first 100 cups of yogurt and their actual weight after filing. Compute the UCL, CL, and LCL to determine if your process is in control. As a reminder, your process is "in control" if you nearly all of the products produced (99.74% or higher) are within the Upper Control Limit (UCL) and Lower Control Limit (LCL) values. Otherwise, your process is not in control and needs to be triaged. | |||||||||||||||||||
| Sample # | Actual Fill (oz) | Within Control Limits? | Statistical Calculations | ||||||||||||||||
| 1 | 8.028 | FALSE | |||||||||||||||||
| 2 | 7.986 | FALSE | Average = | oz | Tip: Reminder that to calculate the average, you can use Excel's AVERAGE function. Just add the function and select all of the actual fill examples. The same is true with standard deviation, by using Excel's STDEV formula. Select the same actual fill examples. | ||||||||||||||
| 3 | 7.985 | FALSE | Standard Deviation = | oz | |||||||||||||||
| 4 | 8.036 | FALSE | |||||||||||||||||
| 5 | 8.018 | FALSE | |||||||||||||||||
| 6 | 7.937 | FALSE | UCL = | oz | |||||||||||||||
| 7 | 8.026 | FALSE | Center Line = | oz | |||||||||||||||
| 8 | 8.050 | FALSE | LCL = | oz | |||||||||||||||
| 9 | 7.920 | FALSE | |||||||||||||||||
| 10 | 7.974 | FALSE | |||||||||||||||||
| 11 | 7.890 | FALSE | Is this process in control? (Yes/No) and Why? | ||||||||||||||||
| 12 | 8.032 | FALSE | |||||||||||||||||
| 13 | 7.948 | FALSE | |||||||||||||||||
| 14 | 8.013 | FALSE | Should I keep making yogurt? | ||||||||||||||||
| 15 | 7.943 | FALSE | |||||||||||||||||
| 16 | 8.069 | FALSE | |||||||||||||||||
| 17 | 7.918 | FALSE | |||||||||||||||||
| 18 | 7.650 | FALSE | |||||||||||||||||
| 19 | 7.895 | FALSE | |||||||||||||||||
| 20 | 7.959 | FALSE | |||||||||||||||||
| 21 | 8.030 | FALSE | |||||||||||||||||
| 22 | 7.845 | FALSE | |||||||||||||||||
| 23 | 7.952 | FALSE | |||||||||||||||||
| 24 | 8.036 | FALSE | |||||||||||||||||
| 25 | 8.004 | FALSE | |||||||||||||||||
| 26 | 7.861 | FALSE | |||||||||||||||||
| 27 | 7.899 | FALSE | |||||||||||||||||
| 28 | 8.036 | FALSE | |||||||||||||||||
| Graph Data (automatically pulls entered values) | 29 | 8.067 | FALSE | ||||||||||||||||
| UCL | CL | LCL | 30 | 8.037 | FALSE | ||||||||||||||
| 1 | 0 | 0.000 | 0 | 31 | 7.959 | FALSE | |||||||||||||
| 100 | 0.000 | 0.000 | 0.000 | 32 | 7.700 | FALSE | |||||||||||||
| 33 | 7.844 | FALSE | |||||||||||||||||
| 34 | 7.990 | FALSE | |||||||||||||||||
| 35 | 7.949 | FALSE | |||||||||||||||||
| 36 | 7.991 | FALSE | |||||||||||||||||
| 37 | 7.986 | FALSE | |||||||||||||||||
| 38 | 7.947 | FALSE | |||||||||||||||||
| 39 | 7.890 | FALSE | |||||||||||||||||
| 40 | 8.071 | FALSE | |||||||||||||||||
| 41 | 8.063 | FALSE | |||||||||||||||||
| 42 | 8.048 | FALSE | |||||||||||||||||
| 43 | 7.900 | FALSE | |||||||||||||||||
| 44 | 8.067 | FALSE | |||||||||||||||||
| 45 | 7.834 | FALSE | |||||||||||||||||
| 46 | 7.946 | FALSE | |||||||||||||||||
| 47 | 7.925 | FALSE | |||||||||||||||||
| 48 | 7.977 | FALSE | |||||||||||||||||
| 49 | 8.007 | FALSE | |||||||||||||||||
| 50 | 8.045 | FALSE | |||||||||||||||||
| 51 | 7.875 | FALSE | |||||||||||||||||
| 52 | 7.928 | FALSE | |||||||||||||||||
| 53 | 7.869 | FALSE | |||||||||||||||||
| 54 | 8.041 | FALSE | |||||||||||||||||
| 55 | 7.849 | FALSE | |||||||||||||||||
| 56 | 7.859 | FALSE | |||||||||||||||||
| 57 | 7.888 | FALSE | |||||||||||||||||
| 58 | 7.887 | FALSE | |||||||||||||||||
| 59 | 7.965 | FALSE | |||||||||||||||||
| 60 | 7.895 | FALSE | |||||||||||||||||
| 61 | 7.964 | FALSE | |||||||||||||||||
| 62 | 7.979 | FALSE | |||||||||||||||||
| 63 | 7.952 | FALSE | |||||||||||||||||
| 64 | 8.003 | FALSE | |||||||||||||||||
| 65 | 8.028 | FALSE | |||||||||||||||||
| 66 | 7.995 | FALSE | |||||||||||||||||
| 67 | 7.903 | FALSE | |||||||||||||||||
| 68 | 7.839 | FALSE | |||||||||||||||||
| 69 | 8.034 | FALSE | |||||||||||||||||
| 70 | 7.827 | FALSE | |||||||||||||||||
| 71 | 8.010 | FALSE | |||||||||||||||||
| 72 | 7.826 | FALSE | |||||||||||||||||
| 73 | 7.934 | FALSE | |||||||||||||||||
| 74 | 7.936 | FALSE | |||||||||||||||||
| 75 | 7.650 | FALSE | |||||||||||||||||
| 76 | 7.965 | FALSE | |||||||||||||||||
| 77 | 7.963 | FALSE | |||||||||||||||||
| 78 | 7.911 | FALSE | |||||||||||||||||
| 79 | 8.072 | FALSE | |||||||||||||||||
| 80 | 7.923 | FALSE | |||||||||||||||||
| 81 | 7.961 | FALSE | |||||||||||||||||
| 82 | 7.929 | FALSE | |||||||||||||||||
| 83 | 7.847 | FALSE | |||||||||||||||||
| 84 | 7.866 | FALSE | |||||||||||||||||
| 85 | 7.841 | FALSE | |||||||||||||||||
| 86 | 8.065 | FALSE | |||||||||||||||||
| 87 | 7.963 | FALSE | |||||||||||||||||
| 88 | 7.858 | FALSE | |||||||||||||||||
| 89 | 7.954 | FALSE | |||||||||||||||||
| 90 | 7.978 | FALSE | |||||||||||||||||
| 91 | 8.022 | FALSE | |||||||||||||||||
| 92 | 7.834 | FALSE | |||||||||||||||||
| 93 | 8.022 | FALSE | |||||||||||||||||
| 94 | 7.875 | FALSE | |||||||||||||||||
| 95 | 8.006 | FALSE | |||||||||||||||||
| 96 | 8.012 | FALSE | |||||||||||||||||
| 97 | 7.975 | FALSE | |||||||||||||||||
| 98 | 7.916 | FALSE | |||||||||||||||||
| 99 | 8.055 | FALSE | |||||||||||||||||
| 100 | 7.837 | FALSE | |||||||||||||||||
Yogurt Fill in Ounces
8.0281736737680873 7.9861588451169245 7.9846191970792937 8.0364573021020096 8.0184423894555596 7.9366937883180242 8.025738956149981 8.0501431232677145 7.9198610915878769 7.9737775522306995 7.890053330381952 8.0320336664575684 7.9478173692205685 8.0131653011324655 7.9432003970486145 8.0685101142754352 7.9179586141594918 7.65 7.8949990807378416 7.9594338556283004 8.0296066595894597 7.8445420747916463 7.9519425458344219 8.0360329293196227 8.0035494900103572 7.8610744660617895 7.899233642161728 8.03585850213166 8.0665533652480335 8.0368954119424316 7.9587304894731314 7.7 7.8435359590405582 7.9898029565495907 7.9487266490670656 7.9907430626068789 7.9857380824479014 7.9468174978560819 7.8896638780722581 8.0706745935935782 8.062999596936816 8.0478656640008417 7.9 8.0666788965712648 7.8344699904575075 7.9459236414269299 7.9252830316579868 7.9766981003676412 8.0074815796778296 8.0449111344826729 7.8748164534288154 7.928393746545388 7.8694911493210897 8.0410221763514969 7.8485725529156118 7.8589881575563627 7.8880615198384074 7.8868253106984083 7.9650250968305727 7.8948599592640383 7.9643067389095972 7.9794055706977352 7.9515281476759965 8.0033058613496131 8.0284574786424976 7.9945994755256065 7.9032096829952199 7.8390235759585849 8.0343593062862979 7.8271361481950752 8.0102463192870612 7.8255513331225117 7.9335959810887662 7.9361700606446632 7.65 7.9647472332234353 7.9632008432100303 7.9106715564262959 8.0715018625874606 7.9230813298074461 7.9605449987698549 7.9294806914746578 7.8472621574071235 7.8656399820822491 7.8410947501254711 8.064949321866818 7.9626724777062208 7.857555709307726 7.9536272543285227 7.978182749197301 8.0222101477150254 7.8338476368459409 8.0224640809551548 7.8749894496785826 8.0057249686272982 8.0118473243159816 7.9747503804735587 7.9159683692246769 8.0551888545321546 7.8367598115535229 UCL 1 100 0 0 CL 1 100 0 0 LCL 1 100 0 0
Sample #
Weight of Cup (oz)
Part 2 of 2
| Assignment: Part 2 - How do I control my production? | ||||||||||
| Background: Once we get the process fixed (i.e., in control), we should still regularly monitor it's performance. Here's how: Remember that our quality measure is the amount (weight) of yogurt in each cup -- not too much or you waste money and make a mess, and not too little or customers (and the FDA) will be upset. Weight is a continuous variable measured very accurately in ounces. We need to be concerned not just with the average weight, but also how well our machine is performing. To do that, we'll need an X-bar chart and an R-chart. | ||||||||||
| Question: What is "n" (the sub-group size), in the quality sampling recorded below? | n = | |||||||||
| Sample # | Time | Cup # | ||||||||
| 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | |||
| 1 | 8:00am | 8.005 | 8.200 | 7.938 | 8.012 | 7.941 | 7.954 | 7.810 | 8.088 | |
| 2 | 10:00am | 7.821 | 8.180 | 8.069 | 7.854 | 8.197 | 8.152 | 8.089 | 8.155 | |
| 3 | 12:00pm | 7.989 | 7.878 | 7.914 | 8.151 | 8.007 | 7.800 | 8.116 | 7.803 | |
| 4 | 2:00pm | 8.147 | 8.164 | 8.167 | 7.862 | 7.952 | 7.976 | 7.838 | 7.965 | |
| 5 | 4:00pm | 8.038 | 8.082 | 7.892 | 7.988 | 7.851 | 7.839 | 8.056 | 8.176 | |
| 6 | 6:00pm | 8.142 | 8.073 | 8.193 | 8.017 | 8.111 | 8.046 | 8.168 | 8.060 | |
| Activity: Next, calculate the Sample Averages based on the above samples. This will ultimately help you calculate X-bar. Then calculate the range within each sample group. To calculate the range in a sample group, use the following formula: | ||||||||||
| Range = | Maximum values of selected cells - Minimum values of selected cells | |||||||||
| MAX(cell range)-MIN(cell range) | ||||||||||
| Sample Average (X-bar) | Range within Sample | Activity: Once you calculate the sample average and range within the sample, you can calculate the Overall Averages for both. This gives the X-double bar and R-bar, which we need for our X-Bar and R-Bar charts. | ||||||||
| Overall average = | ||||||||||
| Activity: In order to create the charts, we need to populate the constants below. You can find these in our textbook in Table 9.1 | ||||||||||
| Activity: Use the formulas below to calculate the Upper Control Limit (UCL), Center Line (CL), and Lower Control Limit (LCL) values. This will automatically populate the X-Bar Chart below. | ||||||||||
| UCL = | ||||||||||
| CL = | ||||||||||
| LCL = | ||||||||||
| Activity: Use the formulas below to calculate the Upper Control Limit (UCL), Center Line (CL), and Lower Control Limit (LCL) values. This will automatically populate the R Chart below. | ||||||||||
| UCL = | ||||||||||
| CL = | ||||||||||
| LCL = | ||||||||||
| Is this process in control? (Yes/No) and Why? | Should I keep making yogurt? | |||||||||
| Graph Data (automatically pulls entered values) | ||||||||||
| X-bar | UCL | CL | LCL | |||||||
| 1 | 0.00 | 0.00 | 0.00 | |||||||
| 8 | 0.00 | 0.00 | 0.00 | |||||||
| R chart | UCL | CL | LCL | |||||||
| 1 | 0.00 | 0.00 | 0.00 | |||||||
| 8 | 0.00 | 0.00 | 0.00 |
X-bar Chart
1 2 3 4 5 6 UCL 1 8 0 0 CL 1 8 0 0 LCL 1 8 0 0
Sample #
Weight of yogurt cup (oz)
R Chart
1 2 3 4 5 6 UCL 1 8 0 0 CL 1 8 0 0 LCL 1 8 0 0
Sample #
Range of Weight of yogurt cup (oz)
