Exercise 7-6 A&D Electronics
Exponential Smoothing
Period Sales Level Forecast
EtAtBias MSE MAD
Percent
Error
MAPE TS
0 1,659
1 1,000 1,461 1,659 659 659 659 434,281 659 66 66 1.00
2 1,113 1,357 1,461 348 348 1,007 277,797 504 31 49 2.00
3 1,271 1,331 1,357 86 86 1,093 187,652 364 735 3.00
4 1,445 1,365 1,331 (114) 114 979 143,984 302 828 3.24
5 1,558 1,423 1,365 (193) 193 786 122,618 280 12 25 2.81
6 1,648 1,491 1,423 (225) 225 561 110,614 271 14 23 2.07
7 1,724 1,561 1,491 (233) 233 328 102,597 265 14 22 1.24
8 1,850 1,647 1,561 (289) 289 39 100,243 268 16 21 0.14
9 1,864 1,712 1,647 (217) 217 (178) 94,317 263 12 20 -0.68
10 2,076 1,821 1,712 (364) 364 (542) 98,107 273 18 20 -1.99
11 2,167 1,925 1,821 (346) 346 (887) 100,042 279 16 19 -3.18
12 2,191 2,005 1,925 (266) 266 (1,153) 97,596 278 12 19 -4.14
13 2,005
14 2,005
Estimate of standard deviation of forecast error: 347.83
Holt’s Model
Period Sales Level Trend Forecast
EtAtBias MSE MAD
Percent
Error
MAPE TS
Estimate of standard deviation of forecast error: 54.13
Holt’s model has lower forecast error and is better than the simple exponential smoothing model
–
500
1,000
1,500
2,000
2,500
12345678910 11 12
Sales
Exercise 7-6 A&D Electronics
Exponential Smoothing
Period Sales Level Forecast
EtAtBias MSE MAD Percent Error MAPE TS
01659
11000
=0.3*D6+(1-0.3)*E5
=E5+F5 =G6-D6 =ABS(J6) =SUM($J$6:J6) =SUMSQ($J$6:J6)/C6
=SUM($K$6:K6)/C6
=K6/D6*100
=AVERAGE($O$6:O6)
=SUM($J$6:J6)/N6
21113
=0.3*D7+(1-0.3)*E6
=E6+F6 =G7-D7 =ABS(J7) =SUM($J$6:J7) =SUMSQ($J$6:J7)/C7
=SUM($K$6:K7)/C7
=K7/D7*100
=AVERAGE($O$6:O7)
=SUM($J$6:J7)/N7
31271
=0.3*D8+(1-0.3)*E7
=E7+F7 =G8-D8 =ABS(J8) =SUM($J$6:J8) =SUMSQ($J$6:J8)/C8
=SUM($K$6:K8)/C8
=K8/D8*100
=AVERAGE($O$6:O8)
=SUM($J$6:J8)/N8
41445
=0.3*D9+(1-0.3)*E8
=E8+F8 =G9-D9 =ABS(J9) =SUM($J$6:J9) =SUMSQ($J$6:J9)/C9
=SUM($K$6:K9)/C9
=K9/D9*100
=AVERAGE($O$6:O9)
=SUM($J$6:J9)/N9
51558
=0.3*D10+(1-0.3)*E9
=E9+F9 =G10-D10 =ABS(J10)
=SUM($J$6:J10)
=SUMSQ($J$6:J10)/C10
=SUM($K$6:K10)/C10
=K10/D10*100
=AVERAGE($O$6:O10)
=SUM($J$6:J10)/N10
61648
=0.3*D11+(1-0.3)*E10
=E10+F10 =G11-D11 =ABS(J11)
=SUM($J$6:J11)
=SUMSQ($J$6:J11)/C11
=SUM($K$6:K11)/C11
=K11/D11*100
=AVERAGE($O$6:O11)
=SUM($J$6:J11)/N11
71724
=0.3*D12+(1-0.3)*E11
=E11+F11 =G12-D12 =ABS(J12)
=SUM($J$6:J12)
=SUMSQ($J$6:J12)/C12
=SUM($K$6:K12)/C12
=K12/D12*100
=AVERAGE($O$6:O12)
=SUM($J$6:J12)/N12
81850
=0.3*D13+(1-0.3)*E12
=E12+F12 =G13-D13 =ABS(J13)
=SUM($J$6:J13)
=SUMSQ($J$6:J13)/C13
=SUM($K$6:K13)/C13
=K13/D13*100
=AVERAGE($O$6:O13)
=SUM($J$6:J13)/N13
91864
=0.3*D14+(1-0.3)*E13
=E13+F13 =G14-D14 =ABS(J14)
=SUM($J$6:J14)
=SUMSQ($J$6:J14)/C14
=SUM($K$6:K14)/C14
=K14/D14*100
=AVERAGE($O$6:O14)
=SUM($J$6:J14)/N14
10 2076
=0.3*D15+(1-0.3)*E14
=E14+F14 =G15-D15 =ABS(J15)
=SUM($J$6:J15)
=SUMSQ($J$6:J15)/C15
=SUM($K$6:K15)/C15
=K15/D15*100
=AVERAGE($O$6:O15)
=SUM($J$6:J15)/N15
11 2167
=0.3*D16+(1-0.3)*E15
=E15+F15 =G16-D16 =ABS(J16)
=SUM($J$6:J16)
=SUMSQ($J$6:J16)/C16
=SUM($K$6:K16)/C16
=K16/D16*100
=AVERAGE($O$6:O16)
=SUM($J$6:J16)/N16
12 2191
=0.3*D17+(1-0.3)*E16
=E16+F16 =G17-D17 =ABS(J17)
=SUM($J$6:J17)
=SUMSQ($J$6:J17)/C17
=SUM($K$6:K17)/C17
=K17/D17*100
=AVERAGE($O$6:O17)
=SUM($J$6:J17)/N17
13 =E17+F17
14 =G18
Estimate of standard deviation of forecast error:
=1.25*N17
Holt’s Model
Period Sales Level Trend Forecast
EtAtBias MSE MAD Percent Error MAPE TS
0948 109
11000
=0.05*D31+(1-0.05)*(E30+F30)
=0.1*(E31-E30)+(1-0.1)*F30
=E30+F30 =G31-D31 =ABS(J31)
=SUM($J$31:J31)
=SUMSQ($J$31:J31)/C31
=SUM($K$31:K31)/C31
=K31/D31*100
=AVERAGE($O$31:O31)
=SUM($J$31:J31)/N31
21113
=0.05*D32+(1-0.05)*(E31+F31)
=0.1*(E32-E31)+(1-0.1)*F31
=E31+F31 =G32-D32 =ABS(J32)
=SUM($J$31:J32)
=SUMSQ($J$31:J32)/C32
=SUM($K$31:K32)/C32
=K32/D32*100
=AVERAGE($O$31:O32)
=SUM($J$31:J32)/N32
31271
=0.05*D33+(1-0.05)*(E32+F32)
=0.1*(E33-E32)+(1-0.1)*F32
=E32+F32 =G33-D33 =ABS(J33)
=SUM($J$31:J33)
=SUMSQ($J$31:J33)/C33
=SUM($K$31:K33)/C33
=K33/D33*100
=AVERAGE($O$31:O33)
=SUM($J$31:J33)/N33
41445
=0.05*D34+(1-0.05)*(E33+F33)
=0.1*(E34-E33)+(1-0.1)*F33
=E33+F33 =G34-D34 =ABS(J34)
=SUM($J$31:J34)
=SUMSQ($J$31:J34)/C34
=SUM($K$31:K34)/C34
=K34/D34*100
=AVERAGE($O$31:O34)
=SUM($J$31:J34)/N34
51558
=0.05*D35+(1-0.05)*(E34+F34)
=0.1*(E35-E34)+(1-0.1)*F34
=E34+F34 =G35-D35 =ABS(J35)
=SUM($J$31:J35)
=SUMSQ($J$31:J35)/C35
=SUM($K$31:K35)/C35
=K35/D35*100
=AVERAGE($O$31:O35)
=SUM($J$31:J35)/N35
61648
=0.05*D36+(1-0.05)*(E35+F35)
=0.1*(E36-E35)+(1-0.1)*F35
=E35+F35 =G36-D36 =ABS(J36)
=SUM($J$31:J36)
=SUMSQ($J$31:J36)/C36
=SUM($K$31:K36)/C36
=K36/D36*100
=AVERAGE($O$31:O36)
=SUM($J$31:J36)/N36
71724
=0.05*D37+(1-0.05)*(E36+F36)
=0.1*(E37-E36)+(1-0.1)*F36
=E36+F36 =G37-D37 =ABS(J37)
=SUM($J$31:J37)
=SUMSQ($J$31:J37)/C37
=SUM($K$31:K37)/C37
=K37/D37*100
=AVERAGE($O$31:O37)
=SUM($J$31:J37)/N37
81850
=0.05*D38+(1-0.05)*(E37+F37)
=0.1*(E38-E37)+(1-0.1)*F37
=E37+F37 =G38-D38 =ABS(J38)
=SUM($J$31:J38)
=SUMSQ($J$31:J38)/C38
=SUM($K$31:K38)/C38
=K38/D38*100
=AVERAGE($O$31:O38)
=SUM($J$31:J38)/N38
91864
=0.05*D39+(1-0.05)*(E38+F38)
=0.1*(E39-E38)+(1-0.1)*F38
=E38+F38 =G39-D39 =ABS(J39)
=SUM($J$31:J39)
=SUMSQ($J$31:J39)/C39
=SUM($K$31:K39)/C39
=K39/D39*100
=AVERAGE($O$31:O39)
=SUM($J$31:J39)/N39
10 2076
=0.05*D40+(1-0.05)*(E39+F39)
=0.1*(E40-E39)+(1-0.1)*F39
=E39+F39 =G40-D40 =ABS(J40)
=SUM($J$31:J40)
=SUMSQ($J$31:J40)/C40
=SUM($K$31:K40)/C40
=K40/D40*100
=AVERAGE($O$31:O40)
=SUM($J$31:J40)/N40
11 2167
=0.05*D41+(1-0.05)*(E40+F40)
=0.1*(E41-E40)+(1-0.1)*F40
=E40+F40 =G41-D41 =ABS(J41)
=SUM($J$31:J41)
=SUMSQ($J$31:J41)/C41
=SUM($K$31:K41)/C41
=K41/D41*100
=AVERAGE($O$31:O41)
=SUM($J$31:J41)/N41
12 2191
=0.05*D42+(1-0.05)*(E41+F41)
=0.1*(E42-E41)+(1-0.1)*F41
=E41+F41 =G42-D42 =ABS(J42)
=SUM($J$31:J42)
=SUMSQ($J$31:J42)/C42
=SUM($K$31:K42)/C42
=K42/D42*100
=AVERAGE($O$31:O42)
=SUM($J$31:J42)/N42
13 =E42+F42
14 =E42+F42*(C44-C42)
Estimate of standard deviation of forecast error:
=1.25*N42
Holt’s model has lower forecast error and is better than the simple exponential smoothing model