ANALYZE-006: Slow-moving inventory analysis

[AliiiBenn]

ANALYZE-006: Slow-Moving Inventory Analysis

Overview

Implement slow-moving inventory analysis to identify products with no or minimal movement over extended periods, indicating potential overstock, obsolescence, or optimization opportunities.

Description

Slow-moving inventory analysis identifies products that haven't moved recently:

• Stale products (no movement > 90 days)
• Slow-moving products (minimal movement > 30 days)
• Dead stock (no movement > 180 days)
• Inventory value at risk
• Storage optimization opportunities

Technical Approach

Slow-Moving Detection Query

def analyze_slow_moving_inventory(conn, stale_threshold_days=90, slow_threshold_days=30):
    """
    Identify slow-moving and stale inventory.

    Args:
        conn: Database connection
        stale_threshold_days: Days without movement to consider "stale"
        slow_threshold_days: Days without movement to consider "slow"

    Returns:
        DataFrame with slow-moving inventory analysis
    """
    query = f"""
    WITH last_movement AS (
        SELECT
            p.no_produit,
            p.nom_produit,
            p.description,
            MAX(m.date_heure) as last_movement_date,
            COUNT(m.oid) as total_movements,
            SUM(CASE WHEN m.type = 'SORTIE' THEN 1 ELSE 0 END) as outbound_count,
            JULIANDAY('now') - JULIANDAY(MAX(m.date_heure)) as days_since_last
        FROM produits p
        LEFT JOIN mouvements m ON p.no_produit = m.no_produit
        GROUP BY p.no_produit
    ),
    with_status AS (
        SELECT
            *,
            CASE
                WHEN total_movements = 0 THEN 'NEVER_MOVED'
                WHEN days_since_last > 180 THEN 'DEAD_STOCK'
                WHEN days_since_last > {stale_threshold_days} THEN 'STALE'
                WHEN days_since_last > {slow_threshold_days} THEN 'SLOW'
                ELSE 'ACTIVE'
            END as inventory_status,
            COALESCE(prix_standard * 1, 0) as estimated_value
        FROM last_movement
    )
    SELECT
        no_produit,
        nom_produit,
        total_movements,
        outbound_count,
        last_movement_date,
        days_since_last,
        inventory_status,
        estimated_value
    FROM with_status
    WHERE inventory_status IN ('STALE', 'SLOW', 'DEAD_STOCK', 'NEVER_MOVED')
    ORDER BY days_since_last DESC, estimated_value DESC
    """

    return pd.read_sql_query(query, conn)

Output Format

Terminal Output

$ wareflow analyze --slow-moving

📦 Slow-Moving Inventory Analysis
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

Inventory Status:
  🔥 Active: 1,078 products (87.3%)
  ⚠️  Slow (>30 days): 89 products (7.2%)
  ❌ Stale (>90 days): 56 products (4.5%)
  💀 Dead Stock (>180 days): 11 products (0.9%)
  🚫 Never Moved: 2 products (0.2%)

Stale Products (>90 days, $45,230 at risk):
┌──────────┬─────────────────────┬──────────────┬────────────┬─────────────┐
│ SKU      │ Name                │ Last Move    │ Days Since │ Est. Value  │
├──────────┼─────────────────────┼──────────────┼────────────┼─────────────┤
│ 1001     │ Product Alpha       │ 2024-08-15   │ 156        │ $12,450     │
│ 1045     │ Product Beta        │ 2024-07-23   │ 179        │ $8,234      │
│ 1123     │ Product Gamma       │ 2024-07-12   │ 189        │ $6,789      │
│ 1089     │ Product Delta       │ 2024-06-28   │ 204        │ $5,432      │
└──────────┴─────────────────────┴──────────────┴────────────┴─────────────┘

Dead Stock (>180 days, $15,678 at risk):
  11 products, $15,678 total value
  Oldest: Product Omega (342 days, $3,456)

Never Moved:
  Product Zeta (introduced 2025-01-01, $1,234)
  Product Eta (introduced 2024-11-15, $2,345)

