Chapter 32: Exercises — Inventory and Supply Chain Analytics

These exercises progress through five tiers of difficulty. Complete each tier before moving to the next. All exercises assume Python 3.10+ with pandas, numpy, scipy, matplotlib, and openpyxl installed.

Tier 1: Foundations

These exercises verify that you understand the core concepts and formulas. Use the sample values provided — no real data required.

Exercise 1.1 — Inventory Turnover and DIO

A regional distributor reports the following annual figures: - Cost of Goods Sold: $4,200,000 - Opening inventory value (at cost): $480,000 - Closing inventory value (at cost): $540,000

Write Python code to calculate: 1. The average inventory value 2. The inventory turnover ratio 3. Days Inventory Outstanding (DIO) 4. If the industry benchmark is DIO of 40 days, is this company above or below benchmark?

Expected: Average inventory = $510,000; Turnover ≈ 8.24×; DIO ≈ 44.3 days

Exercise 1.2 — Reorder Point Calculation

A product has: - Average daily demand: 25 units - Average supplier lead time: 8 days - Safety stock: 40 units

Write a function called simple_reorder_point(avg_daily_demand, lead_time_days, safety_stock) that returns the reorder point. Test it with the values above.

Expected result: 240 units

Then test what happens when safety stock is 0. What does that tell you about a business that holds no safety stock?

Exercise 1.3 — EOQ Calculation

A product has: - Annual demand: 1,200 units - Order cost: $85 per order placed - Unit cost: $24 - Holding cost rate: 22% of unit cost per year

Calculate the EOQ. Then explain in plain language what this number means operationally — how would you use it?

Expected EOQ: approximately 197 units

Exercise 1.4 — Safety Stock Z-Scores

Write a Python snippet using scipy.stats.norm.ppf() to generate a table of service levels and their corresponding z-scores:

Service Level Z-Score
90% ?
95% ?
98% ?
99% ?

In plain language, what does a 95% service level mean? If you run 200 replenishment cycles per year and hold a 95% service level, roughly how many stockouts should you expect?

Exercise 1.5 — ABC Classification

Given these five SKUs with annual consumption values, determine the ABC classification manually, then verify using perform_abc_analysis() from the chapter:

SKU Annual Consumption Value
SKU-001 $85,000
SKU-002 $12,000
SKU-003 $4,500
SKU-004 $62,000
SKU-005 $1,200

Total = $164,700. SKU-001 is 51.6%. SKU-001 + SKU-004 = 89.5%.

What business actions would you recommend differently for an A item versus a C item?

Tier 2: Applied

These exercises require you to write complete, working Python functions.

Exercise 2.1 — Lead Time Variability Analysis

You have purchase order data for two suppliers:

import pandas as pd

po_data = pd.DataFrame({
    "supplier_id": ["SUP-A"] * 10 + ["SUP-B"] * 10,
    "order_date": (
        ["2023-01-03", "2023-01-31", "2023-03-01", "2023-03-29",
         "2023-04-26", "2023-05-24", "2023-06-21", "2023-07-19",
         "2023-08-16", "2023-09-13"] * 2
    ),
    "receipt_date": [
        # Supplier A: consistent 10-12 days
        "2023-01-13", "2023-02-11", "2023-03-12", "2023-04-09",
        "2023-05-07", "2023-06-04", "2023-07-02", "2023-07-29",
        "2023-08-27", "2023-09-24",
        # Supplier B: averages 10 days but varies widely
        "2023-01-13", "2023-02-08", "2023-03-16", "2023-04-06",
        "2023-05-09", "2023-06-07", "2023-07-01", "2023-07-30",
        "2023-08-28", "2023-09-26",
    ],
})
  1. Calculate mean and standard deviation of lead time for each supplier
  2. Calculate the coefficient of variation for each
  3. For a product with average daily demand of 15 units and demand std dev of 3 units, calculate the safety stock required at a 95% service level for each supplier
  4. Which supplier is easier to manage, even if their average lead times are similar? Why?

