MedFlow Pharma · Demand-Planning Case Study
A distribution network with a healthy fill rate… and a quarter-billion-peso blind spot.
global fill rate across 5 distribution centers — every dashboard called this healthy.
in lost sales over 18 months — hiding underneath that healthy average. This is the end-to-end analysis that found it.
All data, figures, and the client are synthetic — a realistic supply-chain engagement built to demonstrate method.
02/The network
A national distributor, mapped end to end.
MedFlow Pharma moves medicine across Mexico from five distribution centers to roughly 200 pharmacy and hospital accounts. The catalog spans 61 active SKUs across six therapeutic classes — two of them, vaccines and insulin, cold-chain. The analysis covers 18 months of daily demand.
5 CEDIS · stylized national footprint
03/The diagnosis
One number was doing all the lying.
A 95.7% fill rate averages a catalog whose value is anything but average. Count fill rate in units and a missed box of analgesics weighs the same as a missed course of oncology therapy. Weigh it in pesos instead, and the loss collapses onto a single class.
Lost sales by therapeutic class
Few units, enormous value each — oncology is 83% of all lost value. Cold-chain classes (vaccine, insulin) follow.
Fill rate vs. lost value, by distribution center
Every DC reports the same reassuring fill rate (~95.6–95.8%). The value leaking out of each is anything but the same. That tells us the problem isn’t a bad site — it’s a uniform policy applied to a non-uniform catalog.
The average wasn’t wrong. It was just hiding where the money was.
04/The data reality
Before you can plan demand, you have to trust it.
The history arrived as five separate feeds — about 167,000 raw rows that disagreed with each other. The planning team had never once held a single clean version of demand. So the first deliverable wasn’t a forecast; it was a feed you could trust, with every correction on the record.
- Duplicate order lines~3,200WMS double-scanDe-duplicated
- Mixed-format dates~6,500One DC's manual entryParsed to ISO
- Non-positive quantities118Returns mis-keyed as ordersQuarantined with reason
- Orphan SKUs200Decommissioned codes, not in product masterQuarantined, flagged
- Blank unit costs3 SKUsExport gapImputed from class median, flagged
The principle: every rejected record is logged with a reason and kept in quarantine — nothing is silently dropped. That audit trail is what turns a number into something a planner will actually stake a decision on. What survived: 162,154 clean order lines.
What 18 months of clean demand revealed
A steady base with a pronounced winter respiratory-season lift — and unmet demand that widens exactly when demand peaks, where a flat policy strains hardest.
Indexed units — an illustrative shape of the seasonal pattern, not a peso figure.
05/The approach
From five messy feeds to one decision.
The system is a straight line: pull every source together, make it trustworthy, model it into a shape a planner can query, then turn that into a daily decision. Five stages, each handing clean work to the next.
- 01
Extract
5 source feeds pulled into one staging layer.
- 02
Transform
Clean, dedupe, quarantine, audit — every reject logged with a reason.
- 03
Load
Star schema (fact_demand + product / DC / date / lead-time dims) in a columnar store.
- 04
Plan
Demand forecast + statistical safety stock, per SKU-DC.
- 05
Decide
The decision cockpit — reorder points, flags, value at risk.
Forecast the demand, don’t chase it.
Reactive reordering always reacts a step late — it restocks after the shelf is already empty, and in a seasonal catalog that lag is exactly when it costs the most. Planning against a forecast lets stock arrive before the demand does, so the buffer is sized for what’s coming, not what already happened.
Protect by value, not by habit.
One uniform service target spreads capital evenly across a catalog whose value is wildly uneven. Instead, the target is segmented — so protection concentrates where value-at-risk and clinical criticality are highest.
High value-at-risk · cold-chain · oncology
Where capital should concentrate.
Mid value, steady rotation
Protected, not gold-plated.
Low value, high count
Held lean by design.
06/The method
Precise where it pays, honest where it doesn’t.
Demand is forecast with a gradient-boosted model on calendar, lag, rolling, and seasonal features, backtested over a 28-day holdout against a naive “same weekday last week” baseline using WMAPE. It wins most of the time — and where it doesn’t, it says so and steps aside.
Forecast vs. baseline, every SKU
Each column is one SKU’s accuracy change. Above the line, the model wins; below it, the baseline does — and the model falls back to it rather than forcing a worse answer.
Where the model loses, it’s on very low-rotation, noise-dominated SKUs — the cases where no model beats “last week.” Distribution shaped to the published 79% win rate, shown for the honesty of it.
Safety stock that respects two kinds of uncertainty
A forecast sets the center; safety stock sets the buffer. It has to absorb variability in both demand and lead time — a stockout doesn’t care which one moved.
SS = Z · √( LT · σ_d² + d² · σ_LT² )ROP = d · LT + SS- Z
- service-level safety factor, set per segment
- d
- mean daily demand
- LT
- mean lead time
- σ_d
- demand variability
- σ_LT
- lead-time variability
The safety factor Z is the one dial set per segment — 99/97/95 — which is exactly how the service-level decision turns into stock on a shelf.
07/The impact
What disciplined segmentation is worth.
Of the $227.8M leaking out, most is recoverable — not by buying more of everything, but by buying the right things to the right level. Net of the inventory it takes to get there, here is the annual case.
Net annual benefit
$167.7M recoverable, less $34.1M of incremental carrying cost — 73.6% of the current loss, turned back into supply.
- $145.5MOncology
- $22.2MAll other classes
- of $167.7M recoverable
Nearly all of it comes from one class.
Oncology alone accounts for $145.5M of the $167.7M recoverable — about 87%. That concentration is the proof: the return isn’t blanket inventory, it’s capital aimed where value-at-risk actually lives.
161 of 305 SKU-DC pairs are already below their reorder point — flagged, ranked by value at risk, ready to act on.
08/Go deeper
This page is the story. The repo is the build.
Everything underneath the narrative — the Python ETL, the cleaning and quarantine logic, the star-schema build, the SQL, the forecast and safety-stock models — lives in the repository. Open it to read the actual code that produced every number on this page.
The full written deliverables