Case Study
← Back to case studiesInventory Analytics Automation → ~$6M Cost Avoidance
At Dollar Tree Canada, I built a repeatable analytics cadence and Power BI dashboards that made inventory risk visible early, translated insights into weekly actions, and reduced excess ordering—driving roughly $6M in cost avoidance.
SAP BI InsightsPower BIRetail AnalyticsBi-Annual OrderingException AlertsWeekly Operating CadenceInventory Control + Category Managers
Outcome
~$6M Cost Avoidance
Cadence
Weekly Review
Tooling
SAP BI → Power BI
Data & Operating Cadence
How the system worked end-to-end
Data source
Reliable inputs for inventory + sales
- • Inventory and sales data pulled from SAP BI Insights.
- • KPIs calculated and modeled in Power BI for consistent definitions and weekly refresh.
- • Report designed to be decision-first: exceptions at the top, drill-down when needed.
Weekly review cadence
Where decisions happened
- • Reviewed weekly with the Inventory Control team and Category Managers.
- • Routine focused on: what changed, what’s at risk, and what actions are required this week.
- • Owners assigned to actions (order adjustments, markdown review, exit/clearance recommendations).
Power BI Report Structure
Report pages built to match the KPI framework
1) Executive Snapshot
6 KPI leadership view (decision-first)
- • Months of Supply
- • Sell-Through %
- • Excess Inventory $
- • % Inventory > 12 months
- • Top 20 SKUs % of Sales
- • Dead Stock Value (no sales 90 days)
2) Coverage & Ordering Control
Bi-annual model KPIs
- • Months of Supply (Order Coverage) with thresholds (6–7 target, 8+ over-ordered, <5 risk)
- • Inventory-to-Sales Ratio trend
- • Category drill-down to understand drivers
3) Excess & Dead Stock
Over-ordering and non-moving detection
- • Excess Inventory % and Excess Inventory $ by category/SKU
- • Dead stock flags (no sales 90 days)
- • Slow movers (<1 unit/week) for invest/reduce decisions
4) Productivity & Sell-Through
What is actually moving
- • Sell-through % vs benchmark (70% good, <50% over-ordered)
- • Inventory turnover (annual sales ÷ average inventory)
- • Seasonal/order-cycle views leading into next buy
5) Aging & Markdown View
Aging buckets + triggers
- • Aging buckets: 0–3, 3–6, 6–12, 12+ months
- • % of inventory > 12 months (action required)
- • Markdown trigger list for review (90/180 days, 12+ months)
6) SKU Post-Mortems
Improve the next cycle
- • Multi-year SKU-level analysis (order quantities, sell-through, reorder frequency)
- • Forecast accuracy views (forecast vs actual variance)
- • Recommendation notes for next bi-annual order quantities
Inventory KPI Framework (Bi-Annual Ordering Model)
Definitions and thresholds used across the dashboards
1) Inventory Investment & Ordering Control
Inventory-to-Sales Ratio
Are we holding too much inventory relative to demand?
Formula: Average Inventory Value ÷ Annual Sales
Red flag: Ratio increasing over time.
Order Coverage (Months of Supply)
Most important KPI in a bi-annual model
Formula: On-hand Units ÷ Avg Monthly Sales
- • Target: ~6–7 months coverage (orders happen 2x/year)
- • 8 months → over-ordered
- • <5 months → stockout risk
2) Over-Ordering & Excess Detection
Excess Inventory %
How much inventory is above the 6–7 month need?
Formula: (Units Above 6–7 Month Need) ÷ Total Inventory
Example
Need: 6,000 units • On hand: 9,000 • Excess: 3,000 → 33% excess
Slow / Non-Moving Inventory
Identify dead stock and slow movers
- • No Sales (90 days) = dead stock
- • Low Sales (<1 unit/week) = slow mover
KPI: % of SKUs with no sales in last 90 days
3) Inventory Productivity (What Actually Moves)
Inventory Turnover
Healthy demand vs overstock risk
Formula: Annual Sales ÷ Average Inventory
- • High turnover = healthy demand
- • Low turnover = overstock risk
Sell-Through %
Best for seasonal / order cycles
Formula: Units Sold ÷ Units Received
- • Benchmark: 70% before next order = good
- • <50% = over-ordered
4) Aging & Markdown Management
Aging Buckets
Track inventory by age and flag action items
- • 0–3 months
- • 3–6 months
- • 6–12 months
- • 12+ months (action required)
KPI: % of inventory > 12 months old
Markdown Trigger Rules
Operational thresholds to drive action
| Condition | Action |
|---|---|
| No sales for 90 days | Review |
| No sales for 180 days | Markdown 20–30% |
| Inventory age > 12 months | Clearance / exit SKU |
5) Demand-Based Ordering (Right Amount Next Cycle)
Forecast Accuracy
Refine the next 6-month buy
Formula: Forecast vs Actual Sales Variance
Reorder Quantity Logic (Bi-Annual)
Purchase formula + safety stock
Order amount: (6 months forecast + Safety stock) – On hand
Safety stock: 10–20% of 6-month demand
6) Prioritization: What to Invest In
Top Movers (ABC Classification)
Protect A items; reduce C items
| Category | Definition | Strategy |
|---|---|---|
| A | Top 20% SKUs = 70–80% sales | Never stock out |
| B | Medium | Monitor |
| C | Low / slow | Reduce orders / exit |
Capital Trapped KPI
Are we investing too much in slow items?
KPI: % of inventory value tied up in C items
If high → capital trapped in slow inventory.
Executive Dashboard (Leadership View)
A compact set of KPIs shown weekly to keep the conversation decision-first.
- • Months of Supply
- • Sell-Through %
- • Excess Inventory $
- • % Inventory > 12 months
- • Top 20 SKUs % of Sales
- • Dead Stock Value (no sales 90 days)
What I owned
My responsibilities end-to-end
- • KPI definition and metric governance (consistent rules + definitions).
- • Power BI report structure, drill-down flows, and weekly reporting logic.
- • Stakeholder alignment and weekly operating cadence with inventory control + category managers.
- • SKU-level post-mortems and ordering recommendations for the next cycle.
- • Action frameworks (thresholds, triggers, and prioritization) that made analytics operational.
Results
What changed after implementation
- • ~$6M cost avoidance through reduced excess ordering and improved inventory discipline.
- • Faster identification of overstock/understock risk and clearer weekly actions.
- • A scalable framework where new SKUs/categories plug into the same KPI + cadence model.
Want to talk through this project?
I can walk through the KPI framework, Power BI report design, and how the weekly cadence drove adoption.