Exercise 2.2 — Building a Reorder Alert List

Write a function create_reorder_alerts(inventory_df) that: 1. Accepts a DataFrame with columns: sku_id, product_name, current_stock, reorder_point, avg_daily_demand, supplier_name 2. Returns only rows where current_stock <= reorder_point 3. Adds a days_of_supply column (current_stock / avg_daily_demand, return None if demand is 0) 4. Adds an urgency column: "CRITICAL" if days_of_supply < 3, "HIGH" if < 7, "MEDIUM" if < 14, "LOW" otherwise 5. Sorts the result by days_of_supply ascending

Test with a manually created DataFrame of at least 8 SKUs that includes examples of all four urgency levels.

Exercise 2.3 — Supplier Scorecard

Given this purchase order data:

po_scorecard_data = pd.DataFrame({
    "supplier_id": ["S1", "S1", "S1", "S1", "S1",
                    "S2", "S2", "S2", "S2", "S2",
                    "S3", "S3", "S3", "S3", "S3"],
    "promised_delivery_date": pd.date_range("2023-01-15", periods=15, freq="3W"),
    "receipt_date": pd.to_datetime([
        "2023-01-15", "2023-02-06", "2023-02-27", "2023-03-20", "2023-04-10",  # S1
        "2023-01-19", "2023-02-10", "2023-03-06", "2023-03-28", "2023-04-22",  # S2
        "2023-01-15", "2023-02-05", "2023-02-26", "2023-03-19", "2023-04-09",  # S3
    ]),
    "ordered_quantity": [200, 200, 200, 200, 200] * 3,
    "received_quantity": [200, 195, 200, 200, 200,    # S1
                          200, 200, 185, 200, 190,    # S2
                          200, 200, 200, 200, 200],   # S3
})

Calculate OTDR and fill rate for each supplier. Assign grades (A/B/C/D). Which supplier would you recommend for a critical A-item?

Exercise 2.4 — ABC Analysis From Scratch

Create a synthetic inventory catalog of 30 SKUs with annual consumption values distributed realistically (a few high-value items, many low-value items). Run ABC analysis and produce: 1. A summary table showing the number and value percentage in each category 2. The minimum number of SKUs that account for at least 75% of total value 3. A simple text description of your findings, as if you were presenting to a purchasing manager

Exercise 2.5 — EOQ Sensitivity

For a product with annual demand of 2,400 units, order cost $65, and unit cost $30 (holding rate 20%): 1. Calculate the EOQ 2. Calculate total annual cost at EOQ, at 0.5×EOQ, and at 2×EOQ 3. Express the cost penalty at each non-optimal quantity as a percentage above the EOQ cost 4. What does this tell you about how important it is to order exactly at EOQ versus approximately at EOQ?

Tier 3: Integration

These exercises require combining multiple techniques.

Exercise 3.1 — Full Reorder Parameter Calculator

Write a function calculate_all_reorder_params that accepts a DataFrame with columns (sku_id, annual_demand_units, demand_std_dev_daily, unit_cost, average_lead_time_days, lead_time_std_dev_days, order_cost_per_order, holding_cost_rate) and returns a new DataFrame with added columns for: - safety_stock (at 95% service level) - reorder_point - eoq - orders_per_year - estimated_annual_inventory_cost

Test with at least five SKUs that have meaningfully different demand and lead time characteristics. Discuss which SKU looks most "expensive" to manage from an inventory cost perspective and why.

Exercise 3.2 — ABC Analysis With Pareto Visualization

Using perform_abc_analysis() on a catalog of 50 SKUs (generate synthetic data with a realistic Pareto distribution), create: 1. A Pareto curve (cumulative value % vs. cumulative SKU % sorted by value) 2. A side-by-side bar chart showing SKU count % and value % for each ABC tier

import numpy as np
import pandas as pd

