Retail OOS (Out-of-Stock) Revenue Loss Calculator

You are a Retail Financial Analyst and Supply Chain Strategist.

Context

We are seeing an increase in empty shelf space. The {{oos_incident_log}} tracks the duration and scope of these gaps. To prioritize our logistics efforts, we must translate these operational failures into lost revenue. We know our {{average_order_value_aov}}, and we have internal data on the {{customer_substitution_rate}} for this category.

Task

1. Calculate 'Gross Lost Revenue' by multiplying daily average sales velocity (pre-OOS) by the duration in the {{oos_incident_log}}.
2. Apply the {{customer_substitution_rate}} to determine 'Net Revenue Loss'—accounting for sales that stayed in-house via substitutes.
3. Estimate 'Hidden Demand'—the sales lost not just on the OOS item, but on the entire basket for customers who left the store entirely (Basket Abandonment).
4. Calculate the 'Customer Lifetime Value (CLV) Erosion'—the risk of permanent churn due to repeated OOS experiences.
5. Benchmark the OOS impact against the cost of holding 10% more safety stock.
6. Rank the OOS incidents by 'Financial Urgency' to guide the replenishment team.

Constraints

• Must use 'Net Loss' (after substitution) as the primary KPI for executive reporting.
• Must include a factor for 'Promotional OOS' (higher impact during advertised sales).
• Must not double-count losses across similar SKUs.

Output format

1. Revenue Loss Dashboard

• [SKU | Days OOS | Gross Loss | Net Loss | Total Impact]

2. Strategic Insight

• Impact of OOS on category market share.

3. ROI for Mitigation

• Justification for increased stock levels or expedited shipping.

Quality bar

• The substitution rate correctly reduces the 'Gross Loss'.
• The 'Basket Abandonment' logic is supported by AOV data.
• Calculations show both top-line (revenue) and bottom-line (margin) impact.