Forecasting is an “inexact science” that relies on the data available to you, the math you use, and how you implement the forecast. There are libraries full of algorithms that are relatively easy to implement in software, but the math is only as good as the data it’s applied to.
And your forecasting success is fundamentally impacted by your understanding of that data, its strengths and limits. In this article we review four data concepts that drive forecast results, and we provide some practical recommendations on how to address them. Better forecasting implementations relate to:
- Preparing data for analysis;
- Measuring data currency, coverage and accuracy;
- Understanding how order fulfilment impacts your forecasts, and
- Managing spikes in the data that may or may not be real demand.
1. Preparing data for analysis
How you roll up your data for forecasting fundamentally impacts accuracy. Managers are usually rolling up a large number of very specific transactions and will need to analyze these transactions at some higher level to get a picture of meaningful sales activities and trends. Usually they want to create a number of dimensions for study.
|Time||Add up all sales by time of day, day of week, month, quarter, etc.|
|Place||Add up sales by facility, region, country – or territories|
|Product||By item, SKU, product, category|
|Supplier||By manufacturer or brand|
|Organization||By rep or team|
Managers will want to summarize by several of these dimensions simultaneously. For example, a category manager wants to track products before and after a promotion, by region and manufacturer. Business intelligence software plays an important role here – a data warehouse or database can support multiple types of aggregations and enable flexible analysis across dimensions instantly.
In forecasting, the higher the level of aggregation, the more accurate the forecast. In an over-simplified explanation, forecast errors decline as the level of aggregation grows, and, more specifically, the standard deviation of the noise terms grows as the square root of the number of units being aggregated declines.
However, the relevance of greater accuracy depends on the business situation. While a quarterly forecast for a chain may be mathematically easier to achieve than a weekly forecast for a store, if your company requires weekly forecasts you’ll need to work harder to understand how to use and communicate disaggregated forecasts that have more volatility and lower accuracy.
Recommendation: Understand the level of granularity required for your business purpose and the relative accuracy you can achieve at each level of aggregation. Here are some examples:
- Sales and Operations Planning: High level of aggregation for strategic planning
- Inventory Management: Low level of aggregation to support SKU level decisions
- S&OP and Inventory Management reconciliation: S&OP plans roll down to inventory decisions – thus, a top-down forecast must disaggregate to a SKU level forecast. Use a methodology that allows for reconciliation. (This requires a specific database structure.)
2. Measuring data currency, coverage and accuracy
In short, sales history is the best measure of future demand. There are dozens of models to analyze sales history, from simple moving averages to advanced regression methods that can measure trends, seasonality and cyclic characteristics of your data. Before you decide on your models though, you need to know whether your sales history is the same as demand for your product.
Using sales history to portray demand assumes that your inventory has always been available for sale. However, for most businesses, out-of-stock situations are a fact of life. Research shows that most grocers have about 8% of items out of stock* – and considering an average store has 25,000 stocks items, that means 2,000 are not available at any given time!
The data around “missing demand” will misrepresent the actual demand in the market. A business won’t know what a customer did when they didn’t find the product – did they buy something else, buy somewhere else, or just pass up the purchase?
Therefore, if we don’t address the gap between sales history and “true demand”, we are underestimating demand. And if we underestimate demand in one period, we are likely to set our inventory targets too low for the following period, thus creating a cycle that will frustrate customers.
Recommendation: Create a measure of true demand by making an adjustment to account for stock-outs. Specifically, adjust for the time the product was available. A simple approach would be:
- True Demand = Observed Demand / Fraction of Time in Stock
- While this requires you to blend data from different sources (CRM and ERP) and create a new measure, it should lead to a better view of your market opportunity.
3. Understanding how order fulfillment impacts your forecasts
Most businesses have situations where stock shortages occur but sales are fulfilled through alternate channels, such as an expedited order from a different location. The customer is happy but this can create chaos in the forecast because data collection can’t easily track this kind of exception. In this case, the company may record the transaction at the regional facility but would need to record the demand at the alternate facility.
A second situation of bias occurs when items aren’t available and the customer buys a substitute. For example, a customer wants a 12-pack of beer in cans but the store is out. Instead, they buy bottles. This distorts the demand estimate, potentially driving down inventory for the preferred cans and driving up inventory for the less preferred bottles.
Recommendation: Some slippage is going to occur in any large inventory system if inventory is not 100% on the shelf. To resolve this, simply record the place, time and item where the transaction actually occurred, along with availability.
4. Managing spikes in the data that may or may not be real demand.
Most businesses see occasional “spikes” in their sales. Sometimes they are data errors, and sometimes they reflect real sales. Unfortunately, spikes tend to “pull” the demand distribution in their direction, possibly skewing inventory planning.
From a forecaster’s perspective, we are most interested in typical demand that is recurring. Spikes should be researched separately to understand what caused them and whether they are recurring or one-time events. In a forecasting system, then, we would generally want to eliminate the spikes from our estimates.
Recommendation: Currently, it’s common to eliminate the spikes and replace the data points with a more typical observation, like the average volume for the previous and subsequent time periods. Another approach relevant to an industrial situation would be to record transactions as “recurring” or “non-recurring”.
For example, a transportation company may operate vehicles from various locations. When repairs or maintenance are done at an alternate facility while a vehicle is in transit, the purchase of spare parts should be recorded as one-time events so that the facility does not begin to stock parts for future repairs.
Forecasting is more than big volumes of data and elegant statistical models. By focusing on data context, you will understand your business situation more fully and present forecasts and explain results with more confidence.
*Gruen and Corsten – “A Comprehensive Guide To Retail Out-of-Stock Reduction In the Fast-Moving Consumer Goods Industry”