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

need it for tomorrow

d>t

>ChartDataSheet_

workshee

contains values required for MegaStat charts.

333

33333333

333333

86.6

2333333

633333333 88.11

333333

86.61

333333 87.3633333333 88.113

33333

86.6132333333 87.3633333333 88.1134333333

87.1 86.6132333333 87.3633333333 88.1134333333

86.6132333333 87.3633333333 88.1134333333 86.6132333333 87.3633333333 88.1134333333

:34.

.000

0 1.3 2.7482

2 0 1.3 2.7482
0 0 1.3 2.7482

0 1.3 2.7482 0 1.3 2.7482

																										This	t
Xbar Plot data
					8						7				6	86.6	1	3	23	33	87.	36	88.	11	4
87.						5	13	87.3		34
87.72	32	43
				87.1
87.34
Rbar Plot data 11/1					9				0			12	16	35
0.9			1.3			2.7	48
1.6
1.5
1.8

Output

Center

ed Moving Average

and

Deseasonalization

Centered

Moving

Ratio to Seasonal Cost

Year Month

Cost Average

CMA Indexes Deseasonalized 1 1 1

78.98 1.8

20 43.

39 1 2 1 2

84.

44 1.882 44.8

56 3 1 3

65.

54 1.

6 46

4 4 1 4

62.

60 1.0

57 59.

7 5 1 5

24 0.574 50.898 6 1 6

10 0.

7 63.0

54 7 1 7

91.

57 58.4

17 1.568 0.679 134.833 8 1 8

48 61.

8 0.106 0.

2 25.7

50 9 1 9

19.35 62.7

38 0.

8 0.333 58.109 10 1 10

29.02 63.777 0.

5 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.

0 1.

1.057

73.295 17 2 5

29.23 61.097 0.478

0.574

50.880 18 2 6

17.1

0 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.9

37 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.2

49 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.2

0 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.8

40 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.5

30 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.0

56 36 3 12

130.95 77.202 1.696

1.725

75.899 37 4 1

155.37 77.628 2.0

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.0

53 45 4 9

34.17

0.333

102.614 46 4 10

88.

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.0

2986 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.5

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.2

87.5

8 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

ing (fantastic exercise) & QC

Year Month Cost

78.98

84.44

65.54

62.6

29.24

18.1

91.57

6.48

19.35

29.02

94.09

101.65

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

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 Ratio to Seasonal Cost
t Year Month Cost Average CMA Indexes

1 1 1

1.820

2 1 2

1.882

3 1 3

1.416

4 1 4

1.057

5 1 5

0.574 50.898

6 1 6

0.287 63.054

7 1 7

1.568 0.679 134.833

8 1 8

0.106 0.252

9 1 9

0.308 0.333 58.109

10 1 10

63.777 0.455 0.649 44.694

11 1 11

64.396 1.461 1.324 71.060

12 1 12

64.354 1.580 1.725 58.916

13 2 1

61.188 1.943 1.820 65.301

14 2 2

58.946 1.888 1.882 59.130

15 2 3

60.224 1.256 1.416 53.391

16 2 4

1.261 1.057 73.295

17 2 5

61.097 0.478 0.574 50.880

18 2 6

60.955 0.281 0.287 59.570

19 2 7

61.209 0.271 0.679 24.428

20 2 8

0.454 0.252 109.834

21 2 9

62.385 0.463 0.333 86.668

22 2 10

62.479 0.772 0.649

23 2 11

62.233 1.079 1.324 50.714

24 2 12

62.990 1.987 1.725 72.554

25 3 1

63.068 1.608 1.820 55.730

26 3 2

62.515 1.955 1.882 64.915

27 3 3

61.535 1.617 1.416 70.244

28 3 4

61.450 0.909 1.057

29 3 5

63.820 0.704 0.574 78.226

30 3 6

66.065 0.296 0.287 68.174

31 3 7

68.553 0.233 0.679 23.530

32 3 8

71.907 0.208 0.252 59.487

33 3 9

73.665 0.245 0.333 54.145

34 3 10

75.568 0.754 0.649 87.756

35 3 11

76.899 1.499 1.324 87.056

36 3 12

77.202 1.696 1.725 75.899

37 4 1

77.628 2.001 1.820 85.359

38 4 2

78.329 1.899 1.882 79.029

39 4 3

79.500 1.448 1.416 81.280

40 4 4

81.489 1.054 1.057 81.262

41 4 5

82.196 0.570 0.574 81.534

42 4 6

83.406 0.299 0.287 86.847

43 4 7

0.679 30.686

44 4 8

0.252

45 4 9

0.333 102.614

46 4 10

0.649 136.424

47 4 11

1.324 75.999

48 4 12

1.725 101.216

Predicted values for: Deseasonalized
Seasonal
t Predicted Indexes Forecast
Jan 88.02986 1.820

Feb 88.80967 1.882

Mar 89.58948 1.416

Apr 90.36928 1.057

May 91.14909 0.574

Jun 91.92889 0.287

Jul 92.70870 0.679 63.0
Aug 93.48850 0.252 23.5
Sep 94.26831 0.333

Oct 95.04811 0.649

Nov 95.82792 1.324 126.9
Dec 96.60772 1.725

1.0

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

1 2 3 4 5

87.1 87.3

87.0 87.0

88.5

87.5

86.9 87.6

88.0 87.2 87.6 87.1

87.1 87.1 87.1 87.1 87.1

88.0

87.4 87.3

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

2.0

Week 5 –	Forecast
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]
	2009					Jan
					Feb
					Mar
					Apr
					May
					Jun
					Jul
					Aug
					Sep
					Oct
					Nov
					Dec
	2010
	2011
Centered Moving Average and Depersonalization
Deseasonalized
78.98	43.391
84.44	44.856
65.54	46.274
62.60	59.227
29.24
18.10
91.57	58.417
6.48	61.198	25.750
19.35	62.738
29.02
94.09
101.65
118.86
111.31
75.62
77.47	61.420
29.23
17.10
16.59
27.64	60.937
28.86
48.21	74.249
67.15
125.18
101.44
122.20
99.49
55.85	52.840
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	107.053
34.17
88.58
100.63
174.63
160.2
167.2
	126.9
95.5
52.4
26.4
31.4
61.7
166.7
USING THE SAME DATA AS ABOVE —-Your turn to do the same this as above.
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
8:00	87.9
8:30		86.9	87.6	87.4
9:00	88.4	88.2
9:30	86.0
10:00
10:30	86.2	87.8
11/19/2012 16:34.35.000
Note all points are in the control ranges
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

Turn in your highest-quality paper
Get a qualified writer to help you with

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

Get high-quality paper

NEW! AI matching with writer

Still stressed from student homework?

Get quality assistance from academic writers!

Order now