Project: Inventory Management Report
Build an inventory management report to track stock levels, optimize reorder points, and minimize stockouts.
Project Overview
| Aspect | Details |
|---|---|
| Objective | Monitor inventory health, prevent stockouts, reduce holding costs |
| Audience | Warehouse managers, procurement, operations |
| Data Sources | Inventory levels, purchase orders, sales orders, suppliers |
| Key Metrics | Stock on hand, reorder point, stockout rate, turnover ratio |
| Refresh | Daily (or real-time via DirectQuery) |
Dashboard Pages
Page 1: Inventory Overview • KPI Cards: Total SKUs, Total Stock Value, Stockout Count, Avg Turnover Days • Stock level by Category (stacked bar) • Stock status distribution (donut: In Stock, Low, Out of Stock) • Inventory value trend (area chart) • Data alert: Items below reorder point
Page 2: Stock Health Analysis • Matrix: Product × Warehouse with stock levels (conditional formatting) • ABC Analysis (classification by value contribution) • Aging inventory (items not sold in 30/60/90+ days) • Slow-moving vs fast-moving products
Page 3: Reorder & Procurement • Items below reorder point (table with red highlighting) • Lead time analysis by supplier • Purchase order status (pending, in transit, received) • Supplier performance scorecard
Page 4: Stock Movement • Daily inbound vs outbound (combination chart) • Stock transfer between warehouses • Seasonal patterns (line chart by month) • Drill-through to product-level movement history
Key DAX Measures
Stock On Hand = SUM(Inventory[Quantity])
Stock Value = SUMX(Inventory, Inventory[Quantity] * Inventory[UnitCost])
Stockout Items =
COUNTROWS(FILTER(Inventory, Inventory[Quantity] = 0))
Stockout Rate =
DIVIDE([Stockout Items], COUNTROWS(Inventory))
Inventory Turnover =
DIVIDE(
SUM(Sales[COGS]),
AVERAGE(Inventory[StockValue])
)
Days of Stock =
DIVIDE([Stock On Hand], AVERAGE(Sales[DailyDemand]))
Below Reorder Point =
COUNTROWS(
FILTER(Inventory, Inventory[Quantity] <= Inventory[ReorderPoint])
)
ABC Classification =
SWITCH(
TRUE(),
[CumulativeContribution%] <= 0.80, "A",
[CumulativeContribution%] <= 0.95, "B",
"C"
)Features to Implement
• ✅ Conditional formatting: Red for out-of-stock, yellow for low stock • ✅ Data alerts for items below reorder point • ✅ Matrix with color scales for stock levels across warehouses • ✅ Drill-through from category to individual product detail • ✅ Power Automate alert when critical items hit zero stock • ✅ Dynamic title showing selected warehouse/category • ✅ Relative date slicer for movement analysis
Skills Applied
Conditional formatting, data alerts, Power Automate integration, matrix visuals, drill-through, DAX calculations for inventory metrics