Forecasting demand with a moving average

At the start of every month the same question arises: "How much do we order this month?" You do not need sophisticated software to answer it. The average sales of the last few months can serve as the forecast for the next one. This method is called a moving average: it "slides" along month by month, and when a new month is added the oldest drops out of the calculation.

How it is calculated

Forecast = the average of sales over the last N periods.

Example: a smartphone case sold through Uzum and a Telegram shop. Monthly sales (units): January 120, February 126, March 118, April 130, May 122, June 128. In July the item joined an advertising campaign on Uzum and sales jumped: July 150, August 158, September 154.

The three-month forecast for October: (150 + 158 + 154) ÷ 3 = 462 ÷ 3 = 154 units. Any month is calculated the same way: for July we take April, May and June, that is (130 + 122 + 128) ÷ 3 ≈ 126.7.

How to choose the window length

The July jump shows both sides of the method. Forecasts from the three-month average: July 126.7 (actual 150, error 23.3), August 133.3 (158, error 24.7), September 145.3 (154, error 8.7). The mean error is 18.9 units. From the six-month average: July 124 (error 26), August 129 (error 29), September 134.3 (error 19.7), and the mean error is 24.9 units.

A short window reaches the new level faster; a long one gives a smooth result but lags. In the calm months (April-June) the three-month forecast was out by 8.7, 2.7 and 4.7 units, barely noticeable. For monthly data people usually take 3-4 months, for weekly data 4-8 weeks.

Do not guess the best window; test it on history: calculate the mean error over past months for different values of N and pick the smallest.

In Excel

Suppose column B holds monthly sales. The forecast for the next month is AVERAGE(B2:B4), dragged down. The error is ABS(actual − forecast). A forecast always refers to the following month: the actual figure in B5 is compared with the forecast made from B2:B4. The separator (comma or semicolon) depends on your Excel settings.

You can also give newer months more weight: 0.5, 0.3 and 0.2. For October, 0.5 × 154 + 0.3 × 158 + 0.2 × 150 = 154.4. In this example the difference is invisible, but for items whose sales change quickly the weighted version reacts sooner.

Daily demand and safety stock

Turn the monthly forecast into daily sales for the reorder point: 154 ÷ 30 ≈ 5.1 units per day. With a 10-day lead time, demand over that period is 5.1 × 10 = 51 units; safety stock is added to it to get the point.

The forecast error also says something about safety stock. If delivery takes about a month and the three-month forecast misses by 19 units on average, do not set the safety stock far below that figure: otherwise ordinary fluctuations alone will empty the shelf. For an exact calculation use the safety stock formula.

Limitations

• If growth or decline continues, the average always lags. In September the forecast was 145.3 against an actual 154.
• For seasonal items the average loses the wave. Supplement it with a seasonal coefficient.
• Months when the item ran out show stock, not demand: real demand was higher. Mark such months.
• One large order lifts the average artificially. Record it separately and keep it out of the forecast.
• A promotion month lifts the average, and the month after usually dips because customers have already stocked up. Mark promotion months and look at them separately.
• A new item has no history: until 3-4 periods accumulate, lean on a similar product.

Conclusion

The moving average is simple, understandable and enough for many items, but it works only with trustworthy sales data. If balances are inaccurate, days without stock enter the data as zero sales and the forecast drifts down. CountLab verifies balances through stock counts and, with ABC/XYZ analysis, helps identify which items need a more refined forecast.