Exercise 7-1: ABC Corporation
Sales Year 1 Year 2 Year 3 Year 4 Year 5
JAN 2,000 3,000 2,000 5,000 5,000 p = 12 (even)
FEB 3,000 4,000 5,000 4,000 2,000
MAR 3,000 3,000 5,000 4,000 3,000
APR 3,000 5,000 3,000 2,000 2,000
MAY 4,000 5,000 4,000 5,000 7,000
JUN 6,000 8,000 6,000 7,000 6,000
JUL 7,000 3,000 7,000 10,000 8,000
AUG 6,000 8,000 10,000 14,000 10,000
SEP 10,000 12,000 15,000 16,000 20,000
OCT 12,000 12,000 15,000 16,000 20,000
NOV 14,000 16,000 18,000 20,000 22,000
DEC 8,000 10,000 8,000 12,000 8,000
Total 78,000 89,000 98,000 115,000 113,000
Static Method for Forecasting
JUN 18 8,000 7,333 7,262 1.10 6,059 (1,941) 1,941 (4,589) 972,128 729 24.26 13.48 -6.29 Regression Statistics
JUL 19 3,000 7,375 7,332 0.41 6,253 3,253 3,253 (1,336) 1,478,005 862 108.44 18.48 -1.55 Multiple R 0.9749256
AUG 20 8,000 7,375 7,402 1.08 8,521 521 521 (815) 1,417,679 845 6.51 17.88 -0.96 R Square 0.95048
SEP 21 12,000 7,500 7,472 1.61 12,950 950 950 135 1,393,120 850 7.91 17.41 0.16 Adjusted R Square 0.9494034
OCT 22 12,000 7,500 7,543 1.59 13,411 1,411 1,411 1,546 1,420,351 875 11.76 17.15 1.77 Standard Error 226.90147
NOV 23 16,000 7,375 7,613 2.10 16,167 167 167 1,713 1,359,815 845 1.05 16.45 2.03 Observations 48
DEC 24 10,000 7,250 7,683 1.30 8,411 (1,589) 1,589 124 1,408,374 876 15.89 16.43 0.14
SEP 33 15,000 8,292 8,315 1.80 14,411 (589) 589 (404) 1,248,087 849 3.93 17.12 -0.48 X Variable 1 70.246 2.36407 29.71392 1.14E-31 65.48716 75.00441 65.48716 75.00441
NOV 47 20,000 9,417 9,299 2.15 19,748 (252) 252 (3,711) 1,396,112 853 1.26 16.13 -4.35 SEP 1.733
DEC 48 12,000 9,458 9,369 1.28 10,256 (1,744) 1,744 (5,454) 1,430,356 872 14.53 16.10 -6.26 OCT 1.778
FEB 62 4,913
MAR 63 4,822
APR 64 4,178
MAY 65 6,563
15,000
20,000
25,000
Sales
Monthly Demand ABC Corporation
(3,000)
(2,000)
Bias
Exercise 7-1: ABC Corporation
Sales Year 1 Year 2 Year 3 Year 4 Year 5
JAN 2000 3000 2000 5000 5000 p = 12 (even)
FEB 3000 4000 5000 4000 2000
MAR 3000 3000 5000 4000 3000
APR 3000 5000 3000 2000 2000
MAY 4000 5000 4000 5000 7000
JUN 6000 8000 6000 7000 6000
JUL 7000 3000 7000 10000 8000
AUG 6000 8000 10000 14000 10000
SEP 10000 12000 15000 16000 20000
OCT 12000 12000 15000 16000 20000
NOV 14000 16000 18000 20000 22000
DEC 8000 10000 8000 12000 8000
Total =SUM(C4:C15) =SUM(D4:D15) =SUM(E4:E15) =SUM(F4:F15) =SUM(G4:G15)
Static Method for Forecasting
Year Month Period Demand DtDeseasonalized Demand Dt
Dt (based on
regression)
Seasonal Factor StForecast EtAtBias MSE MAD Percent Error MAPE TS
Year 1 JAN 1 2000 =$T$52+C21*$T$53 =D21/F21
=($T$52+C21*$T$53)*$T$59
=H21-D21 =ABS(J21) =SUM($J$21:J21) =SUMSQ($J$21:J21)/C21
=SUM($K$21:K21)/C21
=K21/D21*100
=AVERAGE($O$21:O21)
=SUM($J$21:J21)/N21
FEB 2 3000 =$T$52+C22*$T$53 =D22/F22
=($T$52+C22*$T$53)*$T$60
=H22-D22 =ABS(J22) =SUM($J$21:J22) =SUMSQ($J$21:J22)/C22
=SUM($K$21:K22)/C22
=K22/D22*100
=AVERAGE($O$21:O22)
=SUM($J$21:J22)/N22
MAR 3 3000 =$T$52+C23*$T$53 =D23/F23
=($T$52+C23*$T$53)*$T$61
=H23-D23 =ABS(J23) =SUM($J$21:J23) =SUMSQ($J$21:J23)/C23
=SUM($K$21:K23)/C23
=K23/D23*100
=AVERAGE($O$21:O23)
=SUM($J$21:J23)/N23
APR 43000 =$T$52+C24*$T$53 =D24/F24
=($T$52+C24*$T$53)*$T$62
=H24-D24 =ABS(J24) =SUM($J$21:J24) =SUMSQ($J$21:J24)/C24
=SUM($K$21:K24)/C24
=K24/D24*100
=AVERAGE($O$21:O24)
=SUM($J$21:J24)/N24
MAY 5 4000 =$T$52+C25*$T$53 =D25/F25
=($T$52+C25*$T$53)*$T$63
=H25-D25 =ABS(J25) =SUM($J$21:J25) =SUMSQ($J$21:J25)/C25
=SUM($K$21:K25)/C25
=K25/D25*100
=AVERAGE($O$21:O25)
=SUM($J$21:J25)/N25
JUN 6 6000 =$T$52+C26*$T$53 =D26/F26
=($T$52+C26*$T$53)*$T$64
=H26-D26 =ABS(J26) =SUM($J$21:J26) =SUMSQ($J$21:J26)/C26
=SUM($K$21:K26)/C26
=K26/D26*100
=AVERAGE($O$21:O26)
=SUM($J$21:J26)/N26
JUL 7 7000
=(D21+D33+2*SUM(D22:D32))/(2*12)
=$T$52+C27*$T$53 =D27/F27
=($T$52+C27*$T$53)*$T$65
=H27-D27 =ABS(J27) =SUM($J$21:J27) =SUMSQ($J$21:J27)/C27
=SUM($K$21:K27)/C27
=K27/D27*100
=AVERAGE($O$21:O27)
=SUM($J$21:J27)/N27
AUG 8 6000
=(D22+D34+2*SUM(D23:D33))/(2*12)
=$T$52+C28*$T$53 =D28/F28
=($T$52+C28*$T$53)*$T$66
=H28-D28 =ABS(J28) =SUM($J$21:J28) =SUMSQ($J$21:J28)/C28
=SUM($K$21:K28)/C28
=K28/D28*100
=AVERAGE($O$21:O28)
=SUM($J$21:J28)/N28
SEP 910000
=(D23+D35+2*SUM(D24:D34))/(2*12)
=$T$52+C29*$T$53 =D29/F29
=($T$52+C29*$T$53)*$T$67
=H29-D29 =ABS(J29) =SUM($J$21:J29) =SUMSQ($J$21:J29)/C29
=SUM($K$21:K29)/C29
=K29/D29*100
=AVERAGE($O$21:O29)
=SUM($J$21:J29)/N29
OCT 10 12000
=(D24+D36+2*SUM(D25:D35))/(2*12)
=$T$52+C30*$T$53 =D30/F30
=($T$52+C30*$T$53)*$T$68
=H30-D30 =ABS(J30) =SUM($J$21:J30) =SUMSQ($J$21:J30)/C30
=SUM($K$21:K30)/C30
=K30/D30*100
=AVERAGE($O$21:O30)
=SUM($J$21:J30)/N30
NOV 11 14000
=(D25+D37+2*SUM(D26:D36))/(2*12)
=$T$52+C31*$T$53 =D31/F31
=($T$52+C31*$T$53)*$T$69
=H31-D31 =ABS(J31) =SUM($J$21:J31) =SUMSQ($J$21:J31)/C31
=SUM($K$21:K31)/C31
=K31/D31*100
=AVERAGE($O$21:O31)
=SUM($J$21:J31)/N31
DEC 12 8000
=(D26+D38+2*SUM(D27:D37))/(2*12)
=$T$52+C32*$T$53 =D32/F32
=($T$52+C32*$T$53)*$T$70
=H32-D32 =ABS(J32) =SUM($J$21:J32) =SUMSQ($J$21:J32)/C32
=SUM($K$21:K32)/C32
=K32/D32*100
=AVERAGE($O$21:O32)
=SUM($J$21:J32)/N32
Year 2 JAN 13 3000
=(D27+D39+2*SUM(D28:D38))/(2*12)
=$T$52+C33*$T$53 =D33/F33
=($T$52+C33*$T$53)*$T$59
=H33-D33 =ABS(J33) =SUM($J$21:J33) =SUMSQ($J$21:J33)/C33
=SUM($K$21:K33)/C33
=K33/D33*100
=AVERAGE($O$21:O33)
=SUM($J$21:J33)/N33
FEB 14 4000
=(D28+D40+2*SUM(D29:D39))/(2*12)
=$T$52+C34*$T$53 =D34/F34
=($T$52+C34*$T$53)*$T$60
=H34-D34 =ABS(J34) =SUM($J$21:J34) =SUMSQ($J$21:J34)/C34
=SUM($K$21:K34)/C34
=K34/D34*100
=AVERAGE($O$21:O34)
=SUM($J$21:J34)/N34
MAR 15 3000
=(D29+D41+2*SUM(D30:D40))/(2*12)
=$T$52+C35*$T$53 =D35/F35
=($T$52+C35*$T$53)*$T$61
=H35-D35 =ABS(J35) =SUM($J$21:J35) =SUMSQ($J$21:J35)/C35
=SUM($K$21:K35)/C35
=K35/D35*100
=AVERAGE($O$21:O35)
=SUM($J$21:J35)/N35
Deseasonalized Demand Regression
APR 16 5000
=(D30+D42+2*SUM(D31:D41))/(2*12)
=$T$52+C36*$T$53 =D36/F36
=($T$52+C36*$T$53)*$T$62
=H36-D36 =ABS(J36) =SUM($J$21:J36) =SUMSQ($J$21:J36)/C36
=SUM($K$21:K36)/C36
=K36/D36*100
=AVERAGE($O$21:O36)
=SUM($J$21:J36)/N36
SUMMARY OUTPUT
MAY 17 5000
=(D31+D43+2*SUM(D32:D42))/(2*12)
=$T$52+C37*$T$53 =D37/F37
=($T$52+C37*$T$53)*$T$63
=H37-D37 =ABS(J37) =SUM($J$21:J37) =SUMSQ($J$21:J37)/C37
=SUM($K$21:K37)/C37
=K37/D37*100
=AVERAGE($O$21:O37)
=SUM($J$21:J37)/N37
JUN 18 8000
=(D32+D44+2*SUM(D33:D43))/(2*12)
=$T$52+C38*$T$53 =D38/F38
=($T$52+C38*$T$53)*$T$64
=H38-D38 =ABS(J38) =SUM($J$21:J38) =SUMSQ($J$21:J38)/C38
=SUM($K$21:K38)/C38
=K38/D38*100
=AVERAGE($O$21:O38)
=SUM($J$21:J38)/N38
Regression Statistics
JUL 19 3000
=(D33+D45+2*SUM(D34:D44))/(2*12)
=$T$52+C39*$T$53 =D39/F39
=($T$52+C39*$T$53)*$T$65
=H39-D39 =ABS(J39) =SUM($J$21:J39) =SUMSQ($J$21:J39)/C39
=SUM($K$21:K39)/C39
=K39/D39*100
=AVERAGE($O$21:O39)
=SUM($J$21:J39)/N39
Multiple R 0.974925619273633
AUG 20 8000
=(D34+D46+2*SUM(D35:D45))/(2*12)
=$T$52+C40*$T$53 =D40/F40
=($T$52+C40*$T$53)*$T$66
=H40-D40 =ABS(J40) =SUM($J$21:J40) =SUMSQ($J$21:J40)/C40
=SUM($K$21:K40)/C40
=K40/D40*100
=AVERAGE($O$21:O40)
=SUM($J$21:J40)/N40
R Square 0.950479963116077
SEP 21 12000
=(D35+D47+2*SUM(D36:D46))/(2*12)
=$T$52+C41*$T$53 =D41/F41
=($T$52+C41*$T$53)*$T$67
=H41-D41 =ABS(J41) =SUM($J$21:J41) =SUMSQ($J$21:J41)/C41
=SUM($K$21:K41)/C41
=K41/D41*100
=AVERAGE($O$21:O41)
=SUM($J$21:J41)/N41
Adjusted R Square 0.949403440575122
OCT 22 12000
=(D36+D48+2*SUM(D37:D47))/(2*12)
=$T$52+C42*$T$53 =D42/F42
=($T$52+C42*$T$53)*$T$68
=H42-D42 =ABS(J42) =SUM($J$21:J42) =SUMSQ($J$21:J42)/C42
=SUM($K$21:K42)/C42
=K42/D42*100
=AVERAGE($O$21:O42)
=SUM($J$21:J42)/N42
Standard Error 226.901471550744
NOV 23 16000
=(D37+D49+2*SUM(D38:D48))/(2*12)
=$T$52+C43*$T$53 =D43/F43
=($T$52+C43*$T$53)*$T$69
=H43-D43 =ABS(J43) =SUM($J$21:J43) =SUMSQ($J$21:J43)/C43
=SUM($K$21:K43)/C43
=K43/D43*100
=AVERAGE($O$21:O43)
=SUM($J$21:J43)/N43
Observations 48
DEC 24 10000
=(D38+D50+2*SUM(D39:D49))/(2*12)
=$T$52+C44*$T$53 =D44/F44
=($T$52+C44*$T$53)*$T$70
=H44-D44 =ABS(J44) =SUM($J$21:J44) =SUMSQ($J$21:J44)/C44
=SUM($K$21:K44)/C44
=K44/D44*100
=AVERAGE($O$21:O44)
=SUM($J$21:J44)/N44
Year 3 JAN 25 2000
=(D39+D51+2*SUM(D40:D50))/(2*12)
=$T$52+C45*$T$53 =D45/F45
=($T$52+C45*$T$53)*$T$59
=H45-D45 =ABS(J45) =SUM($J$21:J45) =SUMSQ($J$21:J45)/C45
=SUM($K$21:K45)/C45
=K45/D45*100
=AVERAGE($O$21:O45)
=SUM($J$21:J45)/N45
ANOVA
FEB 26 5000
=(D40+D52+2*SUM(D41:D51))/(2*12)
=$T$52+C46*$T$53 =D46/F46
=($T$52+C46*$T$53)*$T$60
=H46-D46 =ABS(J46) =SUM($J$21:J46) =SUMSQ($J$21:J46)/C46
=SUM($K$21:K46)/C46
=K46/D46*100
=AVERAGE($O$21:O46)
=SUM($J$21:J46)/N46
df SS MS F Significance F
MAR 27 5000
=(D41+D53+2*SUM(D42:D52))/(2*12)
=$T$52+C47*$T$53 =D47/F47
=($T$52+C47*$T$53)*$T$61
=H47-D47 =ABS(J47) =SUM($J$21:J47) =SUMSQ($J$21:J47)/C47
=SUM($K$21:K47)/C47
=K47/D47*100
=AVERAGE($O$21:O47)
=SUM($J$21:J47)/N47
Regression 1 45456339.8303692 45456339.8303692 882.916917162753
1.14477843987011E-31
APR 28 3000
=(D42+D54+2*SUM(D43:D53))/(2*12)
=$T$52+C48*$T$53 =D48/F48
=($T$52+C48*$T$53)*$T$62
=H48-D48 =ABS(J48) =SUM($J$21:J48) =SUMSQ($J$21:J48)/C48
=SUM($K$21:K48)/C48
=K48/D48*100
=AVERAGE($O$21:O48)
=SUM($J$21:J48)/N48
Residual 46 2368276.77842709 51484.2777918933
MAY 29 4000
=(D43+D55+2*SUM(D44:D54))/(2*12)
=$T$52+C49*$T$53 =D49/F49
=($T$52+C49*$T$53)*$T$63
=H49-D49 =ABS(J49) =SUM($J$21:J49) =SUMSQ($J$21:J49)/C49
=SUM($K$21:K49)/C49
=K49/D49*100
=AVERAGE($O$21:O49)
=SUM($J$21:J49)/N49
Total 47 47824616.6087963
JUN 30 6000
=(D44+D56+2*SUM(D45:D55))/(2*12)
=$T$52+C50*$T$53 =D50/F50
=($T$52+C50*$T$53)*$T$64
=H50-D50 =ABS(J50) =SUM($J$21:J50) =SUMSQ($J$21:J50)/C50
=SUM($K$21:K50)/C50
=K50/D50*100
=AVERAGE($O$21:O50)
=SUM($J$21:J50)/N50
JUL 31 7000
=(D45+D57+2*SUM(D46:D56))/(2*12)
=$T$52+C51*$T$53 =D51/F51
=($T$52+C51*$T$53)*$T$65
=H51-D51 =ABS(J51) =SUM($J$21:J51) =SUMSQ($J$21:J51)/C51
=SUM($K$21:K51)/C51
=K51/D51*100
=AVERAGE($O$21:O51)
=SUM($J$21:J51)/N51
Coefficients Standard Error t Stat P-value Lower 95% Upper 95% Lower 95.0% Upper 95.0%
AUG 32 10000
=(D46+D58+2*SUM(D47:D57))/(2*12)
=$T$52+C52*$T$53 =D52/F52
=($T$52+C52*$T$53)*$T$66
=H52-D52 =ABS(J52) =SUM($J$21:J52) =SUMSQ($J$21:J52)/C52
=SUM($K$21:K52)/C52
=K52/D52*100
=AVERAGE($O$21:O52)
=SUM($J$21:J52)/N52
Intercept 5997.26051768224 79.19340747569 75.7292899604455
6.12849862068368E-50
5837.85260875478 6156.6684266097 5837.85260875478 6156.6684266097
SEP 33 15000
=(D47+D59+2*SUM(D48:D58))/(2*12)
=$T$52+C53*$T$53 =D53/F53
=($T$52+C53*$T$53)*$T$67
=H53-D53 =ABS(J53) =SUM($J$21:J53) =SUMSQ($J$21:J53)/C53
=SUM($K$21:K53)/C53
=K53/D53*100
=AVERAGE($O$21:O53)
=SUM($J$21:J53)/N53
X Variable 1 70.2457844840067 2.36407008704351 29.7139179032783
1.14477843986988E-31
65.4871627609897 75.0044062070237 65.4871627609897 75.0044062070237
OCT 34 15000
=(D48+D60+2*SUM(D49:D59))/(2*12)
=$T$52+C54*$T$53 =D54/F54
=($T$52+C54*$T$53)*$T$68
=H54-D54 =ABS(J54) =SUM($J$21:J54) =SUMSQ($J$21:J54)/C54
=SUM($K$21:K54)/C54
=K54/D54*100
=AVERAGE($O$21:O54)
=SUM($J$21:J54)/N54
NOV 35 18000
=(D49+D61+2*SUM(D50:D60))/(2*12)
=$T$52+C55*$T$53 =D55/F55
=($T$52+C55*$T$53)*$T$69
=H55-D55 =ABS(J55) =SUM($J$21:J55) =SUMSQ($J$21:J55)/C55
=SUM($K$21:K55)/C55
=K55/D55*100
=AVERAGE($O$21:O55)
=SUM($J$21:J55)/N55
DEC 36 8000
=(D50+D62+2*SUM(D51:D61))/(2*12)
=$T$52+C56*$T$53 =D56/F56
=($T$52+C56*$T$53)*$T$70
=H56-D56 =ABS(J56) =SUM($J$21:J56) =SUMSQ($J$21:J56)/C56
=SUM($K$21:K56)/C56
=K56/D56*100
=AVERAGE($O$21:O56)
=SUM($J$21:J56)/N56
Seasonal Factors
Year 4 JAN 37 5000
=(D51+D63+2*SUM(D52:D62))/(2*12)
=$T$52+C57*$T$53 =D57/F57
=($T$52+C57*$T$53)*$T$59
=H57-D57 =ABS(J57) =SUM($J$21:J57) =SUMSQ($J$21:J57)/C57
=SUM($K$21:K57)/C57
=K57/D57*100
=AVERAGE($O$21:O57)
=SUM($J$21:J57)/N57
Average of Seasonal Factor St
FEB 38 4000
=(D52+D64+2*SUM(D53:D63))/(2*12)
=$T$52+C58*$T$53 =D58/F58
=($T$52+C58*$T$53)*$T$60
=H58-D58 =ABS(J58) =SUM($J$21:J58) =SUMSQ($J$21:J58)/C58
=SUM($K$21:K58)/C58
=K58/D58*100
=AVERAGE($O$21:O58)
=SUM($J$21:J58)/N58
Month Total
MAR 39 4000
=(D53+D65+2*SUM(D54:D64))/(2*12)
=$T$52+C59*$T$53 =D59/F59
=($T$52+C59*$T$53)*$T$61
=H59-D59 =ABS(J59) =SUM($J$21:J59) =SUMSQ($J$21:J59)/C59
=SUM($K$21:K59)/C59
=K59/D59*100
=AVERAGE($O$21:O59)
=SUM($J$21:J59)/N59
JAN 0.426608526754276
APR 40 2000
=(D54+D66+2*SUM(D55:D65))/(2*12)
=$T$52+C60*$T$53 =D60/F60
=($T$52+C60*$T$53)*$T$62
=H60-D60 =ABS(J60) =SUM($J$21:J60) =SUMSQ($J$21:J60)/C60
=SUM($K$21:K60)/C60
=K60/D60*100
=AVERAGE($O$21:O60)
=SUM($J$21:J60)/N60
FEB 0.474546264505078
MAY 41 5000
=(D55+D67+2*SUM(D56:D66))/(2*12)
=$T$52+C61*$T$53 =D61/F61
=($T$52+C61*$T$53)*$T$63
=H61-D61 =ABS(J61) =SUM($J$21:J61) =SUMSQ($J$21:J61)/C61
=SUM($K$21:K61)/C61
=K61/D61*100
=AVERAGE($O$21:O61)
=SUM($J$21:J61)/N61
MAR 0.462622654498944
JUN 42 7000
=(D56+D68+2*SUM(D57:D67))/(2*12)
=$T$52+C62*$T$53 =D62/F62
=($T$52+C62*$T$53)*$T$64
=H62-D62 =ABS(J62) =SUM($J$21:J62) =SUMSQ($J$21:J62)/C62
=SUM($K$21:K62)/C62
=K62/D62*100
=AVERAGE($O$21:O62)
=SUM($J$21:J62)/N62
APR 0.398200257435726
JUL 43 10000
=(D57+D69+2*SUM(D58:D68))/(2*12)
=$T$52+C63*$T$53 =D63/F63
=($T$52+C63*$T$53)*$T$65
=H63-D63 =ABS(J63) =SUM($J$21:J63) =SUMSQ($J$21:J63)/C63
=SUM($K$21:K63)/C63
=K63/D63*100
=AVERAGE($O$21:O63)
=SUM($J$21:J63)/N63
MAY 0.621315499102455
AUG 44 14000
=(D58+D70+2*SUM(D59:D69))/(2*12)
=$T$52+C64*$T$53 =D64/F64
=($T$52+C64*$T$53)*$T$66
=H64-D64 =ABS(J64) =SUM($J$21:J64) =SUMSQ($J$21:J64)/C64
=SUM($K$21:K64)/C64
=K64/D64*100
=AVERAGE($O$21:O64)
=SUM($J$21:J64)/N64
JUN 0.834384917835114
SEP 45 16000
=(D59+D71+2*SUM(D60:D70))/(2*12)
=$T$52+C65*$T$53 =D65/F65
=($T$52+C65*$T$53)*$T$67
=H65-D65 =ABS(J65) =SUM($J$21:J65) =SUMSQ($J$21:J65)/C65
=SUM($K$21:K65)/C65
=K65/D65*100
=AVERAGE($O$21:O65)
=SUM($J$21:J65)/N65
JUL 0.852882394306724
OCT 46 16000
=(D60+D72+2*SUM(D61:D71))/(2*12)
=$T$52+C66*$T$53 =D66/F66
=($T$52+C66*$T$53)*$T$68
=H66-D66 =ABS(J66) =SUM($J$21:J66) =SUMSQ($J$21:J66)/C66
=SUM($K$21:K66)/C66
=K66/D66*100
=AVERAGE($O$21:O66)
=SUM($J$21:J66)/N66
AUG 1.15115374207231
NOV 47 20000
=(D61+D73+2*SUM(D62:D72))/(2*12)
=$T$52+C67*$T$53 =D67/F67
=($T$52+C67*$T$53)*$T$69
=H67-D67 =ABS(J67) =SUM($J$21:J67) =SUMSQ($J$21:J67)/C67
=SUM($K$21:K67)/C67
=K67/D67*100
=AVERAGE($O$21:O67)
=SUM($J$21:J67)/N67
SEP 1.73299998783266
DEC 48 12000
=(D62+D74+2*SUM(D63:D73))/(2*12)
=$T$52+C68*$T$53 =D68/F68
=($T$52+C68*$T$53)*$T$70
=H68-D68 =ABS(J68) =SUM($J$21:J68) =SUMSQ($J$21:J68)/C68
=SUM($K$21:K68)/C68
=K68/D68*100
=AVERAGE($O$21:O68)
=SUM($J$21:J68)/N68
OCT 1.77807831928623
Year 5 JAN 49 5000
=(D63+D75+2*SUM(D64:D74))/(2*12)
=$T$52+C69*$T$53 =D69/F69
=($T$52+C69*$T$53)*$T$59
=H69-D69 =ABS(J69) =SUM($J$21:J69) =SUMSQ($J$21:J69)/C69
=SUM($K$21:K69)/C69
=K69/D69*100
=AVERAGE($O$21:O69)
=SUM($J$21:J69)/N69
NOV 2.12368220128937
FEB 50 2000
=(D64+D76+2*SUM(D65:D75))/(2*12)
=$T$52+C70*$T$53 =D70/F70
=($T$52+C70*$T$53)*$T$60
=H70-D70 =ABS(J70) =SUM($J$21:J70) =SUMSQ($J$21:J70)/C70
=SUM($K$21:K70)/C70
=K70/D70*100
=AVERAGE($O$21:O70)
=SUM($J$21:J70)/N70
DEC 1.09472006496758
MAR 51 3000
=(D65+D77+2*SUM(D66:D76))/(2*12)
=$T$52+C71*$T$53 =D71/F71
=($T$52+C71*$T$53)*$T$61
=H71-D71 =ABS(J71) =SUM($J$21:J71) =SUMSQ($J$21:J71)/C71
=SUM($K$21:K71)/C71
=K71/D71*100
=AVERAGE($O$21:O71)
=SUM($J$21:J71)/N71
APR 52 2000
=(D66+D78+2*SUM(D67:D77))/(2*12)
=$T$52+C72*$T$53 =D72/F72
=($T$52+C72*$T$53)*$T$62
=H72-D72 =ABS(J72) =SUM($J$21:J72) =SUMSQ($J$21:J72)/C72
=SUM($K$21:K72)/C72
=K72/D72*100
=AVERAGE($O$21:O72)
=SUM($J$21:J72)/N72
MAY 53 7000
=(D67+D79+2*SUM(D68:D78))/(2*12)
=$T$52+C73*$T$53 =D73/F73
=($T$52+C73*$T$53)*$T$63
=H73-D73 =ABS(J73) =SUM($J$21:J73) =SUMSQ($J$21:J73)/C73
=SUM($K$21:K73)/C73
=K73/D73*100
=AVERAGE($O$21:O73)
=SUM($J$21:J73)/N73
JUN 54 6000
=(D68+D80+2*SUM(D69:D79))/(2*12)
=$T$52+C74*$T$53 =D74/F74
=($T$52+C74*$T$53)*$T$64
=H74-D74 =ABS(J74) =SUM($J$21:J74) =SUMSQ($J$21:J74)/C74
=SUM($K$21:K74)/C74
=K74/D74*100
=AVERAGE($O$21:O74)
=SUM($J$21:J74)/N74
JUL 55 8000 =$T$52+C75*$T$53 =D75/F75
=($T$52+C75*$T$53)*$T$65
=H75-D75 =ABS(J75) =SUM($J$21:J75) =SUMSQ($J$21:J75)/C75
=SUM($K$21:K75)/C75
=K75/D75*100
=AVERAGE($O$21:O75)
=SUM($J$21:J75)/N75
AUG 56 10000 =$T$52+C76*$T$53 =D76/F76
=($T$52+C76*$T$53)*$T$66
=H76-D76 =ABS(J76) =SUM($J$21:J76) =SUMSQ($J$21:J76)/C76
=SUM($K$21:K76)/C76
=K76/D76*100
=AVERAGE($O$21:O76)
=SUM($J$21:J76)/N76
SEP 57 20000 =$T$52+C77*$T$53 =D77/F77
=($T$52+C77*$T$53)*$T$67
=H77-D77 =ABS(J77) =SUM($J$21:J77) =SUMSQ($J$21:J77)/C77
=SUM($K$21:K77)/C77
=K77/D77*100
=AVERAGE($O$21:O77)
=SUM($J$21:J77)/N77
OCT 58 20000 =$T$52+C78*$T$53 =D78/F78
=($T$52+C78*$T$53)*$T$68
=H78-D78 =ABS(J78) =SUM($J$21:J78) =SUMSQ($J$21:J78)/C78
=SUM($K$21:K78)/C78
=K78/D78*100
=AVERAGE($O$21:O78)
=SUM($J$21:J78)/N78
NOV 59 22000 =$T$52+C79*$T$53 =D79/F79
=($T$52+C79*$T$53)*$T$69
=H79-D79 =ABS(J79) =SUM($J$21:J79) =SUMSQ($J$21:J79)/C79
=SUM($K$21:K79)/C79
=K79/D79*100
=AVERAGE($O$21:O79)
=SUM($J$21:J79)/N79
DEC 60 8000 =$T$52+C80*$T$53 =D80/F80
=($T$52+C80*$T$53)*$T$70
=H80-D80 =ABS(J80) =SUM($J$21:J80) =SUMSQ($J$21:J80)/C80
=SUM($K$21:K80)/C80
=K80/D80*100
=AVERAGE($O$21:O80)
=SUM($J$21:J80)/N80
Year 6 JAN 61 Forecasts
=($T$52+C81*$T$53)*$T$59
Estimate of standard deviation of forecast error:
=N80*1.25
FEB 62
=($T$52+C82*$T$53)*$T$60
MAR 63
=($T$52+C83*$T$53)*$T$61
APR 64
=($T$52+C84*$T$53)*$T$62
MAY 65
=($T$52+C85*$T$53)*$T$63
JUN 66
=($T$52+C86*$T$53)*$T$64
JUL 67
=($T$52+C87*$T$53)*$T$65
AUG 68
=($T$52+C88*$T$53)*$T$66
SEP 69
=($T$52+C89*$T$53)*$T$67
OCT 70
=($T$52+C90*$T$53)*$T$68
NOV 71
=($T$52+C91*$T$53)*$T$69
DEC 72
=($T$52+C92*$T$53)*$T$70
–
5,000
10,000
15,000
20,000
25,000
Sales
Month
Monthly Demand ABC Corporation
(7,000)
(6,000)
(5,000)
(4,000)
(3,000)
(2,000)
(1,000)
–
1,000
2,000
3,000
1 4 7 10 13 16 19 22 25 28 31 34 37 40 43 46 49 52 55 58
Bias
Period
Bias