Supply Chain Forecast Diagnosis

Diagnose finished-goods quantity forecast performance, identify root causes of error, and deliver a formatted Excel report with optional industry and regional macro context.

Detailed metric formulas and methodology rationale are in reference/metrics.md.

Compatibility

• python>=3.8
• pandas>=1.5.0
• numpy>=1.21.0
• openpyxl>=3.0.0

Check imports before installing — only install what is missing:

missing = []
for pkg in ["pandas", "numpy", "openpyxl"]:
    try:
        __import__(pkg)
    except ImportError:
        missing.append(pkg)
if missing:
    import subprocess, sys
    subprocess.check_call([sys.executable, "-m", "pip", "install"] + missing)

Step 1 — Onboard

Ask only for information not already provided:

• Industry — if not stated, ask: "What industry are you in?" (e.g. building materials, HVAC, food & beverage, industrial supply). Skip if they already mentioned it.
• Data readiness — if they've already uploaded a file, go directly to Step 3. Otherwise ask: "Do you have your forecast vs actuals data ready, or would you like a template to fill out first?" Do not assume a file is blank because its filename contains template; inspect the required columns and row count first.

Step 2 — Generate Template

python scripts/generate_template.py

This creates four files in the current directory:

• supply_chain_forecast_diagnosis_template.csv / .xlsx — blank template for the user to fill in
• supply_chain_forecast_diagnosis_example.csv / .xlsx — reference example (do not mix with real data)

Tell the user:

• Required columns: sku, date, forecast_qty, actual_qty
• Optional but recommended: description, product_class, location, area, final_forecast_qty, exclude_flag, exclude_reason
• location = optional branch/site/warehouse/location. If present, Actions and SKU diagnostics are grouped by SKU + location; if absent, the skill still runs at SKU level.
• area = optional broader region/province/market for regional summaries and macro context.
• final_forecast_qty = the planner's complete finished-goods forecast quantity (the whole number, not a delta/adjustment)
• exclude_flag / exclude_reason = optional disruption/outlier exclusions with an audit trail
• Date accepts any common format — 2025-01-01, Jan 2025, 01/01/2025 — the script normalizes to YYYY-MM
• Once filled, upload the file and run /supply-chain-forecast-diagnosis again

Step 3 — Run Diagnosis

python scripts/run_diagnosis.py "<uploaded_file_path>"

Optional: --output custom_name.xlsx to set the output file name.

Optional: --exclude-months 2025-04,2025-05 to remove known disruption months before metrics run.

Portability note: scripts/... paths are relative to this SKILL.md's directory. If the Bash tool's working directory is elsewhere, cd into the skill directory first, or prefix the script with its full path.

The script handles everything:

1. Loads and validates the file (CSV or Excel)
2. Normalizes column names — see Column Name Tolerance below
3. Applies optional exclusions from exclude_flag and/or --exclude-months
4. Computes Detail-Level WMAPE, Demand-Only WMAPE, Supply Chain Accuracy, Finance/Sales Accuracy, Baseline WMAPE, Group-Level Monthly WMAPE, Group-Level Accuracy, Forecast Variance %, denominator coverage, zero-actual bucket diagnostics, and FVA (if final forecast data is present)
5. Runs ABC segmentation and ADI/CV² demand-pattern classification with confidence
6. Builds location and area breakdowns when those optional columns are present
7. Generates a prioritized actions worklist
8. Exports supply_chain_forecast_diagnosis_report.xlsx with Executive Narrative, Executive Summary charts, Preflight Validation, Coverage Diagnostics, Monthly Summary, SKU Month Detail, SKU Detail, Actions, optional Location Breakdown, optional Area Breakdown, How to Read This, and Methodology sheets
9. Adds a monthly Actual vs Baseline vs Final line chart, plus a WMAPE by Location or Area bar chart when available
10. Prints a structured summary to stdout

Read the printed output carefully — it contains all key metrics, top error contributors, tier counts, and location/area breakdowns needed for your narrative.

Step 4 — Industry + Regional Macro Research (conditional)

Run this step when all three conditions are true. If all three are true, this step is required before the final narrative:

• The user provided an industry (Step 1)
• The data contains an area or product_class column
• Web search is available in this Claude environment

If web search is available, search for:

1. "{industry} demand outlook {current_year} {area}" — regional demand trends
2. "{industry} distribution trends {current_year}" — sector dynamics
3. Relevant indicators for detected areas (housing starts, industrial capex, construction permits, etc.)

Summarize in 3–5 bullets per region. Cite sources. Clearly separate data findings from external research.

Skip entirely only if industry is unknown, data has no area/region breakdown, or web search is unavailable.

Step 5 — Narrative Summary

Deliver a plain-language summary using the script output:

1. Headline — Detail-Level WMAPE, Demand-Only WMAPE if relevant, Supply Chain Accuracy, Finance/Sales Accuracy, and whether performance is concerning for the stated industry
2. Forecast Variance — over or under-forecasting direction. Be explicit that this is directional forecast error, not proof of stockouts or inventory excess without inventory/on-hand data.
3. FVA verdict — are planner overrides helping or hurting? If FVA is negative, call out the top 3 SKU/location rows hurting FVA from the Executive Narrative or SKU Detail sheets. Skip if no final_forecast_qty column was present
4. Top error drivers — call out SKUs by error contribution %, not just highest WMAPE
5. Demand pattern flags — count Smooth, Erratic, Intermittent, Lumpy, and No Demand SKUs; use reference/metrics.md to recommend the appropriate forecast method by demand cluster
6. Macro context — only if Step 4 was run; cite sources; clearly separate from data findings
7. Next actions — point the user to the Actions sheet in the Excel report; highlight top 3 priorities by SKU/location when location is present, otherwise by SKU

Column Name Tolerance

The script performs exact and conservative fuzzy matching, then writes all mappings to the Preflight Validation sheet.

Key Principles

• Primary WMAPE = Detail-Level WMAPE with zero-actual misses included. Keep rows where `ac