📦 Smart Inventory Optimization Tool

A production-grade inventory management engine built for distribution agencies. Combines ABC analysis, safety stock modelling, reorder point calculation, and ML demand forecasting into a single automated pipeline — with Tableau-ready outputs.

---

🎯 Problem Statement

Distribution agencies face a constant tension between two costly mistakes:

Problem	Business Impact
Stockouts	Lost sales, damaged customer relationships, emergency restocking costs
Overstocking	Tied-up working capital, storage costs, spoilage (especially perishables)

This tool answers three precise questions every week:

1. What do we need to reorder? (ABC Analysis)
2. When do we place the order? (Reorder Point)
3. How much do we order? (EOQ + Safety Stock)
4. What will demand look like? (ML Forecasting)

---

🏗️ Architecture

┌─────────────────────────────────────────────────────────────┐
│                   DATA LAYER (PostgreSQL)                    │
│   products  ←→  sales_transactions  ←→  current_inventory   │
└───────────────────────┬─────────────────────────────────────┘
                        │
┌───────────────────────▼─────────────────────────────────────┐
│               PYTHON PIPELINE (4 steps)                      │
│                                                              │
│  [1] generate_data.py   → Synthetic sales (6 months, 10 SKU)│
│  [2] optimization.py    → ABC + Safety Stock + ROP + EOQ    │
│  [3] forecasting.py     → Linear Regression (7-day horizon) │
│  [4] export_insights.py → Merge → inventory_insights table  │
└───────────────────────┬─────────────────────────────────────┘
                        │
┌───────────────────────▼─────────────────────────────────────┐
│              TABLEAU DASHBOARD (5 sheets)                    │
│  ABC Revenue │ Reorder Alerts │ Trend │ Forecast │ KPI Cards │
└─────────────────────────────────────────────────────────────┘

---

📁 Project Structure

smart-inventory-tool/
│
├── 01_schema.sql              ← PostgreSQL DDL (tables + views)
├── 02_generate_data.py        ← Synthetic data generator
├── 03_optimization_engine.py  ← Core inventory algorithms
├── 04_forecasting.py          ← ML demand forecasting
├── 05_export_insights.py      ← Final merge + export
├── run_all.py                 ← One-click pipeline runner
├── requirements.txt
├── README.md
│
└── output/                    ← Generated (git-ignored)
    ├── inventory_insights.csv     ← PRIMARY Tableau source
    ├── daily_sales_enriched.csv   ← Trend chart data
    ├── forecast_daily.csv         ← Day-by-day predictions
    ├── forecast_summary.csv       ← 7-day totals
    ├── abc_summary.csv            ← Class breakdown
    └── model_metrics.csv          ← MAE / MAPE per product

---

⚡ Quick Start

1. Clone & install

git clone https://github.com/YOUR_USERNAME/smart-inventory-tool.git
cd smart-inventory-tool
pip install -r requirements.txt

2. Set up the database (optional — CSVs work without it)

psql -U postgres -c "CREATE DATABASE inventory_db;"
psql -U postgres -d inventory_db -f 01_schema.sql

3. Run the full pipeline

python run_all.py

Or step by step:

python 02_generate_data.py        # ~2s
python 03_optimization_engine.py  # ~1s
python 04_forecasting.py          # ~5s
python 05_export_insights.py      # ~3s

Flag: python run_all.py --skip-data to reuse existing sales data on re-runs.

---

🧮 Core Algorithms

ABC Analysis (Pareto Classification)

Products are ranked by 6-month revenue contribution:

Class	Revenue Threshold	Management Policy
A	Top 70%	Weekly stock count, tight ROP, priority supplier terms
B	70–90%	Bi-weekly count, standard ROP, moderate buffer
C	Bottom 10%	Monthly count, relaxed ROP, bulk ordering

Safety Stock

Protects against demand spikes during the supplier lead time window:

Safety Stock = Z × σ_demand × √(lead_time_days)

Where:

• Z = 1.645 → 95% service level (stockout in only 5% of replenishment cycles)
• σ_demand = standard deviation of daily sales (zero-filled, including stockout days)
• √(lead_time) = volatility scales with the square root of time

Reorder Point (ROP)

ROP = (avg_daily_demand × lead_time_days) + safety_stock

The system triggers a purchase order the moment effective_stock ≤ ROP.

effective_stock = stock_on_hand + stock_on_order

