In this guide 6 sections
Build forecasting when you hold two clean years of invoice lines per item and branch, when supplier lead times make a wrong order costly, and when demand follows seasons. Otherwise use min and max reorder levels. They are cheaper. They are easier to trust.
Check the sales history before anything else
A forecast learns from invoice lines, so export them first, take one fast item and one slow item from a single branch, and read every row. Look for gaps, duplicate SKUs and months where a price list change renamed the product, because each of those breaks the run of history the model needs. Returns are the usual trap.
When credit notes sit in a separate register, the model sees sales that were later reversed and learns demand that never really existed. Stockouts bite too.
A week of zero sales may mean an empty shelf, not a quiet week, so mark those weeks before any training run starts. Count the items with a usable run. A long tail that sells twice a year will never forecast well, so leave it to reorder levels and spend the effort on items that sell every week.
- Invoice lines with item code, branch, date and quantity
- Credit notes and returns linked to the original invoice
- Stockout days flagged against each item
- Merged or renamed SKUs mapped to one code
- Bulk tender or project orders tagged separately
Does your lead time give a forecast room to work?
Forecasts earn their keep where orders go out long before the stock is needed, and a ten week sea shipment punishes a bad guess. A wholesaler delivering overnight forgives it. Write down the lead time per supplier.
Then write down how far it swings. A supplier who delivers in two weeks one month and six weeks the next forces you to carry safety stock, and no forecast can remove that need.
When plain reorder levels win
Min and max levels set per item and branch beat a model more often than vendors admit, because a storekeeper can follow them without any training. They fail in plain sight.
A purchase head can correct a bad level in a minute, without asking anyone to retrain a model or rebuild a data table first. Stay with levels when demand is steady, the active range is small and deliveries arrive within days, then review the numbers each quarter. Forecast only the failures.
Try an exception report first. A weekly list of items below minimum, items with no sale in ninety days and items selling at double their usual rate often delivers most of the value.
- Steady demand with little seasonality
- Short and reliable supplier lead times
- A small active range per branch
- Orders placed by one buyer who knows the items
What a forecast must state
A forecast is a set of assumptions with a number attached, so ask for them in writing, starting with the history window the model used. How were festival weeks treated? Which items were dropped for thin history, and which promotions, price changes or stockout days were cleaned out of the figures before training?
New items have no history. Neither do items moving between branches.
The model should say so and fall back to a plain reorder rule rather than invent a figure for an item it has never seen sell. Insist on a backtest. Hide three months of sales, forecast them, compare against what sold, and ask for the error by item group, because one average across items hides the slow movers.
- History window and cleaning rules
- Treatment of promotions and stockout days
- Items excluded and the reason
- Backtest error by item group and branch
Start narrow and compare against your buyers
Pick one supplier and one branch. Run the forecast beside your buyer for two ordering cycles, recording what the buyer ordered, what the model suggested and what actually sold in the weeks that followed. Judge the result on stock held and orders missed, never on how clever the method sounds, and if the buyer wins, you saved the cost of a build.
If the model wins on long lead time items, extend it one supplier at a time, and keep the buyer’s override for new items. This week, take your top twenty items by purchase value and run the history check from the first section, then mark which ones fail.
Forecast readiness worksheet
Print this on one sheet with a line per supplier and columns for item count, years of clean history, lead time, lead time spread and stockout gaps. Two more columns name the current reorder method and a verdict of levels, forecast or review, and a second block lists the assumptions a forecast must state.
Open a blank worksheet to printQuestions owners ask
How much sales history does a distributor need for forecasting?
Two full years per item is a sensible floor, because the model needs to see each season twice and learn which swings repeat and which were one-off events. Younger items stay on levels.
Can we forecast across branches at once?
You can, but branch demand often differs. Forecast at branch level first, then roll up for supplier orders, and separate transfers between branches from real sales so the same carton is not counted twice.
What if our data sits in several systems?
Bring invoice lines into one table first. Mismatched item codes between the billing tool and the warehouse sheet cause more forecast errors than the model itself, so map each code to one master first.
How do we know the forecast is working?
Compare suggested orders and actual orders against what sold over two cycles, and track stockout days and slow stock together, since improving one quietly worsens the other.