Measuring Forecast Accuracy via Crosstabulation Analysis

Overview

The methods shown here explain how to obtain measures of forecast accuracy for any totalling level using data at the relevant base level. In most cases, the correct base level of detail to use is item code.

See also Measurement of Forecast Accuracy for general principles on forecast accuracy measurement.

Forecast Accuracy

To produce this analysis:

From IFP Home, select Reporting > Crosstabulation Analysis
Select the relevant forecast file (file #1) and actual file (file #2)
Note that the forecast file must contain previous forecasts for each period at a suitable lead time (e.g. 2 months). See Maintaining Forecast Accuracy Reference Files for details.
Select one or more months to be included in the analysis.
Select any combination of items at the relevant base level of detail (see Overview)
Select the relevant crosstabulation definition, i.e.
- for single months use 'Forecast Accuracy Analysis - Single Selected Period'
- for multiple months use 'Forecast Accuracy Analysis - Full Year by Month'

Example Analyses

In this type of analysis each forecast value from month-2 is compared with the corresponding actual value. Percentage accuracy is computed using the formula:

Percentage accuracy = 100*(1 - ABS(Forecast - Actual) / Actual)

This produces an analysis as follows:

Notes:

To force display of zero accuracy values, select the inclusion option 'Exclude zero lines' (otherwise zero accuracy values are display as blank)
Negative accuracy values may be replaced with any chosen text by using Format > Number Format with a conditional format string like #0.0; *Neg*

Multi-Period Analyses

For multi-period analyses the format of the report is as below:

3 Month Rolling Bias Analysis

A further analysis option measure bias and % bias over a 3 month rolling period. This analysis uses the same file options for the period January to December combined with the crosstabulation definition, 'Forecast Accuracy Analysis - Full Year by 3 Month Rolling' (copy attached below).

Note: Results are not displayed for January and February as insufficient data are available to calculate 3 month totals for these months.

Downloads

To add the above crosstabulation definitions to your database proceed as follows:

Save the attached files in a convenient location on a local shared drive.
In IFP go to Crosstabulation Definition Manager > General > Tools > Import > [first attached file name]
Then repeat with the second attached file name.

Articles in this section