Demand forecasting: accurate enough to stop over-ordering

Almost every company forecasts demand. Most do it in a spreadsheet, by hand, on the last Friday of the month, using the judgement of whoever has spent twenty years buying. That is not a problem in itself — the judgement is usually good. It becomes a problem when that person goes on holiday, when the catalogue grows from 200 to 2,000 SKUs, or when nobody can say how far off last quarter's forecast actually was. That is the point where a data-driven model starts to pay for itself.

This article stays concrete: how much history you really need, what error is reasonable to expect, how to choose between a moving average and a machine learning model, and — the part nobody explains — how to connect the forecast to purchasing and production decisions so it changes something. Because a forecast nobody uses to place an order is an expensive statistical exercise.

What is demand forecasting actually deciding?

Demand forecasting means estimating how many units of each SKU you will sell over a future period. Put that way it sounds obvious. What is less obvious is that it almost never decides "how much we will sell": it decides how much needs to be available, which is a different question with direct financial consequences.

The difference shows up in which decisions hang off the forecast:

• Purchasing. How much to order from each supplier and when, given their lead time and minimum order quantity.
• Production. What to make next week and in what sequence, if capacity is limited or changeovers are expensive.
• Warehouse. How much safety stock to hold per SKU and in which location.
• Cash. How much money sits locked in inventory over the next three months.
• Staffing. How many shifts or temporary hires are needed in peak season.

Each of those has a different horizon. Purchasing may need twelve weeks of visibility because the supplier is in Asia; production needs next week in detail; finance needs the quarter in aggregate. A single "three-month forecast" serves none of them well. Before modelling anything, write down which specific decision the number will change and how far in advance that decision gets made.

If nobody is going to change an order because of the number, you do not need a forecast — you need a report. They cost the same to build and they are not the same thing.

What history does demand forecasting need?

Short answer: two full years of sales at SKU and week level, if the business has any seasonality. With a single year, a model cannot tell "we sell more in July" apart from "that July was odd". With less than a year you can still work, but with simpler methods and lower expectations.

Beyond raw volume, you need:

• Useful granularity. Date, SKU, quantity, customer or channel, and warehouse or point of sale. If all you have is aggregated monthly revenue, you can forecast the aggregate, not the assortment.
• Demand, not sales. This is the nuance that ruins most forecasts. If you sold nothing one month because you were out of stock, the history records zero and the model learns there is no demand there. Stockouts must be flagged and, where possible, lost demand reconstructed.
• A promotion and price calendar. A spike caused by a 30% discount will not repeat on its own. Without that column, the model attributes the spike to the date and projects it into next year.
• Known events. Regional public holidays, plant shutdowns, SKUs entering and leaving the catalogue, packaging changes.
• A clean item master. If the same product has carried three codes in five years, the history is split three ways and none of the pieces is deep enough.

That last point usually eats half the project. It is not glamorous, but no model survives without it — it is exactly the work we describe in data engineering. If opening the ERP reveals that new products inherit codes from discontinued ones, you have a master data problem before you have a forecasting problem.

What forecast error is acceptable, and how do you measure it?

This is where expectations should come down and rigour should go up. No forecast is right. The correct question is not "how accurate is it?" but "is it more accurate than what we did before, and is the difference worth the cost?".

The two metrics used in practice:

• MAPE (mean absolute percentage error): easy to explain to the board, but it explodes on low-volume SKUs. If you expected 2 units and sold 4, the error is 100% even though the impact is irrelevant.
• WMAPE (the same error weighted by volume): far more honest for decision-making, because it weights each SKU by what it actually contributes.

As a general industry orientation, a high-rotation, stable product tolerates single-digit or low double-digit error; an intermittent or long-tail product can go far above that and still be unavoidable. So the number that matters is not absolute MAPE but the improvement over the naive model: forecasting that next week will look like this week, or like the same week last year. If your sophisticated model does not beat that baseline, do not put it into production — you already have the answer for free.

Two practical rules for measuring properly:

1. Always measure at the horizon you decide on. If you buy eight weeks ahead, evaluate error at eight weeks, not at one. Error grows with horizon and the optimistic picture misleads. 2. Measure on data the model never saw. Training and evaluating on the same period produces spectacular, false results. You hold back the most recent stretch of history for validation, as in any machine learning project.

Moving average, classical statistics or machine learning?

There is no universal winner, only a fit between the type of demand and the method. This table summarises the criteria we apply before touching anything:

