📦 Inventory Optimization & Health Audit

Supply Chain Analytics Portfolio Project | R · ABC Segmentation · (s,Q) Policy · Shiny Dashboard

Turning 1M+ beverage retail transactions into an automated replenishment engine — the same decision framework used by FMCG and beverage supply chains at scale.

---

🔴 Live Interactive Dashboard

👉 Open Dashboard (No R installation needed — runs in any browser)

---

🎯 Why This Project Is Relevant to Beverage & FMCG Supply Chains

This project uses Iowa Liquor Sales data (1M+ transactions) — structurally identical to the inventory challenges faced by Auckland-based beverage and FMCG companies:

Company	Relevance
Lion	Multi-SKU beer, wine & spirits portfolio — same ABC segmentation challenges
Delegat Group	Premium wine export — Class A SKU protection, lead time buffers
DB Breweries (Heineken NZ)	Seasonal demand spikes → adaptive safety stock
Frucor Suntory	FMCG replenishment — EOQ and reorder point logic
Fonterra	Multi-tier supply chain — dead capital detection in slow-moving lines

The core problem is universal: Which SKUs need priority attention? Where is working capital trapped? What are the optimal reorder quantities per high-value line?

---

🔬 Analytical Framework

Stage	Method	Business Output
1. Prioritisation	ABC Segmentation (algorithmic Pareto)	Class A/B/C labels per SKU
2. Capital Efficiency	Inventory Turnover Ratio by category	Dead capital flags
3. Replenishment Policy	Continuous Review (s, Q) model	Per-SKU reorder point + order qty
4. Demand Forecasting	Simple Exponential Smoothing (α=0.3)	Next-day demand estimate
5. Stress Testing	EOQ Cost Sensitivity (+10%, +20% holding)	Impact before cost changes hit
6. Health Audit	Gap analysis: Actual stock vs optimal range	Traffic-light status per SKU

---

📊 Key Parameters & Assumptions

Lead Time        : 7 days
Service Level    : 95%  (Z = 1.645)
Holding Cost     : 20% of unit price per year
Order Cost (K)   : $50 per purchase order
SES Alpha        : 0.3

All parameters are tunable from the Shiny dashboard sidebar — no hardcoding.

---

📈 What the Dashboard Shows

Tab 1 — Overview KPIs Total Class A SKUs, stockout risk count, overstocked count, dead capital flags

Tab 2 — ABC Analysis Revenue by category, Pareto cumulative curve, SKU count breakdown

Tab 3 — Inventory Policy Per-brand EOQ, Safety Stock, Reorder Point table — filterable and downloadable

Tab 4 — Demand Forecasting SES forecast vs actual, MAPE by brand, top-3 Class A monthly trend overlay

Tab 5 — Cost Sensitivity EOQ response to +10%/+20% holding cost increase — stress test for procurement teams

Tab 6 — Health Audit Traffic-light audit: Healthy ✅ / Overstocked ⚠️ / Stockout Risk 🚨 — with stock gap per SKU

Tab 7 — Executive Report Auto-generated HTML report with all results — downloadable for stakeholders

---

🛠️ Tech Stack

Tool	Purpose
R	Core analysis language
dplyr / tidyr	Data wrangling
ggplot2 / patchwork	Static visualisation
plotly	Interactive charts
ABCanalysis	Algorithmic Pareto segmentation
shiny / bslib	Interactive web dashboard
DT	Filterable, downloadable data tables
ggrepel	Non-overlapping chart labels

---

🚀 Run It Yourself

Option A — Shiny Dashboard (Recommended)

install.packages(c(
  "shiny", "bslib", "bsicons", "dplyr", "tidyr", "readr",
  "ggplot2", "plotly", "scales", "DT", "ABCanalysis", "htmltools"
))

shiny::runApp("app.R")

Option B — Static Analysis + Charts

install.packages(c(
  "readr", "dplyr", "tidyr", "ABCanalysis",
  "ggplot2", "scales", "patchwork", "ggrepel", "here"
))

source("Inventory_Optimization_v4.R")

No data? Both scripts fall back to a synthetic 120-brand dataset (set.seed=42) so anyone can run the full analysis without the Kaggle source file.

Data Source (Optional)

Iowa Liquor Sales — Kaggle

Place files in data/:

data/SalesFINAL12312016.csv
data/EndInvFINAL12312016.csv
data/BegInvFINAL12312016.csv

---

📁 Repository Structure

Inventory_Optimization/
│
├── app.R                          ← Shiny dashboard (interactive)
├── Inventory_Optimization_v4.R    ← Static analysis + 9-chart patchwork
├── README.md
├── .gitignore
│
├── output/                        ← Git-ignored, regenerated by scripts
│   ├── dashboard_patchwork.png
│   ├── Final_Inventory_Audit.csv
│   ├── KPI_Scorecard.csv
│   └── EOQ_Sensitivity_Analysis.csv
│
└── data/                          ← NOT committed (Kaggle CSVs)

---

🧮 Core Formulas

Economic Order Quantity (EOQ)

Q* = √(2 × D × K / h)
D = daily demand  |  K = order cost  |  h = daily holding cost per unit

Safety Stock

SS = Z × σ_demand × √(Lead Time)
Z = 1.645 at 95% service level  |  σ = std dev of daily demand

Reorder Point

s = (D × Lead Time) + Safety Stock

Inventory Turnover Ratio

ITR = Annual Sales Value / Ending Inventory Value
ITR < 1.0  →  dead capital (stock costs more than it earns)

---

👤 Author

Hoang Anh Le (Haytham) Master of Business Analytics — Operations & Supply Chain Specialisation University of Auckland, 2025–Present | Auckland, New Zealand

🔗 LinkedIn · 🐙 GitHub

---

Add these topics in repo Settings → About: r supply-chain inventory-optimization abc-analysis eoq shiny fmcg demand-forecasting operations-research portfolio