Financial Impact:
  Total value at risk: $60,908
  Potential storage cost: $1,827/year ($30/sq ft/year)
  Opportunity cost: ~$6,091/year (10% carrying cost)

Recommendations:
  💀 Consider liquidating 11 dead stock items (>$15K)
  💡 Review 56 stale products for discount/promotion
  💡 Investigate 2 never-moved products (quality issues?)
  💡 Optimize storage: Move slow items to back of warehouse
  💡 Set up automatic alerts for products > 120 days

Implementation Plan

Phase 1: Core Detection (1 day)

• Implement slow-moving detection query
• Calculate days since last movement
• Classify inventory status (ACTIVE, SLOW, STALE, DEAD)
• Format terminal output

Phase 2: Enhanced Analytics (1 day)

• Calculate financial impact (value at risk)
• Estimate storage costs
• Identify never-moved products
• Provide actionable recommendations
• Export to Excel with conditional formatting

CLI Usage

# Slow-moving overview
wareflow analyze --slow-moving

# Custom thresholds
wareflow analyze --slow-moving --stale 120 --slow 60

# Include value at risk
wareflow analyze --slow-moving --value

# Export to Excel
wareflow analyze --slow-moving --export slow_moving.xlsx

# Dead stock only
wareflow analyze --slow-moving --dead-only

# Never moved only
wareflow analyze --slow-moving --never-moved

Classification Criteria

Status	Definition	Days Since Last Move	Action
ACTIVE	Regular movement	≤ 30 days	Normal
SLOW	Minimal movement	31-90 days	Monitor
STALE	No movement	91-180 days	Review for liquidation
DEAD_STOCK	Obsolete	> 180 days	Consider write-off
NEVER_MOVED	No movement ever	N/A	Investigate (quality?)

Financial Impact Calculations

Value at Risk

-- Total value of slow/stale inventory
SUM(estimated_value)
WHERE inventory_status IN ('SLOW', 'STALE', 'DEAD_STOCK')

Storage Cost

Annual storage cost = value × storage_cost_rate
Typical rate: $30 per sq ft per year

Opportunity Cost

Annual carrying cost = value × carrying_cost_rate
Typical rate: 10-25% of inventory value

Success Criteria

• Identify all slow-moving products
• Classify by inventory status
• Calculate days since last movement
• Estimate financial impact
• Provide actionable recommendations
• Support custom thresholds
• Export to Excel with color coding

Actionable Recommendations

For Stale Inventory (91-180 days)

• Review for promotion/discount
• Consider bundle deals
• Evaluate for return to supplier
• Move to less expensive storage

For Dead Stock (>180 days)

• Liquidation sale
• Donate to charity (tax deduction)
• Write-off and recycle
• Auction if valuable

For Never-Moved Items

• Quality check (defective?)
• Verify listing errors
• Marketing/promotion needed
• Return to supplier if possible

Future Enhancements

• Seasonal Slow-Movers: Products slow only in off-season
• Trend Analysis: Products becoming slower over time
• Forecast-Based: Predict which products will become slow
• Multi-Warehouse: Consolidate slow items to fewer locations
• Automatic Alerts: Notify when products cross thresholds

Dependencies

Required

• CORE-002 (analyze command)
• Product and movement data
• Optional: Product value/cost data

Related Issues

• Depends on: CORE-002
• Related to: ANALYZE-001 (ABC Classification)
• Related to: ANALYZE-002 (Product Performance)

References

• Inventory management best practices
• Carrying cost calculations
• Obsolescence identification

Notes

This analysis is critical for inventory optimization:

• Reduces carrying costs
• Frees up warehouse space
• Improves cash flow
• Identifies quality issues

Key benefits:

• Financial: Recover capital tied up in dead stock
• Operational: Optimize storage space utilization
• Strategic: Better inventory turnover ratio

The analysis should trigger alerts for:

• Products crossing 90-day threshold
• Dead stock accumulation
• Never-moved new products