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.

This figure shows the 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, 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.

This figure shows the 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:

This figure shows the Summary Stats output table


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:

This figure shows the Correlogram Wizard in Excel

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

This figure shows the 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.

This figure shows the Airline Model Wizard in Excel

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.

This figure shows the 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 toolbar.

This figure shows the 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.

This figure shows the 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.

This figure shows the 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.

This figure shows the Airline forecast output table in Excel

Plot the forecast and its’ confidence intervals.

This figure shows the Airline forecast plot with confidence - upper and lower limits - region


Please sign in to leave a comment.

Was this article helpful?
0 out of 0 found this helpful