Demand forecasting: methods, forecast error and a calculator

What demand forecasting is and what it is for

A demand forecast is an estimate, by item and by period, of what customers will order. Many decisions hang on that number: how much to buy from suppliers and when, how much stock to hold, what goes into the production plan and how much cash will sit in the warehouse, the DIO of the cash conversion cycle.

Demand and sales are not the same thing. Sales are the demand you managed to serve: when an item is out of stock, the customer gives up or buys elsewhere, and sales drop even though the demand was there. A good forecast starts from demand, not just from what was sold.

The data you need

The data matters more than the method. A few sources are usually enough, as long as they are clean and at the right grain:

• Sales history by item and period, ideally by day or week, going back at least two years if there is seasonality.
• Promotions and prices: a promoted week sells more and the week after often less; if it is not flagged, the model mistakes it for normal demand.
• Calendar: public holidays, long weekends, shutdowns, working days in the month.
• Stock levels and stock-outs: days with zero stock hide demand, and need to be recognised so the forecast does not predict an empty shelf.
• Known events: new customers, items replacing others, price list changes.

Forecasting methods, starting from the simplest

There is no need to start with the most complex method. Each one earns its place when it beats a simpler one on the same history; otherwise it only adds work.

What level to forecast at

Forecast at the grain of the decision the forecast supports. Purchasing usually works by item and week, production by product family and week, the budget by category and month. Forecasting by day when you order once a week adds noise without adding information.

The more you aggregate, the more errors cancel out: a family is easier to forecast than its items, a month easier than a week. You can forecast at the top and split by mix, or forecast each item and add them up.

The horizon matters too. For purchasing, the forecast that counts is the one made one lead time earlier: if the supplier delivers in three weeks, the error to measure is that of the forecast made three weeks before, not the one updated yesterday.

A band, not just a number

A single number hides how uncertain it is. A useful forecast also gives the range sales will fall into with a given probability: for example 115 units next week, with an 80% band between 95 and 135. That means sales should land between those two values in roughly eight weeks out of ten.

A band is checked the same way as the forecast: if an 80% band contains actual sales only six times out of ten, it is too narrow. And it is the band, more than the central value, that sizes safety stock, as the guide to stock coverage explains.

Measuring forecast error: MAE, MAPE, WAPE and bias

Four measures cover almost every case. The first three tell you how far off the forecast is, the fourth in which direction.

MAE is in units and easy to read for one item, but it cannot be compared across items with different volumes. MAPE is a percentage, but it weighs every period equally. WAPE divides total error by total sales, so it weighs more where more is sold. Bias tells you whether the forecast runs consistently above (positive) or below (negative) sales; some teams use the opposite sign, and what matters is sticking to one convention.

Example: four weeks of one item

An item sold 120, 95, 140 and 110 units over four weeks, 465 in total. The forecast made before each week said 110, 100, 125 and 118 units, 453 in total. The example is fictional.

That gives the four measures: MAE = 38 ÷ 4 = 9.50 units; MAPE = (8.33% + 5.26% + 10.71% + 7.27%) ÷ 4 = 7.9%; WAPE = 38 ÷ 465 = 8.2%; bias = (453 − 465) ÷ 465 = −2.6%, so the forecast ran slightly below sales on average. For an item with steady volumes MAPE and WAPE are close; they drift apart when volumes vary a lot.

To know whether the forecast is worth anything, you need a comparison. Over weeks 2–4 the naive forecast, last week's sales (120, 95, 140), misses by 25, 45 and 30 units: 100 out of 345 sold, a WAPE of 29.0%. The example's forecast, over the same three weeks, misses by 28 units, 8.1%. The gain over the simplest method is the measure of the work that went into it.

Why MAPE misleads on low-volume items

MAPE divides each error by that period's sales. On an item that sells only a few units, a small miss becomes a huge percentage and counts in the average as much as a miss on an item that drives revenue.

Two units too many on item B make the forecast look wrong by more than half. MAPE has two further limits: it is undefined when a period sells nothing, and it penalises forecasting high more than forecasting low, because below sales the error cannot exceed 100% while above it has no ceiling.

That is why portfolios with many slow movers usually rely on WAPE, in units or in euros, read by item class (A, B and C by revenue) so it does not hide errors on the small ones.

Demand forecasting in Excel, and where it stops

For a few dozen items Excel can be enough. The FORECAST.ETS function applies exponential smoothing with seasonality, and Forecast Sheet, on the Data tab, plots the forecast with its confidence interval.

The limits are rarely in the formulas and mostly in the work around them: the ERP export to redo every week, promotions to line up by hand, last week's forecasts overwritten and so no longer comparable with actual sales. Once items run into the hundreds, the forecast needs to refresh on its own, or several people need to use it, it usually makes sense to move it to where the data already lands.

Common mistakes

• Forecasting sales instead of demand: weeks out of stock teach the model that the item sells little, and the forecast brings the stock-out back.
• Not measuring bias: a small error that always leans the same way builds up excess stock or shortages week after week.
• No baseline to beat: without comparing against the naive forecast, you cannot tell whether the method adds anything.
• Not keeping past forecasts: if only the latest is kept, the error at the horizon that matters cannot be calculated.
• Unmeasured manual overrides: adjustments from sales can help, but they need to be compared with the forecast they replaced.

Demand forecasting in Muvia

In Muvia, sales and stock arrive from the ERP every night and become tables, features are prepared in SQL, and the forecast runs every night in a scheduled flow: it can be the model your data scientist built, or a forecast predictor that estimates the trend with its band. Forecasts are kept in a table, night after night, so forecast and sales sit on the same dashboard and the error is one query away. The full path is in the demand and stock forecasting use case; days of cover and the low-stock alarm are in the stock coverage one.

Calculator

Enter four weeks of sales and forecast: the calculator returns MAE, MAPE, WAPE and bias. The starting values are the ones in the example.