Economic Order Quantity (EOQ)

Minimises the total of ordering costs + holding costs:

EOQ = √( 2 × D × S / H )

Where:

• D = annual demand (units)
• S = cost per order placed (₦500 default)
• H = annual holding cost per unit (20% of unit cost)

Final recommended_order_qty = max(EOQ, supplier_MOQ)

Demand Forecasting (Linear Regression)

10 engineered features trained on 6 months of daily demand:

Feature Group	Features
Lag	lag_1, lag_7, lag_14
Rolling statistics	rolling_mean_7, rolling_mean_14, rolling_std_7
Calendar	day_of_week, week_of_year, is_weekend
Trend	t (linear time index)

Model is trained on the first ~166 days and evaluated on the final 14 days (MAE / MAPE reported per SKU).

---

📊 Tableau Dashboard Guide

Connect

1. CSV mode: Data → Text File → output/inventory_insights.csv
2. Live mode: Data → PostgreSQL → localhost:5432/inventory_db

Recommended Sheets

Sheet 1 — ABC Revenue Breakdown

• Chart type: Horizontal Bar
• Rows: product_name | Measure: total_revenue_6m
• Colour: abc_class (Red=A, Amber=B, Green=C)
• Sort: Descending by revenue

Sheet 2 — Reorder Alert Table

• Chart type: Text / Highlight Table
• Rows: product_name, stock_status, effective_stock, reorder_point, recommended_order_qty
• Filter: stock_status ≠ OK
• Colour: stock_status (Red = REORDER NOW, Amber = MONITOR)

Sheet 3 — Daily Sales Trend

• Chart type: Dual-axis Line
• Source: daily_sales_enriched.csv
• X: date | Y1: units_sold | Y2: rolling_avg_7d
• Filter: Product parameter (dropdown)

Sheet 4 — 7-Day Demand Forecast

• Chart type: Bar (grouped)
• Source: forecast_daily.csv
• X: forecast_date | Y: predicted_units
• Colour: product_name

Sheet 5 — KPI Cards (Big Numbers)

Create calculated fields:

// Items Needing Action
COUNTD(IF [stock_status] != "OK" THEN [product_id] END)

// Total Immediate Order Value
SUM([estimated_order_cost])

// Avg Days of Supply
AVG([days_of_supply])

// Class A Revenue Share
SUM(IF [abc_class] = "A" THEN [total_revenue_6m] END)
/ SUM([total_revenue_6m]) * 100

---

🗄️ PostgreSQL Loading (for live dashboards)

from sqlalchemy import create_engine
import pandas as pd

engine = create_engine("postgresql://user:password@localhost:5432/inventory_db")

tables = {
    "products":             pd.read_csv("output/products.csv"),
    "sales_transactions":   pd.read_csv("output/sales_transactions.csv"),
    "current_inventory":    pd.read_csv("output/current_inventory.csv"),
    "inventory_insights":   pd.read_csv("output/inventory_insights.csv"),
    "daily_sales_enriched": pd.read_csv("output/daily_sales_enriched.csv"),
}

for table_name, df in tables.items():
    df.to_sql(table_name, engine, if_exists="replace", index=False)
    print(f"✅  {table_name} loaded ({len(df):,} rows)")

---

🔧 Configuration Reference

All tunable parameters are at the top of each script:

Script	Parameter	Default	Description
03_optimization_engine.py	SERVICE_LEVEL	0.95	Target fill rate (0.99 = tighter buffer)
03_optimization_engine.py	ORDER_COST	₦500	Admin cost per purchase order
03_optimization_engine.py	HOLDING_COST_RT	0.20	Annual holding cost as % of unit cost
04_forecasting.py	FORECAST_HORIZON	7	Days ahead to forecast

---

🚀 Upgrade Path

Enhancement	Tool	Effort
Better forecasting	Facebook Prophet	2h — replace LinearRegression block
Automated alerts	Airflow / cron + SMTP	4h — wrap run_all.py in a DAG
Live POS integration	Kafka / REST API	1–2 days
Multi-warehouse	Add warehouse_id FK to schema	1 day
Price elasticity	Add promo flag + regression	3h
Interactive UI	Streamlit app	4–6h

---

📄 Licence

MIT — free to use, modify, and include in your portfolio.

---

Built as a portfolio project demonstrating supply chain analytics, statistical inventory modelling, and ML-based demand forecasting.