Your data analysis solution

Fit an ARIMA model to a time series in Excel

2016-11-03

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 results can be downloaded by clicking here. 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.

hw1.gif

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.

arima1.gif

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.

menu arima

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.

dialog box arima general

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.

dialog box arima validation

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 arima optimization

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 arima coefficients

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 residuals arima

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

graphic arima time series

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