Case study 03 · Pharmacy spend · Healthcare
Was it the price, or the quantity?
Making complex drug-cost data easier to understand and act on.
of data points made comparable period over period
The situation
With thousands of data points and significant variation in drug prices and utilization, Pharmacy and Finance leaders needed an easier way to understand what was driving spending. I designed and built a dashboard that allows leaders to compare periods, identify high-cost drugs, understand price and quantity changes, support supplier negotiations, and make more informed purchasing decisions.
The problem
Leaders needed a clearer way to answer the questions that mattered: how much the organisation pays for drugs now compared with previous periods, which drugs are driving higher spending, whether a change is caused by price or by volume, how average drug costs are moving, and where better purchasing decisions were available.
My role
Sole owner. Designed and built it end to end, from the first Pharmacy and Finance conversation through deployment and support.
What I did
Four phases · condensed spine
Phase 01
Understand
- Business questions from Pharmacy & Finance
- Identifying the cost and purchasing data
Phase 02
Source & model
- SQL and underlying calculations
- Validating drug, quantity, and pricing data
Phase 03
Build & validate
- Period-over-period comparison design
- Views separating price from utilization
Phase 04
Ship & support
- Stakeholder testing
- Deployment and production support
Hardest part
Designing a dashboard that could make a very large and highly variable drug dataset understandable. There are many drugs and significant variation in price, purchasing quantity, and utilization — the challenge was presenting that complexity so leaders could quickly see which drugs were driving cost changes, and why.
Result
Pharmacy and Finance leadership use it to identify higher-cost drugs, separate price changes from utilization changes, compare periods, support supplier negotiations, and find savings opportunities. No dollar-savings figure is claimed — none has been validated.