SPC
Year Quarter
1I
II p = 4 (even)
III
IV
2I
II
III
IV
III
IV
3,486
13,186
5,448
3,485
7,728
16,591
4,742
13,673
2,737
4,315
5,648
3,696
8,253
4,382
4,120
2,766
5,491
8,236
3,316
3,654
8,680
5,695
1,953
4,843
13,097
Black Plastic Demand
(’000 lbs)
Clear Plastic Demand
(’000 lbs)
3,200
7,658
2,250
1,737
4,420
2,384
3,459
7,056
2,412
7,269
3,514
2,143
10000
12000
14000
16000
18000
Quarter
Quarterly Historical Demand for Clear and Black Plastic Containers
Years 1-5
II 30 21,898
III 31 11,181
IV 32 5,000
CR – 12/3/2020 9:50 AM Chapter 7 SPC case study answer.xlsx – Static Forecast
SPC
Year Quarter
1I
II
III
IV
2I
II
III
IV
3I
II
III
IV
4I
4,382
4,843
8,236
3,316
Quarter Period Demand DtLevel Trend
Seasonal
Forecast EtAtBias MSE MAD
Percent
Error
MAPE TS Deseasonalized Demand Regression
III 11 2,556 5,088 227 0.70 3,549 993 993 862 290,567 406 39 11.36 2.12 ANOVA
II 26 0.60 5,085 II 0.599
Quarter Period Demand DtLevel Trend
Seasonal
Factor St
Forecast EtAtBias MSE MAD
Percent
Error
MAPE TS Deseasonalized Demand Regression
3,612 264
I 13,200 3,876 264 0.76 2,952 (248) 248 (248) 61,682 248 87.76 -1.00
II 27,658 4,140 264 1.90 7,862 204 204 (44) 51,747 226 35.22 -0.19 SUMMARY OUTPUT
III 34,420 4,404 264 0.95 4,175 (245) 245 (289) 54,508 233 65.32 -1.24
IV 42,384 4,668 264 0.41 1,936 (448) 448 (737) 91,129 287 19 8.70 -2.57 Regression Statistics
2,412
7,269
3,514
2,143
3,459
7,056
4,120
2,766
2,556
8,253
5,491
Black Plastic Demand
(‘000 lbs)
Clear Plastic Demand
(‘000 lbs)
3,200
7,658
2,250
1,737
4,420
2,384
3,654
8,680
5,695
1,953
4,742
13,673
6,640
2,737
3,486
6000
8000
10000
12000
14000
16000
18000
(‘000 lbs)
Quarterly Historical Demand for Clear and Black Plastic Containers
Years 1-5
III 19 8,236 8,627 264 0.95 8,179 (57) 57 (181) 827,207 671 112.18 -0.27 X Variable 1 263.94 27.65 9.55 0.00 204.64 323.24 204.64 323.24
IV 20 3,316 8,891 264 0.41 3,687 371 371 190 792,727 656 11 12.13 0.29
I 21 0.76 5,602 Estimate of standard deviation of forecast error: 820
II 22 1.90 14,403
III 23 0.95 7,405 Average of Seasonal Factor St
IV 24 0.41 3,333 Quarter Total
I 25 0.76 6,293 I 0.762
II 26 1.90 16,126 II 1.899
III 27 0.95 8,265 III 0.948
IV 28 0.41 3,709 IV 0.415
I 29 0.76 6,984
II 30 1.90 17,850
III 31 0.95 9,125
IV 32 0.41 4,086
SPC
Year Quarter
2005 I
II p = 4 (even)
III
IV
2006 I
II
III
IV
2007 I
II
III
IV
2008 I
II
III
IV
2009 I
II
III
IV
Black Plastics
Quarter Period Demand Dt
Deseasonalized
Demand Dt
Dt (based on
regression)
Seasonal Factor StForecast EtAtBias MSE MAD Percent Error MAPE TS Deseasonalized Demand Regression
I 12250 =$S$46+B29*$S$47 =C29/E29
=($S$46+B29*$S$47)*$S$53
=G29-C29 =ABS(I29) =SUM($I$29:I29) =SUMSQ($I$29:I29)/B29
=SUM($J$29:J29)/B29
=J29/C29*100
=AVERAGE($N$29:N29)
=SUM($I$29:I29)/M29
II 21737 =$S$46+B30*$S$47 =C30/E30
=($S$46+B30*$S$47)*$S$54
=G30-C30 =ABS(I30) =SUM($I$29:I30) =SUMSQ($I$29:I30)/B30
=SUM($J$29:J30)/B30
=J30/C30*100
=AVERAGE($N$29:N30)
=SUM($I$29:I30)/M30 SUMMARY OUTPUT
III 32412
=(C29+C33+2*SUM(C30:C33))/(2*4)
=$S$46+B31*$S$47 =C31/E31
=($S$46+B31*$S$47)*$S$55
=G31-C31 =ABS(I31) =SUM($I$29:I31) =SUMSQ($I$29:I31)/B31
=SUM($J$29:J31)/B31
=J31/C31*100
=AVERAGE($N$29:N31)
=SUM($I$29:I31)/M31
IV 47269
=(C30+C34+2*SUM(C31:C34))/(2*4)
=$S$46+B32*$S$47 =C32/E32
=($S$46+B32*$S$47)*$S$56
=G32-C32 =ABS(I32) =SUM($I$29:I32) =SUMSQ($I$29:I32)/B32
=SUM($J$29:J32)/B32
=J32/C32*100
=AVERAGE($N$29:N32)
=SUM($I$29:I32)/M32 Regression Statistics
I 53514
=(C31+C35+2*SUM(C32:C35))/(2*4)
=$S$46+B33*$S$47 =C33/E33
=($S$46+B33*$S$47)*$S$53
=G33-C33 =ABS(I33) =SUM($I$29:I33) =SUMSQ($I$29:I33)/B33
=SUM($J$29:J33)/B33
=J33/C33*100
=AVERAGE($N$29:N33)
=SUM($I$29:I33)/M33 Multiple R
0.8875503453542
II 62143
=(C32+C36+2*SUM(C33:C36))/(2*4)
=$S$46+B34*$S$47 =C34/E34
=($S$46+B34*$S$47)*$S$54
=G34-C34 =ABS(I34) =SUM($I$29:I34) =SUMSQ($I$29:I34)/B34
=SUM($J$29:J34)/B34
=J34/C34*100
=AVERAGE($N$29:N34)
=SUM($I$29:I34)/M34 R Square
0.78774561553836
III 73459
=(C33+C37+2*SUM(C34:C37))/(2*4)
=$S$46+B35*$S$47 =C35/E35
=($S$46+B35*$S$47)*$S$55
=G35-C35 =ABS(I35) =SUM($I$29:I35) =SUMSQ($I$29:I35)/B35
=SUM($J$29:J35)/B35
=J35/C35*100
=AVERAGE($N$29:N35)
=SUM($I$29:I35)/M35 Adjusted R Square
0.772584588076814
IV 87056
=(C34+C38+2*SUM(C35:C38))/(2*4)
=$S$46+B36*$S$47 =C36/E36
=($S$46+B36*$S$47)*$S$56
=G36-C36 =ABS(I36) =SUM($I$29:I36) =SUMSQ($I$29:I36)/B36
=SUM($J$29:J36)/B36
=J36/C36*100
=AVERAGE($N$29:N36)
=SUM($I$29:I36)/M36 Standard Error
800.986989959857
I 94120
=(C35+C39+2*SUM(C36:C39))/(2*4)
=$S$46+B37*$S$47 =C37/E37
=($S$46+B37*$S$47)*$S$53
=G37-C37 =ABS(I37) =SUM($I$29:I37) =SUMSQ($I$29:I37)/B37
=SUM($J$29:J37)/B37
=J37/C37*100
=AVERAGE($N$29:N37)
=SUM($I$29:I37)/M37 Observations 16
II 10 2766
=(C36+C40+2*SUM(C37:C40))/(2*4)
=$S$46+B38*$S$47 =C38/E38
=($S$46+B38*$S$47)*$S$54
=G38-C38 =ABS(I38) =SUM($I$29:I38) =SUMSQ($I$29:I38)/B38
=SUM($J$29:J38)/B38
=J38/C38*100
=AVERAGE($N$29:N38)
=SUM($I$29:I38)/M38
III 11 2556
=(C37+C41+2*SUM(C38:C41))/(2*4)
=$S$46+B39*$S$47 =C39/E39
=($S$46+B39*$S$47)*$S$55
=G39-C39 =ABS(I39) =SUM($I$29:I39) =SUMSQ($I$29:I39)/B39
=SUM($J$29:J39)/B39
=J39/C39*100
=AVERAGE($N$29:N39)
=SUM($I$29:I39)/M39 ANOVA
IV 12 8253
=(C38+C42+2*SUM(C39:C42))/(2*4)
=$S$46+B40*$S$47 =C40/E40
=($S$46+B40*$S$47)*$S$56
=G40-C40 =ABS(I40) =SUM($I$29:I40) =SUMSQ($I$29:I40)/B40
=SUM($J$29:J40)/B40
=J40/C40*100
=AVERAGE($N$29:N40)
=SUM($I$29:I40)/M40 df SS MS F Significance F
I 13 5491
=(C39+C43+2*SUM(C40:C43))/(2*4)
=$S$46+B41*$S$47 =C41/E41
=($S$46+B41*$S$47)*$S$53
=G41-C41 =ABS(I41) =SUM($I$29:I41) =SUMSQ($I$29:I41)/B41
=SUM($J$29:J41)/B41
=J41/C41*100
=AVERAGE($N$29:N41)
=SUM($I$29:I41)/M41 Regression 133335600.6266544 33335600.6266544 51.9585903749854
4.50680003643016E-06
II 14 4382
=(C40+C44+2*SUM(C41:C44))/(2*4)
=$S$46+B42*$S$47 =C42/E42
=($S$46+B42*$S$47)*$S$54
=G42-C42 =ABS(I42) =SUM($I$29:I42) =SUMSQ($I$29:I42)/B42
=SUM($J$29:J42)/B42
=J42/C42*100
=AVERAGE($N$29:N42)
=SUM($I$29:I42)/M42 Residual 14 8982122.21318934 641580.158084953
III 15 4315
=(C41+C45+2*SUM(C42:C45))/(2*4)
=$S$46+B43*$S$47 =C43/E43
=($S$46+B43*$S$47)*$S$55
=G43-C43 =ABS(I43) =SUM($I$29:I43) =SUMSQ($I$29:I43)/B43
=SUM($J$29:J43)/B43
=J43/C43*100
=AVERAGE($N$29:N43)
=SUM($I$29:I43)/M43 Total 15 42317722.8398437
IV 16 12035
=(C42+C46+2*SUM(C43:C46))/(2*4)
=$S$46+B44*$S$47 =C44/E44
=($S$46+B44*$S$47)*$S$56
=G44-C44 =ABS(I44) =SUM($I$29:I44) =SUMSQ($I$29:I44)/B44
=SUM($J$29:J44)/B44
=J44/C44*100
=AVERAGE($N$29:N44)
=SUM($I$29:I44)/M44
I 17 5648
=(C43+C47+2*SUM(C44:C47))/(2*4)
=$S$46+B45*$S$47 =C45/E45
=($S$46+B45*$S$47)*$S$53
=G45-C45 =ABS(I45) =SUM($I$29:I45) =SUMSQ($I$29:I45)/B45
=SUM($J$29:J45)/B45
=J45/C45*100
=AVERAGE($N$29:N45)
=SUM($I$29:I45)/M45 Coefficients Standard Error t Stat P-value Lower 95% Upper 95% Lower 95.0% Upper 95.0%
II 18 3696
=(C44+C48+2*SUM(C45:C48))/(2*4)
=$S$46+B46*$S$47 =C46/E46
=($S$46+B46*$S$47)*$S$54
=G46-C46 =ABS(I46) =SUM($I$29:I46) =SUMSQ($I$29:I46)/B46
=SUM($J$29:J46)/B46
=J46/C46*100
=AVERAGE($N$29:N46)
=SUM($I$29:I46)/M46 Intercept
3052.16378676471
498.13709071066 6.12715624610563
0.0000261855643684614
1983.76503555567 4120.56253797374 1983.76503555567 4120.56253797374
III 19 4843 =$S$46+B47*$S$47 =C47/E47
=($S$46+B47*$S$47)*$S$55
=G47-C47 =ABS(I47) =SUM($I$29:I47) =SUMSQ($I$29:I47)/B47
=SUM($J$29:J47)/B47
=J47/C47*100
=AVERAGE($N$29:N47)
=SUM($I$29:I47)/M47 X Variable 1
313.122794117647
43.4396186096948 7.20823073818987
4.50680003643023E-06
219.95399552571 406.291592709583 219.95399552571 406.291592709583
IV 20 13097 =$S$46+B48*$S$47 =C48/E48
=($S$46+B48*$S$47)*$S$56
=G48-C48 =ABS(I48) =SUM($I$29:I48) =SUMSQ($I$29:I48)/B48
=SUM($J$29:J48)/B48
=J48/C48*100
=AVERAGE($N$29:N48)
=SUM($I$29:I48)/M48
I 21
=($S$46+B49*$S$47)*$S$53
Estimate of standard deviation of forecast error:
=1.25*M48
II 22
=($S$46+B50*$S$47)*$S$54
III 23
=($S$46+B51*$S$47)*$S$55
Average of Seasonal Factor St
IV 24
=($S$46+B52*$S$47)*$S$56
Quarter Total
I 25
=($S$46+B53*$S$47)*$S$53
I
0.712104547192459
II 26
=($S$46+B54*$S$47)*$S$53
II
0.482573350145003
III 27
=($S$46+B55*$S$47)*$S$53
III
0.566574797247742
IV 28
=($S$46+B56*$S$47)*$S$53
IV
1.48599041994634
I 29
=($S$46+B57*$S$47)*$S$53
II 30
=($S$46+B58*$S$47)*$S$53
III 31
=($S$46+B59*$S$47)*$S$53
IV 32
=($S$46+B60*$S$47)*$S$53
Clear Plastics
Quarter Period Demand Dt
Deseasonalized
Demand Dt
Dt (based on
regression)
Seasonal Factor StForecast EtAtBias MSE MAD Percent Error MAPE TS Deseasonalized Demand Regression
I 13200 =$S$82+B65*$S$83 =C65/E65
=($S$82+B65*$S$83)*$S$89
=G65-C65 =ABS(I65) =SUM($I$65:I65) =SUMSQ($I$65:I65)/B65
=SUM($J$65:J65)/B65
=J65/C65*100
=AVERAGE($N$65:N65)
=SUM($I$65:I65)/M65
II 27658 =$S$82+B66*$S$83 =C66/E66
=($S$82+B66*$S$83)*$S$90
=G66-C66 =ABS(I66) =SUM($I$65:I66) =SUMSQ($I$65:I66)/B66
=SUM($J$65:J66)/B66
=J66/C66*100
=AVERAGE($N$65:N66)
=SUM($I$65:I66)/M66 SUMMARY OUTPUT
III 34420
=(C65+C69+2*SUM(C66:C68))/(2*4)
=$S$82+B67*$S$83 =C67/E67
=($S$82+B67*$S$83)*$S$91
=G67-C67 =ABS(I67) =SUM($I$65:I67) =SUMSQ($I$65:I67)/B67
=SUM($J$65:J67)/B67
=J67/C67*100
=AVERAGE($N$65:N67)
=SUM($I$65:I67)/M67
IV 42384
=(C66+C70+2*SUM(C67:C69))/(2*4)
=$S$82+B68*$S$83 =C68/E68
=($S$82+B68*$S$83)*$S$92
=G68-C68 =ABS(I68) =SUM($I$65:I68) =SUMSQ($I$65:I68)/B68
=SUM($J$65:J68)/B68
=J68/C68*100
=AVERAGE($N$65:N68)
=SUM($I$65:I68)/M68 Regression Statistics
I 53654
=(C67+C71+2*SUM(C68:C70))/(2*4)
=$S$82+B69*$S$83 =C69/E69
=($S$82+B69*$S$83)*$S$89
=G69-C69 =ABS(I69) =SUM($I$65:I69) =SUMSQ($I$65:I69)/B69
=SUM($J$65:J69)/B69
=J69/C69*100
=AVERAGE($N$65:N69)
=SUM($I$65:I69)/M69 Multiple R
0.931048613999323
II 68680
=(C68+C72+2*SUM(C69:C71))/(2*4)
=$S$82+B70*$S$83 =C70/E70
=($S$82+B70*$S$83)*$S$90
=G70-C70 =ABS(I70) =SUM($I$65:I70) =SUMSQ($I$65:I70)/B70
=SUM($J$65:J70)/B70
=J70/C70*100
=AVERAGE($N$65:N70)
=SUM($I$65:I70)/M70 R Square
0.86685152163006
III 75695
=(C69+C73+2*SUM(C70:C72))/(2*4)
=$S$82+B71*$S$83 =C71/E71
=($S$82+B71*$S$83)*$S$91
=G71-C71 =ABS(I71) =SUM($I$65:I71) =SUMSQ($I$65:I71)/B71
=SUM($J$65:J71)/B71
=J71/C71*100
=AVERAGE($N$65:N71)
=SUM($I$65:I71)/M71 Adjusted R Square
0.857340916032208
IV 81953
=(C70+C74+2*SUM(C71:C73))/(2*4)
=$S$82+B72*$S$83 =C72/E72
=($S$82+B72*$S$83)*$S$92
=G72-C72 =ABS(I72) =SUM($I$65:I72) =SUMSQ($I$65:I72)/B72
=SUM($J$65:J72)/B72
=J72/C72*100
=AVERAGE($N$65:N72)
=SUM($I$65:I72)/M72 Standard Error
509.772991726529
I 94742
=(C71+C75+2*SUM(C72:C74))/(2*4)
=$S$82+B73*$S$83 =C73/E73
=($S$82+B73*$S$83)*$S$89
=G73-C73 =ABS(I73) =SUM($I$65:I73) =SUMSQ($I$65:I73)/B73
=SUM($J$65:J73)/B73
=J73/C73*100
=AVERAGE($N$65:N73)
=SUM($I$65:I73)/M73 Observations 16
II 10 13673
=(C72+C76+2*SUM(C73:C75))/(2*4)
=$S$82+B74*$S$83 =C74/E74
=($S$82+B74*$S$83)*$S$90
=G74-C74 =ABS(I74) =SUM($I$65:I74) =SUMSQ($I$65:I74)/B74
=SUM($J$65:J74)/B74
=J74/C74*100
=AVERAGE($N$65:N74)
=SUM($I$65:I74)/M74
III 11 6640
=(C73+C77+2*SUM(C74:C76))/(2*4)
=$S$82+B75*$S$83 =C75/E75
=($S$82+B75*$S$83)*$S$91
=G75-C75 =ABS(I75) =SUM($I$65:I75) =SUMSQ($I$65:I75)/B75
=SUM($J$65:J75)/B75
=J75/C75*100
=AVERAGE($N$65:N75)
=SUM($I$65:I75)/M75 ANOVA
IV 12 2737
=(C74+C78+2*SUM(C75:C77))/(2*4)
=$S$82+B76*$S$83 =C76/E76
=($S$82+B76*$S$83)*$S$92
=G76-C76 =ABS(I76) =SUM($I$65:I76) =SUMSQ($I$65:I76)/B76
=SUM($J$65:J76)/B76
=J76/C76*100
=AVERAGE($N$65:N76)
=SUM($I$65:I76)/M76 df SS MS F Significance F
I 13 3486
=(C75+C79+2*SUM(C76:C78))/(2*4)
=$S$82+B77*$S$83 =C77/E77
=($S$82+B77*$S$83)*$S$89
=G77-C77 =ABS(I77) =SUM($I$65:I77) =SUMSQ($I$65:I77)/B77
=SUM($J$65:J77)/B77
=J77/C77*100
=AVERAGE($N$65:N77)
=SUM($I$65:I77)/M77 Regression 123685916.2135225 23685916.2135225 91.1457753884533
1.65357109492825E-07
II 14 13186
=(C76+C80+2*SUM(C77:C79))/(2*4)
=$S$82+B78*$S$83 =C78/E78
=($S$82+B78*$S$83)*$S$90
=G78-C78 =ABS(I78) =SUM($I$65:I78) =SUMSQ($I$65:I78)/B78
=SUM($J$65:J78)/B78
=J78/C78*100
=AVERAGE($N$65:N78)
=SUM($I$65:I78)/M78 Residual 14 3638159.04331342 259868.503093816
III 15 5448
=(C77+C81+2*SUM(C78:C80))/(2*4)
=$S$82+B79*$S$83 =C79/E79
=($S$82+B79*$S$83)*$S$91
=G79-C79 =ABS(I79) =SUM($I$65:I79) =SUMSQ($I$65:I79)/B79
=SUM($J$65:J79)/B79
=J79/C79*100
=AVERAGE($N$65:N79)
=SUM($I$65:I79)/M79 Total 15 27324075.2568359
IV 16 3485
=(C78+C82+2*SUM(C79:C81))/(2*4)
=$S$82+B80*$S$83 =C80/E80
=($S$82+B80*$S$83)*$S$92
=G80-C80 =ABS(I80) =SUM($I$65:I80) =SUMSQ($I$65:I80)/B80
=SUM($J$65:J80)/B80
=J80/C80*100
=AVERAGE($N$65:N80)
=SUM($I$65:I80)/M80
I 17 7728
=(C79+C83+2*SUM(C80:C82))/(2*4)
=$S$82+B81*$S$83 =C81/E81
=($S$82+B81*$S$83)*$S$89
=G81-C81 =ABS(I81) =SUM($I$65:I81) =SUMSQ($I$65:I81)/B81
=SUM($J$65:J81)/B81
=J81/C81*100
=AVERAGE($N$65:N81)
=SUM($I$65:I81)/M81 Coefficients Standard Error t Stat P-value Lower 95% Upper 95% Lower 95.0% Upper 95.0%
II 18 16591
=(C80+C84+2*SUM(C81:C83))/(2*4)
=$S$82+B82*$S$83 =C82/E82
=($S$82+B82*$S$83)*$S$90
=G82-C82 =ABS(I82) =SUM($I$65:I82) =SUMSQ($I$65:I82)/B82
=SUM($J$65:J82)/B82
=J82/C82*100
=AVERAGE($N$65:N82)
=SUM($I$65:I82)/M82 Intercept
3611.97886029412
317.02991210163 11.3931800199857
1.81539310264743E-08
2932.01672034974 4291.94100023851 2932.01672034974 4291.94100023851
III 19 8236 =$S$82+B83*$S$83 =C83/E83
=($S$82+B83*$S$83)*$S$91
=G83-C83 =ABS(I83) =SUM($I$65:I83) =SUMSQ($I$65:I83)/B83
=SUM($J$65:J83)/B83
=J83/C83*100
=AVERAGE($N$65:N83)
=SUM($I$65:I83)/M83 X Variable 1
263.940257352941
27.6463221197055 9.5470296631179
1.65357109492828E-07
204.644740959899 323.235773745983 204.644740959899 323.235773745983
IV 20 3316 =$S$82+B84*$S$83 =C84/E84
=($S$82+B84*$S$83)*$S$92
=G84-C84 =ABS(I84) =SUM($I$65:I84) =SUMSQ($I$65:I84)/B84
=SUM($J$65:J84)/B84
=J84/C84*100
=AVERAGE($N$65:N84)
=SUM($I$65:I84)/M84
I 21
=($S$82+B85*$S$83)*$S$89
Estimate of standard deviation of forecast error:
=1.25*M84
II 22
=($S$82+B86*$S$83)*$S$90
III 23
=($S$82+B87*$S$83)*$S$91
Average of Seasonal Factor St
IV 24
=($S$82+B88*$S$83)*$S$92
Quarter Total
I 25
=($S$82+B89*$S$83)*$S$89
I
0.761533439524
II 26
=($S$82+B90*$S$83)*$S$90
II
1.89921436419794
III 27
=($S$82+B91*$S$83)*$S$91
III
0.948042726052106
IV 28
=($S$82+B92*$S$83)*$S$92
IV
0.414692765315435
I 29
=($S$82+B93*$S$83)*$S$89
II 30
=($S$82+B94*$S$83)*$S$90
III 31
=($S$82+B95*$S$83)*$S$91
IV 32
=($S$82+B96*$S$83)*$S$92
2412
7269
3514
2143
3459
7056
4120
2766
2556
8253
5491
4382
4315
12035
5648
3696
4843
13097
Black Plastic Demand (‘000 lbs)
Clear Plastic Demand (‘000 lbs)
3200
7658
2250
1737
4420
2384
3654
8680
5695
1953
4742
13673
6640
2737
3486
13186
8236
3316
5448
3485
7728
16591
0
2000
4000
6000
8000
10000
12000
14000
16000
18000
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21
(’000 lbs)
Quarter
Quarterly Historical Demand for Clear and Black Plastic Containers
Years 1-5
SPC
Year Quarter
2005 I
II
III
IV
2006 I
II
III
IV
2007 I
II
III
IV
2008 I
II p = 4 (even)
III
IV a1 =0.167007151143964
2009 I b1 =0.161001618724026
II g1 =-0.276910694641128
III
IV
Solver solution for minimizing MAD(20)
Black Plastics
Quarter Period Demand DtLevel Trend Seasonal Factor StForecast EtAtbias MSE MAD Percent Error MAPE TS Deseasonalized Demand Regression
=S47 =S48
I 12250
=alpha*(C30/F30)+(1-alpha)*(D29+E29)
=beta*(D30-D29)+(1-beta)*E29
0.712104547192459 =(D29+E29)*F30 =G30-C30 =ABS(I30) =SUM($I$30:I30) =SUMSQ($I$30:I30)/B30
=SUM($J$30:J30)/B30
=J30/C30*100
=AVERAGE($N$30:N30)
=SUM($I$30:I30)/M30
II 21737
=alpha*(C31/F31)+(1-alpha)*(D30+E30)
=beta*(D31-D30)+(1-beta)*E30
0.482573350145003 =(D30+E30)*F31 =G31-C31 =ABS(I31) =SUM($I$30:I31) =SUMSQ($I$30:I31)/B31
=SUM($J$30:J31)/B31
=J31/C31*100
=AVERAGE($N$30:N31)
=SUM($I$30:I31)/M31 SUMMARY OUTPUT
III 32412
=alpha*(C32/F32)+(1-alpha)*(D31+E31)
=beta*(D32-D31)+(1-beta)*E31
0.566574797247742 =(D31+E31)*F32 =G32-C32 =ABS(I32) =SUM($I$30:I32) =SUMSQ($I$30:I32)/B32
=SUM($J$30:J32)/B32
=J32/C32*100
=AVERAGE($N$30:N32)
=SUM($I$30:I32)/M32
IV 47269
=alpha*(C33/F33)+(1-alpha)*(D32+E32)
=beta*(D33-D32)+(1-beta)*E32
1.48599041994634 =(D32+E32)*F33 =G33-C33 =ABS(I33) =SUM($I$30:I33) =SUMSQ($I$30:I33)/B33
=SUM($J$30:J33)/B33
=J33/C33*100
=AVERAGE($N$30:N33)
=SUM($I$30:I33)/M33 Regression Statistics
I 53514
=alpha*(C34/F34)+(1-alpha)*(D33+E33)
=beta*(D34-D33)+(1-beta)*E33
=gamma*(C30/D30)+(1-gamma)*F30
=(D33+E33)*F34 =G34-C34 =ABS(I34) =SUM($I$30:I34) =SUMSQ($I$30:I34)/B34
=SUM($J$30:J34)/B34
=J34/C34*100
=AVERAGE($N$30:N34)
=SUM($I$30:I34)/M34 Multiple R
0.8875503453542
II 62143
=alpha*(C35/F35)+(1-alpha)*(D34+E34)
=beta*(D35-D34)+(1-beta)*E34
=gamma*(C31/D31)+(1-gamma)*F31
=(D34+E34)*F35 =G35-C35 =ABS(I35) =SUM($I$30:I35) =SUMSQ($I$30:I35)/B35
=SUM($J$30:J35)/B35
=J35/C35*100
=AVERAGE($N$30:N35)
=SUM($I$30:I35)/M35 R Square
0.78774561553836
III 73459
=alpha*(C36/F36)+(1-alpha)*(D35+E35)
=beta*(D36-D35)+(1-beta)*E35
=gamma*(C32/D32)+(1-gamma)*F32
=(D35+E35)*F36 =G36-C36 =ABS(I36) =SUM($I$30:I36) =SUMSQ($I$30:I36)/B36
=SUM($J$30:J36)/B36
=J36/C36*100
=AVERAGE($N$30:N36)
=SUM($I$30:I36)/M36 Adjusted R Square
0.772584588076814
IV 87056
=alpha*(C37/F37)+(1-alpha)*(D36+E36)
=beta*(D37-D36)+(1-beta)*E36
=gamma*(C33/D33)+(1-gamma)*F33
=(D36+E36)*F37 =G37-C37 =ABS(I37) =SUM($I$30:I37) =SUMSQ($I$30:I37)/B37
=SUM($J$30:J37)/B37
=J37/C37*100
=AVERAGE($N$30:N37)
=SUM($I$30:I37)/M37 Standard Error
800.986989959857
I 94120
=alpha*(C38/F38)+(1-alpha)*(D37+E37)
=beta*(D38-D37)+(1-beta)*E37
=gamma*(C34/D34)+(1-gamma)*F34
=(D37+E37)*F38 =G38-C38 =ABS(I38) =SUM($I$30:I38) =SUMSQ($I$30:I38)/B38
=SUM($J$30:J38)/B38
=J38/C38*100
=AVERAGE($N$30:N38)
=SUM($I$30:I38)/M38 Observations 16
II 10 2766
=alpha*(C39/F39)+(1-alpha)*(D38+E38)
=beta*(D39-D38)+(1-beta)*E38
=gamma*(C35/D35)+(1-gamma)*F35
=(D38+E38)*F39 =G39-C39 =ABS(I39) =SUM($I$30:I39) =SUMSQ($I$30:I39)/B39
=SUM($J$30:J39)/B39
=J39/C39*100
=AVERAGE($N$30:N39)
=SUM($I$30:I39)/M39
III 11 2556
=alpha*(C40/F40)+(1-alpha)*(D39+E39)
=beta*(D40-D39)+(1-beta)*E39
=gamma*(C36/D36)+(1-gamma)*F36
=(D39+E39)*F40 =G40-C40 =ABS(I40) =SUM($I$30:I40) =SUMSQ($I$30:I40)/B40
=SUM($J$30:J40)/B40
=J40/C40*100
=AVERAGE($N$30:N40)
=SUM($I$30:I40)/M40 ANOVA
IV 12 8253
=alpha*(C41/F41)+(1-alpha)*(D40+E40)
=beta*(D41-D40)+(1-beta)*E40
=gamma*(C37/D37)+(1-gamma)*F37
=(D40+E40)*F41 =G41-C41 =ABS(I41) =SUM($I$30:I41) =SUMSQ($I$30:I41)/B41
=SUM($J$30:J41)/B41
=J41/C41*100
=AVERAGE($N$30:N41)
=SUM($I$30:I41)/M41 df SS MS F Significance F
I 13 5491
=alpha*(C42/F42)+(1-alpha)*(D41+E41)
=beta*(D42-D41)+(1-beta)*E41
=gamma*(C38/D38)+(1-gamma)*F38
=(D41+E41)*F42 =G42-C42 =ABS(I42) =SUM($I$30:I42) =SUMSQ($I$30:I42)/B42
=SUM($J$30:J42)/B42
=J42/C42*100
=AVERAGE($N$30:N42)
=SUM($I$30:I42)/M42 Regression 133335600.6266544 33335600.6266544 51.9585903749854
4.50680003643016E-06
II 14 4382
=alpha*(C43/F43)+(1-alpha)*(D42+E42)
=beta*(D43-D42)+(1-beta)*E42
=gamma*(C39/D39)+(1-gamma)*F39
=(D42+E42)*F43 =G43-C43 =ABS(I43) =SUM($I$30:I43) =SUMSQ($I$30:I43)/B43
=SUM($J$30:J43)/B43
=J43/C43*100
=AVERAGE($N$30:N43)
=SUM($I$30:I43)/M43 Residual 14 8982122.21318934 641580.158084953
III 15 4315
=alpha*(C44/F44)+(1-alpha)*(D43+E43)
=beta*(D44-D43)+(1-beta)*E43
=gamma*(C40/D40)+(1-gamma)*F40
=(D43+E43)*F44 =G44-C44 =ABS(I44) =SUM($I$30:I44) =SUMSQ($I$30:I44)/B44
=SUM($J$30:J44)/B44
=J44/C44*100
=AVERAGE($N$30:N44)
=SUM($I$30:I44)/M44 Total 15 42317722.8398437
IV 16 12035
=alpha*(C45/F45)+(1-alpha)*(D44+E44)
=beta*(D45-D44)+(1-beta)*E44
=gamma*(C41/D41)+(1-gamma)*F41
=(D44+E44)*F45 =G45-C45 =ABS(I45) =SUM($I$30:I45) =SUMSQ($I$30:I45)/B45
=SUM($J$30:J45)/B45
=J45/C45*100
=AVERAGE($N$30:N45)
=SUM($I$30:I45)/M45
I 17 5648
=alpha*(C46/F46)+(1-alpha)*(D45+E45)
=beta*(D46-D45)+(1-beta)*E45
=gamma*(C42/D42)+(1-gamma)*F42
=(D45+E45)*F46 =G46-C46 =ABS(I46) =SUM($I$30:I46) =SUMSQ($I$30:I46)/B46
=SUM($J$30:J46)/B46
=J46/C46*100
=AVERAGE($N$30:N46)
=SUM($I$30:I46)/M46 Coefficients Standard Error t Stat P-value Lower 95% Upper 95% Lower 95.0% Upper 95.0%
II 18 3696
=alpha*(C47/F47)+(1-alpha)*(D46+E46)
=beta*(D47-D46)+(1-beta)*E46
=gamma*(C43/D43)+(1-gamma)*F43
=(D46+E46)*F47 =G47-C47 =ABS(I47) =SUM($I$30:I47) =SUMSQ($I$30:I47)/B47
=SUM($J$30:J47)/B47
=J47/C47*100
=AVERAGE($N$30:N47)
=SUM($I$30:I47)/M47 Intercept
3052.16378676471
498.13709071066 6.12715624610563
0.0000261855643684614
1983.76503555567 4120.56253797374 1983.76503555567 4120.56253797374
III 19 4843
=alpha*(C48/F48)+(1-alpha)*(D47+E47)
=beta*(D48-D47)+(1-beta)*E47
=gamma*(C44/D44)+(1-gamma)*F44
=(D47+E47)*F48 =G48-C48 =ABS(I48) =SUM($I$30:I48) =SUMSQ($I$30:I48)/B48
=SUM($J$30:J48)/B48
=J48/C48*100
=AVERAGE($N$30:N48)
=SUM($I$30:I48)/M48 X Variable 1
313.122794117647
43.4396186096948 7.20823073818987
4.50680003643023E-06
219.95399552571 406.291592709583 219.95399552571 406.291592709583
IV 20 13097
=alpha*(C49/F49)+(1-alpha)*(D48+E48)
=beta*(D49-D48)+(1-beta)*E48
=gamma*(C45/D45)+(1-gamma)*F45
=(D48+E48)*F49 =G49-C49 =ABS(I49) =SUM($I$30:I49) =SUMSQ($I$30:I49)/B49
=SUM($J$30:J49)/B49
=J49/C49*100
=AVERAGE($N$30:N49)
=SUM($I$30:I49)/M49
I 21
=gamma*(C46/D46)+(1-gamma)*F46
=($D$49+$E$49*(B50-20))*F50
Estimate of standard deviation of forecast error:
=M49*1.25
II 22
=gamma*(C47/D47)+(1-gamma)*F47
=($D$49+$E$49*(B51-20))*F51
III 23
=gamma*(C48/D48)+(1-gamma)*F48
=($D$49+$E$49*(B52-20))*F52
Average of Seasonal Factor St
IV 24
=gamma*(C49/D49)+(1-gamma)*F49
=($D$49+$E$49*(B53-20))*F53
Quarter Total
I 25 =F50
=($D$49+$E$49*(B54-20))*F54
I
0.712104547192459
II 26 =F51
=($D$49+$E$49*(B55-20))*F55
II
0.482573350145003
III 27 =F52
=($D$49+$E$49*(B56-20))*F56
III
0.566574797247742
IV 28 =F53
=($D$49+$E$49*(B57-20))*F57
IV
1.48599041994634
I 29 =F54
=($D$49+$E$49*(B58-20))*F58
II 30 =F55
=($D$49+$E$49*(B59-20))*F59
III 31 =F56
=($D$49+$E$49*(B60-20))*F60
IV 32 =F57
=($D$49+$E$49*(B61-20))*F61
p = 4 (even)
Clear Plastics
a2 =-0.332370743365246
Solver solution for minimizing MAD(20)
b2 =-0.0963815697294055
g2 =0.0740675481247197
Quarter Period Demand DtLevel Trend Seasonal Factor StForecast EtAtbias MSE MAD Percent Error MAPE TS Deseasonalized Demand Regression
=S89 =S90
I 13200
=alpha2*(C72/F72)+(1-alpha2)*(D71+E71)
=beta2*(D72-D71)+(1-beta2)*E71
=S96 =(D71+E71)*F72 =G72-C72 =ABS(I72) =SUM($I$72:I72) =SUMSQ($I$72:I72)/B72
=SUM($J$72:J72)/B72
=J72/C72*100
=AVERAGE($N$72:N72)
=SUM($I$72:I72)/M72
II 27658
=alpha2*(C73/F73)+(1-alpha2)*(D72+E72)
=beta2*(D73-D72)+(1-beta2)*E72
=S97 =(D72+E72)*F73 =G73-C73 =ABS(I73) =SUM($I$72:I73) =SUMSQ($I$72:I73)/B73
=SUM($J$72:J73)/B73
=J73/C73*100
=AVERAGE($N$72:N73)
=SUM($I$72:I73)/M73 SUMMARY OUTPUT
III 34420
=alpha2*(C74/F74)+(1-alpha2)*(D73+E73)
=beta2*(D74-D73)+(1-beta2)*E73
=S98 =(D73+E73)*F74 =G74-C74 =ABS(I74) =SUM($I$72:I74) =SUMSQ($I$72:I74)/B74
=SUM($J$72:J74)/B74
=J74/C74*100
=AVERAGE($N$72:N74)
=SUM($I$72:I74)/M74
IV 42384
=alpha2*(C75/F75)+(1-alpha2)*(D74+E74)
=beta2*(D75-D74)+(1-beta2)*E74
=S99 =(D74+E74)*F75 =G75-C75 =ABS(I75) =SUM($I$72:I75) =SUMSQ($I$72:I75)/B75
=SUM($J$72:J75)/B75
=J75/C75*100
=AVERAGE($N$72:N75)
=SUM($I$72:I75)/M75 Regression Statistics
I 53654
=alpha2*(C76/F76)+(1-alpha2)*(D75+E75)
=beta2*(D76-D75)+(1-beta2)*E75
=gamma2*(C72/D72)+(1-gamma2)*F72
=(D75+E75)*F76 =G76-C76 =ABS(I76) =SUM($I$72:I76) =SUMSQ($I$72:I76)/B76
=SUM($J$72:J76)/B76
=J76/C76*100
=AVERAGE($N$72:N76)
=SUM($I$72:I76)/M76 Multiple R
0.931048613999323
II 68680
=alpha2*(C77/F77)+(1-alpha2)*(D76+E76)
=beta2*(D77-D76)+(1-beta2)*E76
=gamma2*(C73/D73)+(1-gamma2)*F73
=(D76+E76)*F77 =G77-C77 =ABS(I77) =SUM($I$72:I77) =SUMSQ($I$72:I77)/B77
=SUM($J$72:J77)/B77
=J77/C77*100
=AVERAGE($N$72:N77)
=SUM($I$72:I77)/M77 R Square
0.86685152163006
III 75695
=alpha2*(C78/F78)+(1-alpha2)*(D77+E77)
=beta2*(D78-D77)+(1-beta2)*E77
=gamma2*(C74/D74)+(1-gamma2)*F74
=(D77+E77)*F78 =G78-C78 =ABS(I78) =SUM($I$72:I78) =SUMSQ($I$72:I78)/B78
=SUM($J$72:J78)/B78
=J78/C78*100
=AVERAGE($N$72:N78)
=SUM($I$72:I78)/M78 Adjusted R Square
0.857340916032208
IV 81953
=alpha2*(C79/F79)+(1-alpha2)*(D78+E78)
=beta2*(D79-D78)+(1-beta2)*E78
=gamma2*(C75/D75)+(1-gamma2)*F75
=(D78+E78)*F79 =G79-C79 =ABS(I79) =SUM($I$72:I79) =SUMSQ($I$72:I79)/B79
=SUM($J$72:J79)/B79
=J79/C79*100
=AVERAGE($N$72:N79)
=SUM($I$72:I79)/M79 Standard Error
509.772991726529
I 94742
=alpha2*(C80/F80)+(1-alpha2)*(D79+E79)
=beta2*(D80-D79)+(1-beta2)*E79
=gamma2*(C76/D76)+(1-gamma2)*F76
=(D79+E79)*F80 =G80-C80 =ABS(I80) =SUM($I$72:I80) =SUMSQ($I$72:I80)/B80
=SUM($J$72:J80)/B80
=J80/C80*100
=AVERAGE($N$72:N80)
=SUM($I$72:I80)/M80 Observations 16
II 10 13673
=alpha2*(C81/F81)+(1-alpha2)*(D80+E80)
=beta2*(D81-D80)+(1-beta2)*E80
=gamma2*(C77/D77)+(1-gamma2)*F77
=(D80+E80)*F81 =G81-C81 =ABS(I81) =SUM($I$72:I81) =SUMSQ($I$72:I81)/B81
=SUM($J$72:J81)/B81
=J81/C81*100
=AVERAGE($N$72:N81)
=SUM($I$72:I81)/M81
III 11 6640
=alpha2*(C82/F82)+(1-alpha2)*(D81+E81)
=beta2*(D82-D81)+(1-beta2)*E81
=gamma2*(C78/D78)+(1-gamma2)*F78
=(D81+E81)*F82 =G82-C82 =ABS(I82) =SUM($I$72:I82) =SUMSQ($I$72:I82)/B82
=SUM($J$72:J82)/B82
=J82/C82*100
=AVERAGE($N$72:N82)
=SUM($I$72:I82)/M82 ANOVA
IV 12 2737
=alpha2*(C83/F83)+(1-alpha2)*(D82+E82)
=beta2*(D83-D82)+(1-beta2)*E82
=gamma2*(C79/D79)+(1-gamma2)*F79
=(D82+E82)*F83 =G83-C83 =ABS(I83) =SUM($I$72:I83) =SUMSQ($I$72:I83)/B83
=SUM($J$72:J83)/B83
=J83/C83*100
=AVERAGE($N$72:N83)
=SUM($I$72:I83)/M83 df SS MS F Significance F
I 13 3486
=alpha2*(C84/F84)+(1-alpha2)*(D83+E83)
=beta2*(D84-D83)+(1-beta2)*E83
=gamma2*(C80/D80)+(1-gamma2)*F80
=(D83+E83)*F84 =G84-C84 =ABS(I84) =SUM($I$72:I84) =SUMSQ($I$72:I84)/B84
=SUM($J$72:J84)/B84
=J84/C84*100
=AVERAGE($N$72:N84)
=SUM($I$72:I84)/M84 Regression 123685916.2135225 23685916.2135225 91.1457753884533
1.65357109492825E-07
II 14 13186
=alpha2*(C85/F85)+(1-alpha2)*(D84+E84)
=beta2*(D85-D84)+(1-beta2)*E84
=gamma2*(C81/D81)+(1-gamma2)*F81
=(D84+E84)*F85 =G85-C85 =ABS(I85) =SUM($I$72:I85) =SUMSQ($I$72:I85)/B85
=SUM($J$72:J85)/B85
=J85/C85*100
=AVERAGE($N$72:N85)
=SUM($I$72:I85)/M85 Residual 14 3638159.04331342 259868.503093816
III 15 5448
=alpha2*(C86/F86)+(1-alpha2)*(D85+E85)
=beta2*(D86-D85)+(1-beta2)*E85
=gamma2*(C82/D82)+(1-gamma2)*F82
=(D85+E85)*F86 =G86-C86 =ABS(I86) =SUM($I$72:I86) =SUMSQ($I$72:I86)/B86
=SUM($J$72:J86)/B86
=J86/C86*100
=AVERAGE($N$72:N86)
=SUM($I$72:I86)/M86 Total 15 27324075.2568359
IV 16 3485
=alpha2*(C87/F87)+(1-alpha2)*(D86+E86)
=beta2*(D87-D86)+(1-beta2)*E86
=gamma2*(C83/D83)+(1-gamma2)*F83
=(D86+E86)*F87 =G87-C87 =ABS(I87) =SUM($I$72:I87) =SUMSQ($I$72:I87)/B87
=SUM($J$72:J87)/B87
=J87/C87*100
=AVERAGE($N$72:N87)
=SUM($I$72:I87)/M87
I 17 7728
=alpha2*(C88/F88)+(1-alpha2)*(D87+E87)
=beta2*(D88-D87)+(1-beta2)*E87
=gamma2*(C84/D84)+(1-gamma2)*F84
=(D87+E87)*F88 =G88-C88 =ABS(I88) =SUM($I$72:I88) =SUMSQ($I$72:I88)/B88
=SUM($J$72:J88)/B88
=J88/C88*100
=AVERAGE($N$72:N88)
=SUM($I$72:I88)/M88 Coefficients Standard Error t Stat P-value Lower 95% Upper 95% Lower 95.0% Upper 95.0%
II 18 16591
=alpha2*(C89/F89)+(1-alpha2)*(D88+E88)
=beta2*(D89-D88)+(1-beta2)*E88
=gamma2*(C85/D85)+(1-gamma2)*F85
=(D88+E88)*F89 =G89-C89 =ABS(I89) =SUM($I$72:I89) =SUMSQ($I$72:I89)/B89
=SUM($J$72:J89)/B89
=J89/C89*100
=AVERAGE($N$72:N89)
=SUM($I$72:I89)/M89 Intercept
3611.97886029412
317.02991210163 11.3931800199857
1.81539310264743E-08
2932.01672034974 4291.94100023851 2932.01672034974 4291.94100023851
III 19 8236
=alpha2*(C90/F90)+(1-alpha2)*(D89+E89)
=beta2*(D90-D89)+(1-beta2)*E89
=gamma2*(C86/D86)+(1-gamma2)*F86
=(D89+E89)*F90 =G90-C90 =ABS(I90) =SUM($I$72:I90) =SUMSQ($I$72:I90)/B90
=SUM($J$72:J90)/B90
=J90/C90*100
=AVERAGE($N$72:N90)
=SUM($I$72:I90)/M90 X Variable 1
263.940257352941
27.6463221197055 9.5470296631179
1.65357109492828E-07
204.644740959899 323.235773745983 204.644740959899 323.235773745983
IV 20 3316
=alpha2*(C91/F91)+(1-alpha2)*(D90+E90)
=beta2*(D91-D90)+(1-beta2)*E90
=gamma2*(C87/D87)+(1-gamma2)*F87
=(D90+E90)*F91 =G91-C91 =ABS(I91) =SUM($I$72:I91) =SUMSQ($I$72:I91)/B91
=SUM($J$72:J91)/B91
=J91/C91*100
=AVERAGE($N$72:N91)
=SUM($I$72:I91)/M91
I 21
=gamma2*(C88/D88)+(1-gamma2)*F88
=($D$49+$E$49*(B92-20))*F92
Estimate of standard deviation of forecast error:
=M91*1.25
II 22
=gamma2*(C89/D89)+(1-gamma2)*F89
=($D$49+$E$49*(B93-20))*F93
III 23
=gamma2*(C90/D90)+(1-gamma2)*F90
=($D$49+$E$49*(B94-20))*F94
Average of Seasonal Factor St
IV 24
=gamma2*(C91/D91)+(1-gamma2)*F91
=($D$49+$E$49*(B95-20))*F95
Quarter Total
I 25 =F92
=($D$49+$E$49*(B96-20))*F96
I
0.761533439524
II 26 =F93
=($D$49+$E$49*(B97-20))*F97
II
1.89921436419794
III 27 =F94
=($D$49+$E$49*(B98-20))*F98
III
0.948042726052106
IV 28 =F95
=($D$49+$E$49*(B99-20))*F99
IV
0.414692765315435
I 29 =F96
=($D$49+$E$49*(B100-20))*F100
II 30 =F97
=($D$49+$E$49*(B101-20))*F101
III 31 =F98
=($D$49+$E$49*(B102-20))*F102
IV 32 =F99
=($D$49+$E$49*(B103-20))*F103
3486
13186
8236
3316
5448
3485
7728
16591
4742
13673
6640
2737
3654
8680
5695
1953
4843
13097
Black Plastic Demand (‘000 lbs)
Clear Plastic Demand (‘000 lbs)
3200
7658
2250
1737
4420
2384
4315
12035
5648
3696
2556
8253
4382
3459
7056
4120
2766
2412
7269
3514
2143
5491
0
2000
4000
6000
8000
10000
12000
14000
16000
18000
12345678910 11 12 13 14 15 16 17 18 19 20 21
(‘000 lbs)
Quarter
Quarterly Historical Demand for Clear and Black Plastic Containers
Years 1-5