Inventory ABC-XYZ Matrix Analysis

The Prompt

This prompt transforms raw inventory data into a strategic segmentation framework with executable analysis:

<role>
You are a Supply Chain Analytics Director who has implemented inventory optimization programs at companies like Amazon, Walmart, and Toyota. You combine deep statistical expertise with practical implementation experience. Your recommendations balance working capital efficiency with service level requirements.
</role>

<task>
Perform a comprehensive ABC-XYZ inventory segmentation analysis on the provided data and deliver actionable stocking policies for each segment.
</task>

<context>
<business_parameters>
- Target service level: [e.g., 95% for A items, 90% for B items, 85% for C items]
- Review period: [e.g., Weekly, Monthly]
- Lead time range: [e.g., 2-6 weeks]
- Carrying cost percentage: [e.g., 25% annually]
- Stockout cost estimate: [e.g., $50 per incident or 3x product cost]
</business_parameters>

<data_description>
My Excel/CSV data contains:
- SKU/Item Number
- Item Description
- Annual Units Sold (or usage)
- Unit Cost
- Monthly demand for past 12-24 months (for variability calculation)
</data_description>

<raw_data>
[PASTE YOUR INVENTORY DATA HERE]
</raw_data>
</context>

<analysis_steps>
Execute these analyses in sequence with Python:

## Step 1: Data Validation & Preparation
- Parse the data and validate completeness
- Calculate: Annual revenue per SKU, average monthly demand, demand standard deviation
- Flag data quality issues

## Step 2: ABC Classification (Value-Based)
- Calculate cumulative revenue percentage
- Segment using Pareto principle:
  - A: Top items representing 80% of value
  - B: Next items representing 15% of value
  - C: Remaining items representing 5% of value
- Create Pareto curve visualization

## Step 3: XYZ Classification (Variability-Based)
- Calculate Coefficient of Variation (CV) for each SKU: CV = StdDev / Mean
- Segment by demand predictability:
  - X: CV < 0.5 (Highly predictable)
  - Y: CV 0.5-1.0 (Moderately variable)
  - Z: CV > 1.0 (Highly unpredictable)
- Create CV distribution histogram

## Step 4: 9-Box Matrix Construction
Create the combined matrix:

        |    X (Stable)    |   Y (Variable)   |  Z (Erratic)   |
--------|------------------|------------------|----------------|
A (High)|  AX - Strategic  | AY - Key Watch   | AZ - Critical  |
B (Med) |  BX - Stable     | BY - Standard    | BZ - Review    |
C (Low) |  CX - Routine    | CY - Opportune   | CZ - Consider  |


## Step 5: Visualization Suite
Generate these charts:
1. **Pareto Curve**: SKUs vs Cumulative Revenue %
2. **9-Box Scatter Plot**: Revenue (Y) vs CV (X), colored by segment
3. **Segment Distribution**: Pie charts for ABC and XYZ
4. **Matrix Heatmap**: Item count by segment cell

## Step 6: Stocking Policy Recommendations
For each of the 9 segments, provide:

| Segment | Reorder Policy | Safety Stock Method | Review Frequency | Automation Level |
|---------|----------------|---------------------|------------------|------------------|
| AX      | ?              | ?                   | ?                | ?                |
| ...     | ...            | ...                 | ...              | ...              |

Include specific formulas for:
- Safety stock calculation by segment
- Reorder point formulas
- Economic order quantity adjustments
</analysis_steps>

<output_format>
1. **Executive Dashboard** (first): Key metrics and the 9-box visual
2. **Detailed Analysis** (middle): All charts with interpretations
3. **Policy Matrix** (end): Complete table with formulas
4. **Appendix**: Full Python script for reproducibility

Include interpretation after EVERY chart explaining what it means for the business.
</output_format>

<success_criteria>
- All Python code must execute and render visualizations
- Every segment must have a specific, implementable policy
- Include working capital impact estimate
- Identify the top 10 items that need immediate attention
</success_criteria>

How to Use This Prompt

1. Export data from ERP: Pull SKU master with 12-24 months of transaction history
2. Include key columns: SKU, description, unit cost, and monthly demand columns
3. Set your parameters: Adjust service levels and cost factors for your business
4. Paste and run: The AI will execute full analysis with live charts
5. Apply policies: Use output to configure your inventory planning system

Sample Data Format

SKU,Description,UnitCost,Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec
SKU001,Widget A,45.00,120,135,128,142,138,145,152,148,155,160,158,165
SKU002,Component B,12.50,450,425,480,395,410,445,460,420,485,490,475,510
...

Advanced Follow-Ups

• “Now calculate the working capital reduction if we move all CZ items to vendor-managed inventory”
• “What’s the sensitivity of total safety stock to a 10% increase in demand variability?”
• “Generate a transition roadmap to move from current state to recommended policies”
• “Which items should we consider for drop-ship or make-to-order?”