Case Study 01
Pharma
Inventory
Optimization
Unified data visibility enabling $20M+ projected annual savings for a global manufacturer.
Role
BI Consultant
Duration
6 months
Tech Stack
SAP • Snowflake • Microsoft Fabric • Power BI
01 The Challenge
“We are throwing away millions in expired product, but nobody can tell us exactly how much, where, or why.”
A global pharmaceutical manufacturer faced a critical visibility gap. Data was scattered across SAP, custom ERPs, and spreadsheets. Manufacturing operated disconnected from demand planning, leading to a costly cycle of overproduction and expiration.
1.2B
Rows of Data
4
Disconnected Systems
0
Unified Reports
02 Core Indicators
Days Inventory On Hand
DIOThe number of days current inventory would last at the current sales rate.
A DIO of 90 means you're holding roughly three months of stock. In pharma, DIO must be evaluated against shelf life.
Discard Rate
DRThe percentage of manufactured units that are destroyed before sale.
A 5% discard rate might be acceptable; 30%+ signals systemic overproduction or demand forecasting failure.
Turnover Rate
TRHow many times inventory fully cycles through in a year.
Higher turnover generally indicates healthier demand-supply alignment. Low turnover combined with short shelf life is a red flag.
Sell-Through Rate
STRThe percentage of received inventory that actually sells within a defined period.
Useful for identifying products that move immediately (potential underproduction) versus those that sit (overproduction risk).
Shelf Life
SLThe window from manufacturing date to expiration.
The gap between DIO and remaining shelf life determines discard risk. Products expiring before they can be sold are a guaranteed loss.
03 The Solution
Architecting the Truth
We built a unified data pipeline that ingests, cleans, and harmonizes inventory data from all global sources.
Core Metrics Output
Inconsistent IDs
Product codes varied across acquisition-heavy legacy systems.
Granularity Mismatch
Daily actuals vs monthly forecasts required complex interpolation.
Data Gaps
Missing historical fields necessitated probabilistic backfilling.
Implementation Timeline
Key Activities
- Stakeholder interviews
- Source system inventory
- Data availability assessment
- Define core metrics (DIO, turnover, discards)
Deliverables
- Data source map
- Metric definitions
- Integration requirements
Main Challenge
No single source of truth existed - each team had their own version of 'inventory health'
Key Activities
- Extract from SAP, ERPs, Excel
- Standardize product codes
- Reconcile time granularity
- Build transformation pipelines
Deliverables
- Unified data model
- ETL pipelines
- Snowflake staging layer
Main Challenge
Product identifiers varied across systems - matching required fuzzy logic and manual validation
Key Activities
- Build semantic model in Fabric
- Develop Power BI report
- Validate metrics with finance
- Iterate on visualizations
Deliverables
- Power BI dashboard
- Quadrant framework
- SKU-level drill-through
Main Challenge
Balancing detail (SKU-level) with executive summary views required multiple design iterations
Key Activities
- Portfolio segmentation
- Root cause analysis
- Savings quantification
- Recommendation development
Deliverables
- $20M+ savings projection
- Optimization shortlist
- Divestiture validation
Main Challenge
Connecting operational data to financial impact required close partnership with finance team
Key Activities
- Executive presentation
- Planning team enablement
- Monthly review cadence
- Threshold alerting (planned)
Deliverables
- C-level adoption
- Strategic decisions enabled
- Continuous monitoring
Main Challenge
Shifting from one-time analysis to operational tool required change management
04 Portfolio Analysis
The Efficiency Matrix
We developed a quadrant model to segment the entire product portfolio based on inventory health and discard risk.
05 Impact
$20M+
Projected Annual Savings
By identifying divestiture candidates and optimizing production schedules for high-risk products.