Inventory Optimization

Project Overview

You are a supply chain analyst at RetailMax, a multi-channel retailer with a warehouse operation serving both online and brick-and-mortar stores. The operations director has flagged two problems:

• Excess inventory: $2.3M in slow-moving stock is tying up warehouse space and capital.
• Stockouts: 12% of orders in the last quarter had at least one backordered item, causing customer dissatisfaction.

Your goal is to build an inventory optimization model that classifies products using ABC analysis, calculates reorder points and safety stock, and forecasts short-term demand using moving averages.

Dataset Description

The project uses inventory and sales data from the warehouse management system.

SQL Analysis

These queries extract the key metrics needed for inventory optimization decisions.

Query 1: Sales Velocity and Stock Coverage

Calculates average daily sales per product and how many days of stock remain at the current sell rate.

SELECT
    i.product_id,
    i.product_name,
    i.category,
    i.current_stock,
    ROUND(AVG(ds.quantity_sold), 2)          AS avg_daily_sales,
    ROUND(STDDEV(ds.quantity_sold), 2)       AS sales_std_dev,
    CASE
        WHEN AVG(ds.quantity_sold) > 0
        THEN ROUND(i.current_stock / AVG(ds.quantity_sold), 1)
        ELSE NULL
    END                                       AS days_of_stock_remaining,
    i.supplier_lead_time_days
FROM inventory i
LEFT JOIN daily_sales ds ON i.product_id = ds.product_id
    AND ds.sale_date >= DATE_SUB(CURDATE(), INTERVAL 90 DAY)
GROUP BY i.product_id, i.product_name, i.category,
         i.current_stock, i.supplier_lead_time_days
ORDER BY days_of_stock_remaining ASC;

Query 2: ABC Classification by Revenue

Classifies products into A (top 80% of revenue), B (next 15%), and C (bottom 5%) categories using cumulative percentages.

WITH product_revenue AS (
    SELECT
        i.product_id,
        i.product_name,
        SUM(ds.quantity_sold * i.unit_price) AS total_revenue
    FROM inventory i
    JOIN daily_sales ds ON i.product_id = ds.product_id
    GROUP BY i.product_id, i.product_name
),
ranked AS (
    SELECT *,
        SUM(total_revenue) OVER (ORDER BY total_revenue DESC) AS cumulative_revenue,
        SUM(total_revenue) OVER ()                             AS grand_total
    FROM product_revenue
)
SELECT
    product_id,
    product_name,
    total_revenue,
    ROUND(cumulative_revenue / grand_total * 100, 2) AS cumulative_pct,
    CASE
        WHEN cumulative_revenue / grand_total <= 0.80 THEN 'A'
        WHEN cumulative_revenue / grand_total <= 0.95 THEN 'B'
        ELSE 'C'
    END AS abc_class
FROM ranked
ORDER BY total_revenue DESC;

Query 3: Slow-Moving and Dead Stock

Identifies products with high stock levels but minimal recent sales, candidates for clearance or discontinuation.

SELECT
    i.product_id,
    i.product_name,
    i.current_stock,
    i.current_stock * i.unit_cost  AS inventory_value,
    COALESCE(SUM(ds.quantity_sold), 0) AS units_sold_90_days,
    CASE
        WHEN COALESCE(SUM(ds.quantity_sold), 0) = 0 THEN 'Dead Stock'
        WHEN COALESCE(SUM(ds.quantity_sold), 0) < 10 THEN 'Slow Moving'
        ELSE 'Active'
    END AS stock_status
FROM inventory i
LEFT JOIN daily_sales ds ON i.product_id = ds.product_id
    AND ds.sale_date >= DATE_SUB(CURDATE(), INTERVAL 90 DAY)
GROUP BY i.product_id, i.product_name, i.current_stock, i.unit_cost
HAVING stock_status IN ('Dead Stock', 'Slow Moving')
ORDER BY inventory_value DESC;

Query 4: Stockout Impact Analysis

Quantifies the business impact of stockouts by product, showing both frequency and estimated lost revenue.

SELECT
    i.product_name,
    i.category,
    COUNT(sl.stockout_id)               AS stockout_events,
    SUM(sl.days_out_of_stock)           AS total_days_out,
    SUM(sl.estimated_lost_sales)        AS total_lost_units,
    SUM(sl.estimated_lost_sales * i.unit_price) AS estimated_lost_revenue
