Overview

This project focused on transforming inventory management for a supermarket specializing in imported candies and chocolates.

Unlike typical retail, this store operated under two constraints:

  • Unpredictable demand (trend-driven, niche products)
  • Long and inconsistent lead times (imports)

The existing system was entirely reactive:

  • Purchase decisions based on gut feel
  • No structured understanding of product performance
  • No buffer planning for uncertainty

The goal was not just analytics — but decision support: Build a system that tells what to buy, when, and how much — consistently.

Problem Breakdown

Inventory issues were not isolated — they were systemic:

1. Stockouts of High-Demand Products

Popular imported brands would frequently go out of stock, directly impacting revenue.

2. Dead Inventory Accumulation

Low-demand products stayed on shelves for months, locking working capital.

3. No Demand Understanding

There was no clear distinction between:

  • Fast-moving vs slow-moving SKUs
  • Seasonal vs consistent demand

4. Lead Time Blindness

Import delays (2–6 weeks) were not factored into purchasing decisions.

System Design Philosophy

Instead of building a complex ERP-like system, the focus was:

  • Simple, explainable logic over black-box models
  • Actionable outputs over dashboards
  • SKU-level decisions over aggregated insights

Architecture

The system was designed as a lightweight decision layer on top of existing data.

Data Layer

  • POS transaction data (daily sales)
  • Product metadata (brand, category, origin)

Processing Layer (SQL - BigQuery)

  • Daily SKU-level aggregation
  • Rolling demand metrics
  • Revenue contribution calculations

Decision Layer (Python)

  • Product classification
  • Demand variability modeling
  • Reorder point computation

Output Layer

  • Decision dashboard
  • Restock recommendations per SKU

Core System Components

1. Demand Modeling

Instead of raw totals, demand was modeled using:

  • Rolling averages (7 / 14 / 30 day)
  • Standard deviation (volatility)
  • Sales frequency (days with sales)

2. ABC Classification (Business Prioritization)

Products were segmented by revenue contribution:

  • A (Top ~70%) → Must always be in stock
  • B (~20%) → Moderate priority
  • C (~10%) → Candidates for liquidation

3. Reorder Point System

reorder_point = (avg_daily_sales * lead_time_days) + safety_stock

4. Safety Stock

safety_stock = demand_std * sqrt(lead_time_days)

5. SKU-Level Decision Output

For each product:

  • Current stock vs reorder point
  • Recommended reorder quantity
  • Priority classification
  • Risk flag (stockout / overstock)

Key Design Decisions

Why Not ML First?

  • Data sparsity
  • Need for explainability
  • Faster ROI from rules

Why ABC First?

  • Focus on high-impact SKUs
  • Reduce noise from long-tail

Why Simple System?

  • Adoption > complexity
  • Trust > accuracy

Tradeoffs

  • Rule-based logic → easier to adopt, less adaptive
  • SKU-level modeling → precise but compute-heavy
  • Rolling averages → stable but slower to react

Results

Operational Impact

  • Reduced stockouts for high-priority products
  • Identified dead inventory
  • Improved restocking consistency

Business Impact

  • Better cash flow
  • Increased revenue capture
  • Reduced reliance on intuition

What This Enables Next

  • Demand forecasting
  • Supplier optimization
  • Automated alerts
  • Multi-store balancing

Takeaway

From reactive stocking → to systematic decision-making.

Well-designed heuristics + clean data can outperform complex systems when aligned with real business needs.