Reordering from memory goes like this: someone walks the shelves, remembers what sold last season and places the order. It works until the catalog grows past what one person can hold in their head.
A forecast replaces memory with arithmetic. This guide works one product through from last year’s sales to next month’s order. The numbers are made up so you can follow the math, and you can run the same steps in a spreadsheet.
What is inventory forecasting?
It is estimating how much of each product you will sell in a coming period, so you can order before you run out and without overbuying. A forecast starts from past sales and is adjusted for growth and seasons.
It answers two questions for every product: when to order, and how much.
What do you need before you start?
Four numbers for each product, which your ERP, your accounting system or your own spreadsheets usually hold:
- Sales by month for at least the past year.
- Stock on hand today, and anything already on order.
- Lead time: how long the vendor takes from order to delivery.
- Pack size: the smallest quantity the vendor will ship.
How do you forecast one product, step by step?
Take a product, here a medium kraft bag sold by the case, and plan the order for November. The steps use the min-max method: a reorder point (the minimum) and an order-up-to level (the maximum).
| Step | What you do | The math |
|---|---|---|
| 1 | Find last year’s sales for the same month | Medium kraft bag, November last year: 520 cases |
| 2 | Measure this year’s growth: the last three months against the same three last year | 1,150 cases against 1,000, so 1,150 ÷ 1,000 = 1.15 |
| 3 | Forecast the month | 520 × 1.15 = 598, call it 600 cases |
| 4 | Turn it into a daily rate | 600 ÷ 30 = 20 cases a day |
| 5 | Work out what sells while you wait for the vendor (a ten-day lead time) | 20 × 10 = 200 cases |
| 6 | Add safety stock: your busiest day (28 cases) × the longest the vendor has taken (fourteen days), minus an average day × the usual lead time | (28 × 14) − (20 × 10) = 192 cases |
| 7 | Set the reorder point: lead time demand plus safety stock | 200 + 192 = 392 cases |
| 8 | Set the order-up-to level: the reorder point plus one month of forecast | 392 + 600 = 992 cases |
| 9 | Size the order: the order-up-to level, minus the 250 cases on hand and anything already on order (none here) | 992 − 250 − 0 = 742, rounded up to full pallets of 50 = 750 cases |
The result is two numbers you can act on. Order 750 cases now, because the 250 on hand is already below the reorder point. Order again whenever stock on hand plus stock on order falls to 392.
What is the reorder point formula?
Reorder point = average daily sales × lead time in days + safety stock. In the example that is 20 × 10 + 192 = 392 cases. When stock on hand plus stock on order falls to that number, it is time to order.
What is the safety stock formula?
A simple one is the busiest day’s sales × the longest lead time, minus an average day’s sales × the usual lead time. In the example, 28 × 14 − 20 × 10 = 192 cases.
It covers the case where sales run hot and the vendor runs late at the same time. It is a cautious formula, so a product with steady sales and a reliable vendor can carry less.
Why start from the same month last year?
Because last month can mislead you. A product that sells twice as much in December will look like it is taking off if you compare December with November, and like it is dying when you compare January with December.
Comparing against the same month a year ago removes the season. The growth figure in step 2 then carries this year’s trend. For one distributor we built for, a quarter of the year’s revenue lands in one holiday season, so a forecast that ignored the calendar would be wrong when it mattered most.
Adjust by hand for anything the history cannot know: a large customer you won or lost this year, a price change, a promotion.
Which forecasting method should you use?
The simplest one that explains your sales. Three cover most small catalogs.
- Same month last year, times growth. The method above. It carries the season, so it suits products with a busy time of year.
- A moving average. The mean of the last three months. It suits products with no season.
- A seasonal index. Each month’s sales divided by the average month for the year. Apply that ratio to this year’s level when the season is strong and you have two or more years of history.
What about new products and promotions?
History cannot forecast a product that has none. For a new product, borrow the sales pattern of the most similar product you already carry, and check it after the first month.
For a promotion, take the promotion’s sales out of the history before you forecast the same month next year. If you leave them in, the forecast expects the promotion again.
How do you check a forecast’s accuracy?
Compare it with what sold. At the end of each month, take the gap between the forecast and actual sales and divide it by actual sales. A product forecast at 600 cases that sold 540 was off by 60 ÷ 540, about a ninth.
Track that number for each product. If the forecast is wrong in the same direction every month, adjust the growth figure. If it swings both ways, carry more safety stock.
How do you find slow-moving inventory?
Divide the stock on hand by average monthly sales. The answer is how many months that stock will last.
Say a large foam tray has 31 cases on the shelf and sells 2 cases a month. 31 ÷ 2 = 15.5, so there is more than a year of stock sitting there. Run that for every product and sort the list from the highest number down.
The top of that list is cash you cannot spend. It can get there in a plain way: someone reordered the product because it had always been reordered.
How do you reduce slow-moving inventory?
Stop feeding it first, then clear what is there.
- Take slow lines off the automatic reorder. This costs nothing and stops the problem from growing.
- Order smaller quantities more often for products that sell but slowly, even at a slightly higher unit cost.
- Clear the shelf. Discount it, bundle it with a fast seller, or ask the vendor about a return.
- Check the list every month. A slow line this year may have been a best seller two years ago.
When does a spreadsheet stop being enough?
When the number of products times the number of vendors is more than a person can review. The math above takes a few minutes for one product, and it has to be redone every time sales or stock change.
Airu Labs builds custom AI systems for small and mid-size companies, wired into the software they already use. One of its clients, a paper and packaging distributor, carries about 3,150 products from 280 vendors. Every reorder decision lived in the owner’s head.
What does an AI purchasing system add?
It does the looking-up for every product every night, and it ranks the result.
The purchasing system we built for that distributor reads sales and stock from the ERP each night, and the owner starts each morning with a buy list to approve. How the list is ranked, and what happens after he approves it, is on our page about purchase order automation.
Four out of every ten lines they had been reordering out of habit turned out to be money sitting still. Stopping those orders put about $22,000 a year back into products that sell, by the system’s own records.
What are the common mistakes?
Four come up again and again:
- Forecasting from last month, with no allowance for the season.
- Leaving out lead time, so the order arrives after the shelf is empty.
- Using one safety margin for every product, fast or slow.
- Building the forecast once and never checking it against what sold.
Start with your twenty best sellers and run the nine steps. Before you hire anyone to build a system for it, read the ten questions to ask an AI developer. If the math is sound and the hours are the problem, book a 30-minute call and we look at whether a system should run it for you.