Question

2. Consider the following time series data: 2Month Value 20 15 23 6 4 13 6 18 25 10 8 10 9 24 12 10 21 13 19 14 15 la. Use a α # 0.25 to compute the exponential smoothing values for the time series. Compute MSE and a forecast for Month 12. b. Compare the two-month moving average forecast with the exponential smoothing forecast using a 0.25. Which appears to 17 provide the better forecast based on MSE? rial and error to find a value of the exponential smoothing coofficilent a that results in a smaller MSE than what you 19. calculated for α :0.25 Can you piease Show how got the answer neps me Undlerstand H better hank you So much

Answer 1

a. If Y(t) is the value for month t, the forecast for month t+1 using exponential smoothing model is

F(t + 1) ay(t) + (1-0)F(t) 0.25y(t) + (1-0.25)F(t)

We will set the first month forecast as the the actual value, that is F(1) = 20. The forecast for month 2 is

F(2) = 0.25%(1) (1-0.25)F( 1 ) = 0.25 × 20 (1-0.25) × 20 20

We compute the 2 month moving average forecast using

F(t +1) = 백110

For example the forecast for month 3 is

Y(t) 20 +15175

Since we want to compare a 2 month moving average with exponential forecast, we will calculate the MSE (Mean Square error) using the data from 3rd month to month 11

$\text{MSE}=\frac{\sum_{t=3}^{11} (Y(t)-F(t))^2}{9}$

Prepare the following sheet

alpha 0.25 Exponential smoothing 2 month mving 2 Month Value average forecast MSE MSE orecast 20 -B3 15 (B4+B3)/2 -B5-E5%2 23 (B5+B4)/2 (B6-E6)A2 -(B6+B5)/2 (B7+B6)/2 (B8+B7)/2 13 (B7-E7)A2 18 25 $C$1*B8+(1-$C$1) C8(B9-C9)A2 -$C$1* B9+(1-SC$1) C9(B10-C10)A2 $C$1 B10+(1-SC$1) C10 (B11-C11)A2 $C$1 B11+(1-SC$1) C11 (B12-C12)A2 SC$1 B12+(1-SC$1)*C12 (B13-C13)A2 -SCS1"B13+(1-SCSI)"C13 (B9-E9)A2 (B9+B8)/2 (B10+B9)/2 10 8 10 (B10-E10)A2 11 9 24 (B11-E11)A2 12 10 21 (B11+B10)/2 (B12-E12)A2 13 11 19 (B12+B11)/2 (B13+B12)/2 (B13-E13)A2 14 12 AVERAGE (D5:D13) AVERAGE(F5:F13) 15 MSE

We have kept the value of alpha in a separate cell so that we can do the trial and error required in part c)

get this

0.25 alpha 2 month mving Exponential average smoothing forecast MSE 2 Month Value forecastMSE 20.00 20 20.00 15 30.25 17.5 18.06 18.75 23 64 19 77.66 19.81 4 16 17 21.25 17.61 13 5 36 12 2.38 16.46 18 6 90.25 15.5 66.54 16.84 25 21.5 132.25 78.89 18.88 10 10 42.25 17.5 53.85 16.66 24 16 17 6.27 18.50 21 10 12 12.25 22.5 0.01 19.12 19 13 20 19.09 12 14 48.81 36.10 MSE 15

a) MSE for exponential forecast with alpha=0.025 is 34.99, Forecast for the month of 12 is 19.09

b) The MSE for 2 month moving average forecast is 48.81 and it is larger than the MSE for exponential smoothing forecast. Hence we can say that exponential smoothing method provides a better forecast based on MSE

c. Now we can use trial and error by changing the value of alpha in cell C1. Try values between 0 and 1 starting from 0.01 to 1.

Or we can use the solver in excel to find the value of alpha which will give the lowest MSE.

Set up the solver using data---> solver

Solver Parameters alpha 0.25 Set Objective: SD$15 2 month mving Exponential To: smoothing average 2 Month Valueforecast MSE forecast MSE By Changing Variable Cells: 20 20.00 SCS1 15 4 20.00 Subject to the Constraints 23 18.75 18.06 17.5 19 19.81 77.66 17.61 13 21.25 17 Change 18 6 16.46 2.38 12 25 16.84 66.54 15.5 Delete 10 78.89 10 18.88 21.5 24 16.66 53.85 17.5 Reset All 12 17 10 21 18.50 6.27 13 19 0.01 22.5 19.12 14 20 12 19.09 Make Unconstrained Variables Non-Negative 15 MSE 36.10 Select a Solving Method: GRG Nonlinear Options 16 17 Solving Method 18 19 Select the GRG Nonlinear engine for Solver Problems that are smooth nonlinear. Select the LP Simplex engine for linear Solver Problems, and select the Evolutionary engine for Solver problems that are non-smooth. 20

get this

0.026 alpha 2 month mving Exponential smoothing average forecast MSE 2 Month Value forecastMSE 20.00 20 20.00 15 30.25 17.5 9.80 19.87 23 64 19 80.12 19.95 4 16 17 45.12 19.72 13 5 36 12 2.38 19.54 18 6 90.25 15.5 30.23 19.50 25 21.5 132.25 19.65 93.03 10 10 42.25 17.5 21.22 19.39 24 16 17 2.21 19.51 21 10 12 12.25 22.5 19.55 0.31 19 13 20 19.54 12 48.81 31.60 MSE 15

We can see that for alpha=0.03, results in a smaller MSE than we calculated for alpha=0.25