Fit an ARIMA model to a time series in Excel

This tutorial will help you set up and interpret an ARIMA - Autoregressive Integrated Moving Average - model in Excel using the XLSTAT software.

Dataset to fit an ARIMA model to a time series

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 the fitting of an ARIMA model to a time series

• Open XLSTAT

• Select the XLSTAT / Time Series Analysis / ARIMA command. Once you've clicked on the button, the ARIMA dialog box will appear.

• Select the data on the Excel sheet. 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.

• Click OK to launch the computations.

Interpreting the results of an ARIMA model fitting to a time series

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.

On the chart below, we can visually see that the predictions (Validation) are very close to the data.