Best Practices for History Cleansing
There is an old saying “Your forecasting accuracy is as good as your sales history “. History cleansing is the process to derive the baseline history of a product, which is its normal historical demand without promotion, external stimulation, or any other anomaly or biasness. An outlier is a too-high or too-low sales figure in a product’s history that may occur under special conditions.
History Cleansing is the process of cleansing the historical sales data, which is a prerequisite when a company is using the Statistical Forecasting functionality in SAP IBP for Demand. The better the history is, the better the forecast. The purpose of history cleansing is to derive a baseline history, free of any promotions, shortages, or any other unexpected changes. Statistical forecasting attempt to identify trends or patterns that might repeat in the future. Because events such as shortages or promotions cannot be predicted using patterns, that related data needs to be removed from the history, which is an input in forecasting. Although promotions are often regular occurrences for CPG products, these events may not always happen in the same time frames as they did in the past. Once the sales history is cleansed, forecasting algorithm uses a baseline history, to generate a baseline forecast. History cleansing is one of the most important activities for any IBP Demand Planning project, but it can also be the most time-consuming activity. For organizations handling thousands of SKU’s, cleansing history for periods of 2 to 3 years can consume around 30% of the overall project timeline. Beyond a one-time history cleansing during the implementation of SAP IBP for Demand, cleansing needs to an ongoing process. Having completed quite a few DP projects that involve history cleansing, below are the 8 best practices that will help organizations to complete the history cleanup process in a short time frame.
What is the Scope of the History Cleansing
A very common practice, that I have seen is to try to cleanse the history of every possible product that the organization had sold in the past. If the purpose of history cleansing is to get the best statistical forecast, then defining the scope is critical. Ideally, history should be cleansed only for those products that can be statistically forecasted. There are certain products for which statistical forecasting is difficult, so organizations often prefer to forecast them manually. For example, new products or products that are about to be discontinued soon, or seasonal products for which very little history is available. Statistical forecasting in IBP Demand needs 24 to 36 months of sales history to generate a good statistical data. For these cases, history cleansing may not be important from a statistical forecasting perspective.
How to prioritize the Cleansing Process
Improvements of just a few percentage points in forecast accuracy for these products can help in achieving the overall accuracy KPI metrics. Once history for these products is cleansed, the cleansing effort can be extended to other products. The 80/20 analysis is also known as ABC analysis where the top 20% of products contributes to 80% of sales and are defined as A-class products. This A class is most important from a cleansing perspective, while the C class the least priority.
How Grouping Products for Cleansing Helps
This best practice complements the first two with an alternate approach, also called grouping of products, which can further focus history cleansing efforts on selected product sets. Grouping of products differs from industry to industry and from product characteristics to characteristics. Each category is treated differently from a history cleansing perspective. Examples could be Base Product Group, Promo product group, Repack product group, etc. For example, for a commodity industry that deals minerals, there can be many products from a Base group for which there is not much promotion. Therefore, cleansing is only needed for production shortage or non-delivery scenarios. A CPG company perhaps will have most products in a Mix category and a few in promotional categories that are launched during certain seasons or events.
How to Identify Components for Baseline History
If the purpose of cleansing is to get a baseline history for shorter horizon, it is important to identify what should be included as a part of the baseline history and what should not. Typical examples of components to include, are permanent promotions, month or quarter end picks & seasonal behavior. On the other hand, what should not be included are trade promotions, ATL, and special NPI promotions. Clearly identifying the inclusion and exclusion components eliminates any future confusion & conflicts. One common conflict is deciding which history to use? the invoiced quantities or shipped quantities. Ideally, there should not be much difference, but the reality is that they can be different. In these cases, the history of shipped quantities is a better choice.
How to Determine Time Buckets and Hierarchy for Cleansing
There is no strict guideline on time buckets for history cleansing. The decision is mostly influenced by the time bucket for forecasting. If the forecasting happens at a weekly level, the history also needs to be cleansed at a weekly level. The ground rule is the history cleansing time bucket needs to be the same or one level lower than the forecasting time bucket. If a business needs to perform cleansing at a more granular level (e.g., weekly) or at more of an aggregated level (e.g., monthly), the decision needs be made after a careful evaluation of both approaches. Cleansing at a more granular level involves more effort, but also helps in identifying patterns more precisely. Whereas in aggregate level, minute variation cannot be predicted, which will result in a biased base history. It is also important to decide on the product hierarchy for history cleansing. Again, the decision is influenced by multiple factors. A robust approach is that cleansing needs to happen at least at the same level or one level below the level at which forecasting is done. In most cases the cleansing is performed at a lower level. Cleansing done at the product level and forecasting at the product group level is an ideal example.
How much data should we Clean
This is a common debate at the beginning of any project. Although the most common is a 3-year history cleansing approach, it’s important to remember that the objective of this exercise is to generate the best statistical forecasts. Determining the length of history is predominantly dependent by which algorithms we will use for its statistical forecasting. In IBP for Demand, different statistical forecasting algorithms need history for different duration. For example, a seasonal algorithm might need history for a longer duration (e.g., 2 to 3 years), whereas a trend algorithm with a high alpha value will calculate forecast that are 95% dependent on history of the last eight-time buckets. Most statistical models give more weight to the most recent history, so it’s always important to focus on the most recent history and work backwards. In IBP demand you can select a longer sales history and let the system decide to choose the best model based on the minimum error (MAPE, MAD, etc.) that we choose.
How do we Handle & Correct Outliers
IBP demand provides the functionality of automatic outlier correction (i.e. correction of past sales values that are too high or too low) with multiple options to control the correction threshold. Some also prefer to use their own logic for such outlier correction. As an one-time exercise before any IBP demand project go-live, an outlier correction can be done using Excel macros, but post go-live, this needs to be done on an ongoing activity in IBP only.
How to do Promotional effect correction
Excel macros can be developed to calculate the approximate effect of promotions by averaging the weekly or monthly sales of the promotional period for the entire year. Once the exact periods of the past are known, such macros can be applied to take out the effect of such promotions. However, the same logic often may not work for different types of promotions, and manual cleansing by planners may still be required post-auto cleansing. Mathematical automation also comes in handy for ongoing data cleansing. IBP demand provides the functionality of automatic adjusting the promotion effect, which can be found in the pre-processing step.