Demand Forecasting for Retail: From Excel to Machine Learning

The short answer

A demand forecast estimates how many units of a product will be bought in a given store in a given period. The usual path is Excel → statistical methods → ML models, and each step is justified only if it beats the previous one on your data.

The main rule: data quality matters more than the algorithm. A model trained on "dirty" sales will confidently predict yesterday's mistakes.

Sales are not demand

Receipts record sales, not demand. Three factors drive them apart:

• Stockouts. If the product was not on the shelf, sales are zero even though there was demand. Flag those days and either exclude them or reconstruct demand (for example, from average sales on similar days).
• Promotions. A discount lifts sales temporarily, and afterwards there is often a dip — customers stocked up. You need a "on promo" flag, the mechanic type and the discount depth.
• Seasonality and calendar. Weekly cycle, holidays, Ramadan, back-to-school, weather. Moving holiday dates must be set explicitly rather than hoping the model "sees" them.

Also: cannibalization (a promo on one item reduces sales of its neighbors), assortment changes, a competitor opening nearby, layout changes.

Simple methods: the baseline

Before building ML, set a baseline:

If a complex model cannot beat the naive forecast, the problem is in the data or the problem framing.

When to move to ML

Models like gradient boosting (LightGBM, XGBoost) train on all products and stores at once and use features such as:

• lags and rolling averages of sales;
• calendar: day of week, holidays, paydays;
• price, promo mechanic, discount depth;
• product and store attributes;
• external data: weather, events — if it is truly available at forecast time.

ML pays off when there are many SKUs, frequent promotions and enough history. For new products with no history, use analogs by category and attributes.

Key technical rules:

• No future leakage. Features must be available at forecast time. Actual weather on the forecast day is leakage; a weather forecast is not.
• Time-based validation. Train on the past and test on the following period (rolling-window backtesting), not on a random split.
• Hierarchy. Store-item forecasts should reconcile with category and chain forecasts.

Accuracy metrics

• MAE — mean absolute error in units. Easy to read but not comparable across products.
• MAPE — percentage error. Breaks on items with zero sales.
• WAPE — sum of absolute errors divided by sum of actual sales. More robust to zeros and convenient for aggregates.
• Bias — systematic over- or under-forecasting. A model with small error but constant under-forecasting will chronically cause stockouts.

import numpy as np

def wape(actual, forecast):
    actual, forecast = np.asarray(actual), np.asarray(forecast)
    return np.abs(actual - forecast).sum() / actual.sum()

def bias(actual, forecast):
    actual, forecast = np.asarray(actual), np.asarray(forecast)
    return (forecast - actual).sum() / actual.sum()

Evaluate metrics by segment: fast movers, the long tail, promo periods.

From forecast to auto-replenishment

A forecast alone changes nothing — the value appears in auto-replenishment:

1. Forecast demand over a horizon = lead time + review period.
2. Safety stock — from the spread of forecast error and the target service level for the category.
3. Order = forecast + safety stock − current stock − in-transit stock.
4. Constraints: pack size, minimum order quantity, shelf and warehouse capacity, shelf life.
5. The recommendation goes to the category manager for approval; over time, stable items are ordered automatically.

This requires accurate stock levels, in-transit data and integration with your ERP or inventory system.

Common mistakes

• Training on sales without accounting for stockouts — the model under-forecasts demand.
• Measuring accuracy only as a chain-wide average, without segments.
• No monitoring: the model degrades after an assortment change and nobody notices.
• The forecast is not wired into ordering and stays a "nice-to-know report".