FROM stockouts_log sl
JOIN inventory i ON sl.product_id = i.product_id
GROUP BY i.product_name, i.category
ORDER BY estimated_lost_revenue DESC
LIMIT 15;

Python Analysis

Use Python to perform ABC analysis, calculate reorder points and safety stock, and create demand forecasts.

Data Loading and ABC Analysis

import pandas as pd
import numpy as np
import matplotlib.pyplot as plt

# Load data
inventory = pd.read_csv('inventory.csv')
daily_sales = pd.read_csv('daily_sales.csv', parse_dates=['sale_date'])

# Calculate total revenue per product
product_revenue = daily_sales.merge(inventory[['product_id', 'unit_price']], on='product_id')
product_revenue['revenue'] = product_revenue['quantity_sold'] * product_revenue['unit_price']

abc = product_revenue.groupby('product_id')['revenue'].sum().reset_index()
abc = abc.merge(inventory[['product_id', 'product_name', 'category']], on='product_id')
abc = abc.sort_values('revenue', ascending=False).reset_index(drop=True)

# Calculate cumulative percentage
abc['cumulative_revenue'] = abc['revenue'].cumsum()
abc['cumulative_pct'] = abc['cumulative_revenue'] / abc['revenue'].sum() * 100

# Assign ABC class
abc['abc_class'] = np.where(
    abc['cumulative_pct'] <= 80, 'A',
    np.where(abc['cumulative_pct'] <= 95, 'B', 'C')
)

# Summary
abc_summary = abc.groupby('abc_class').agg(
    product_count=('product_id', 'count'),
    total_revenue=('revenue', 'sum')
).reset_index()
abc_summary['revenue_pct'] = (abc_summary['total_revenue'] / abc_summary['total_revenue'].sum() * 100).round(1)
abc_summary['product_pct'] = (abc_summary['product_count'] / abc_summary['product_count'].sum() * 100).round(1)

print("ABC Analysis Summary:")
print(abc_summary.to_string(index=False))

Reorder Point and Safety Stock Calculation

# Calculate daily demand statistics per product (last 90 days)
cutoff = daily_sales['sale_date'].max() - pd.Timedelta(days=90)
recent = daily_sales[daily_sales['sale_date'] >= cutoff]

demand_stats = recent.groupby('product_id').agg(
    avg_daily_demand=('quantity_sold', 'mean'),
    std_daily_demand=('quantity_sold', 'std'),
    total_sold=('quantity_sold', 'sum')
).reset_index()

# Merge with inventory data
inv_analysis = inventory.merge(demand_stats, on='product_id', how='left')
inv_analysis = inv_analysis.merge(abc[['product_id', 'abc_class']], on='product_id', how='left')
inv_analysis['std_daily_demand'] = inv_analysis['std_daily_demand'].fillna(0)

# Service level Z-scores: A=99% (2.33), B=95% (1.65), C=90% (1.28)
z_scores = {'A': 2.33, 'B': 1.65, 'C': 1.28}
inv_analysis['z_score'] = inv_analysis['abc_class'].map(z_scores)

# Safety Stock = Z * std_demand * sqrt(lead_time)
inv_analysis['safety_stock'] = (
    inv_analysis['z_score'] *
    inv_analysis['std_daily_demand'] *
    np.sqrt(inv_analysis['supplier_lead_time_days'])
).round(0).astype(int)

# Reorder Point = (avg_daily_demand * lead_time) + safety_stock
inv_analysis['reorder_point'] = (
    inv_analysis['avg_daily_demand'] * inv_analysis['supplier_lead_time_days'] +
    inv_analysis['safety_stock']
).round(0).astype(int)

# Flag items that need reordering now
inv_analysis['needs_reorder'] = inv_analysis['current_stock'] <= inv_analysis['reorder_point']

reorder_now = inv_analysis[inv_analysis['needs_reorder']]
print(f"\nProducts needing immediate reorder: {len(reorder_now)}")
print(reorder_now[['product_name', 'abc_class', 'current_stock',
                    'reorder_point', 'safety_stock']].to_string(index=False))

Demand Forecasting with Moving Averages

# Weekly demand aggregation for a sample product
sample_product = inv_analysis.loc[inv_analysis['abc_class'] == 'A', 'product_id'].iloc[0]
product_sales = daily_sales[daily_sales['product_id'] == sample_product].copy()
product_sales = product_sales.set_index('sale_date').resample('W')['quantity_sold'].sum()

