expert on excel using mega stat, you need to have megastat

profileYOLA
week5_forecasting26qualitycontrol.xlsx
Home>Business & Finance homework help>expert on excel using mega stat, you need to have megastat

ChartDataSheet_

This worksheet contains values required for MegaStat charts.
Xbar Plot data
87.26 86.6132333333 87.3633333333 88.1134333333
87.58 86.6132333333 87.3633333333 88.1134333333
87.72 86.6132333333 87.3633333333 88.1134333333
87.18 86.6132333333 87.3633333333 88.1134333333
87.1 86.6132333333 87.3633333333 88.1134333333
87.34 86.6132333333 87.3633333333 88.1134333333 86.6132333333 87.3633333333 88.1134333333
Rbar Plot data 11/19/2012 16:34.35.000
0.9 0 1.3 2.7482
1.6 0 1.3 2.7482
1.5 0 1.3 2.7482
2 0 1.3 2.7482
0 0 1.3 2.7482
1.8 0 1.3 2.7482 0 1.3 2.7482

Output

Centered Moving Average and Deseasonalization
Centered
Moving Ratio to Seasonal Cost
t Year Month Cost Average CMA Indexes Deseasonalized
1 1 1 78.98 1.820 43.391
2 1 2 84.44 1.882 44.856
3 1 3 65.54 1.416 46.274
4 1 4 62.60 1.057 59.227
5 1 5 29.24 0.574 50.898
6 1 6 18.10 0.287 63.054
7 1 7 91.57 58.417 1.568 0.679 134.833
8 1 8 6.48 61.198 0.106 0.252 25.750
9 1 9 19.35 62.738 0.308 0.333 58.109
10 1 10 29.02 63.777 0.455 0.649 44.694
11 1 11 94.09 64.396 1.461 1.324 71.060
12 1 12 101.65 64.354 1.580 1.725 58.916
13 2 1 118.86 61.188 1.943 1.820 65.301
14 2 2 111.31 58.946 1.888 1.882 59.130
15 2 3 75.62 60.224 1.256 1.416 53.391
16 2 4 77.47 61.420 1.261 1.057 73.295
17 2 5 29.23 61.097 0.478 0.574 50.880
18 2 6 17.10 60.955 0.281 0.287 59.570
19 2 7 16.59 61.209 0.271 0.679 24.428
20 2 8 27.64 60.937 0.454 0.252 109.834
21 2 9 28.86 62.385 0.463 0.333 86.668
22 2 10 48.21 62.479 0.772 0.649 74.249
23 2 11 67.15 62.233 1.079 1.324 50.714
24 2 12 125.18 62.990 1.987 1.725 72.554
25 3 1 101.44 63.068 1.608 1.820 55.730
26 3 2 122.20 62.515 1.955 1.882 64.915
27 3 3 99.49 61.535 1.617 1.416 70.244
28 3 4 55.85 61.450 0.909 1.057 52.840
29 3 5 44.94 63.820 0.704 0.574 78.226
30 3 6 19.57 66.065 0.296 0.287 68.174
31 3 7 15.98 68.553 0.233 0.679 23.530
32 3 8 14.97 71.907 0.208 0.252 59.487
33 3 9 18.03 73.665 0.245 0.333 54.145
34 3 10 56.98 75.568 0.754 0.649 87.756
35 3 11 115.27 76.899 1.499 1.324 87.056
36 3 12 130.95 77.202 1.696 1.725 75.899
37 4 1 155.37 77.628 2.001 1.820 85.359
38 4 2 148.77 78.329 1.899 1.882 79.029
39 4 3 115.12 79.500 1.448 1.416 81.280
40 4 4 85.89 81.489 1.054 1.057 81.262
41 4 5 46.84 82.196 0.570 0.574 81.534
42 4 6 24.93 83.406 0.299 0.287 86.847
43 4 7 20.84 0.679 30.686
44 4 8 26.94 0.252 107.053
45 4 9 34.17 0.333 102.614
46 4 10 88.58 0.649 136.424
47 4 11 100.63 1.324 75.999
48 4 12 174.63 1.725 101.216
Calculation of Seasonal Indexes
1 2 3 4 5 6 7 8 9 10 11 12
1 1.568 0.106 0.308 0.455 1.461 1.580
2 1.943 1.888 1.256 1.261 0.478 0.281 0.271 0.454 0.463 0.772 1.079 1.987
3 1.608 1.955 1.617 0.909 0.704 0.296 0.233 0.208 0.245 0.754 1.499 1.696
4 2.001 1.899 1.448 1.054 0.570 0.299
mean: 1.851 1.914 1.440 1.075 0.584 0.292 0.691 0.256 0.339 0.660 1.346 1.754 12.202
adjusted: 1.820 1.882 1.416 1.057 0.574 0.287 0.679 0.252 0.333 0.649 1.324 1.725 12.000
Regression Analysis
r² 0.194 n 48
r 0.441 k 1
Std. Error 22.473 Dep. Var. Deseasonalized
ANOVA table
Source SS df MS F p-value
Regression 5,601.7827 1 5,601.7827 11.09 .0017
Residual 23,231.9286 46 505.0419
Total 28,833.7112 47
Regression output confidence interval
variables coefficients std. error t (df=46) p-value 95% lower 95% upper
Intercept 49.8194 6.590145953808421 7.559681821289353 1.3279221317601898E-9 36.554130988226404 63.08468214507181
t 0.7798 0.2341 3.330 .0017 0.3085 1.2511
Predicted values for: Deseasonalized
95% Confidence Intervals 95% Prediction Intervals
t Predicted lower upper lower upper Leverage
49 88.02986 74.76459 101.29514 40.88890 135.17083 0.086
50 88.80967 75.13216 102.48718 41.55105 136.06829 0.091
51 89.58948 75.49603 103.68292 42.20880 136.97015 0.097
52 90.36928 75.85651 104.88205 42.86219 137.87637 0.103
53 91.14909 76.21389 106.08428 43.51125 138.78692 0.109
54 91.92889 76.56843 107.28935 44.15602 139.70176 0.115
55 92.70870 76.92035 108.49704 44.79654 140.62085 0.122
56 93.48850 77.26986 109.70714 45.43283 141.54417 0.129
57 94.26831 77.61715 110.91946 46.06495 142.47167 0.135
58 95.04811 77.96239 112.13383 46.69292 143.40330 0.143
59 95.82792 78.30573 113.35010 47.31679 144.33905 0.150
60 96.60772 78.64731 114.56813 47.93659 145.27886 0.158
Quality Control Process Charts
Sample size 5
Number of samples 6
Mean Range
Upper Control Limit, UCL 88.113 2.748
Center 87.363 1.300
Lower Control Limit, LCL 86.613 0.000
11/19/2012 16:34.35.000

