In this video, we demonstrate how to use NumXL to construct and calibrate a seasonal ARIMA (aka SARIMA) with Exogenous factors model (aka SARIMAX) in Excel. We'll also project a forecast using the same model.
Hello and welcome to the NumXL tutorial video for the SARIMAX model. In this tutorial we will use the log of the monthly airline passengers data from between January 1949 and December 1960. For the explanatory or exogenous variable, we will use the number of calendar days in each month. As shown the exogenous factor can take values between 28 and 31.
Let's begin! Select an empty cell in your worksheet, now locate the ARMAX icon in the NumXL toolbar and click it.
The ARMAX SARIMAX wizard pops up. Note the output range references the currently selected cell in your worksheet. Next select the response also known as dependent input data.
Now select the exogenous factors also known as explanatory variables, the factors data must be organized in your worksheet such that each column represents a separate variable and a row a separate observation.
In our example we have one factor so we select column D. Please note the number of observations of the factors must match the response number of the observations. The different tabs in the wizard are now enabled and available. Click on the model tab.
In the model tab, let's select SARIMAX 0 1 1 0 1 1 with a period equal to 12 months click on the seasonal checkbox to select SARIMAX versus ARMAX. Now switch to the option tab.
Okay, now let's instruct the wizard to find the optimal values for the model's parameters.
Click on calibrate and set the maximum number of iterations to 200. When you're done click OK.
The SARIMA wizard generates different tables for the model's parameters goodness of fit and residuals diagnosis, the number of days our exogenous variable exhibits a coefficient value of 0.04. Furthermore the wizard generates comments for the different cells to illustrate their use and/or meaning in the context of our analysis. Note the red arrow tips on the upper right corners of the cells.
Now let's do forecasting using the SARIMAX model. Select the cell that heads the model table and locate the forecast icon on the NumXL toolbar and click it.
The forecast wizard pops up, note that the model references the currently selected cell. Now select a cells range for the latest observations for the response or dependent variable, we chose the last 20 observations.
For factors, select the cells range that starts at the same row as the response variable, but ends at the furthest road to cover at least the forecast horizon. In this example we selected 18 steps beyond the end of the response variable.
Now change the forecast horizon or max steps to 18. Finally, select an empty cell in your worksheet to display the forecast output table, click OK when you're done.
The forecast wizard generates the forecast values and formulas, error and confidence intervals. Now let's plot the forecast.
Now to get a better picture of our future values over the next 18 months, we will use a simulation. In this tutorial we will use 10 simulation paths also known as scenarios for the next 18 months.
Again, similar to what we did with the forecasts, select the cell that heads the model table, then locate the simulation icon in the NumXL toolbar and click on it.
The simulation wizard pops up. As we did with the forecast step, select the latest observations for the response variable.
For the factors again select the cells range starting with the same row as the response cells range, but include more rows to cover the next 18 months.
Under options set the horizon to 18 steps. Now set the number of simulation paths also known as scenarios to 10. Select an empty cell in your worksheet to display the different simulation paths. When you're done click OK.
The simulation wizard generates 10 simulation paths in the output table.
Here we see the simulation paths plotted next to the table. Remember that these simulation paths are dynamic. To refresh and generate a new one, press F9. Keep pressing F9 to generate new paths.
That's it for now, thank you for watching!
Note: For more information (e.g. example spreadsheet, screenshots and write-up), see SARIMAX section in the user's guide.