Check out our Holt-Winters' Triple Exponential Smoothing tutorial below. The playlist contains tutorials that teach you how to utilize the optimization switch and calculate with seasonal decomposition.
Optimization On
In this video, we show you how to use Holt's triple exponential smoothing function in NumXL with an optimization switch for smoothing factors.
Scene 1:
Hello and welcome to the exponential smoothing tutorial series. In this tutorial we'll resume the demonstration of Holt Winters Triple Exponential Smoothing functions and the built-in optimizer in NumXL. For sample data we are using the same monthly international passengers data set.
Let's begin by selecting the cell D11. Examine the cell formula in the formula toolbar. Notice we already have a call for the triple exponential smoothing function, so press F2 to edit, and then click on the FX button found on the left side of the equation toolbar.
Scene 2:
The triple exponential smoothing function argument's dialog box will pop up, and notice that the cell D4 is used for the optimize argument, so we don't need to change the formula just the cell D4. Now click cancel to go back to your worksheet.
Scene 3:
Select the cell D4 then type in true or one and hit enter when done.
Scene 4:
Since we have the automatic calculation on, all of the values of the smooth time series are recalculated using the optimal smoothing factors known as alpha beta and gamma. Notice the favorable change is in mean squared error, mean absolute scaled error, and symmetric mean absolute percentage error. The calibrated triple exponential smoothing function has a 22% lower mean absolute error than that of a naive reference model.
Now what about the alpha value in D1, the beta value in D2, and the gamma in D3? When the optimizer flag is turned on the function uses the value of alpha, beta, and gamma as a starting value for the optimizer. The NumXL optimizer is very robust such that you may leave the alpha beta and/or gamma blank and the optimizer will most likely return the same values.
Scene 5:
The triple exponential smoothing function calculates the optimal values for alpha and beta using the available information or data. The available data increases the time, so the function calculates a new value for each step. Let's examine the values of those parameters, so select the cell E11. Start typing the triple exponential smoothing function =TESMTH(. Once the function is found click on the FX button found on the left side of the equation toolbar.
Scene 6:
This will invoke the function arguments dialog box for the triple exponential smoothing function. Select the input cell range, this is the same cell range we used earlier for the forecasting column D and then lock the cell reference by pressing F4. Enter a value of true or one in the order field.
For the initial value of alpha let's use a value in D1 and then lock the cell by pressing F4. For the initial value for beta let's use the value in D2 and then lock the cell by pressing F4. For the initial value for gamma let's use the value in D3 and then lock the cell by pressing F4. For the season length or duration let's use a value in D5 and then lock the cell by pressing F4. For the optimize switch let's use the value in cell D3 and then lock the cell by pressing F4. Set the forecast time to zero or set it to the value in A11 then lock the cell for column movement. For the return type, type in one for returning the value of the level smoothing parameter alpha then click OK.
Scene 7:
The built-in optimizer requires a few non missing observations to run or it will return the starting alpha value as it is the case here. Copy the formulas to the cells below it then plot the optimal value for the level smoothing parameter, alpha.
Scene 8:
Let's repeat the same procedure for beta so select the cell in F11 and start typing =TESMTH( and when the function is found click on the FX button found on the left side of the equation toolbar.
Scene 9:
Use the same input like earlier for all of the arguments except for return type. For the return type, type in two for returning the value of the transmitting parameter beta and then click OK.
Scene 10:
Similar to alpha the built-in optimizer requires a few non missing observations to run or it will return the same starting beta value as it is the case here. Copy the formula to the cells below it and then plot the optimal beta values throughout the sample. Let's repeat the same procedure for gamma, so select the cell in G11 and start typing =TESMTH( and when the function is found click on the FX button found on the left side of the equation toolbar.
Scene 11:
Use the same input like earlier for all arguments except for return type. For the return type, type in 3 for returning the value of the seasonal indices smoothing parameter, gamma, and then click OK.
Scene 12:
Similar to the case of alpha and beta, the built-in optimizer requires a few non-missing observations to run or it will return the same starting gamma value as is the case here. Copy the formula to the cells below it and then plot the optimal gamma values throughout the sample.
Scene 13:
That's all for now, thank you for watching!
Optimization Off
In this video, we show you how to use Holt's triple exponential smoothing function in NumXL without the optimization switch for smoothing factors.
Scene 1:
Hello and welcome to the Exponential Smoothing Tutorial series. In this tutorial we'll demonstrate Holt Winters' triple exponential smoothing function in NumXL. For the sample data we are using the monthly international volume dataset from Bak Jenkin's textbook.
The Holt Winters' triple exponential smoothing is best used with a dataset that exhibits stable trend over time and seasonality. Let's examine the data plot over time. It easily demonstrates the presence of both trend and seasonality, so we're good to go.
Now select the empty cell D11. Type in the function name =TESMTH( in the cell or in the equation toolbar. Once you find the correct function click on the FX button located on the left side of the equation toolbar, this will invoke the function arguments dialog box for the triple exponential smoothing function.
Scene 2:
Using the function arguments dialog box, specify the input cell range and then lock the reference by pressing F4. In the order field specify one for the time order in the series or simply put the first observation in the input series that corresponds to the earliest date.
For the level smoothing parameter alpha, you can type in a value between 0 and 1 or reference an existing cell. In this example let's reference the value in D1 and then lock the cell by pressing F4.
For the transmitting parameter, beta you can also type in a value between 0 and 1 or reference an existing cell. In this situation let's reference value D2 and then lock the cell reference by pressing F4.
For the seasonal indices smoothing parameter gamma you can also type in a value between 0 & 1 or reference an existing cell. In this example let's reference the cell in D3 and then lock the cell reference by pressing F4.
For the season length or duration you can also type in a positive integer or reference an existing cell. In this example let's reference the cell in D5 and then locked with reference by pressing F4.
For enabling and disabling the function's built an optimizer you may type in a true/false value or reference an existing cell in your worksheet. Let's reference the cell D3 and then lock the reference by pressing F4.
For forecast time we'll be referencing the value at the end of the input data, so set T to zero, or better yet, reference a cell in column A, so we can change it after the end of the input data and then lock the cell for column movement by hitting F4.
For return type leave blank or type in zero for forecast then click OK.
Scene 3:
The function returns N/A since we don't have enough data.
Scene 4:
Copy the formula to the cells below it, even after the end of the sample data. Don't worry about the empty cells picked up in after the end of the input data, the function will ignore them.
Let's plot the one-step smoothing forecast. The triple exponential smoothing forecast follows the data pretty well even with sub optimal values for smoothing parameters.
To quantify the overall forecasting power of the triple exponential smoothing, we'll use three functions: mean squared error, mean absolute percentage error, and mean absolute scaled error. Since we are handling seasonal data, the reference model in mean absolute scaled error must be seasonal, so select D8 and click on the FX button located on the left side of the equation toolbar.
Scene 5:
Notice that the seasonal period is set to twelve similar to our sample data seasonal link, so click on cancel.
Scene 6:
Let's try to change the smoothing parameters, so leave alpha at 5%, beta at 20% and then change gamma to 60%. The mean squared error and mean absolute scaled error shows marginal improvement.
Let's try to change the smoothing parameters again. Leave alpha at 5, beta at 20, and then change gamma to 70%. You can see that the mean squared error shows marginal improvement while the mean absolute scaled error and the mean absolute percentage error remains the same.
Let's try to change the smoothing parameters, leave alpha at 5%, beta at 20, and then change the gamma to 90%. Now the mean squared error shows a change to the worse.
Let's try to change beta now. Restore gamma to its original value of 50% and then set beta to 30%. Hmm, not a great improvement.
How about setting beta to 40%? Looks like we don't have any change.
Now let's set beta to 50%. The mean squared error and the mean absolute scaled error are worse than before.
Let's change only alpha now. Restore the old beta value to 20% and then set alpha to 10%. This is a good improvement, both the mean squared error and mean absolute scaled error dropped around 5%.
Now let's try to set alpha to 15%. Now we can see more improvement.
Let's set alpha to 20%. We have a small improvement but that's still good nevertheless.
In conclusion, tuning three parameters for best fit is a tough problem, fortunately NumXL's smoothing function has a built-in optimizer to help you and we'll demonstrate its use in the next tutorial.
Scene 7:
That's all for now, thank you for watching!
Holt-Winters' Triple Exponential Smoothing Components
In this video, we show you how to calculate triple exponential smoothing with seasonal decomposition.
Scene 1:
Hello and welcome to the Exponential Smoothing Tutorial series. In our last few tutorials we discussed how to construct one or multiple steps out of a sample forecast and the calibration process for smoothing parameters for Holt Winters' triple exponential smoothing.
Now, we'll demonstrate how to calculate fitted or in sample forecast and its components: level, trend and seasonal indices. For sample data we will use a popular textbook example, the Monthly International Airline Passengers dataset.
Scene 2:
First select the cell E11, type in the triple exponential function name, =TESMTH(. Once you find the function, click on the FX button found on the left side of the equation toolbar. This will invoke the function arguments dialog box or the triple exponential smoothing function.
Scene 3:
For the input data select the cell range corresponding to the whole demand volume sample data which is C11 to C103, then lock the cell range by pressing F4.
For the time order in the input data type in true or 1 to designate the first observation as the earliest observation.
For the level smoothing parameter, alpha you can type in a value or reference an existing cell. In this example let's reference the value in D1 and then lock the cell by pressing F4.
For the trend smoothing parameter, beta you can type in a value or reference an existing cell. In this example let's reference the cell in D2 and then lock the cell by pressing F4.
For the seasonal indices smoothing parameter, gamma you can type in a value or reference an existing cell. In this example let's reference the value in D3 and then lock the cell reference by pressing F4.
For the seasonal length or duration you can type in a positive integer or reference an existing cell. In this example let's reference the cell value of D5 and lock the cell by pressing F4.
For the optimization switch leave it disabled by referencing the D4 cell and lock the cell reference by pressing F4.
For forecast time type in or select the cell with the value of the number of steps past the end of the data to include then lock the cell reference by pressing F4.
For return type, type in four for the one step in sample level component series then click OK.
Scene 4:
The function returns the first value in the array. To display the whole array select the cells starting with E11 and all rows below it then press F2 to edit the formula.
Scene 5:
Hold down ctrl + shift and then enter. The selected cells are now populated with the array values and the formula is surrounded by italic braces.
Scene 6:
Now let's plot the level component over time and do the same for the trend component. To make it easier this time let's copy the formula from E11 so select E11 and copy the cell to F11 and now we need to edit the formula in F11 and change the return type, so select the cell F11 and then click on the FX button found on the left side of the equation toolbar.
Scene 7:
The function arguments dialog box for the triple exponential function will pop up, leave all arguments intact and then go to the return type field and change the value from four to five and then click OK.
Scene 8:
The function returns the first value in the trend component array, to display the whole array select the cells starting with F11 and all rows below it, then press F2 to edit the formula. Hold down ctrl + shift and then hit enter.
Scene 9:
The selected cells are now populated with the array values and the formula is surrounded by italic braces.
Scene 10:
Let's plot the trend component over time.
Scene 11:
Let's do the same for the seasonal indices component, to make it easier this time let's copy the formula from F11. Select the cell F11 and copy it to the cell G11. Now we need to edit the formula in G11 and change the return type, so select the cell G11 and then click on the FX button found on the left side of the equation toolbar.
Scene 12:
The function arguments dialog box for the triple exponential function will pop up, leave all arguments intact and go to the return type field and change the value from 5 to 6 then click OK.
Scene 13:
The function returns the first value in the seasonal indices component array. To display the whole array select the cell starting with G11 and all rows below it. Press F2 to edit the formula, hold down ctrl + shift then hit enter.
Scene 14:
The selected cells are now populated with the array values and the formula is surrounded by italic braces.
Scene 15:
Let's plot the seasonal indices component over time, and now we'll repeat the same process for the in-sample forecast component, so select the cell G11 and copy it into cell H11. Select the H11 cell and click on the FX button.
Scene 16:
In the triple exponential function arguments dialog box change the return type value from 6 to 7 and then click OK.
Scene 17:
The function returns the first value in the forecast component array. To display the whole array select the cell starting with H11 and all rows below it, then press F2 to edit the formula. Hold down ctrl + shift and then hit enter.
Scene 18:
The selected cells are now populated with the array values and the formula is surrounded by italic braces.
Scene 19:
Finally let's plot the forecast component over time.
Scene 20:
That's all for now, thank you for watching!
Comments
In the second part of our tutorial, we will demonstrate the Winters' triple exponential smoothing function in NumXL. But this time we'll delegate the task of finding the optimal values of the smoothing parameters to NumXL.
For more information and examples on triple exponential smoothing, visit http://bit.ly/MAjKQF
Article is closed for comments.