This case study is anonymised. Figures are left out and the process is simplified to protect confidential information.
The business problem
Finished goods came out of several breweries and had to be deployed to depots and distributors across the country. When deployment went wrong, one location ran short while another held too much of the same product. That meant lost sales in one place and ageing stock, extra handling and transfer costs in another.
Current state (as-is)
As-is deployment cycle
Root causes
- Data latency: decisions were based on a snapshot that was already out of date by the time allocation happened.
- No shared allocation rules: priorities varied by planner and by week, so outcomes were hard to repeat or explain.
- Everything reviewed at once: planners spent time checking every SKU and location instead of focusing on the exceptions.
- Siloed forecasts: sales forecasts and production plans weren't reconciled before deployment.
Future state (to-be)
To-be deployment cycle
Business rules (sample)
| ID | Rule |
|---|---|
| BR-01 | Each location has a target days-of-cover band per SKU class (fast / medium / slow movers). |
| BR-02 | When supply is short, allocate in proportion to forecast demand, with a minimum floor for priority customers. |
| BR-03 | Suggest an inter-depot transfer only when projected excess at one site covers a projected shortfall at another within the transfer lead time. |
| BR-04 | A location/SKU is flagged as an exception when projected cover falls outside its band. |
KPIs to measure success
- Service level / fill rate by location
- Stock imbalance index (the spread of days-of-cover across locations)
- Number and cost of reactive inter-depot transfers
- Share of planner time spent on exceptions rather than routine checks
Turning the rules into a working planner
To show the to-be process working, I built a rule-based Excel planner on synthetic data (5 depots, 6 SKUs) that applies BR-01 to BR-04 with formulas, and a Python test that checks the workbook against the rules.
- Rows planners need to review fell from 17 to 4 of 30.
- The spread of days of cover across depots fell from 5.2 to 3.5 days.
- It surfaced a question for stakeholders: allocating in proportion to forecast does not equalise cover between depots.
What this shows as a BA
- Mapping a real end-to-end process and pinpointing where value leaks
- Separating symptoms (shortages) from root causes (data, rules, focus)
- Writing clear business rules that a system or a new team member could follow
- Defining KPIs so improvement can be measured, not just claimed
Reflection
The biggest lesson was that the tooling problem was really an alignment problem. Rules only work if sales, logistics and planning agree on them. Next time I'd run a short workshop to agree the rules before building anything.