np.random.seed(7)
raw_values = np.sort(np.random.pareto(1.5, 50) * 15000)[::-1]
catalog = pd.DataFrame({
    "sku_id": [f"SKU-{i:03d}" for i in range(50)],
    "annual_consumption_value": raw_values,
})

Add commentary in your code explaining what a business should do differently for A, B, and C items.

Exercise 3.3 — Connecting SQLite and CSV

Write a complete script that: 1. Creates an in-memory SQLite database with an inventory table (15+ SKUs) 2. Loads the inventory data using sqlite3 and pandas 3. Creates synthetic daily sales data for those SKUs (last 90 days) 4. Joins the data using the join logic from section 32.8 5. Generates a reorder alert report

This end-to-end script is the core pattern of the chapter. No shortcuts — implement the full pipeline.

Exercise 3.4 — Slow-Moving Inventory Identification

Write a function identify_slow_movers(inventory_df, sales_df, slow_days, obsolete_days) that: 1. Calculates days since last sale for each SKU 2. Classifies each SKU as "Active", "Slow-Moving", or "Potentially Obsolete" 3. Returns the total working capital tied up in each category 4. Returns the top 5 most valuable items in each non-active category

Use threshold of 60 days for slow-moving, 120 days for potentially obsolete. Test with a mix of active and stale SKUs.

Exercise 3.5 — Multi-Region Inventory Summary

Write a function regional_inventory_summary(enriched_inventory_df) that takes the output of join_inventory_with_sales() and produces a summary table grouped by region showing: - Total SKU count - Total inventory value - Number and percentage of SKUs below reorder point - Average days of supply - Number of A/B/C items per region

Format the output so it could be pasted directly into an email.

Tier 4: Challenge

Exercise 4.1 — EOQ Sensitivity Tornado Chart

Build an EOQ sensitivity analysis that: 1. Takes a base case: annual_demand=3000, order_cost=$75, unit_cost=$20, holding_rate=0.20 2. Varies each parameter by ±10%, ±25%, ±50% 3. Shows the resulting change in EOQ and total annual cost for each variation 4. Creates a tornado chart (horizontal bar chart) showing which parameter has the largest impact on EOQ

This analysis answers the practical question: "How much does it matter if my demand estimate is wrong?"

Exercise 4.2 — Service Level vs. Safety Stock Tradeoff

For a product with average daily demand of 30 units, demand std dev of 6 units, average lead time of 12 days, and lead time std dev of 2 days:

  1. Calculate safety stock for service levels from 80% to 99.9% in 0.1% steps
  2. Calculate the annual holding cost of that safety stock (unit cost $15, holding rate 20%)
  3. If each expected stockout costs the business $800 in lost margin and service recovery, plot the total cost (holding + expected stockout cost) vs. service level
  4. Find the service level that minimizes total cost
  5. Discuss whether minimizing total cost is always the right objective — when might you choose a higher service level anyway?

Exercise 4.3 — Complete Supply Chain Health Score

Design a "Supply Chain Health Score" (0-100) that combines: - Inventory turnover vs. benchmark (weight: 25%) - Percentage of SKUs above reorder point (weight: 30%) - Average supplier OTDR (weight: 25%) - Average fill rate (weight: 20%)

Implement calculate_supply_chain_health_score(turnover, benchmark_turnover, pct_above_rop, avg_otdr, avg_fill_rate). Define clear thresholds for Poor/Acceptable/Good/Excellent. Test with three scenarios representing genuinely different performance levels.

Exercise 4.4 — Automated Weekly Report Generator

Build a function generate_weekly_supply_chain_report(db_path, sales_csv, po_csv) that: 1. Runs the full dashboard calculation 2. Produces a formatted plain-text report with: - Executive summary (3-5 bullet points) - Top 5 items requiring immediate reorder action - Top 3 supplier issues based on scorecard - One recommended action the purchasing team should take this week 3. Saves the report to a timestamped file

This is the kind of automation that makes a data analyst genuinely irreplaceable.

