Your data analysis solution

Fit an ARIMA model to a time series in Excel

2017-04-24

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

An Excel sheet with both the data and the results can be downloaded by clicking on the button below:
Download the data

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.
Plot Passengers vs Time

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.
Plot (Log)Passengers vs Time

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

After opening XLSTAT, select the XLSTAT / Time Series Analysis / ARIMA command.

XLSTAT Ribbon

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.

The Center option is left activated as we want XLSTAT to automatically center the series before optimizing the ARIMA model.

After you selected the data, 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).

The option Series labels is activated because the first row of the selected data contains the header of the variable.

XLSTAT dialog box for Arima-General Tab

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.

XLSTAT dialog box for Arima-Validation tab

The computations begin once you have clicked on OK. The results will then be displayed.

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).

Results-Goodness of fit statistics

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.

Results-Model Parameters

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.

Results-Predictions

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

Chart Predictions

1c26995d494fb3061dd0ae8571ffc0a4@xlstat.desk-mail.com
https://cdn.desk.com/
false
desk
Loading
seconds ago
a minute ago
minutes ago
an hour ago
hours ago
a day ago
days ago
about
false
Invalid characters found
/customer/portal/articles/autocomplete
9283