XLSTAT: Fit an ARIMA model to a time series in Excel

 

Dataset for Example

The data have been obtained in [Box, G.E.P. and Jenkins, G.M. (1976). Time Series Analysis: Forecasting and Control. Holden-Day, San Francisco], and correspond to monthly international airline passengers (in thousands) from January 1949 to December 1960.

We notice on the chart, that there is a global upward trend, that every year a similar cycle starts, and that the variability within a year seems to increase over time. Before we fit the ARIMA model, we need to stabilize the variability. To do that, we transform the series using a log transformation. We can see on the chart below that the variability is reduced.

We can now fit an ARIMA(0,1, 1)(0,1,1)12 model which seems to be appropriate to remove the trend effect and the yearly seasonality of the data.

 

Setting Up

• Open XLSTAT.

• Click on the Forecasting/ Time series visualization menu and select the ARIMA feature.

• Once you've clicked on the button, the ARIMA dialog box will appear.

In the General tab:

• In the Times series field, you can now select the Log(Passengers) data.

• Activate the  Center option because we want XLSTAT to automatically center the series before optimizing the ARIMA model.

• Define the type of ARIMA model by entering the value of the (p,d,q)(P,D,Q)s orders. The period of the series is set to 12, because it seems the cycles are repeated every year (12 months).

• Activate the Series labels the first row of the selected data contains the header of the variable.

In the Validation tab:

• Enter 12 so that the last 12 values are not used to fit the model, but only to validate the model.

In the Prediction tab:

• Enter 12, to forecast the next 12 values of the series.

• Click on the OK button to launch the computations.

• The results are displayed on a new Excel sheet.

 

Interpreting the Results

After the summary statistics of the series, a table displays the various criteria that allow to evaluate the quality of the fit, and to compare the fit of this model with other models (if available).

The next table displays the parameters of the model.

We notice that both the MA(1) and SMA(1) parameters are significantly different from 0 as the 95% confidence interval does not include 0. The confidence intervals are computed using the Hessian after optimization which is what other software usually display, and using an asymptotical method. The constant of the model is fixed as it comes from the removal of the mean.

The ARIMA model writes:

Y(t) = 0.000+Z(t-1)-0.348.Z(t-1)-0.562.Z(t-12)+0.195*Z(t-13) where Z(t) is a white noise N(0, 0.001) Y(t)=(1-B)(1-B12)X(t), and X(t) is the input series.

The forecasting equation for the X(t) series is given by: X(t+1) = Y(t+1)+X(t)+X(t-11)-X(t-12)

A table gives the values of the original series, and the smoothed series (the predictions). Because of the constraints of the model, predictions are not available for the 13 first observations (the predictions are replaced by the values of the input series). Notice that a time variable "T" has been created to facilitate the graphical representation. For the last 12 observations predictions have been computed in validation mode and a confidence range is available. We notice that almost all residuals (in red) are negative. This means that in forecasting mode the model overestimates the traffic.