Data Preparation and Cleaning

Overview

Any mathematical analysis of trends and seasonality will be of limited value if the historical data being used has not been previously cleaned to remove the effects of known unusual events. For example, actual sales data are often distorted by out of stock situations.

This topic outlines some of the issues and solutions available in preparing suitable clean data.

Historical Data Collection

Factors to consider when collecting historical data for use in Statistical Forecasting are as follows:

1. Use ex-factory and/or in-market sales data
2. Treatment of free/bonus goods
3. Seasonality
4. Level of detail to use
5. Inclusion of geographical details (e.g. by country/region)
6. Inclusion of customer/customer group details
7. Relevant period of history to use
8. Inclusion of launch periods for item
9. Development of new markets in a selected period of historical data
10. Development of new uses of a product in a selected period of historical data
11. Appearance of new competitors in a selected period of historical data

Dirty Data - Causes

In practice, many factors may cause dirty data, e.g. values which do not follow general trends and seasonality. Example factors are as follows:

1. Supply issues
2. Changes in item code
3. Switching between similar item codes
4. Price change effects on purchasing behaviour
5. Temporary marketing effects (e.g. buy one get one free offers)
6. Year-end manipulation in order to meet annual targets
7. Unusual weather
8. Epidemics
9. Inventory adjustments by customers
10. Changes in competition

Data Cleaning Principles

The process of cleaning available data may include the following adjustments:

1. Smoothing of short-term supply shortage effects
2. Conversion of data for old item codes into current item codes
3. Adjusting for switching between similar item codes
4. Smoothing of price change effects
5. Removal of estimated temporary marketing effects
6. Removal of year-end manipulation effects
7. Removal of unusual weather effects
8. Removal of unusual epidemic effects
9. Smoothing of customer inventory adjustment effects

Demand Files in IFP 

In IFP, actual data are normally stored in files with suffix A (or similar).

Special 'demand' files are used to store cleaned data. Normally, these files are given the suffix D.

Editing Demand Files

Demand files may be edited via Standard Actual/Forecast Editor in the same way as forecast data are edited. Initial data for demand files are best created via Data File Manager by copying all data from actual (A) files into corresponding demand (D) files for each year.

Please see our Video Tutorial on the Data File Manager for full information on Adding and Editing, and copying data between Data Files. If required, we also have a more in-depth video for Copying Data from One File to Another.

Various options exist for editing such data. For example, editing of demand data over several years for each item could use the type of grid shown below:

Note: It is important to enter comments for each item for each year where demand data does not equal actual data.