Demand forecasting: the data quality problem behind every bad forecast

Demand forecasting projects are usually scoped as a modelling exercise: pick a method, train on sales history, measure the error. When the result underperforms the spreadsheet it replaced, the response is a better method. In most estates DNA Solutions works in, the history described something different from demand, and no algorithm recovers information the data does not contain. This article covers the input problems that limit forecast quality.

Why the method rarely limits demand forecasting accuracy

Forecasting methods are mature and largely commoditised. Exponential smoothing families, gradient boosting on engineered calendar and price features, and the standard hierarchical reconciliation approaches are all available in libraries, well documented, and adequate for most commercial demand series. The difference between two competent implementations on the same clean data is usually a few percentage points of error.

The difference between a clean history and a compromised one is much larger, and it is invisible in the model evaluation, because the evaluation uses the same compromised data for training and for testing. A model that learns a systematic distortion present in both will report a good score and forecast the distortion.

This is why forecast accuracy programmes that begin with method selection frequently plateau. Which method fits which question is covered in our article on predictive analytics methods. What follows assumes the method is adequate and asks what the history records.

Censored demand: why sales history understates demand

This is the most consequential input problem and the most common.

The system of record holds sales, shipments or invoiced quantities. Demand is what customers wanted. The two agree only while stock was available. During a stockout, sales fall to whatever the remaining inventory allowed, and that reduced figure enters the history as the demand for that period.

The consequence is a loop that reinforces itself. The model learns that demand for that item in that location was low, forecasts low, the replenishment system orders accordingly, stock runs short again, and the next period confirms the pattern. Items with genuine growth are the ones most exposed, because they are the ones that ran out.

Correcting it requires knowing when each item was unavailable, which is inventory position over time at the level the forecast is made. Many organisations retain only current stock, which makes historical availability unrecoverable, and the first fix is therefore to begin recording it rather than to reconstruct it. Where daily stock history exists, periods of zero or near-zero availability can be flagged and the demand for those periods treated as censored: excluded from training, or estimated from comparable unaffected locations.

Two adjacent effects belong here. Substitution, where a customer buys an alternative, records demand against the wrong item and hides the shortfall in the aggregate. And lost sales that leave no trace at all, particularly online, where an unavailable item may never appear in any log. Where a search or basket log exists, unfulfilled interest can be partially observed, and it is worth connecting to the forecasting inputs.

Demand forecasting granularity and hierarchical reconciliation

A forecast has to be produced at the level the decision is taken. Replenishment needs item and location. Production planning may need item and week. Financial planning needs category and month. These are different forecasts of the same demand, and they have to agree.

Two failure modes recur. Forecasting only at an aggregate level and allocating downward using historical shares, which assigns growth to locations that are not growing. And forecasting only at the finest level and summing upward, which produces a total that inherits the noise of thousands of sparse series.

The workable approach forecasts at more than one level and reconciles, so the item-location figures sum to a category figure that is itself defensible. This requires the product and location hierarchies to be correct and stable, which brings the next problem.

Master data changes that break sales history

Forecast quality degrades after range reviews, system migrations and reorganisations, and the cause is usually structural rather than behavioural.

• A product is renamed or reissued under a new code. The old series ends, a new one starts with no history, and the model treats an established item as a new launch.
• Two codes are merged, or one is split into variants. The history of the resulting series is a discontinuity that no seasonal method handles correctly.
• The category hierarchy is reorganised. Aggregate series change composition, so a year-on-year comparison spans two different definitions.
• Units of measure change. A case size revision alters the numeric series while the item appears unchanged, which produces a step that looks like a demand shift.
• A location is reassigned between regions, moving volume between aggregates without any change in underlying demand.

None of these raise an error. The pipeline runs, the forecast is produced, and the deterioration is attributed to market conditions.

The control is a maintained mapping of code changes, merges and splits, applied when constructing the history so that the series follows the product rather than the identifier. That mapping is a master data responsibility, and where it does not exist it has to be built, which is the argument set out in our article on master data management. A change log with effective dates is the minimum: without dates, a correction cannot be applied to the right part of the history.

Promotions and price: the demand drivers missing from history

A meaningful share of demand variation in most commercial series is caused by things the organisation did: a promotion, a price change, a listing gained or lost, a campaign, a range change in a competitor.

