Check out our Simple (Brown) Exponential Smoothing tutorial below. The playlist contains tutorials that teach you how to utilize the optimization switch, calibrate with a training set, and calculate an in-sample forecast using NumXL.
Optimization On
In this video, we show you how to use Brown's simple 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 Brown simple exponential smoothing function and use the built-in optimizer in NumXL. For the sample data, we are using the same sales data of a hypothetical company.
Let's begin by selecting cell D9. Examine the cell formula in the formula toolbar, notice we already have a call for the simple exponential smoothing function so press F2 to edit.
Scene 2:
Click on the FX button found on the left side of the equation toolbar, this will invoke the function arguments dialog box for the simple exponential smoothing function.
Scene 3:
Notice that the cell in D2 is used for the optimize argument, so we don't need to change the formula just the value in D2 to turn on the optimizer. Now click cancel to go back to your worksheet.
Scene 4:
Select cell D2 and type in true or 1 then hit enter when done. 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 favorable changes in mean squared error, mean absolute scaled error, and symmetric mean absolute percentage error.
The calibrated simple exponential smoothing has a 10% lower mean absolute error than that of a naive reference model. Also, the function calculates the optimal value for alpha using the available data or information.
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 past value of alpha as a starting value for the optimizer.
Scene 5:
Now let's examine the value of the smoothing parameter at each period. Select cell E10 and start typing in =SESMTH(. Then click on the FX button found on the left side of the equation toolbar, this will invoke the function arguments dialog box for the simple exponential smoothing function.
Scene 6:
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, enter a value of true or one in the order field, and for alpha let's use the same value in D1. Lock the cell by pressing F4.
For the optimized switch let's use the value in cell D2. Lock the cell by pressing F4 and then set the forecast time to zero or set to the value of the cell in A10. Lock the cell for column movement.
For return type, type in 1 for returning the value of the smoothing parameter, then click OK now.
Scene 7:
Then copy the formula to the cells below it. The value of the smoothing parameter at each period is calculated, now let's plot the values of the smoothing factor versus time.
Scene 8:
The graph shows the volatility of the computed alpha value throughout the sample in the first year. In the second year, the value of the smoothing factor is converging to around 0.8.
Does the simple exponential smoothing function use all of the values of alpha? Well, yes it does!
Can I force the simple exponential smoothing function to use one value for alpha? You could, but you would need to turn off the optimizer.
In a follow-up tutorial, we will calculate the value of alpha using a subset of the sample data then use the calibrated alpha for the rest of the sample data.
Scene 9:
That's all for now, thank you for watching!
Calibration dataset
In this video, we show you how to designate a portion of data to calibrate your smoothing factor for Simple Exponential Smoothing.
Scene 1:
Hello and welcome to the exponential smoothing tutorial series. In our last tutorial we showed you how you can enable the built-in optimizer in the simple exponential smoothing function to find the optimal values for the smoothing parameters.
In this tutorial we will demonstrate how to designate a portion of data for calibrating the smoothing parameter, then we will use the found value with the rest of the sample data.
For the sample data, we will continue using the two years sales data from our hypothetical company, and we'll use the first year to calibrate the simple exponential smoothing model.
Scene 2:
First, let us disable the optimization for the smooth time series. In our other videos, we reference cell D2 as the optimization switch, so select cell D2 and type in false or 0.
Scene 3:
Select the cells in column D that correspond to the training set and delete the formula.
Scene 4:
Now we are ready for calibrating the smoothing parameter. Select the cell in D1 and type in =SESMTH(.
Scene 5:
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 simple exponential smoothing function.
Scene 6:
For input data select the cell range in the training set. In this case, it is C9 to C20. Set the chronicle order of the time series to true or 1, this will signal that the first observation in the input data corresponds to the earliest date.
Scene 7:
You can leave alpha blank or enter any value between 0 and 1 this number will be used as a starting value for the optimizer.
Scene 8:
Turn on the optimizer by typing in true or 1, set the forecast time to 1, so that the whole training set is used. In return type, enter 1 then click OK.
Scene 9:
The optimal value of the smoothing factor in the training set is returned to D1. We have the automatic calculation turned on so the smooth time series in D21 to D36 are updated automatically. Here you can note the change in the forecast performance measures.
Scene 10:
That's all for now, thank you for watching!
Optimization Off
In this video, we show you how to use Brown's simple 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 will demonstrate Brown simple exponential smoothing function in NumXL. For this example, we will be using monthly sales figures from the last two years of a hypothetical company.
And let's begin! Start by selecting cell D9. Type in the function name SESMTH in the cell or in the equation toolbar. Note that the autocomplete feature in Microsoft Excel will help you find the correct function. 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 simple exponential smoothing function.
Scene 2:
Using the function arguments dialog box specify the input cell range and lock the starting cell in the input 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 with 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 value in D1 and lock the cell reference by pressing F4.
For enabling and disabling the functions built-in optimizer you may type a true or false value or reference the cell in your worksheet. Let's reference cell D2 and 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 0 or better yet reference the 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.
For the return type, leave blank or type in 0 for forecasts then click OK.
Scene 3:
The function returns N/A since we don't have enough data. Copy the formulas to the cells below it. Once you've copied the formulas to the cells at the end of the input data notice how we change the step value incrementally. Don't worry about the empty cells picked up in and after the end of the input data, the function will ignore them.
Scene 4:
The multi-step forecast of the Browns simple exponential is flat or constant as is shown in the plot. Let's evaluate the forecasting power of the simple exponential using three functions: mean squared error, MSE; mean absolute percentage error, MAPE; and mean absolute scaled error, MASE. Select the cell in D4 and type in MSE(. Once found click on the FX button found on the left side of the equation toolbar.
Scene 5:
In the function arguments dialog justify the cell range for the sales input data. For Y, select the cell range in column D. Don't worry about the empty cells or cells with missing values, the function will examine the value and ignore those observations. Click OK.
Scene 6:
For the mean absolute scaled error (MASE), select the cell D5, then type in =MASE(. Once the function is found click the FX button found on the left side of the equation toolbar.
Scene 7:
Again, select the cell range of the sales data as X then press F4 to lock the range, and select the cell range of the smooth time series as Y. Press F4 to lock the range, for seasonality please leave blank or type in one for non-seasonal data then click OK.
Scene 8:
For MAPE select the cell in D6 then type in MAPE(. Once the function is found click on the FX button to the left of the equation toolbar.
Scene 9:
Select the sales data cell range as X, press F4 to lock the range, and select the smooth time-series cell range as Y. Press F4 to lock the range, for return type select two for symmetric MAPE or SMAPE then click OK.
Scene 10:
That's it! The one step forecasts mean squared error using Brown's exponential smoothing with a 0.85 smoothing factor is 1.2. The MASE shows only a 2% improvement in mean absolute error over a naive reference forecast model. MAPE is also very mild, so we need to do better. Let's try changing the smoothing parameter.
Scene 11:
That's not good now the forecast performance measure looks worse. How about 0.3?
Scene 12:
No that's not correct. How about 0.9?
Scene 13:
No that's not correct either. In the next part of this tutorial, we will use the function 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 14:
That's all for now, thank you for watching!
Forecasting Using Brown's Simple Exponential Smoothing
In this video, we show you how to calculate a fitted or in-sample forecast for your Simple 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 for the smoothing parameters for simple exponential smoothing.
Now we will demonstrate how to calculate a fitted or in sample forecast, but first, let's discuss what an in sample forecast is and how it is different from an out-of-sample forecast. The in sample forecast refers to forecasting an observation that was part of the data sample used to calibrate the model, so it's not really a forecast more like a model fitted value.
For the sample data, we'll continue using the sales data of a hypothetical company from the last two years.
First, select the E9 cell and type in the simple exponential function name =SESMTH(. 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 simple exponential smoothing function.
Scene 2:
For the input data, select the whole cell range corresponding to the sales data which is C9 to C33. 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 alpha value select the cell in D1. The value in D1 was calculated during the calibration using the first year subset as a training set. Lock the cell reference by pressing F4 and leave the optimization switch disabled by referencing cell D2. Lock the cell reference by pressing F4. For forecast time, type in or select the cell with a value equal to the number of steps past the end of the data to include. For return type, type in 2 for one step in sample forecast 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 and enter.
Scene 4:
The selected cells are now populated with the array values and the formula is italicized. Now let's plot the in-sample forecast against our data and compute the different forecast performance measures.
Scene 5:
As we can see the in-sample forecast performs worse than out-of-sample, but this is okay as we would sacrifice in sample forecast accuracy for good generalization and a better out-of-sample forecast.
Scene 6:
That's all for now, thank you for watching!
Comments
This tutorial video is the second part of demonstrating how to use Brown's simple exponential smoothing function in Microsoft Excel with the NumXL software. This time we will use NumXL to find the optimal values of the smoothing factors.
Please sign in to leave a comment.