📊 How to Optimize Inventory Levels – Supply Chain & Operations – Power BI

---

Built interactive Power BI dashboards to track inventory levels, identify stock shortages, and optimize warehouse operations for better decision-making for AdventureWorks2022 Database.

📝Author: Loc Ha

📅Date: 2025-06-18

---

📑 Table of Contents

1. 📌 Background & Overview
2. 📂 Dataset Description & Data Structure
3. 🧠 Design Thinking Process
4. 📊 Key Insights & Visualizations
5. 🔎 Final Conclusion & Recommendations

---

📌 Background & Overview

🎯 Objective

• Provide stakeholders with a comprehensive and easy-to-understand picture of warehouse inventory.
• Track and control inventory levels across multiple locations.
• Identify products that are out-of-stock, below reorder point, or at safety stock level.
• Support decision-making for warehouse managers, sales, and marketing teams.

📺 Stakeholders

✔️ Warehouse managers & staff – ensure inventory meets required thresholds.

✔️ Sales & marketing teams – align sales campaigns with available stock.

✔️ Decision-makers – optimize operations and reduce costs.

---

📂 Dataset Description & Data Structure

💻 Data Source

• Source: AdventureWorks2022 (imported from SQL Express).
• Format: Relational database (SQL Server).
• Tables Used: Product, ProductInventory, ProductCategory, ProductSubcategory, Sales, Calendar, Production tables.

⚙️ Data Model (ERD)

• The model connects product information with sales, inventory, and production data.
• Key tables: Dim_Product, Dim_ProductInventory, Fact_Sales, Dim_Calendar, Production_WorkOrder, Production_Location.
• Measures built in a dedicated MeasuresTable (Inventory KPIs).

---

🧠 Design Thinking Process

1️⃣ Empathize – Understand warehouse managers’ and sales teams’ needs for monitoring stock levels.

2️⃣ Define – Problem: lack of visibility into stock status leads to overstocking or stockouts.

3️⃣ Ideate – Brainstorm KPIs: Total Inventory, Products Below Reorder Point, Out-of-Stock %, Turnover.

4️⃣ Prototype & Review – Build Power BI dashboards with key metrics, validate with stakeholders, refine.

---

⚒️ Main Process

1️⃣ Data Cleaning & Preprocessing – Use Power Query to transform AdventureWorks tables.

2️⃣ Exploratory Data Analysis – Review stock distribution by category, location, and time.

3️⃣ DAX Measures – Key examples:

#Inventory Products = CALCULATE(DISTINCTCOUNT(Dim_ProductInventory[ProductID]), Dim_ProductInventory[Quantity] > 0)

Products Out-Of-Stock = CALCULATE(DISTINCTCOUNT(Dim_ProductInventory[ProductID]), Dim_ProductInventory[Inventory Status] = "Out of Stock")

Inventory Value = SUMX(Dim_ProductInventory, Dim_ProductInventory[Quantity] * RELATED(Dim_Product[ListPrice]))

Inventory Δ% by Month = VAR CurrentInventory = [Total Inventory Quantity]
VAR PrevInventory = CALCULATE([Total Inventory Quantity], DATEADD(Dim_Calendar[Date], -1, MONTH))
RETURN DIVIDE(CurrentInventory - PrevInventory, PrevInventory)

4️⃣ Power BI Visualization – Build 3 dashboards: Overview, Inventory vs Sales, Reorder Point Analysis.

---

📊 Key Insights & Visualizations

1️⃣ Inventory Overview Dashboard

• Total Inventory Quantity: 336K units across 428 unique products.

• Products Below Reorder Point: 343 SKUs (~80%).

• Out-of-Stock Products: Only 4 SKUs (~0.93%).

• Inventory Turnover: ~4.66 times.

• Low Inventory Categories: Bikes (279), Components (54).

• Insight: Most shortages are concentrated in Bikes, suggesting high demand but insufficient stock planning.

  

2️⃣ Inventory vs Sales Dashboard

• Bikes: Highest inventory but slow turnover, tying up capital.

• Accessories & Clothing: Very low inventory relative to sales → potential lost sales.

• Trend Analysis: Stock rose sharply in Q2 2012 but sales did not match, indicating forecasting issues.

• Insight: Misalignment between stock allocation and market demand.

  

3️⃣ Reorder Point Analysis Dashboard

• ~80% of products are below reorder point, mainly in Bikes & Components.
• Clothing: 90% safe, but still the highest Out-of-Stock rate (~10%).
• Inventory by Location: Finished Goods storage has safe levels, but many other warehouses are 100% below reorder point.
• Insight: Inefficient allocation across warehouses leads to stockouts in some while others are safe.

---

🔎 Final Conclusion & Recommendations

📌 Key Takeaways

✔️ Majority of SKUs (~80%) are below reorder point → urgent restocking required.

✔️ Bikes consume the largest share of inventory but turn over slowly.

✔️ Accessories & Clothing are understocked despite demand, risking lost revenue.

✔️ Inventory turnover (4.66) needs benchmarking against industry standards.

✔️ Stock allocation across warehouses is unbalanced.

📌 Recommendations

1. Adjust Reorder Points – Recalibrate safety stock and reorder levels based on actual sales trends.
2. Focus on Bikes & Components – Prioritize replenishment for these categories while managing turnover.
3. Boost Accessories & Clothing Stock – Increase supply to capture unmet demand and avoid lost sales.
4. Improve Forecasting – Use time-series analysis to predict seasonal demand (e.g., Q2 2012 spike).
5. Reallocate Inventory Across Warehouses – Shift products from overstocked to understocked locations.
6. Benchmark Turnover – Compare with industry KPIs to set realistic performance targets.

---