Check out our Holt's Double 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 double 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 the double exponential smoothing function and use the built-in optimizer in NumXL.
For the sample data we are using the same weekly demand volume data of a hypothetical product. Let's begin by selecting cell D10.
Scene 2:
Examine the self formula in the formula toolbar. Notice we already have a call for the double exponential smoothing function so press F2 to edit.
Click on the FX button located on the left side of the equation toolbar this will invoke the function arguments dialog box for the double exponential smoothing function.
Scene 3:
Notice that the cell D3 is used for the optimize argument so we don't need to change the formula, just the value in D3 to turn on the optimizer. Now click cancel to return to your worksheet.
Scene 4:
Select cell D3, then type in true or 1 then hit enter when done.
Scene 5:
Since we have the automatic calculation on, all the values of the smooth time series are recalculated using optimal smoothing factors known as alpha and beta. Notice the favorable changes in mean squared error, mean absolute scaled error and symmetric mean absolute percentage error.
The calibrated double exponential smoothing has a 22% lower a mean absolute error than that of a naive reference model. So what about alpha value in D1 and beta value in D2? When the optimizer flag is turned on the function uses a value of alpha as a starting value for the optimizer.
Scene 5:
The double exponential smoothing function calculates the optimal values for beta and alpha 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 E10. Start typing the double exponential smoothing function, =DESMTH(. When 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 double exponential smoothing function.
Scene 7:
Select the input cell range, this is the same cell range we used earlier for the forecast in column D and then lock the cell reference by pressing F4.
Scene 8:
Enter a value of true or one in the order field. For the initial value of alpha let's use the value in D1 and unlock the cell by pressing F4.
Scene 9:
For the initial beta value let's use the value in D2 and lock it by pressing F4. For the optimize switch, let's use the value in cell D3 and then lock the cell reference by pressing F4. Set the forecast time to zero, or set it to the value in cell A10 and then lock the cell for column movement.
For return type, type in one, for returning the value of the level smoothing parameter alpha and then click OK.
Scene 10:
The built-in optimizer requires a few non-missing observations to run or it will return the same starting value alpha, as it is the case here.
Scene 11:
Let's repeat the same procedure for beta. Select the cell in F10 and start typing =DESMTH(, and when a function is found, click on the FX button found on the left side of the equation toolbar.
Scene 12:
Use the same input we used earlier for all arguments except for return type. For return type, type in two for returning the value of the transmitting parameter beta and then click OK.
Scene 13:
Similar to alpha, the built-in optimizer requires a few non-missing observations to run or it will return the same starting value as is the case here. Select E10 and F10 cell range. So copy the formula to the cells below it.
Scene 14:
The values of the smoothing parameters at each period is calculated, so let's plot the values of the level smoothing parameter versus time.
The graph for alpha shows some volatility of the computed alpha value throughout the sample in the first year. In the second year the value of the smoothing factor is relatively stable.
So what's going on? The optimal user finds optimal values by minimizing the SSE, in our case the SSE function seems to have multiple minima and their values are comparably close. Earlier in the sample data the first minima seemed to have a lower value and as we have more observations, the second minima have a lower value, so it was picked up by the optimizer.
So which alpha do we use? Well we prefer a stable alpha over time so we could designate a part of the sample as a training set and use the rest to assess the forecasting power.
Scene 15:
Let's plot the value of the transmitting factor versus time. The graph for alpha shows some volatility of the computed beta value throughout the sample in the first year. In the second year the value of the smoothing factor is relatively stable and equal to zero.
In a follow-up tutorial we'll decompose the input data into two components, level and trend, and query the double exponential function for their values.
Scene 16:
That's all for now, thank you for watching!
Optimization Off
In this video, we show you how to use Holt's double 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 the double exponential smoothing in NumXL. For the sample data we are using weekly demand volume for a hypothetical product.
Holt's double exponential smoothing is best used with data that exhibits stable trend over time and no seasonality, so let's examine the data set for the trend assumption. Using Excel's built-in chart function draw a trend through the data.
Scene 2:
The weekly demand data shows a significant downtrend. This is good for double exponential smoothing, so let's begin and select the cell D10. Type in the function name, =DESMTH( 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 double exponential smoothing function.
Scene 3:
Using the function arguments dialog box specify the input cell range and 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. For this example let's reference the value in D1 and lock the cell reference 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 example let's reference the value in D2 and then lock the cell by pressing F4.
For enabling and disabling the functions built-in optimizer you can type in a true/false value or reference a cell in your worksheet. Let's reference the cell D3 and then lock the reference by pressing F4.
For the forecast time we'll be forecasting the value at the end of the input data, so set T to zero or better yet reference a cell in a column A, so we can change it after the end of the input data, and then lock the cell for column movement by pressing F4.
For return type leave blank or type in zero for forecast, and then click OK.
Scene 4:
The function returns N/A since we don't have enough data, so 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 after the end of the input data, the function will ignore them.
Scene 5:
Let's plot the one-step smoothing forecast. Although this is an out-of-sample forecast, which means it uses data available up to the forecast time not the whole sample, the double exponential smoothing is converging to a linear trend line using the whole sample. The multi-step forecast of the double exponential is a sloped line as is shown in the plot.
Scene 6:
To quantify the overall forecasting power of the double exponential smoothing we will use three functions: mean squared error, mean absolute percentage error, and mean absolute scale error as in shown in cells D4 to D6.
Scene 7:
Let's set alpha to 10% and leave beta at 20%. Hmm, the mean absolute scaled error has improved by 4%.
Now let's set both alpha and beta to 10%. Great! The mean absolute scaled error has improved by another 4%.
Let's examine this scenario, set alpha to 25% and beta to 15%. Ouch! The mean absolute scaled error surged to 80%.
Scene 8:
How about 15% for alpha and 10% for beta? All right, the mean absolute scaled error went down about 5%, but it's not the best scenario.
Scene 9:
How about we set alpha to 20% and beta to 10%? Nope, it worsened.
One last scenario. How about 20% and 5%? Now we don't have any change this time.
Finding the optimal values for alpha and beta is not a straightforward process. Fortunately, NumXL has a built-in optimizer for finding such values as you will see in the next tutorial video.
Scene 10:
That's all for now, thank you for watching!
Holt's Double Exponential Smoothing Components
In this video, we show you how to decompose your data set into trend and level components using the double exponential smoothing function in NumXL.
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 from smoothing parameters for Holt winters double exponential smoothing.
Now we'll demonstrate how to calculate a fitted or in sample forecast and its component, level and trend. For the data we're going to be using the weekly volume demand data of a hypothetical product.
First select the cell E10, and then type in the double exponential function name, =DESMTH(. 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 for the double exponential function.
Scene 2:
For the input data select the cell range corresponding to the whole demand volume sample data which is C10 to C103, and then lock the cells 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 we'll 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 value in D2 and then lock the cell reference by pressing F4.
For the optimization switch, leave it disabled by referencing the D3 cell, and then lock the cell reference by pressing F4.
For forecast time, type in or select a cell with a value of the number of steps past the end of the data to include and then lock the cell by pressing F4.
For the return type, type in three for one step in sample level component series and then click OK.
Scene 3:
The function returns the first value in the array, to display the whole array select the cells starting with E10 and all the rows below it, press F2 to edit the formula and then press ctrl + shift and then enter. The selected cells are now populated with the array values and the formula is surrounded by italic braces.
Let's do the same for the trend component. To make it easier this time, let's copy the formula from E10, so select the E10 cell and then copy the cell to F10. Now we can edit the formula in F10 and then change the return type, so select F10 and then click on the FX button found on the left side of the equation toolbar.
Scene 4:
The function arguments dialog box for the double exponential function will pop up, leave all arguments intact and go to the return type field and change the value from 3 to 4 for trend component and then click OK.
Scene 5:
The function returns the first value in the trend component array. To display the whole array select the cell starting with F10 and all the rows below it and then press F2 to edit the formula. Hold down ctrl + shift and enter and then the selected cells will be populated with the array values and the formula is surrounded by italic braces.
We'll repeat the same procedure for the in sample forecast component, so select the cell F10 and copy it to the cell G10. Select the G10 cell and then click the FX button.
Scene 6:
In the function arguments dialog box, change the return type value from 4 to 5 and then click OK.
Scene 7:
The function returns the first value in the forecast component array. To display the whole array, select the cell starting with G10 and all the rows below it then press F2 to edit the formula. Hold down ctrl + shift then hit enter, the selected cells are now populated with the array values and the formula is surrounded by italic braces.
Scene 8:
Finally, let's plot the level and trend components. Since the level and trend have different magnitudes we used a separate axis scale for each, level on the left hand side and trend on the right hand side. The trend component values are relatively stable around negative 9 per step.
Scene 9:
That's all for now, thank you for watching!
Comments
This tutorial video is the second part of demonstrating the Holt-Winters double exponential smoothing function in Microsoft Excel with the help of NumXL. This time we will use NumXL to find the optimal values of the smoothing factors.
Article is closed for comments.