Weekly demand forecasting for a regional food distributor

Context

The client distributes fresh, chilled and ambient groceries to around 300 independent retailers and restaurants across one region. Their purchasing team of three places supplier orders twice a week, covering roughly 2,400 active product lines.

Ordering decisions were made in a large shared spreadsheet. Each line had a rolling four-week average of sales, and buyers adjusted it by hand using experience: a bit more before bank holidays, a bit less when a promotion ended. It worked well enough while the business was small. As the range grew, the spreadsheet became slow, fragile and dependent on the two people who understood its formulas.

The challenge

Two problems kept coming up in the weekly operations meeting.

• Stock-outs on fast movers. Popular lines ran out every few weeks, usually after a promotion or a warm spell. Customers then ordered elsewhere, and some didn't come back.
• Write-offs on perishables. Short-life products such as salads, soft fruit and dairy were over-ordered "to be safe" and regularly written off.

The finance team estimated that waste and lost sales together were costing several percentage points of margin. The operations director wanted to know whether a forecasting model could do better than the spreadsheet, without asking the buyers to learn a new tool.

Our approach

Starting with the data, not the model

We spent the first two weeks on data. Three years of order lines came from the client's ERP, promotions lived in a separate marketing calendar, and product attributes (category, shelf life, supplier lead time) were spread across two systems.

Most of the effort went into cleaning:

• Separating genuine zero-sales weeks from weeks when a product was simply out of stock, which would otherwise teach the model that demand had dropped.
• Joining promotions to the right product lines, including multi-buy deals that applied to whole categories.
• Building a calendar of bank holidays, school holidays and local events that visibly moved demand.

Setting an honest baseline

Before building anything, we reproduced the spreadsheet method in code. That gave us a baseline to beat and a fair way to compare. Every model we tried was evaluated with a rolling-origin backtest: train on data up to a given week, forecast the following weeks, move forward and repeat. This mirrors how forecasts are used in practice and avoids flattering results from random train/test splits.

Choosing the model

We compared three approaches:

1. Classical per-product time-series models.
2. A single global gradient-boosted model trained across all products.
3. A hybrid that falls back to simpler methods for products with very little history.

The global LightGBM model won clearly on fast-moving and promotional lines, because it could learn shared patterns such as "this category responds strongly to multi-buys" across hundreds of products. For new or very slow lines we kept a simple seasonal baseline, which proved more stable than any model trained on a handful of data points.

Forecasts people can act on

A single number isn't much use for a perishable product. We produced a range for each line (a low, expected and high forecast) and worked with the buyers to turn that into an order suggestion that accounts for shelf life and supplier lead time. Perishables are ordered closer to the expected value, long-life products closer to the high end.

How it works

The production setup is deliberately simple:

• A scheduled job runs every Sunday night in a Docker container on the client's existing cloud server.
• It pulls the latest sales and promotions from the ERP database with SQL, rebuilds features and produces forecasts for the next four weeks.
• Results are written into a new tab of the buyers' existing ordering spreadsheet, alongside the old average, so they can compare before committing.
• A short data-quality report flags anything unusual, such as missing sales for a supplier or a promotion with no matching products.

No new interface, no new logins. The buyers open the same sheet on Monday morning and see a better number next to the one they're used to.

Results

Across a 12-week backtest covering a summer period and two bank holidays, the model's forecast error was 31% lower than the spreadsheet baseline. The improvement was largest on promoted lines and short-life products, which were exactly the categories causing the most pain.

After eight weeks of live use, the operations team reported fewer emergency top-up orders and noticeably less perishable waste. The buyers now spend their time reviewing exceptions the model flags, rather than adjusting every line by hand, which saves around four hours a week.

What we delivered

• A forecasting pipeline with documented assumptions, features and known limitations.
• A scheduled batch job that writes forecasts and order suggestions into the existing ordering sheet.
• A backtest report comparing the model against the previous method, product category by category.
• A runbook covering how to retrain, how to add a new product category and what to do if the job fails.
• A handover session with the buyers and the client's IT contact.

What we learned

The model was the easy part. The biggest gains came from separating out-of-stock weeks from genuine low demand, and from putting the forecast exactly where the buyers already worked. A technically better model delivered in a new dashboard would probably have been ignored.