In this video, we demonstrate how to use NumXL to construct and calibrate an ARIMA model in Excel. We'll also project a forecast using the same model.
Hello and welcome to the NumXL ARIMA tutorial. In this video we will demonstrate the steps to construct an ARIMA one-one-one model, calibrate it, and create a forecast value and confidence interval. We will do the whole tutorial using only NumXL 1.63 and Microsoft Excel functionality.
To begin we run an ADF test to examine the stationarity of the data under different conditions, for instance constant deterministic trend etc. The ADF test shows that the sample data exhibits non-stationarity aka presence of unit roots. For three of those scenarios we will ignore the squared trend case.
Select an empty cell in your spreadsheet where you wish to place the ARIMA model, then using the NumXL toolbar locate the ARMA icon and select the integrated ARMA or ARIMA option.
Now the ARIMA wizard pops up by default the output range references the current selected cell in your worksheet. Let's select the input data range.
Once the input data is selected the rest of the wizard options are now enabled and available. For the model order let's leave it at one for ARMA and the integration order. Under options let's instruct the wizard to find the optimal values for this model. By default the maximum order of iterations is set to 100, this is acceptable. Now click OK.
The ARIMA 1 1 1 model is displayed in your worksheet along with a goodness of fit and residuals diagnosis section. Note the values of the models parameters, model checking, and the results of the statistical tests under the residuals diagnosis. The model seems to fit the data pretty well.
For better illustration of the output table NumXL generates comments for the different cells in the table. Note the red arrow tips at the upper right hand corners.
Select the cell that heads up the ARIMA model in this case E27. Now locate the forecast icon on the NumXL toolbar.
Now the forecast wizard pops up, note that the model input references the currently selected cell in your worksheet, in this case E27. Select the latest observations or the whole input sample if you wish.
Under options change the number of steps to 12.
Select an empty cell in your worksheet where you wish the forecast output table displayed. Click OK when you're done.
And here you go the forecast output table is generated. Note that the forecast error increases significantly with the forecast horizon.
That's it for now, thank you for watching!
Note: For more information (e.g. example spreadsheet, screenshots and write-up), see ARIMA section in NumXL user's guide.