Deseasonalization

Cost 1.0 2.0 3.0 4.0 5.0 6.0 7.0 8.0 9.0 10.0 11.0 12.0 13.0 14.0 15.0 16.0 17.0 18.0 19.0 20.0 21.0 22.0 23.0 24.0 25.0 26.0 27.0 28.0 29.0 30.0 31.0 32.0 33.0 34.0 35.0 36.0 37.0 38.0 39.0 40.0 41.0 42.0 43.0 44.0 45.0 46.0 47.0 48.0 78.98 84.44 65.54 62.6 29.24 18.1 91.57 6.48 19.35 29.02 94.09 101.65 118.86 111.31 75.62 77.47 29.23 17.1 16.59 27.64 28.86 48.21 67.15000000000001 125.18 101.44 122.2 99.49 55.85 44.94 19.57 15.98 14.97 18.03 56.98 115.27 130.95 155.37 148.77 115.12 85.89 46.84 24.93 20.84 26.94 34.17 88.58 100.63 174.63 Deseasonalized

1.0 2.0 3.0 4.0 5.0 6.0 7.0 8.0 9.0 10.0 11.0 12.0 13.0 14.0 15.0 16.0 17.0 18.0 19.0 20.0 21.0 22.0 23.0 24.0 25.0 26.0 27.0 28.0 29.0 30.0 31.0 32.0 33.0 34.0 35.0 36.0 37.0 38.0 39.0 40.0 41.0 42.0 43.0 44.0 45.0 46.0 47.0 48.0 43.39096623911426 44.85605375214149 46.27416789973552 59.2266703092875 50.89763461711603 63.05354128008874 134.8333225075606 25.74987388404096 58.10881216450544 44.69441097039092 71.0599262409136 58.91647922984285 65.30071216993062 59.12988326801124 53.39109820839182 73.29536979010387 50.8802277653318 59.56992021489047 24.42814044338136 109.83433860415 86.66771674768098 74.24939878988787 50.71393396830002 72.5544994588463 55.73030660035135 64.91484804016685 70.24438456430708 52.84040793568222 78.2263919183719 68.1744642459302 23.52993877548126 59.48697716729833 54.14480017188801 87.75629004455115 87.05577317238932 75.8987993620061 85.35900765473768 79.02931213531608 81.27986281076524 81.26164078058631 81.5336937573774 86.84667315539293 30. 68610288366893 107.0527164253185 102.6138558998011 136.4242220453903 75.9991537636639 101.2157871904324

Month

Cost

Control Chart for the Mean

