Holt's Double Exponential Smoothing in Excel (DESMTH)

Check out our Double Exponential Smoothing tutorial below. The playlist contains tutorials that teach you how to utilize the optimization switch and calculate with seasonal decomposition.

 

Video script


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!

 

This playlist contains the following individual videos about Double Exponential Smoothing in NumXL:

 

Double Exponential Smoothing with Automatic Optimization in NumXL: In this video, we show you how to use Holt's double exponential smoothing function in NumXL with an optimization switch for smoothing factors.

 

Double Exponential Smoothing without Optimization in NumXL: In this video, we show you how to use Holt's double exponential smoothing function in NumXL without the optimization switch for smoothing factors.

 

Trend-Level decomposition using Double Exponential Smoothing in NumXL: 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.

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.

    0
    Comment actions Permalink

Article is closed for comments.

Was this article helpful?
0 out of 0 found this helpful