Tier 5: Stretch

Exercise 5.1 — Multi-Echelon Safety Stock

A company has one central warehouse and four regional warehouses. Under the "square root law" of inventory pooling, consolidating N locations into 1 reduces safety stock by a factor of sqrt(N).

  1. Implement this rule as a Python function
  2. If each regional warehouse holds $120,000 in safety stock, how much would a single central warehouse need?
  3. But consolidating means longer delivery times to customers, which increases required safety stock at retail locations. Model this tradeoff.
  4. At what number of regional warehouses does the total system safety stock reach a minimum?

Exercise 5.2 — Dynamic Reorder Points

Standard reorder points are static. Seasonal businesses need dynamic reorder points.

  1. Using 12 months of daily demand data (generate with seasonal pattern), fit a seasonal model
  2. Calculate a reorder point for each month of the year based on that month's expected demand
  3. Compare total safety stock investment: static (one reorder point for all months) vs. dynamic (monthly reorder points)
  4. Estimate how much the dynamic approach reduces stockout risk in peak months and reduces excess inventory in slow months

Exercise 5.3 — Inventory Simulation

Use Monte Carlo simulation to validate that your safety stock calculation actually achieves the target service level:

  1. Simulate 365 days of inventory for one SKU with stochastic demand and lead time
  2. Track daily inventory level, stockout events, and order history
  3. Calculate the realized service level from the simulation
  4. Verify it is close to the theoretical 95% (or whatever level you set)
  5. Run the simulation 50 times and report the distribution of realized service levels

This exercise reveals whether the mathematical assumptions underlying safety stock calculations hold in practice.

Exercise 5.4 — Supplier Consolidation Analysis

Acme works with 12 suppliers, many supplying overlapping categories.

Build an analysis that: 1. Groups suppliers by category overlap 2. For each potential consolidation opportunity, estimates the savings (fewer orders, volume discounts) and risks (single-source dependency, reduced competition) 3. Identifies which SKUs have only one qualified supplier (a supply chain risk) 4. Produces a ranked list of consolidation recommendations with estimated annual savings per recommendation

Use synthetic data that realistically models a 12-supplier, 100-SKU distributor.

Exercise 5.5 — End-to-End Automated Pipeline

Build a complete supply chain monitoring pipeline that could run as a scheduled daily job:

  1. Loads data from all three sources (database, sales CSV, PO CSV)
  2. Calculates the full dashboard
  3. Compares KPIs to configurable thresholds
  4. Writes alert notifications to a log file when thresholds are breached
  5. Saves a dated snapshot to a history folder
  6. Produces a human-readable daily summary report

Document how you would schedule this to run automatically every morning at 6 AM on both Windows (Task Scheduler) and Linux (cron).

Answer Key — Tier 1

1.1: Average inventory = $510,000; Turnover = $4,200,000 / $510,000 = 8.24×; DIO = 365 / 8.24 = 44.3 days. Company is above the 40-day benchmark (higher DIO = holding more inventory than needed).

1.2: ROP = (25 × 8) + 40 = 240 units. With safety stock = 0: ROP = 200 units. That means any delay or demand spike during lead time will cause a stockout — no buffer exists.

1.3: EOQ = sqrt(2 × 1200 × 85 / (24 × 0.22)) = sqrt(204,000 / 5.28) ≈ 197 units. This means you should order 197 units approximately 6 times per year (every ~61 days).

1.4: Z-scores: 90% → 1.282, 95% → 1.645, 98% → 2.054, 99% → 2.326. At 95% service level with 200 cycles/year, you expect approximately 10 stockouts per year (5% × 200).

1.5: Sorted: SKU-001 ($85K, 51.6% cumulative) → A; SKU-004 ($62K, 89.2% cumulative) → A; SKU-002 ($12K, 96.5% cumulative) → B; SKU-003 ($4.5K, 99.2%) → C; SKU-005 ($1.2K, 100%) → C.