87.26 87.58 87.72 87.17999999999999 87.1 87.34 86.61323333333333 86.61323333333333 86.61323333333333 86.61323333333333 86.61323333333333 86.61323333333333 87.36333333333333 87.36333333333333 87.36333333333333 87.36333333333333 87.36333333333333 87.36333333333333 88.11343333333333 88.11343333333333 88.11343333333333 88.11343333333333 88.11343333333333 88.11343333333333

86.61323333333333

87.36333333333333

88.11343333333333

Sample Number

Sample Mean

Control Chart for the Range

0.900000000000006 1.599999999999994 1.5 2.0 0.0 1.799999999999997 0.0 0.0 0.0 0.0 0.0 0.0 1.3 1.3 1.3 1.3 1.3 1.3 2.748199999999999 2.748199999999999 2.748199999999999 2.748199999999999 2.748199999999999 2.748199999999999

0.0

1.3

2.748199999999999

Sample Number

Sample Range

Sheet1

Week 5 - Forecasting (fantastic exercise) & QC
You have a job, if you have one, because someone forecasted they could pay you. This week you will learn how to do rather sophisticated forecasting in a simple way. I hope you take this out of this class as you can use it personally.
Sample - forecast monthly cost and graph the forecasted cost using Deseasonization and regression
[Deseasonalize the data and forecast for 2004 by month]
Year Month Cost
2009 Jan 78.98
Feb 84.44
Mar 65.54
Apr 62.6
May 29.24
Jun 18.1
Jul 91.57
Aug 6.48
Sep 19.35
Oct 29.02
Nov 94.09
Dec 101.65
2010 Jan 118.86
Feb 111.31
Mar 75.62
Apr 77.47
May 29.23
Jun 17.1
Jul 16.59
Aug 27.64
Sep 28.86
Oct 48.21
Nov 67.15
Dec 125.18
2011 Jan 101.44
Feb 122.2
Mar 99.49
Apr 55.85
May 44.94
Jun 19.57
Jul 15.98
Aug 14.97
Sep 18.03
Oct 56.98
Nov 115.27
Dec 130.95
2012 Jan 155.37
Feb 148.77
Mar 115.12
Apr 85.89
May 46.84
Jun 24.93
Jul 20.84
Aug 26.94
Sep 34.17
Oct 88.58
Nov 100.63
Dec 174.63
Centered Moving Average and Depersonalization
Centered
Moving Ratio to Seasonal Cost
t Year Month Cost Average CMA Indexes Deseasonalized
1 1 1 78.98 1.820 43.391
2 1 2 84.44 1.882 44.856
3 1 3 65.54 1.416 46.274
4 1 4 62.60 1.057 59.227
5 1 5 29.24 0.574 50.898
6 1 6 18.10 0.287 63.054
7 1 7 91.57 58.417 1.568 0.679 134.833
8 1 8 6.48 61.198 0.106 0.252 25.750
9 1 9 19.35 62.738 0.308 0.333 58.109
10 1 10 29.02 63.777 0.455 0.649 44.694
11 1 11 94.09 64.396 1.461 1.324 71.060
12 1 12 101.65 64.354 1.580 1.725 58.916
13 2 1 118.86 61.188 1.943 1.820 65.301
14 2 2 111.31 58.946 1.888 1.882 59.130
15 2 3 75.62 60.224 1.256 1.416 53.391
16 2 4 77.47 61.420 1.261 1.057 73.295
17 2 5 29.23 61.097 0.478 0.574 50.880
18 2 6 17.10 60.955 0.281 0.287 59.570
19 2 7 16.59 61.209 0.271 0.679 24.428
20 2 8 27.64 60.937 0.454 0.252 109.834
21 2 9 28.86 62.385 0.463 0.333 86.668
22 2 10 48.21 62.479 0.772 0.649 74.249
23 2 11 67.15 62.233 1.079 1.324 50.714
24 2 12 125.18 62.990 1.987 1.725 72.554
25 3 1 101.44 63.068 1.608 1.820 55.730
26 3 2 122.20 62.515 1.955 1.882 64.915
27 3 3 99.49 61.535 1.617 1.416 70.244
28 3 4 55.85 61.450 0.909 1.057 52.840
29 3 5 44.94 63.820 0.704 0.574 78.226
30 3 6 19.57 66.065 0.296 0.287 68.174
31 3 7 15.98 68.553 0.233 0.679 23.530
32 3 8 14.97 71.907 0.208 0.252 59.487
33 3 9 18.03 73.665 0.245 0.333 54.145
34 3 10 56.98 75.568 0.754 0.649 87.756
35 3 11 115.27 76.899 1.499 1.324 87.056
36 3 12 130.95 77.202 1.696 1.725 75.899
37 4 1 155.37 77.628 2.001 1.820 85.359
38 4 2 148.77 78.329 1.899 1.882 79.029
39 4 3 115.12 79.500 1.448 1.416 81.280
40 4 4 85.89 81.489 1.054 1.057 81.262
41 4 5 46.84 82.196 0.570 0.574 81.534
42 4 6 24.93 83.406 0.299 0.287 86.847
43 4 7 20.84 0.679 30.686
44 4 8 26.94 0.252 107.053
45 4 9 34.17 0.333 102.614
46 4 10 88.58 0.649 136.424
47 4 11 100.63 1.324 75.999
48 4 12 174.63 1.725 101.216
Predicted values for: Deseasonalized
Seasonal
t Predicted Indexes Forecast
Jan 88.02986 1.820 160.2
Feb 88.80967 1.882 167.2
Mar 89.58948 1.416 126.9
Apr 90.36928 1.057 95.5
May 91.14909 0.574 52.4
Jun 91.92889 0.287 26.4
Jul 92.70870 0.679 63.0
Aug 93.48850 0.252 23.5
Sep 94.26831 0.333 31.4
Oct 95.04811 0.649 61.7
Nov 95.82792 1.324 126.9
Dec 96.60772 1.725 166.7
1.0 USING THE SAME DATA AS ABOVE ----Your turn to do the same this as above.
Year Month Cost
2009 Jan 78.98
Feb 84.44
Mar 65.54
Apr 62.6
May 29.24
Jun 18.1
Jul 91.57
Aug 6.48
Sep 19.35
Oct 29.02
Nov 94.09
Dec 101.65
2010 Jan 118.86
Feb 111.31
Mar 75.62
Apr 77.47
May 29.23
Jun 17.1
Jul 16.59
Aug 27.64
Sep 28.86
Oct 48.21
Nov 67.15
Dec 125.18
2011 Jan 101.44
Feb 122.2
Mar 99.49
Apr 55.85
May 44.94
Jun 19.57
Jul 15.98
Aug 14.97
Sep 18.03
Oct 56.98
Nov 115.27
Dec 130.95
2012 Jan 155.37
Feb 148.77
Mar 115.12
Apr 85.89
May 46.84
Jun 24.93
Jul 20.84
Aug 26.94
Sep 34.17
Oct 88.58
Nov 100.63
Dec 174.63
Now we end the class showing the QC is all based upon statistics
A new machine has jus been installed to cut and rough shape slugs. The slugs are then transferred to a
precision grinder. One of the measurements is the outside diameter. The quality control inspector
randomly selected five slugs each hour, measured the outside diameter, and recorded the results.
The measurements follow. Develop/make a Six Sigma Quality Control Chart.
Outside Diameter
Time 1 2 3 4 5
8:00 87.1 87.3 87.9 87.0 87.0
8:30 86.9 88.5 87.6 87.5 87.4
9:00 87.5 88.4 86.9 87.6 88.2
9:30 86.0 88.0 87.2 87.6 87.1
10:00 87.1 87.1 87.1 87.1 87.1
10:30 88.0 86.2 87.4 87.3 87.8
Quality Control Process Charts
Sample size 5
Number of samples 6
Mean Range
Upper Control Limit, UCL 88.113 2.748
Center 87.363 1.300
Lower Control Limit, LCL 86.613 0.000
11/19/2012 16:34.35.000
Note all points are in the control ranges
2.0 Your Turn - you do yourws in the final test

