Safety Stock Calculation and Warehouse Capacity Analysis

Safety stock is the buffer inventory held to protect against demand uncertainty and supplier delays. This lesson shows how to calculate it in SQL and combines it with warehouse capacity analysis.

1. Safety Stock Formula

Safety Stock = Z × σ_demand × √(lead_time)

Where:

Z = service level factor (1.65 for 95% service level)
σ_demand = standard deviation of demand
lead_time = supplier lead time in days

We approximate demand variability using purchase order quantity variance:

WITH demand_stats AS (
    SELECT
        po.warehouse_id,
        po.sku_id,
        AVG(po.quantity)    AS avg_demand,
        STDDEV(po.quantity) AS stddev_demand,
        sup.lead_time_days
    FROM lg_purchase_orders po
    JOIN lg_skus s     ON s.id = po.sku_id
    JOIN lg_suppliers sup ON sup.id = po.supplier_id
    GROUP BY po.warehouse_id, po.sku_id, sup.lead_time_days
)
SELECT
    w.code,
    s.sku,
    s.name,
    i.quantity                           AS current_stock,
    ROUND((ds.avg_demand)::NUMERIC, 0)              AS avg_order_qty,
    ROUND((ds.stddev_demand)::NUMERIC, 1)           AS demand_stddev,
    ds.lead_time_days,
    ROUND((1.65 * COALESCE(ds.stddev_demand, 0) * SQRT(ds.lead_time_days))::NUMERIC, 0) AS safety_stock_95pct,
    ROUND((1.28 * COALESCE(ds.stddev_demand, 0) * SQRT(ds.lead_time_days))::NUMERIC, 0) AS safety_stock_90pct
FROM demand_stats ds
JOIN lg_inventory i    ON i.warehouse_id = ds.warehouse_id AND i.sku_id = ds.sku_id
JOIN lg_warehouses w   ON w.id = i.warehouse_id
JOIN lg_skus s         ON s.id = i.sku_id
ORDER BY safety_stock_95pct DESC;

2. Inventory Below Safety Stock

Flag SKUs where current stock is below the calculated safety stock:

WITH safety AS (
    SELECT
        po.warehouse_id,
        po.sku_id,
        ROUND((1.65 * COALESCE(STDDEV(po.quantity), 0)
            * SQRT(MAX(sup.lead_time_days)))::NUMERIC, 0) AS safety_stock
    FROM lg_purchase_orders po
    JOIN lg_suppliers sup ON sup.id = po.supplier_id
    GROUP BY po.warehouse_id, po.sku_id
)
SELECT
    w.code,
    s.sku,
    s.name,
    i.quantity AS current_stock,
    sf.safety_stock,
    sf.safety_stock - i.quantity AS deficit
FROM safety sf
JOIN lg_inventory i  ON i.warehouse_id = sf.warehouse_id AND i.sku_id = sf.sku_id
JOIN lg_warehouses w ON w.id = i.warehouse_id
JOIN lg_skus s       ON s.id = i.sku_id
WHERE i.quantity < sf.safety_stock
ORDER BY deficit DESC;

3. Warehouse Capacity Utilisation

Estimate space used based on SKU weight as a proxy for volume:

SELECT
    w.code,
    w.city,
    w.capacity_m3,
    SUM(i.quantity * s.weight_kg)                                          AS total_weight_kg,
    ROUND((100.0 * SUM(i.quantity * s.weight_kg) / NULLIF(w.capacity_m3 * 500, 0))::NUMERIC, 1) AS estimated_util_pct,
    COUNT(DISTINCT i.sku_id)                                               AS distinct_skus,
    SUM(i.quantity * s.unit_cost)                                          AS inventory_value_usd
FROM lg_inventory i
JOIN lg_warehouses w ON w.id = i.warehouse_id
JOIN lg_skus s       ON s.id = i.sku_id
GROUP BY w.id, w.code, w.city, w.capacity_m3
ORDER BY estimated_util_pct DESC;

What This Returns

code  | city        | capacity_m3 | total_weight_kg | estimated_util_pct | inventory_value_usd
------+-------------+-------------+-----------------+--------------------+--------------------
US-NY | New York    |       50000 |        12450.30 |               49.8 |           482300.00
...

4. Overstocked SKUs

Identify SKUs where stock > 3× the reorder quantity (over-purchasing risk):

SELECT
    w.code,
    s.sku,
    s.name,
    i.quantity,
    200,
    ROUND(i.quantity::NUMERIC / NULLIF(200, 0), 1) AS stock_to_reorder_ratio,
    ROUND((s.unit_cost * (i.quantity - 200 * 2))::NUMERIC, 2) AS excess_inventory_value_usd
FROM lg_inventory i
JOIN lg_warehouses w ON w.id = i.warehouse_id
JOIN lg_skus s       ON s.id = i.sku_id
WHERE i.quantity > 200 * 3
ORDER BY excess_inventory_value_usd DESC;

Key Takeaway

STDDEV(quantity) in PostgreSQL calculates sample standard deviation, which is appropriate for demand variability calculations. The √lead_time term in the safety stock formula accounts for the fact that variability compounds over longer supply chains. Always validate that STDDEV has enough data points (at least 10 orders) before using it — for sparse data, use the supplier's stated reliability_score to estimate variability instead.

← Previous Next →