NumXL Cookbook - AirLine Model

In this document, we will demonstrate the few steps to convert a 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 Jeddeh (Saudi-Arabia) between 2003 and 2010.

Residential Power demand plot

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, the 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 NumXL toolbar, launch the descriptive statistics Wizard.

Descriptive Stat Wizard in  Excel

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:

Summary Stats output table

IMPORTANT: In the table above, NumXL generates the formulas into your spreadsheet. You can easily review the calculation, and, if needed, make any proper changes.

Step 2: Correlogram Analysis

Using the NumXL toolbar, launch the Correlogram Wizard:

Correlogram Wizard in Excel

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

Correlogram Wizard output table in Excel

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.

Airline Model Wizard in Excel

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

Airline model specifications/output parameters in Excel

2B: Model Calibration

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

Airline calibration wizard in Excel

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.

Optimal values for AirLine model

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.

Airline forecast Wizard in Excel

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.

Airline forecast output table in Excel

Plot the forecast and its’ confidence intervals.

Airline forecast plot with confidence - upper and lower limits - region

Have more questions? Submit a request