&P of &N

Forecast 2013

Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec 160.231479316853 167.18119183200 72 126.8892444654047 95.51637676417315 52.36391211917345 26.38888939310198 62.96170084048643 23.52654201405185 31.3909658139636 61.71456680247302 126.8851407085733 166.6795967518449

Control Chart for the Mean

87.26 87.58 87.72 87.17999999999999 87.1 87.34 86.61323333333333 86.61323333333333 86.61323333333333 86.61323333333333 86.61323333333333 86.61323333333333 87.36333333333333 87.36333333333333 87.36333333333333 87.36333333333333 87.36333333333333 87.36333333333333 88.11343333333333 88.11343333333333 88.11343333333333 88.11343333333333 88.11343333333333 88.11343333333333

86.61323333333333

87.36333333333333

88.11343333333333

Sample Number

Sample Mean

Control Chart for the Range

0.900000000000006 1.599999999999994 1.5 2.0 0.0 1.799999999999997 0.0 0.0 0.0 0.0 0.0 0.0 1.3 1.3 1.3 1.3 1.3 1.3 2.748199999999999 2.748199999999999 2.748199999999999 2.748199999999999 2.748199999999999 2.748199999999999

0.0

1.3

2.748199999999999

Sample Number

Sample Range

Sheet2

Sheet3