Chapter 32: Key Takeaways — Inventory and Supply Chain Analytics

The Central Insight

Supply chain problems come in two flavors that often coexist: too much of the wrong things, and too little of the right things. Both are data problems. When you have accurate demand velocity data, reliable supplier performance metrics, and rigorous reorder point calculations, neither problem needs to be permanent.

Python gives you the tools to move from reactive (managing stockouts and write-offs after they occur) to proactive (preventing them through automated monitoring and optimized inventory policies).

Core Metrics Reference Table

Reorder Point and Safety Stock

Reorder Point = (Average Daily Demand × Average Lead Time) + Safety Stock

You place a purchase order when stock drops to this level, not when it hits zero. The time between ordering and receiving is when you consume safety stock — which is why the reorder point must be above zero.

Safety Stock = Z × sqrt(LT × σ²_demand + demand² × σ²_lead_time)

• The Z-score corresponds to your target service level
• Lead time variability (σ_lead_time) contributes to safety stock just as much as demand variability
• An unreliable supplier costs money twice: late deliveries disrupt operations, and high variability forces you to hold more safety stock permanently

Service Level by ABC Tier: | Category | Recommended Service Level | Z-Score | |----------|--------------------------|---------| | Class A | 97-99% | 1.88-2.33 | | Class B | 95% | 1.645 | | Class C | 85-90% | 1.04-1.28 |

Most common mistake: Updating product demand but not updating reorder points. Demand grows by 30% while the reorder trigger stays at its 2019 value — and suddenly you are stocking out on your best-selling products.

Economic Order Quantity (EOQ)

Formula: Q* = sqrt(2DS / H)

Where D = annual demand, S = cost per order, H = annual holding cost per unit

At the EOQ: Total annual ordering cost = Total annual holding cost (they are equal at the optimum)

# Quick EOQ calculation
import math

def eoq(annual_demand, order_cost, unit_cost, holding_rate=0.20):
    holding_cost_per_unit = unit_cost * holding_rate
    return math.sqrt((2 * annual_demand * order_cost) / holding_cost_per_unit)

# Example: annual demand 1200, order cost $85, unit cost $24
print(f"EOQ: {eoq(1200, 85, 24):.0f} units")  # approx 197 units

EOQ rules of thumb: - If your current order quantity is 2× or more the EOQ, you are likely over-ordering - If your current order quantity is less than 0.5× EOQ, you are likely ordering too frequently - EOQ is a benchmark, not a mandate — quantity discounts and min order requirements often justify deviations

ABC Analysis

Classification logic: - Class A: Top SKUs accounting for the first 80% of annual consumption value (typically 10-20% of SKUs) - Class B: Next SKUs accounting for the following 15% of value (typically 20-30% of SKUs) - Class C: Remaining SKUs accounting for the last 5% of value (typically 50-60% of SKUs)

def perform_abc_analysis(df, value_column):
    """Minimal ABC classification."""
    df = df.sort_values(value_column, ascending=False).copy()
    df["cumulative_share"] = df[value_column].cumsum() / df[value_column].sum()
    df["abc_category"] = pd.cut(
        df["cumulative_share"],
        bins=[-float("inf"), 0.80, 0.95, float("inf")],
        labels=["A", "B", "C"],
    )
    return df

The inventory efficiency ratio: Revenue share / Inventory share - Class A typically runs 2.0-3.0 (each dollar of A-item inventory generates $2-3 in revenue) - Class C typically runs 0.1-0.2 (each dollar of C-item inventory generates $0.10-0.20 in revenue)

The implication: treating all products identically in purchasing, storage, and management policies is almost always suboptimal.

Lead Time Analysis

Coefficient of Variation (CV) = Standard Deviation / Mean

A dimensionless measure of consistency. Lower is more predictable.

Key principle: A supplier with average lead time of 18 days and CV of 0.10 is easier and cheaper to manage than a supplier with average lead time of 12 days and CV of 0.45. Average lead time affects your base reorder calculation; variability drives your safety stock cost.

Building the Reorder Alert System

The workflow from data to actionable purchasing decisions:

acme_inventory.db  -->  load_inventory_from_database()
                                    |
acme_sales_2023.csv -->  join_inventory_with_sales()
                          (adds: avg_daily_demand,
                                 days_of_supply,
                                 annual_consumption_value,
                                 below_reorder_point flag)
                                    |
              +---------+-----------+-----------+
              |         |           |           |
     perform_abc   generate_    analyze_   calculate_
     _analysis()   reorder_     lead_      supplier_
                   alerts()     times()    scorecard()
              |         |           |           |
              +--------------------------------------------+
                                    |
                     build_supply_chain_dashboard()
                          (Excel output, 5 sheets)

Most Common Supply Chain Analytics Mistakes

The Data Architecture

Supply chain analytics requires joining three types of data:

1. Reference data (inventory master, supplier records) — what products exist, who supplies them, what parameters govern replenishment
2. Flow data (sales transactions) — what has been consumed, how fast demand is moving
3. Event data (purchase orders) — what has been ordered, when it arrived, how reliable suppliers have been

Analyzing any single source in isolation misses the story. The power comes from joining all three to answer questions that none can answer alone: "Given how fast this product is moving, how much runway does current stock provide, and when should I place the next order?"

Chapter Checklist

Before moving to Chapter 33, you should be able to:

• [ ] Calculate inventory turnover and DIO from COGS and inventory value data
• [ ] Apply the reorder point formula, including safety stock using the combined variance formula
• [ ] Calculate EOQ for a product given demand, order cost, and holding cost
• [ ] Run ABC analysis on an inventory DataFrame and interpret the results
• [ ] Calculate lead time statistics (mean, std, CV) from purchase order history
• [ ] Build a supplier scorecard with OTDR and fill rate
• [ ] Connect to a SQLite inventory database and join it with sales CSV data
• [ ] Generate prioritized reorder alerts with urgency levels
• [ ] Assemble a multi-sheet Excel dashboard from the combined analysis

One-Sentence Summaries

• Inventory Turnover: How many times does your entire stock cycle through per year?
• DIO: How many days of supply are you currently holding?
• Reorder Point: The stock level that tells you it is time to order before you run out.
• Safety Stock: The buffer that keeps you in stock when demand or lead times are unpredictable.
• EOQ: The order size that minimizes the combined cost of ordering and holding inventory.
• ABC Analysis: Focus management attention and capital where 80% of the value lives.
• Lead Time CV: How predictable is your supplier? Lower is better.
• OTDR: Is your supplier keeping their delivery promises?
• Days of Supply: At current consumption rates, how long until this item runs out?