Question

The Emmett Bath Tub Company sells bathtub in West Virginia. The number sold for the past several months is given along with the forecasts using both 3-month moving average and exponential smoothing (α = .2). Determine the better forecast using the MSE.

Month	Sales	3-month moving average	Error	Exponential    Smoothing	Error
Jan	13
Feb	18
March	15
Apr	12
May	14
June	15
July	10

Answer 1

Moving Average:

period 3 mont MA forecast error ABS(error) month Jan sales 13 18 Feb WN Mar Apr May Jun 15.33 15.00 13.67 13.67 -1.00 1.33 -3.67 3.33 1.00 1.33 3.67 Jul -1.67 BIAS 2.33 MAD 6.833 MSE

Formula Ref:

period sales 3 mont MA forecast error ABS(error) month Jan Feb Mar NO Apr May =AVERAGE(C6:08) =AVERAGE(C7:09) =AVERAGE(C8:C10) =AVERAGE(C9:C11) =C9-09 =C10-D10 =C11-D11 =C12-D12 =ABS(E9) =ABS(E10) =ABS(E11) =ABS(E12) =AVERAGE(E9:E25) =AVERAGE(F9:F25) =SUMSQ(E9:E25)/COUNT(E9:E17) MAD MSE AVERACEF93 F25) "SUMSDE BIAS

Exponential:

Alpha = 0.2 period sales error ABS(error) month Jan Feb Mar Apr May Jun forecast 13.00 13.00 14.00 14.20 13.76 13.81 14.05 5.00 1.00 -2.20 0.24 1.19 -4.05 5.00 1.00 2.20 0.24 1.19 4.05 Jul 14 18 19 20 22 23 24 0.20 BIAS 2.28 MAD 8.12 MSE 26

Formula Ref:

Alpha = 0.2 period sales ABS(error) 4 5 1 62 month Jan Feb Mar 8 4 forecast error =C5 =$D$3*C5+(1-$D$3)*D5 =C6-D6 =$D$3*C6+(1-$D$3)*D6 =C7-D7 =$D$3*C7+(1-$D$3)*D7 =C8-D8 =$D$3*C8+(1-$D$3)*D8 =C9-D9 =$D$3*C9+(1-$D$3)*D9 =C10-D10 =$D$3*C10+(1-$D$3)*D10 I=C11-D11 Apr =ABS(E6) =ABS(E7) =ABS(E8) =ABS(E9) =ABS(E10) I =ABS(E11) May Jun Jul 15 19 20 21 23 24 =AVERAGE(E6:E24) BIAS =AVERAGE(F6:F24) =SUMSQ(E6:E24)/COUNT(E6:E16) MSE MAD 27