Where the history records the resulting sales without the cause, the model has to explain a spike using the only information available, which is the calendar. It concludes that the third week of March is strong, and forecasts a spike next March whether or not the promotion repeats. The error is then largest exactly where the commercial stakes are highest.

Getting promotional history into the inputs is usually an integration problem rather than a modelling one. The promotional calendar lives in a trade planning system or a set of spreadsheets, with mechanics, dates, participating locations and discount levels held in a form that was designed for execution rather than analysis. Reconstructing it retrospectively is slow, and the practical position is to start capturing it in a usable structure now and accept that the first year of promotional modelling works from partial history.

Price deserves separate treatment. Where the actual transacted price is available per period and location, price elasticity can be estimated and the forecast becomes conditional on a pricing decision, which is considerably more useful to a planner than an unconditional projection. Where only list price is held, most of the effect is invisible.

Intermittent demand forecasting for slow-moving items

Long-tail items with many zero periods are common in spare parts, industrial distribution and wholesale, and standard methods handle them poorly. Averaging produces a fractional forecast that is never a plausible order quantity, and error measures based on percentages break down when the actual value is zero.

Two things follow. These series should be identified explicitly and routed to methods intended for intermittent demand, which forecast the demand size and the interval between events separately. And the output that matters is often not a point forecast at all: for a spare part, the useful answer is a stock level that meets a service target, which is a question about the distribution rather than the mean. Forcing intermittent items into the same pipeline and the same accuracy metric as fast movers produces a number that is technically a forecast and operationally unusable.

Returns, cancellations and the target variable definition

The target variable needs a written definition, and the obvious candidate is ambiguous in practice.

Is the unit the order, the shipment or the invoice? Are cancelled orders included? Are returns netted off, and against the period of the original sale or the period of the return? Are inter-company transfers and stock movements between locations excluded, or do they inflate the series? Where returns are material, netting them against the return period produces negative demand in some periods, which most methods handle badly.

None of these has a universally correct answer. What matters is that the definition is written down, agreed with the planners who consume the forecast, and applied consistently to both history and evaluation. A large share of disputes about forecast accuracy turn out to be two parties measuring different quantities.

Forecast bias, forecast accuracy and the planner override

Forecast error is usually reported as an accuracy percentage. Bias, meaning the average signed error, is the more actionable measurement and it is reported less often.

A forecast that is consistently low is being corrected downstream, by a planner adding a margin or a safety stock parameter absorbing the gap. The correction is invisible in the accuracy figure and expensive in working capital, and it compounds through each stage of the supply chain. Measuring bias by item group, location and horizon locates it.

The related structural problem is the consensus process. A statistical forecast is produced, then adjusted in a planning meeting where sales, marketing and finance each hold a view, and where at least one participant has a commercial incentive attached to the number. The adjusted figure becomes the plan.

Overrides are legitimate: a planner often knows about a customer contract or a listing decision that is absent from any history. The requirement is that both numbers are retained and both are measured. Where the statistical forecast and the final plan are stored separately and their errors compared over time, the value added by the override becomes visible, and it is frequently negative for some groups and clearly positive for others. Without that record, the discussion has no evidence in it.

For accuracy itself, weighted absolute percentage error computed on volumes is less distorted than a simple average across items, since it stops thousands of tiny series dominating the figure. And the measurement has to be taken at the horizon the decision uses: a forecast evaluated one week ahead says little about the eight-week lead time the purchase order depends on.

The order to fix demand forecasting data problems

In the estates we see, the order that produces the fastest improvement is consistent.

Begin by writing the target definition and reconciling it with the planners. Start recording inventory availability over time, if it is not already retained, since nothing recovers it retrospectively. Build the code change mapping so the history follows products rather than identifiers. Separate intermittent items and route them to appropriate methods. Bring the promotional calendar and transacted price into the inputs, accepting partial history at first. Then measure bias by segment and horizon, and store the statistical forecast alongside the final plan.

Method improvement comes after that, and it is worth more once the inputs describe demand.

Talk through your demand forecasting inputs

DNA Solutions does this work as part of our predictive analytics practice, and in most engagements the first phase is an audit of the history rather than a model. Where the product and location hierarchies have to be repaired across several source systems before any of it holds, that runs under data and analytics. Talk to us.