Inventory forecasting
Last reviewed
Inventory forecasting estimates how much of each product you will sell before your next order can arrive, so you can buy enough to cover it.
What inventory forecasting is
Inventory forecasting is estimating how many units of each product you will sell, so you buy enough to avoid stockouts without tying up cash in stock that sits. The forecast is an input. The output you need is an order: which products, how many, from which supplier, by when. Turning one into the other is inventory planning.
The inputs
A useful forecast uses six inputs for every product:
- Sales history. Units sold per day, per channel, over a recent window.
- Out-of-stock days. Days with no stock are days with no sales, which says nothing about demand.
- Seasonality. Peaks and quiet months that repeat each year.
- Lead time. How long an order takes from placing it to having it on the shelf. See the lead time calculator.
- Stock on hand. What you have now, across every location.
- Stock on order. Open purchase orders and inbound shipments, so you don’t buy the same units twice.
A spreadsheet method
You can do this in Excel or Google Sheets for each product.
- Count in-stock days in your window (for example the last 28 days).
- Divide units sold by in-stock days to get daily sales speed.
- Apply a seasonal factor. If this period usually sells 20% more than your window did, multiply by 1.2. If you have no history, use 1.
- Set your cover. Cover is lead time plus the days between your orders. Add a few days of safety stock.
- Work out the need: daily speed × cover days.
- Subtract stock on hand and stock on order. The result is what to buy. If it’s zero or negative, buy nothing.
Worked example
One product sold 168 units in the last 28 days, but it was out of stock for 8 of those days.
- In-stock days: 28 − 8 = 20
- Daily speed: 168 ÷ 20 = 8.4 units a day
- Seasonal factor 1.2: 8.4 × 1.2 = 10.08 units a day
- Cover: lead time 42 days + 14 days between orders = 56 days
- Demand over cover: 10.08 × 56 = 564.48
- Safety stock, 7 days: 10.08 × 7 = 70.56
- Total need: 564.48 + 70.56 = 635.04, rounded up to 636 units
- Stock on hand 300 + on order 150 = 450
- Buy: 636 − 450 = 186 units
Now do the same sum the quick way: 168 ÷ 28 = 6 units a day, no seasonal factor, 56 days plus 7 safety days: 6 × 63 = 378. That is less than the 450 you already hold, so the answer is “buy nothing”. The product stocks out again.
Where spreadsheets break
- Stockout days get averaged in. The example above shows it: the quick average understates demand by about 29% (6 against 8.4). Every stockout makes the next forecast lower, which causes the next stockout.
- Quoted lead time is not real lead time. A supplier quote of 35 days is the factory’s number. Your number runs from placing the order to stock on the shelf. Measure it from past orders.
- Kits and bundles. A bundle sells components that also sell alone. Forecast the bundle on its own and you double count or miss demand. Components need their own demand, including what leaves inside bundles.
- Many channels and locations. One product may sell on a store, a marketplace and a wholesale account, and sit in several warehouses. The sheet needs one row per product that adds them correctly.
- Maintenance. Someone has to refresh the data and fix broken formulas each week.
How to measure accuracy
Keep the forecast, then compare it with what actually sold.
Example: you forecast 70 units a week for four weeks and sold 63, 77, 84 and 56.
- Absolute errors: 7, 7, 14, 14 = 42
- Actual sales: 63 + 77 + 84 + 56 = 280
- WAPE: 42 ÷ 280 = 15%
- Bias: forecast 280 minus actual 280 = 0
Track both numbers. WAPE says how far off you are. Bias says whether you are consistently high (too much stock) or low (stockouts). Judge accuracy over the lead time you order against, not just next week. Measure it per product group, because fast sellers and slow sellers behave differently.
Related tools
- Reorder point calculator
- Lead time calculator
- Safety stock calculator
- Inventory turnover calculator
- Next step: inventory replenishment methods
Where Kerno fits
Kerno Forecast is in early access. It reads sales, stock, stock on order and lead times from the systems you already use, leaves out-of-stock days out of sales speed, and shows the reasons behind every quantity. Approving the buy list drafts the purchase orders, which you review and send.
Frequently asked questions
What is inventory forecasting?
Inventory forecasting estimates how many units of each product you will sell over a future period, so you can decide how much to buy and when. It turns sales history, stock levels and supplier lead times into an order quantity.
How do you forecast demand for a product?
Start with units sold per day over the last 4 to 12 weeks, leaving out days the product was out of stock. Adjust for seasonality, multiply by the days you need to cover (lead time plus your reorder cycle), then subtract stock on hand and stock already on order.
Can you do inventory forecasting in Excel?
Yes. A spreadsheet can hold daily sales, divide by in-stock days, apply a seasonal factor and subtract stock and open orders. It works for a few dozen products. It gets hard to maintain when you sell on several channels, run kits, or have many suppliers.
How do you measure forecast accuracy?
Compare forecast with actual sales over the same period. Weighted absolute percentage error (WAPE) is the sum of the absolute errors divided by total actual sales. Also track bias, which is whether you consistently forecast too high or too low.
What is the difference between inventory forecasting and inventory planning?
Forecasting estimates demand. Inventory planning uses that estimate, plus lead times, stock on hand and stock on order, to decide what to order, how much and when.