Check out Brown's Linear Exponential Smoothing (LESMTH) tutorial in NumXL 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 Brown's Linear Exponential Smoothing in NumXL with an optimization switch for smoothing factors.
Scene 1:
Hello and welcome to the exponential smoothing tutorial series. In this tutorial we will resume the demonstration of Brown's linear 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 D9. examine the cell formula in the formula toolbar. Notice we already have a call for the linear exponential smoothing function, so press F2 to edit. Click on the FX button found on the left side of the equation toolbar, this will invoke the function arguments dialog box for the linear exponential smoothing function.
Scene 2:
Notice that the cell D2, is used for the optimize function, so we don't need to change the formula just the value in D2 to turn on the optimizer. Please click cancel to go back to your worksheet.
Scene 3:
Select the D2 cell and type in true or 1 then 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 optimal smoothing factor known as alpha. Notice the change in mean squared error mean, absolute scaled error and symmetric mean absolute percentage error.
The calibrated linear exponential smoothing has a 21 percent lower mean absolute error than that of a naive reference model. Note that the function calculates the optimal value for alpha using the available information or data as a training set. The available data increases with time so the function calculates a new value each time.
What about the alpha value in D1? Well when the optimizer flag is turned on the function uses the value of alpha as a starting value for the optimizer.
Now let's examine the value of the smoothing parameter at each period. Select the E10 cell and start typing in the function =LESMTH(. Click on the FX button found on the left side of the equation toolbar, this will invoke the function arguments dialog box for the linear exponential smoothing function.
Scene 5:
Then specify the input cell range, this is the same cell range we used earlier for the forecast in column D. Lock the starting cell in the input range by pressing F4, enter a value of true or one in the order field.
Scene 6:
For the initial value of alpha let's use the value in D1, then lock the cell by pressing F4. For the optimized switch let's use the value in cell D2 up the cell by pressing F4.
Scene 7:
Set the forecast time to zero or set the value in cell A10, and lock the cell for column movement. For the return type in one for returning the value of the smoothing parameter.
Scene 8:
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.
Scene 9:
Now copy the formula to the cells below it.
Scene 10:
The value of the smoothing parameter at each period is calculated, let's plot the values of the smoothing parameter versus time. The graph shows the volatility of the computed alpha values throughout the sample in the first year. In a second year the value of the smoothing factor declines to very low levels then picks up to three point five percent.
So what's going on? Well the SSE function seems to have multiple minima and their values are comparable close. Earlier in the sample data the first minimum seem to have a lower value and as we have more observations the second minima have lower value and so it was picked up by the optimizer. To see for yourself simply compute the SSE when alpha is 3.5% and for 0.6%.
Does it matter which alpha I use? We prefer a stable alpha over time so we can designate part of the sample data as a training set and use the rest to assess the forecasting power. In this scenario we'd use the first 85 observations, about 1.5 years as a training set and leave nine observations for measuring forecast accuracy.
In a follow-up tutorial we will decompose the input data into two components, level and trend, and query the linear exponential smoothing for their values.
Scene 11:
That's all for now, thank you for watching!
Optimization Off
In this video, we show you how to use Brown´s Linear Exponential Smoothing 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 Brown's linear exponential smoothing function in Microsoft Excel.
For the sample data we are using weekly demand volume for a hypothetical item. Brown's linear exponential smoothing is best used with a data set that exhibits trend over time, but no seasonality, so let's examine the data set for the trend assumption. Using the Excel chart built-in function draw a trend through the data.
Scene 2:
The weekly demand data showing a significant downtrend with an r-squared value of around 10%, this is good, so let's begin! Select the empty cell D9 then type in the function name =LESMTH( 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 linear exponential smoothing function.
Scene 3:
Using the functions argument dialog box, specify the input cell range. Lock the starting cell range 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 smoothing parameter you can type in a value or reference an existing cell. In this example let's reference the cell value in D1 and then lock the cell reference by pressing F4.
For enabling and disabling the functions built-in optimizer you may use a true/false value or reference the cell in your worksheet, let's reference cell D2. Lock the cell reference by pressing F4.
For the forecast time we will be forecasting 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. Lock the cell for column movement by hitting F4 until the dollar sign is showing at the left.
For return type leave blank or type in 0 for forecast 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.
Scene 5:
Copy the formula to cells after the end of the input data, notice how we change the step value incrementally. The multi steps forecast of the Brown's linear exponential is a straight line as is shown in the plot. To quantify the overall forecasting power of the linear exponential smoothing, we will use three functions: mean squared error, mean absolute percentage error, and mean absolute scaled error as shown in cell D4 to D6.
Let's try to change the smoothing parameter to 7.5. That's not good. Now forecast performance measure looks worse.
Scene 6:
How about 10%? No, that's not better either. In the next part of this tutorial we will use the function's built in optimizer to find the best value of the smoothing parameter, alpha. We will also examine the improvement in the forecasting power of the model.
Scene 7:
That's all for now, thank you for watching!
Brown's Linear Exponential Smoothing Components
In this video, we show you how to use Linear Exponential Smoothing in NumXL to decompose time series into level+trend components.
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 the submitting parameters for linear exponential smoothing.
Now we will demonstrate how to calculate fitted or in sample forecast and its component, level and trend. For the sample data we will continue using the weekly demand volume data of a hypothetical product.
First, select the E9 cell and type in the linear exponential function name, =LESMTH(. 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 linear exponential smoothing function.
Scene 2:
For the input data select the whole cell range corresponding to the demand volume data which is C9 to C102. 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, C9 as the earliest observation.
For the alpha value select the cell in D1. The value in D1 was calculated during the calibration process using the whole dataset. Lock the cells by pressing F4.
For the optimization switch leave it disabled by referencing the D2 cell. Lock this reference by pressing F4.
For the 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 this reference by pressing F4.
For return type, type in two for one step in sample level component series then click OK.
Scene 3:
The function returns the first value in the array, to display the whole array select all the cells below then press F2 to edit then press ctrl + shift then enter.
Scene 4:
The selective cells are now populated with the array values and the formula is surrounded by an italic braces.
Scene 5:
For trend component values copy the formula from E10. Select the F10 cell and hit F2 to edit the copied formula. Click on the FX button found on the left side of the equation toolbar, this will invoke the function arguments dialog box for the linear exponential smoothing function.
Scene 6:
In the function arguments dialog box change the return type to 3 and then hit OK.
Scene 7:
To display the whole trend component array select all the cells below then press F2 to edit then press ctrl + shift and enter.
Scene 8:
The selected cells are now populated with the array values and the formula is surrounded by italic braces.
Scene 9:
Repeat the same steps for the one step in sample forecasting.
Scene 10:
Change the return type to four which is the in-sample forecasting and click OK.
Scene 11:
To display the whole trend component array select all the cells below then press F2 to edit then press ctrl + shift and enter. The cells are now populated with the array values and the formula is surrounded by italic braces.
Scene 12:
Let's plots the level and trend component values throughout the sample data. The magnitude values of level and trend are very different, so for illustration we plotted the level series using the left hand axis and the trend component using the right hand axis.
Scene 13:
That's all for now, thank you for watching!
Comments
In the second part of this tutorial, we demonstrate how to use NumXL s Brown s linear exponential smoothing (LESMTH) function, which is a special form of the double exponential smoothing forms, in Microsoft Excel. But this time we let NumXL itself find the optimal value of the smoothing factors.
Please sign in to leave a comment.