# Calculate moving averages
product_sales_df = product_sales.reset_index()
product_sales_df.columns = ['week', 'units_sold']
product_sales_df['ma_4week'] = product_sales_df['units_sold'].rolling(4).mean()
product_sales_df['ma_8week'] = product_sales_df['units_sold'].rolling(8).mean()

# Forecast next 4 weeks using weighted moving average
weights = [0.4, 0.3, 0.2, 0.1]  # More weight to recent weeks
last_4 = product_sales_df['units_sold'].tail(4).values
forecast = sum(w * v for w, v in zip(weights, last_4))
print(f"\n4-week weighted forecast for product {sample_product}: {forecast:.0f} units/week")

# Visualization
fig, axes = plt.subplots(2, 2, figsize=(14, 10))

# Plot 1: ABC Pareto Chart
ax1 = axes[0, 0]
colors_abc = {'A': '#e07a2f', 'B': '#4a90d9', 'C': '#999999'}
for cls in ['A', 'B', 'C']:
    mask = abc['abc_class'] == cls
    ax1.bar(abc.loc[mask].index, abc.loc[mask, 'revenue'], color=colors_abc[cls], label=f'Class {cls}')
ax1_twin = ax1.twinx()
ax1_twin.plot(abc.index, abc['cumulative_pct'], color='red', linewidth=2)
ax1_twin.axhline(y=80, color='red', linestyle='--', alpha=0.5)
ax1_twin.axhline(y=95, color='red', linestyle='--', alpha=0.5)
ax1_twin.set_ylabel('Cumulative %')
ax1.set_title('ABC Analysis - Pareto Chart', fontweight='bold')
ax1.set_ylabel('Revenue ($)')
ax1.legend()

# Plot 2: Demand Forecast
ax2 = axes[0, 1]
ax2.plot(product_sales_df['week'], product_sales_df['units_sold'],
         label='Actual', color='#333', alpha=0.5)
ax2.plot(product_sales_df['week'], product_sales_df['ma_4week'],
         label='4-Week MA', color='#e07a2f', linewidth=2)
ax2.plot(product_sales_df['week'], product_sales_df['ma_8week'],
         label='8-Week MA', color='#4a90d9', linewidth=2)
ax2.set_title('Demand Forecast (Moving Averages)', fontweight='bold')
ax2.set_ylabel('Units Sold')
ax2.legend()
plt.setp(ax2.xaxis.get_majorticklabels(), rotation=45)

# Plot 3: Current Stock vs Reorder Point
ax3 = axes[1, 0]
top_a = inv_analysis[inv_analysis['abc_class'] == 'A'].head(10)
x = range(len(top_a))
ax3.bar(x, top_a['current_stock'], label='Current Stock', color='#4a90d9', width=0.4)
ax3.bar([i + 0.4 for i in x], top_a['reorder_point'], label='Reorder Point',
        color='#e07a2f', width=0.4)
ax3.set_xticks([i + 0.2 for i in x])
ax3.set_xticklabels(top_a['product_name'].str[:15], rotation=45, ha='right', fontsize=8)
ax3.set_title('Stock Level vs Reorder Point (Class A)', fontweight='bold')
ax3.set_ylabel('Units')
ax3.legend()

# Plot 4: Inventory Value by ABC Class
ax4 = axes[1, 1]
inv_analysis['inventory_value'] = inv_analysis['current_stock'] * inv_analysis['unit_cost']
class_value = inv_analysis.groupby('abc_class')['inventory_value'].sum()
ax4.pie(class_value, labels=[f'Class {c}\n${v:,.0f}' for c, v in class_value.items()],
        colors=[colors_abc[c] for c in class_value.index],
        autopct='%1.1f%%', startangle=90)
ax4.set_title('Inventory Value by ABC Class', fontweight='bold')

plt.suptitle('Inventory Optimization Dashboard', fontsize=16, fontweight='bold')
plt.tight_layout()
plt.savefig('inventory_optimization.png', dpi=150)
plt.show()

Excel Dashboard Instructions

Build an inventory management dashboard that the operations team can use daily to monitor stock levels and trigger reorders.

Key Insights & Recommendations

Present these findings to the operations and finance teams to drive inventory optimization decisions.

Knowledge Check Quiz

Test your understanding of inventory optimization concepts.