Demand forecasting: when the spreadsheet stops being enough

A spreadsheet and a moving average hold up for longer than ML vendors admit. But there are clear signs it is time to change approach. We list them, along with a plan for the first weeks of a project.

Almost every trading or distribution company starts with a spreadsheet. The planner takes the last few weeks of sales, calculates an average, adds their own experience and places the order. For years this works surprisingly well. The trouble is that nobody notices the moment it stops.

This article is about that moment: how to recognise it, what to do next and how not to waste the first three months of a project.

What a good spreadsheet does, and why not to mock it

Moving averages and exponential smoothing are simple, but not naive. Exponential smoothing gives more weight to recent data and can handle trend and seasonality. For a stable product sold through one channel, it produces a result that is hard to beat by more than a few percentage points.

We say this plainly because some ML projects start with the slogan "we are replacing the spreadsheet" and end in disappointment. It is better to treat the simple method as a baseline. If a model does not beat it on data it has never seen, there is nothing to deploy.

Competitions show how high that bar is. In M5 (Walmart sales, almost 6,400 teams), only 7.5% of participants beat a very simple benchmark based on exponential smoothing. The winning solutions, though, were pure ML rather than statistical methods.

How the ML approach differs

The M5 results showed two things. Models based on gradient boosting (mainly LightGBM) beat the classics. And the best teams trained one model on many series at once, instead of a separate model for each product. This is called a global model.

It matters in business because most SKUs in distribution have a short or patchy history: a new product, a few units a week, a seasonal range. A separate model for each of them has nothing to learn from. A global model takes patterns from similar products and carries them over to new ones.

The second advantage is external variables. M5 was the first Makridakis competition where the organisers released prices and special events. A spreadsheet struggles with such inputs, while a tree-based model takes them in naturally:

promotions: discount depth, mechanic, leaflet, placement,
calendar: days of the week, moving holidays, long weekends, paydays,
weather, especially for food, drinks, garden and DIY,
own price and competitor price, if we have it.

Signs it is time to change approach

There is no single threshold. In the projects we run, we most often see several of the following at once:

Planners spend most of the week on manual corrections rather than decisions.
Promotions are the biggest source of error, and their effect is estimated "by eye" from the previous campaign.
There are so many SKUs or locations that nobody can review all the forecasts.
You have too much of some goods and shortages of others, while the average stock level looks fine.
Waste or expired stock is growing in short shelf-life categories.
The forecast "for the whole" is right, but at store or regional warehouse level it is not.

If three or more of these apply, the cost of bad forecasts usually exceeds the cost of the project. Customers notice too: in a survey described by Supermarket News, consumers said that one in five items they want to buy is unavailable in their store. The scale of losses is large: according to IHL Group, North American retailers lost around $349 billion in 2022 to out-of-stocks and overstocks. That is US data, but the mechanism is the same in Poland. In the Polish food sector there is also waste: according to estimates by the Federation of Polish Food Banks (Federacja Polskich Banków Żywności), retail accounts for about 7% of wasted food, roughly 0.3 million tonnes a year (this is our own calculation, and estimates differ in methodology).

Metrics: how not to fool yourself

The most popular measure is MAPE, the mean absolute percentage error. In retail its pitfalls show up quickly.

When sales are zero, the percentage error is undefined. Low-volume items can dominate the result even though they matter little to the business. For intermittent demand, MAPE is simply useless.

A better starting point is WAPE: the sum of absolute errors divided by the sum of sales. It weights products by volume, so it avoids the zero trap. It has its own flaws, though: on intermittent series it can reward a model that always predicts zero.

Neither measure shows the direction of the error. That is why we track bias alongside them, meaning systematic over- or under-forecasting. A model with WAPE of 25% and bias close to zero is often much better for a warehouse than a model with WAPE of 22% and a constant 8% under-forecast. The first creates noise, the second creates stockouts.

Bad input data: demand you cannot see

The most common substantive error is a quiet one. Sales are not demand. If a product sold out on Tuesday at noon, the data shows zero (or very little) for the rest of the day, even though customers came. Recorded sales are capped by stock on hand, and the model learns from them that demand was low.

The effect feeds on itself. The model under-forecasts, the order is too small, the product runs out again. Add substitution: a customer who could not find product A buys B, so B looks better than it is.

The cure is to clean the history before training. We flag stockout periods (from stock data, not from sales), and in those periods we estimate demand from similar, undisturbed days. This needs daily stock levels, which often have to be collected first. Ask for them at the very start.

A realistic 8-12 week plan

The plan assumes one category or one wholesaler, not the whole company.

Weeks 1-2: data and baseline. We collect sales from at least 2-3 years at SKU, day and location level, stock data, the promotion calendar, prices, and a list of launches and delistings. We calculate how wrong today's forecast is (WAPE and bias). That is the number we will be trying to beat.

Weeks 3-4: cleaning and features. We flag stockouts, correct returns and anomalies, and build the calendar and promotion features. This stage usually takes longer than anyone expects: it turns out that the same product has three different codes in three systems, and promotions live in emails and sales reps' spreadsheets. Better to find that out in week three than in week ten.

Weeks 5-7: models and backtest. We train a global model (gradient boosting) and compare it with exponential smoothing over several historical periods, not just one. We look separately at fast-moving, slow, promoted and new products.

Weeks 8-10: shadow mode. The model forecasts in parallel with the planners, but nobody orders based on it yet. Every week we compare results and talk to the planners about the differences. This is the most valuable part of the project.

Weeks 10-12: decision pilot. The model's forecast becomes the starting point for orders in one category, and the planner can adjust it. We measure how many adjustments were justified.

How to measure business success

Forecast accuracy is a technical measure. The board should see something else:

service level (availability of goods on the shelf or in the warehouse),
stock value and turnover,
waste and expired stock in fresh categories,
planner time spent on corrections,
sales lost to stockouts (an estimate, but a consistent one).

Agree on these numbers before the start. A good project shows them for a test group and a control group, not before and after, because season, prices and range all change along the way. From experience: it is better to promise an improvement in one indicator that can be checked than in five that cannot.

Common mistakes

Forecasting without planning. The model spits out a number, but nobody has changed the ordering process. A forecast is worth something only when someone knows its uncertainty and turns it into safety stock. That is why we usually forecast an interval, not a single number.

Planners who do not trust the model. If the model is a black box, people override it by hand. So we show what drives the forecast (promotion, holiday, weather) and measure whether planners' corrections improve the result. Sometimes they do, and then that knowledge has to be built into the model.

Too many SKUs at once. Starting with the whole range ends in a thousand exceptions that nobody has time to review. It is better to start with the category where errors hurt most and expand after the pilot.

Ignoring launches and end of life. This is where a global model has the biggest advantage and a spreadsheet the biggest weakness. If the pilot does not cover these cases, it will not show the full value.

What to do on Monday

Pull sales and stock for the last two years for one category out of your system. Calculate WAPE and bias of the current forecast, separately for promoted and non-promoted items. If the bias is clearly one-sided or promotions come out several times worse, you have your first argument and your first baseline. If you want to do it together, we are happy to talk.