Triple Exponential Smoothing with Optimization in NumXL скачать в хорошем качестве

Triple Exponential Smoothing with Optimization in NumXL

NumXL 1.65 (Hammock) has an automatic optimizer for Triple Exponential Smoothing. Triple Exponential Smoothing Reference: https://support.numxl.com/hc/en-us/ar... For more information, visit us at https://www.numxl.com/products/numxl   / numxl     / numxl   https://twitter.com/intent/follow?sou... _________________________________________________________________ Select the cell D11 Examine the cell formula in the formula toolbar. Notice we already have a call for the double exponential smoothing function. Click on the “fx” button found on the left of the equation toolbar. The TESMTH function arguments dialog box pops up. Notice that 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. type in “True” or one (1). Hit Enter when done Since we have the automatic calculation on, all of the values of the smoothed time series are re-calculated using optimal smooth factors also known as alpha, beta, and gamma. 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 mean absolute error (MAE) than that of a naïve reference model. What about the alpha value in D1, the beta value in D2 and 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 probably return the same values. The triple exponential smoothing function calculates the optimal values for alpha and beta using the available information or data. The available data increases with time, so the function calculates a new value for each step. Let’s examine the values of those parameters. Select the cell in E11. Start typing the TESMTH Function. “TESMTH(“. Note that the auto-complete feature in Microsoft Excel will help find the correct function. The function is found. Click on the “fx” button found on the left of the equation toolbar. This will invoke the function arguments dialog box for the triple exponential smoothing function. Select the input cells-range. This is the same cells range we used earlier for the forecast in column D. Lock the cells reference. Enter a value of True or one (1) in the order field For initial value for alpha, let’s use the value in D1. For initial value for beta, let’s use the value in D2. For initial value for gamma, let’s use the value in D3. For season length or duration, let’s use the value in D5. For the optimize switch, let’s use the value in cell D4. Set the forecast time to zero or set to the value in cell A10. Lock the cell for column movement For the return type, type in one (1) for returning the value of the level smoothing parameter (alpha). The built-in optimizer requires 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. Plot the optimal value for level smoothing parameter (aka alpha). Let’s repeat the same procedure for beta. Select the cell in F11, and start typing “=TESMTH(“. when the function is found, click on the “fx” button found on the left of the equation toolbar Scene 34-36 Use the same input like earlier for all arguments, except for “Return Type” For the return type, type in two (2) for returning the value of the trend smoothing parameter (beta). Similar to the case of the alpha, the built-in optimizer requires few non-missing observations to run, or it will return the starting beta value, as it is the case here. Copy the formulas to the cells below it. Plot the optimal beta values throughout the sample. Let’s repeat the same procedure for gamma. Select the cell in G11, and start typing “=TESMTH(“. when the function is found, click on the “fx” button found on the left of the equation toolbar Use the same input like earlier for all arguments, except for “Return Type” For the return type, type in three (3) for returning the value of the seasonal Indices smoothing parameter (gamma). Click on OK button now. Similar to the case of the alpha and the beta, the built-in optimizer requires few non-missing observations to run, or it will return the starting gamma value, as it is the case here Copy the formulas to the cells below it. Plot the optimal gamma values throughout the sample.