NumXL Cookbook - AirLine Model

In this document, we will demonstrate the few steps to convert raw time-series data into a robust forecast using NumXL.

As an example, we are using the monthly electric power demand (in MWh) for the city of Jeddah (Saudi-Arabia) between 2003 and 2010.

The time series in the figure above demonstrates a seasonality of twelve (12) months and an upward trend over time.

This data can be explained by breaking down the power demand into its' two primary components: (1) the industrial demand, and (2) the residential and business demand. The residential and business demand is influenced by weather patterns (e.g. season). You can see this through the increase/decrease use of electricity for cooling/heating. On the other hand, industrial demand is rather flat. As an input, the industrial demand is driven primarily by the economic conditions of the city or industry, and to less extent by weather patterns.

Step 1: Summary Statistics

Using the NumXL toolbar, launch the descriptive statistics Wizard.

For our illustration, we will keep all options selected. For output, select the cell in your spreadsheet where you wish the table to be written to:

Step 2: Correlogram Analysis

Using the NumXL toolbar, launch the Correlogram Wizard:

For this illustration, we will compute and plot the ACF and PACF for the first 20-lags.

The ACF plot demonstrates a classic case for the AirLine model with a cycle length of 12 steps

Step 3: Data Transformation

There is no need to transform the data before modeling. The AirLine model will stabilize the input series through the differencing terms in its’ definition.

Step 4: Modeling

Using NumXL, the modeling process consists of 3 major steps: (1A) model specification, (2B) calibration, and (3C) residuals diagnosis.

1A: Model Specification

Click the AirLine Icon on the NumXL toolbar.

NumXL will prepare a table in your spreadsheet with the model’s coefficient, the goodness of fit, and residual diagnosis routines. Note: the values of the model are not optimal yet.

2B: Model Calibration

Select the cell where the model table starts from (i.e. AIRLINE(12)). Click on the “calibration” icon in the toolbar.

The MS Excel solver dialog will pop up on your screen with your model specifications filled in. Click the Solve button.

3C: Residual diagnosis

The residuals diagnosis routines are already populated in your spreadsheet with your model parameters. Now is a good time to re-examine whether the optimal model satisfies the underlying assumptions.

Step 5: Forecasting

To perform a forecast using NumXL, select the cell of the model’s table (i.e. AirLine). Click on the “Forecast” icon in the toolbar.

The forecast wizard will pop up. Select the realized observations (i.e. tail points of your sample data), and the forecast horizon in units of steps.

For this illustration, the last 23 observations in the sample data were chosen, then the forecast horizon was set to 30 steps (or units), as shown above.

Plot the forecast and its’ confidence intervals.