Welcome.
In this video, I’m going to calculate exponential smoothing forecasts for these time series
data in Excel and use Solver to determine the best smoothing
constant or alpha value.
I’m going to calculate absolute errors, squared errors,
and absolute percent errors.
For now, I have chosen a random value for alpha, which is 0.4 here.
In exponential smoothing, if you’re not given a forecast for the first period, then
the forecast is the actual value for the first period.
Consequently, it will be the forecast for the second period as well.
So, we start by setting the forecast in the second period to the actual value in the first
period.
Our first calculation is therefore in the 3rd period.
The formula is α*Actual (in the last period)
+ (1-α)*Forecast (in the last period) We type = alpha and press F4 to make it absolute.
In essence, those dollar signs ensure that the alpha cell is locked down when we copy
the formula here to other cells.
So α *actual in the 2nd period + (1- α) - F4 to lock it down - times the forecast
in the second period.
Then grab the handle and drag it down till Period 7.
For the absolute errors, we type =ABS(), and then Actual minus Forecast Value.
You can then double-click on the handle to copy the formula all the way down.
For the squared error, square the absolute error and double click the handle to copy
it down.
For Absolute Percent Error, divide the absolute error by the Actual value and multiply by
100.
Then double-click to copy it down.
To find MAD, we use =AVERAGE to find the mean of the absolute errors.
Then drag to copy the formula for MSE and MAPE.
Next, we determine the best alpha using Excel’s Solver.
Note that the best alpha that will minimize MAD may differ from the one that will minimize
MSE.
Click the Data tab, then Solver.
If you don’t see Solver there, please see description on how to install it.
Now, suppose we want the best alpha that minimizes MAD:
For Set Objective, we click on the MAD value.
Click Min for minimization.
The variable value we want to change is alpha, so we click the alpha value for the Changing
Variable Cells.
Now alpha needs to be between 0 and 1.
This checked box here says, Make Unconstrained Variable Non-negative.
That is, we leave this box checked if we want alpha to be greater or equal to zero.
To ensure alpha is less than or equal to 1, we click Add here to add the constraint.
For cell reference, we select the alpha value.
Make sure the ≤ sign is selected, then input 1 on the right here.
Click OK.
Leave this Method here as Non-Linear and click Solve.
You can see here that the optimal alpha value is 0.3084.
That is, to obtain the smallest MAD possible, we should set alpha to about 0.3084.
Now, let’s determine the best alpha value that will minimize MSE.
Again, click Data, then Solver.
We now set the objective to the MSE value.
The other settings from before are still valid.
Click Solve.
And we can see here that the best alpha value that minimizes MSE is 0.3144.
Doing the same for MAPE, we obtain 0.3084, just like with MAD.
And that’s how to use Solver to find the best alpha for exponential smoothing.