🛒 Retail Inventory & Supply Chain Optimization Engine

End-to-end supply chain analytics project — transforming 4 raw, unstructured retail datasets into actionable business intelligence using Python, Pandas, and SQL logic.

---

📌 Problem Statement

A retail business had no visibility into:

• Which products were driving revenue vs. wasting capital
• When to reorder stock before running out
• Which products had been sitting unsold for the entire year

This project solves all three problems using real data analysis techniques.

---

🔍 Key Business Insights Found

Finding	Detail
🏆 Top revenue SKUs (A-class)	Top 20% of SKUs drive 80% of revenue
💀 Dead Stock detected	Brand 271: 279 units, Brand 156: 191 units — zero sales all year
📦 Reorder Point modeled	Prevents stockouts for high-demand A-class items
💰 Capital blockage uncovered	Dead stock = locked working capital — liquidation recommended

---

🛠️ Tech Stack

Tool	Usage
Python 3	Core language
Pandas	Data ingestion, cleaning, transformation
NumPy	EOQ & Reorder Point calculations
Matplotlib	Visualizations (ABC chart, Dead Stock bar)
SQL (logic)	LEFT JOIN for preserving zero-sales SKUs
Jupyter Notebook	Development environment

---

🚀 What This Project Does — Step by Step

1️⃣ Data Cleaning & Preparation

• Ingested 4 raw CSV datasets: Sales, Purchases, Beginning Inventory, Ending Inventory
• Resolved null records, type mismatches, and duplicate entries using Pandas
• Produced clean, analysis-ready DataFrames

2️⃣ SQL LEFT JOIN Logic (in Python)

• Used LEFT JOIN logic to map opening → closing stock
• Why LEFT JOIN? INNER JOIN would silently drop sold-out SKUs — making them invisible in reports
• LEFT JOIN preserved them with NULL sales → confirmed as dead stock

3️⃣ ABC Classification (Pareto Principle)

• Sorted all SKUs by revenue (descending)
• Calculated cumulative revenue percentage
• Labeled: A = top 80% revenue | B = 80–95% | C = bottom 5%
• Output: Clear procurement priority framework

4️⃣ EOQ + Dynamic Reorder Point Model

EOQ  = √(2 × Annual Demand × Ordering Cost / Holding Cost)
ROP  = (Average Daily Demand × Lead Time) + Safety Stock

• Applied to A-category (high-value) items only
• Prevents stockouts while minimizing holding costs

5️⃣ Dead Stock Analysis

• Cross-table analysis: EndInventory vs SalesQuantity
• Detected items with stock > 0 but sales = 0 for the full year
• Recommended: halt orders + liquidate via clearance discounts

---

📊 Visual Outputs

Chart	Description
Insight_1_ABC_Revenue.png	ABC category distribution by revenue
Insight_2_DeadStock.png	Dead stock brands by units held

---

📁 Project Structure

Retail-Inventory-Optimization-Engine/
│
├── Retail_Inventory_Optimization.ipynb   ← Main analysis notebook
├── ABC_Analysis_Result.csv               ← SKU classification output
├── EOQ_ROP_Strategy.csv                  ← Reorder model output
├── Purchasing_Mistakes_DeadStock.csv     ← Dead stock findings
├── Insight_1_ABC_Revenue.png             ← ABC chart
├── Insight_2_DeadStock.png               ← Dead stock chart
└── README.md

---

▶️ How to Run

# 1. Clone the repo
git clone https://github.com/Vijay4532/Retail-Inventory-Optimization-Engine.git

# 2. Install dependencies
pip install pandas numpy matplotlib jupyter

# 3. Add raw data files to root directory
# (SalesFINAL.csv, PurchasesFINAL.csv, BegInvFINAL.csv, EndInvFINAL.csv)

# 4. Open notebook
jupyter notebook Retail_Inventory_Optimization.ipynb

---

👤 Author

Vijay Ahirwar
B.S. Data Science & Applications — IIT Madras
LinkedIn | GitHub