This demand forecasting case study starts with a contradiction that surprised the client before it surprised me: a pharmacy chain with eight branches was simultaneously running out of common medications and throwing away boxes of expired stock, sometimes in the same week, sometimes for the same product at different branches. The root cause was not poor staff, it was that every branch manager was ordering by instinct, and instinct does not scale across eight locations with different customer bases.
I want to walk through what we found, what we built, and the part of this problem that is specific to pharmacy retail and rarely discussed in generic inventory case studies: expiry dates turn overstock from a cash problem into a write-off problem.
The Starting Point: Eight Managers, Eight Gut Feelings
Each branch manager at this chain had years of experience and genuinely good instincts about their own store's rhythm. The problem was structural, not personal. Nobody had visibility across branches, so:
- Branch A ran out of a common antihypertensive medication for four days, sending customers to a competitor, while Branch C two kilometers away had 40 boxes sitting on the shelf, six weeks from expiry.
- Reorders were placed on a fixed weekly schedule regardless of actual sell-through rate, so slow-moving branches over-ordered out of habit and fast-moving branches under-ordered out of caution.
- Nobody had a clean, shared view of which SKUs were approaching expiry across the network until a manual monthly audit caught it, often too late to redistribute.
The chain estimated, conservatively, that stockouts on their top 50 SKUs were costing them measurable walk-away sales every month, while expired stock write-offs ran into the tens of millions of rupiah annually across the network. Both numbers were invisible in the day-to-day, because each branch only saw its own losses, never the network-wide pattern.
What We Built: Forecast Plus Inter-Branch Transfer Logic
The fix had two connected parts, and both mattered. Forecasting alone would have told them what to order but not what to do with existing imbalances. Transfer logic alone would have moved stock around without fixing the root ordering behavior.
1. A sales-driven demand forecast per SKU per branch. Instead of fixed reorder schedules, the system pulled historical sales data per branch, adjusted for seasonality (certain medications spike predictably around specific months, flu season being the obvious one but not the only one), and generated a suggested reorder quantity and timing per branch. Managers kept override authority, because local knowledge still matters (a nearby clinic opening, a doctor's prescribing habits shifting), but the default suggestion was now data-driven rather than a round-number habit.
2. Inter-branch transfer alerts before expiry, not after. The system flagged, on a rolling basis, any SKU approaching expiry at one branch while another branch was projected to sell through its own stock and need a reorder soon. Instead of writing off the aging stock, it triggered a transfer suggestion: move the 40 boxes from Branch C to Branch A instead of letting one expire and reordering fresh stock for the other.
This second piece is the one generic inventory advice usually misses, because it assumes single-location retail. Multi-branch pharmacy chains have an advantage single stores do not: their overstock problem and their stockout problem are often the same problem, just misallocated across locations. The fix is logistics, not just forecasting. If your own network struggles with the same visibility gap across locations, a pharmacy chain synced stock across 8 branches (finally) covers the underlying stock-sync foundation this forecasting layer was built on top of.
The Expiry-Date Angle: Why Pharmacy Inventory Is Harder Than Retail Inventory
Most inventory case studies treat overstock as a cash-flow problem: money tied up in shelf stock that could be working elsewhere. Pharmacy retail has that problem plus a harder one. Expired medication is not just slow-moving, it is legally required to be destroyed, meaning overstock does not just tie up cash, it becomes a guaranteed loss with a hard deadline.
This changes the forecasting priorities in ways worth calling out:
- Forecast confidence intervals need to be tighter for high-cost, short-shelf-life products than for stable, long-shelf-life ones. A generic "order more to be safe" buffer strategy that works for durable retail goods is actively expensive here.
- Expiry-aware alerts need to fire early enough that a transfer is still logistically possible, not just early enough to know a write-off is coming. We set the alert threshold at 90 days before expiry for slower-moving SKUs, giving branches real time to act rather than a helpless heads-up.
- Batch-level tracking, not just SKU-level, matters because two batches of the same medication can have very different expiry dates sitting on the same shelf. The forecast and transfer logic had to operate at batch granularity to actually prevent the specific box from expiring, not just the SKU in aggregate.
The Results
Within the first two quarters after rollout across all eight branches:
| Metric | Before | After |
|---|---|---|
| Stockouts on top 50 SKUs | Frequent, multiple per month per branch | Reduced by roughly two-thirds |
| Expired stock write-offs | Tens of millions IDR annually | Cut by more than half |
| Manager time on manual ordering | Several hours weekly per branch | Reduced to review and override only |
The manager override rate settled around 15 to 20 percent of suggested orders, which the chain saw as healthy, not a failure of the model. Local knowledge still corrects for things data alone cannot see: a new clinic opening nearby, a doctor changing prescribing habits, a one-off community health event. The forecast handles the baseline; managers handle the exceptions, which is exactly the ratio you want.
The Takeaway
Multi-branch inventory problems are rarely a single-branch demand-forecasting problem in disguise, they are a network visibility problem, and pharmacy retail raises the stakes because expiry turns overstock into a guaranteed write-off, not just tied-up cash. If your branches are each solving their own inventory in isolation, the fastest win is not a better forecast at each location, it is connecting the network so nobody's overstock and nobody's stockout stay invisible to each other.