| Method | When it fits | What it requires | What to expect | |---|---|---|---| | Moving average / smoothing | Stable demand, short catalogue, little seasonality | 6-12 months of history | An honest baseline; sometimes enough on its own | | Classical statistics (seasonality + trend) | Clear, recurring seasonality | 2 years, holiday calendar | Solid gain at low cost, fully explainable | | Intermittent demand models | SKUs that sell on only a few days a month | Long history, even if sparse | Avoids the classic error of forecasting 0.3 units | | Machine learning | Wide catalogue, promotions, price, external drivers | 2-3 years, clean promotion data | The biggest gain — but only with data and maintenance | | Adjusted expert judgement | Launches, products with no history | A documented consensus process | Essential; using it is not a failure |

Two observations that save money. First: start with the simplest method that solves the case, measure, and move up a level only when the gain pays for the cost. Second: most companies do not need one model but need to segment the catalogue — for example an ABC analysis crossed with variability — and apply a different method to each group. The SKUs carrying the bulk of revenue deserve the good model; the long tail is managed with rules and safety stock.

How do you get the forecast into purchasing and production?

This is the step that decides whether the project is useful or ends up in a shared folder. The number alone buys nothing; it has to become a proposed order inside the process that already exists.

What needs building:

1. Translate forecast into requirement. Forecast demand minus available stock minus goods in transit, plus safety stock. That calculation depends on the target service level and on supplier lead-time variability, not just on the average. 2. Respect real constraints. Minimum order quantity, pallet multiples, truck capacity, shelf life, line capacity. A proposal that ignores minimum order quantity gets corrected by hand — and once it has been corrected twice, people stop looking at it. 3. Define the cycle and the owner. Who reviews the proposal, how often, and with what latitude to change it. A weekly cycle with a short consensus meeting between sales, purchasing and operations beats an automated report nobody validates. 4. Log manual overrides. Every time someone changes a quantity, record why. Three months later that log tells you exactly what the model is missing: promotions not loaded, a customer who calls ahead, an unmodelled constraint. 5. Close the loop with follow-up. Error by SKU, stockouts, days of cover and excess inventory, reviewed in the same place as every other indicator. On building that panel without the obligatory forty metrics, we wrote an executive dashboard guide.

Point 4 is the most underrated. Forecasts improve mainly through process learning, not by swapping algorithms. And it is worth accepting from the start that sales will keep holding information the history does not have: a tender won, a customer closing down, a project pushed back. The goal is not to replace that knowledge — it is to stop rebuilding it by hand every month for the 1,800 SKUs nobody looks at.

Where do you start without launching a year-long project?

A 90-day start that holds up:

• Weeks 1-3. Extract the history, flag stockouts and clean the item master. Calculate the naive model's error: that is the baseline to beat.
• Weeks 4-6. Segment the catalogue by volume and variability. Pick a method per segment and validate on recent months, at the real decision horizon.
• Weeks 7-10. Connect to the order proposal for a limited set of SKUs, with purchasing constraints loaded. Purchasing works from the proposal but can override it freely.
• Weeks 11-13. Review error, manual overrides and stockouts. Decide what to extend, what to adjust and which method needs upgrading.

When any of this stalls, it is almost always the data and not the model — the same pattern we see again and again in predictive analytics. A project that starts without resolving the item master and the stockouts ends up arguing about whether the algorithm is the right one when the problem sits three layers below.

Frequently asked questions

How much history do I need to forecast demand?

With seasonality, two full years at SKU and week level. One year can work, but the model will not be able to tell an annual pattern from a one-off anomaly. Below a year, stick to simple methods and lean on documented expert judgement.

What forecast error is normal?

It depends on the product: high-rotation, stable items tolerate fairly low error, while intermittent demand runs high by nature. The useful reference is not the absolute percentage but how much you improve on simply forecasting the same as the previous period.

Can AI forecast demand for new products?

Not directly, for a launch with no history. What works is leaning on analogous products, on the introduction curve of previous launches and on sales judgement, then switching to the model as soon as there are enough real weeks of data.

Can I do demand forecasting in a spreadsheet?

For a short, stable catalogue, yes — a well-built moving average with seasonality solves more than people expect. The limit arrives with SKU volume, traceability of changes and the automatic link to purchasing, which is where a spreadsheet stops being maintainable.

Demand forecasting is one of the few cases where the return shows up on the balance sheet — fewer stockouts, less cash locked in inventory — provided the number reaches the person who signs the orders. If you want to know whether your history supports this before investing in anything, in the audit we review your sales, stock and master data and tell you what can be forecast today and what needs fixing first. And if you would rather talk it through in half an hour, let's talk.