Exercise 7-3: Flowers
Exponential Smoothing
Year Quarter Period Sales Level Forecast
EtAtBias MSE MAD
Percent
Error
MAPE TS Regression – Sales vs. Period
0 132.6
1I 198 129.1 132.6 34.6 34.6 34.6 1194.6 34.6 35.3 35.3 1.00 Regression Statistics
II 2106 126.8 129.1 23.1 23.1 57.7 864.2 28.8 21.8 28.5 2.00 Multiple R 0.892597371
III 3109 125.0 126.8 17.8 17.8 75.5 681.7 25.2 16.3 24.5 3.00 R Square 0.796730067
IV 4133 125.8 125.0 -8.0 8.0 67.5 527.2 20.9 6.0 19.8 3.23 Adjusted R Square 0.782210786
2I 5130 126.2 125.8 -4.2 4.2 63.3 425.3 17.5 3.2 16.5 3.61 Standard Error 9.513794717
II 6116 125.2 126.2 10.2 10.2 73.5 371.9 16.3 8.8 15.2 4.51 Observations 16
III 7133 126.0 125.2 -7.8 7.8 65.7 327.4 15.1 5.9 13.9 4.36
IV 8116 125.0 126.0 10.0 10.0 75.7 298.9 14.5 8.6 13.2 5.24 ANOVA
3I 9138 126.3 125.0 -13.0 13.0 62.7 284.5 14.3 9.4 12.8 4.39 df SS MS F Significance F
II 10 130 126.7 126.3 -3.7 3.7 59.0 257.5 13.2 2.9 11.8 4.46 Regression 1 4966.765441 4966.765441 54.8739342 3.31348E-06
III 11 147 128.7 126.7 -20.3 20.3 38.7 271.7 13.9 13.8 12.0 2.79 Residual 14 1267.172059 90.51228992
IV 12 141 129.9 128.7 -12.3 12.3 26.4 261.6 13.8 8.7 11.7 1.92 Total 15 6233.9375
4I 13 144 131.3 129.9 -14.1 14.1 12.3 256.7 13.8 9.8 11.6 0.89
II 14 142 132.4 131.3 -10.7 10.7 1.6 246.5 13.6 7.5 11.3 0.12 Coefficients Standard Error t Stat P-value
III 15 165 135.7 132.4 -32.6 32.6 -31.0 301.0 14.8 19.8 11.9 -2.09 Intercept 100.08 4.989076039 20.05882436 1.03336E-11
IV 16 173 139.4 135.7 -37.3 37.3 -68.3 369.3 16.2 21.6 12.5 -4.21 X Variable 1 3.82 0.515957961 7.407694257 3.31348E-06
5I 17 139.4 Estimate of standard deviation of forecast error: 20.29
II 18 139.4
III 19 139.4
IV 20 139.4
Holt’s Model
Year Quarter Period Sales Level Trend Forecast
EtAtBias MSE MAD
Percent
Error
MAPE TS
0 100.0 3.82
1I 198 103.2 3.76 103.8 5.8 5.8 5.8 33.9 5.8 5.9 5.9 1.00
II 2106 106.9 3.75 107.0 1.0 1.0 6.8 17.4 3.4 0.9 3.4 2.00
III 3109 110.5 3.74 110.7 1.7 1.7 8.5 12.5 2.8 1.5 2.8 3.00
Exercise 7-3: Flowers
Exponential Smoothing
Year Quarter Period Sales Level Forecast
EtAtBias MSE MAD Percent Error MAPE TS
0100
1I 198 =0.1*D6+(1-0.1)*E5 =E5+F5 =G6–D6 =ABS(I6) =SUM($I$6:I6)
=SUMSQ($I$6:I6)/C6
=SUM($J$6:J6)/C6 =J6/D6*100
=AVERAGE($N$6:N6)
=SUM($I$6:I6)/M6 Regression Statistics
II 2106 =0.1*D7+(1-0.1)*E6 =E6+F6 =G7–D7 =ABS(I7) =SUM($I$6:I7)
=SUMSQ($I$6:I7)/C7
=SUM($J$6:J7)/C7 =J7/D7*100
=AVERAGE($N$6:N7)
=SUM($I$6:I7)/M7 Multiple R 0.892597371080638
III 3109 =0.1*D8+(1-0.1)*E7 =E7+F7 =G8–D8 =ABS(I8) =SUM($I$6:I8)
=SUMSQ($I$6:I8)/C8
=SUM($J$6:J8)/C8 =J8/D8*100
=AVERAGE($N$6:N8)
=SUM($I$6:I8)/M8 R Square 0.796730066860066
IV 4133 =0.1*D9+(1-0.1)*E8 =E8+F8 =G9–D9 =ABS(I9) =SUM($I$6:I9)
=SUMSQ($I$6:I9)/C9
=SUM($J$6:J9)/C9 =J9/D9*100
=AVERAGE($N$6:N9)
=SUM($I$6:I9)/M9 Adjusted R Square 0.782210785921499
2I 5130 =0.1*D10+(1-0.1)*E9 =E9+F9 =G10-D10 =ABS(I10) =SUM($I$6:I10)
=SUMSQ($I$6:I10)/C10
=SUM($J$6:J10)/C10
=J10/D10*100
=AVERAGE($N$6:N10)
=SUM($I$6:I10)/M10
Standard Error 9.51379471693427
II 6116
=0.1*D11+(1-0.1)*E10
=E10+F10 =G11-D11 =ABS(I11) =SUM($I$6:I11)
=SUMSQ($I$6:I11)/C11
=SUM($J$6:J11)/C11
=J11/D11*100
=AVERAGE($N$6:N11)
=SUM($I$6:I11)/M11
Observations 16
III 7133
=0.1*D12+(1-0.1)*E11
=E11+F11 =G12-D12 =ABS(I12) =SUM($I$6:I12)
=SUMSQ($I$6:I12)/C12
=SUM($J$6:J12)/C12
=J12/D12*100
=AVERAGE($N$6:N12)
=SUM($I$6:I12)/M12
IV 8116
=0.1*D13+(1-0.1)*E12
=E12+F12 =G13-D13 =ABS(I13) =SUM($I$6:I13)
=SUMSQ($I$6:I13)/C13
=SUM($J$6:J13)/C13
=J13/D13*100
=AVERAGE($N$6:N13)
=SUM($I$6:I13)/M13
ANOVA
3I 9138
=0.1*D14+(1-0.1)*E13
=E13+F13 =G14-D14 =ABS(I14) =SUM($I$6:I14)
=SUMSQ($I$6:I14)/C14
=SUM($J$6:J14)/C14
=J14/D14*100
=AVERAGE($N$6:N14)
=SUM($I$6:I14)/M14
df SS MS F Significance F
II 10 130
=0.1*D15+(1-0.1)*E14
=E14+F14 =G15-D15 =ABS(I15) =SUM($I$6:I15)
=SUMSQ($I$6:I15)/C15
=SUM($J$6:J15)/C15
=J15/D15*100
=AVERAGE($N$6:N15)
=SUM($I$6:I15)/M15
Regression 1 4966.76544117647 4966.76544117647 54.8739342004022
III 11 147
=0.1*D16+(1-0.1)*E15
=E15+F15 =G16-D16 =ABS(I16) =SUM($I$6:I16)
=SUMSQ($I$6:I16)/C16
=SUM($J$6:J16)/C16
=J16/D16*100
=AVERAGE($N$6:N16)
=SUM($I$6:I16)/M16
Residual 14 1267.17205882353 90.5122899159664
IV 12 141
=0.1*D17+(1-0.1)*E16
=E16+F16 =G17-D17 =ABS(I17) =SUM($I$6:I17)
=SUMSQ($I$6:I17)/C17
=SUM($J$6:J17)/C17
=J17/D17*100
=AVERAGE($N$6:N17)
=SUM($I$6:I17)/M17
Total 15 6233.9375
4I 13 144
=0.1*D18+(1-0.1)*E17
=E17+F17 =G18-D18 =ABS(I18) =SUM($I$6:I18)
=SUMSQ($I$6:I18)/C18
=SUM($J$6:J18)/C18
=J18/D18*100
=AVERAGE($N$6:N18)
=SUM($I$6:I18)/M18
II 14 142
=0.1*D19+(1-0.1)*E18
=E18+F18 =G19-D19 =ABS(I19) =SUM($I$6:I19)
=SUMSQ($I$6:I19)/C19
=SUM($J$6:J19)/C19
=J19/D19*100
=AVERAGE($N$6:N19)
=SUM($I$6:I19)/M19
Coefficients Standard Error t Stat P-value
III 15 165
=0.1*D20+(1-0.1)*E19
=E19+F19 =G20-D20 =ABS(I20) =SUM($I$6:I20)
=SUMSQ($I$6:I20)/C20
=SUM($J$6:J20)/C20
=J20/D20*100
=AVERAGE($N$6:N20)
=SUM($I$6:I20)/M20
Intercept 100.075 4.98907603939755 20.0588243614111
1.03336499348649E-11
IV 16 173
=0.1*D21+(1-0.1)*E20
=E20+F20 =G21-D21 =ABS(I21) =SUM($I$6:I21)
=SUMSQ($I$6:I21)/C21
=SUM($J$6:J21)/C21
=J21/D21*100
=AVERAGE($N$6:N21)
=SUM($I$6:I21)/M21
X Variable 1 3.82205882352941 0.515957960884316 7.40769425667679
3.31347969327451E-06
5I 17 =E21
Estimate of standard deviation of forecast error:
=1.25*O21
II 18 =G22
III 19 =G23
IV 20 =G24
Holt‘s Model
Year Quarter Period Sales Level Trend Forecast
EtAtBias MSE MAD Percent Error MAPE TS
0100 3.82
1I 198
=0.1*D31+(1-0.1)*(E30+F30)
=0.1*(E31–E30)+(1-0.1)*F30
=E30+F30 =G31-D31 =ABS(I31) =SUM($I$31:I31)
=SUMSQ($I$31:I31)/C31
=SUM($J$31:J31)/C31
=J31/D31*100
=AVERAGE($N$31:N31)
=SUM($I$31:I31)/M31
II 2106
=0.1*D32+(1-0.1)*(E31+F31)
=0.1*(E32–E31)+(1-0.1)*F31
=E31+F31 =G32-D32 =ABS(I32) =SUM($I$31:I32)
=SUMSQ($I$31:I32)/C32
=SUM($J$31:J32)/C32
=J32/D32*100
=AVERAGE($N$31:N32)
=SUM($I$31:I32)/M32
III 3109
=0.1*D33+(1-0.1)*(E32+F32)
=0.1*(E33–E32)+(1-0.1)*F32
=E32+F32 =G33-D33 =ABS(I33) =SUM($I$31:I33)
=SUMSQ($I$31:I33)/C33
=SUM($J$31:J33)/C33
=J33/D33*100
=AVERAGE($N$31:N33)
=SUM($I$31:I33)/M33
IV 4133
=0.1*D34+(1-0.1)*(E33+F33)
=0.1*(E34–E33)+(1-0.1)*F33
=E33+F33 =G34-D34 =ABS(I34) =SUM($I$31:I34)
=SUMSQ($I$31:I34)/C34
=SUM($J$31:J34)/C34
=J34/D34*100
=AVERAGE($N$31:N34)
=SUM($I$31:I34)/M34
2I 5130
=0.1*D35+(1-0.1)*(E34+F34)
=0.1*(E35–E34)+(1-0.1)*F34
=E34+F34 =G35-D35 =ABS(I35) =SUM($I$31:I35)
=SUMSQ($I$31:I35)/C35
=SUM($J$31:J35)/C35
=J35/D35*100
=AVERAGE($N$31:N35)
=SUM($I$31:I35)/M35
II 6116
=0.1*D36+(1-0.1)*(E35+F35)
=0.1*(E36–E35)+(1-0.1)*F35
=E35+F35 =G36-D36 =ABS(I36) =SUM($I$31:I36)
=SUMSQ($I$31:I36)/C36
=SUM($J$31:J36)/C36
=J36/D36*100
=AVERAGE($N$31:N36)
=SUM($I$31:I36)/M36
III 7133
=0.1*D37+(1-0.1)*(E36+F36)
=0.1*(E37–E36)+(1-0.1)*F36
=E36+F36 =G37-D37 =ABS(I37) =SUM($I$31:I37)
=SUMSQ($I$31:I37)/C37
=SUM($J$31:J37)/C37
=J37/D37*100
=AVERAGE($N$31:N37)
=SUM($I$31:I37)/M37
IV 8116
=0.1*D38+(1-0.1)*(E37+F37)
=0.1*(E38–E37)+(1-0.1)*F37
=E37+F37 =G38-D38 =ABS(I38) =SUM($I$31:I38)
=SUMSQ($I$31:I38)/C38
=SUM($J$31:J38)/C38
=J38/D38*100
=AVERAGE($N$31:N38)
=SUM($I$31:I38)/M38
3I 9138
=0.1*D39+(1-0.1)*(E38+F38)
=0.1*(E39–E38)+(1-0.1)*F38
=E38+F38 =G39-D39 =ABS(I39) =SUM($I$31:I39)
=SUMSQ($I$31:I39)/C39
=SUM($J$31:J39)/C39
=J39/D39*100
=AVERAGE($N$31:N39)
=SUM($I$31:I39)/M39
II 10 130
=0.1*D40+(1-0.1)*(E39+F39)
=0.1*(E40–E39)+(1-0.1)*F39
=E39+F39 =G40-D40 =ABS(I40) =SUM($I$31:I40)
=SUMSQ($I$31:I40)/C40
=SUM($J$31:J40)/C40
=J40/D40*100
=AVERAGE($N$31:N40)
=SUM($I$31:I40)/M40
III 11 147
=0.1*D41+(1-0.1)*(E40+F40)
=0.1*(E41–E40)+(1-0.1)*F40
=E40+F40 =G41-D41 =ABS(I41) =SUM($I$31:I41)
=SUMSQ($I$31:I41)/C41
=SUM($J$31:J41)/C41
=J41/D41*100
=AVERAGE($N$31:N41)
=SUM($I$31:I41)/M41
IV 12 141
=0.1*D42+(1-0.1)*(E41+F41)
=0.1*(E42–E41)+(1-0.1)*F41
=E41+F41 =G42-D42 =ABS(I42) =SUM($I$31:I42)
=SUMSQ($I$31:I42)/C42
=SUM($J$31:J42)/C42
=J42/D42*100
=AVERAGE($N$31:N42)
=SUM($I$31:I42)/M42
4I 13 144
=0.1*D43+(1-0.1)*(E42+F42)
=0.1*(E43–E42)+(1-0.1)*F42
=E42+F42 =G43-D43 =ABS(I43) =SUM($I$31:I43)
=SUMSQ($I$31:I43)/C43
=SUM($J$31:J43)/C43
=J43/D43*100
=AVERAGE($N$31:N43)
=SUM($I$31:I43)/M43
II 14 142
=0.1*D44+(1-0.1)*(E43+F43)
=0.1*(E44–E43)+(1-0.1)*F43
=E43+F43 =G44-D44 =ABS(I44) =SUM($I$31:I44)
=SUMSQ($I$31:I44)/C44
=SUM($J$31:J44)/C44
=J44/D44*100
=AVERAGE($N$31:N44)
=SUM($I$31:I44)/M44
III 15 165
=0.1*D45+(1-0.1)*(E44+F44)
=0.1*(E45–E44)+(1-0.1)*F44
=E44+F44 =G45-D45 =ABS(I45) =SUM($I$31:I45)
=SUMSQ($I$31:I45)/C45
=SUM($J$31:J45)/C45
=J45/D45*100
=AVERAGE($N$31:N45)
=SUM($I$31:I45)/M45
IV 16 173
=0.1*D46+(1-0.1)*(E45+F45)
=0.1*(E46–E45)+(1-0.1)*F45
=E45+F45 =G46-D46 =ABS(I46) =SUM($I$31:I46)
=SUMSQ($I$31:I46)/C46
=SUM($J$31:J46)/C46
=J46/D46*100
=AVERAGE($N$31:N46)
=SUM($I$31:I46)/M46
5I 17
=$E$46+$F$46*(C47-16)
Estimate of standard deviation of forecast error:
=1.25*O46
II 18
=$E$46+$F$46*(C48-16)
III 19
=$E$46+$F$46*(C49-16)
IV 20
=$E$46+$F$46*(C50-16)