Building Supply Chain KPI Dashboards with AI
A good supply chain dashboard answers three questions: Are we meeting customer promise? Are we running efficiently? Are we protecting cash? AI doesn't replace your BI tool, but it can help you design the KPIs, write the SQL or formulas, and turn raw data into narratives that leadership reads.
What You'll Learn
- The 12 core supply chain KPIs and how they relate to each other
- Using AI to generate SQL, Excel, and Power BI formulas
- Writing dashboard narratives for S&OP and ops reviews
- Diagnosing root causes from dashboard anomalies
The 12 Core Supply Chain KPIs
Every SCM dashboard should include a version of these 12:
Customer service
- OTIF (On Time In Full)
- Perfect Order %
- Customer-reported fill rate
Inventory 4. Days Inventory Outstanding (DIO) 5. Inventory turns 6. Excess & Obsolete (E&O) %
Cost 7. Total landed cost per unit 8. Freight cost as % of revenue 9. Purchase Price Variance (PPV)
Operations 10. Supplier OTIF 11. Warehouse productivity (units per labor hour) 12. Forecast accuracy (MAPE)
Ask AI to help frame the set:
"We are a mid-market B2B distributor, $200M revenue. Design a supply chain KPI dashboard covering customer service, inventory, cost, and operations. For each KPI: (1) definition, (2) formula, (3) typical industry target, (4) common root causes when it goes red, (5) what it correlates with. Output as a markdown table for our ops leadership."
Generating SQL and Excel Formulas
Most SCMs are not SQL experts. AI writes the query for you.
"Our ERP (Oracle EBS) has tables: OE_ORDER_HEADERS_ALL, OE_ORDER_LINES_ALL, WSH_DELIVERY_DETAILS, MTL_SYSTEM_ITEMS_B. Write a SQL query that returns daily OTIF % for the last 90 days, broken down by customer segment and SKU category. Assume an order is 'on time' if ship_date <= promised_ship_date, and 'in full' if shipped_qty >= ordered_qty. Include proper joins and aliases. Comment every major step."
For Excel users:
"I have a sheet with columns: PO#, Supplier, Promised Date, Actual Receipt Date, Qty Ordered, Qty Received. Write formulas for cells that calculate (1) OTIF % by supplier, (2) average days early/late, (3) a conditional format that flags any supplier with OTIF < 90% over 30 rolling days."
AI writes the formulas and even explains how to set up named ranges and pivot tables.
Power BI / Tableau Measures
"Write a DAX measure for OTIF % in Power BI. Fact table is 'ShipmentDetails' with columns ShipmentID, PromisedDate, ActualShipDate, QtyOrdered, QtyShipped. Also write a measure for rolling 30-day OTIF. Explain what ALL and DATESINPERIOD do in the expression."
Dashboard Narratives
The numbers are only half the story. Every dashboard should come with a 2-3 paragraph narrative that leaders actually read. AI writes it from the data.
"Below are this month's KPI values with prior-month values. Write a 250-word narrative for the operations leadership review: what's green, what's red, most important 2-3 issues, recommended actions. Tone: direct, no jargon, accountable. End with 3 questions we need the team to answer by next review. [paste KPI table]"
Root Cause Analysis from Anomalies
When a KPI moves the wrong way, the first reaction should be why before who.
"Our OTIF dropped from 95% to 88% this month. Possible root causes fall into buckets: demand volatility, supplier issues, inventory position, warehouse execution, carrier issues, data quality. Ask me clarifying questions to narrow down. Use a systematic diagnostic tree."
AI will walk through a structured diagnosis with you — often surfacing a root cause your team wouldn't have thought to check.
Correlating Multiple KPIs
KPIs don't move independently. AI can spot correlations.
"Below are 12 months of KPI values (OTIF, DIO, PPV, forecast accuracy, supplier OTIF, warehouse productivity). Identify any statistically suggestive correlations or lagged relationships. Example: does a drop in forecast accuracy lead to DIO increase 2 months later? Propose 2 hypotheses for deeper analysis. [paste data]"
This is pattern recognition — AI's home turf.
Designing an Executive 1-Page View
Leadership cares about 5-8 metrics, not 40.
"Design a 1-page executive dashboard layout covering the top 6 supply chain KPIs for our CEO. For each: what you track, how you display it (big number, trend line, traffic light), and the 1 supporting detail a CEO would want on hover. Assume we're building in Tableau."
Building a 'Metric Dictionary'
One of the sneakiest causes of bad dashboards: different teams calculate the same KPI differently. Build a dictionary.
"Produce a metric dictionary for our supply chain dashboard. For each of these 12 KPIs: (1) definition, (2) formula with numerator/denominator, (3) data source, (4) refresh cadence, (5) owner, (6) common traps and exclusions. Output as a reference table."
This becomes your single source of truth.
Presenting KPIs at S&OP
"Prepare a 5-slide S&OP KPI deck outline covering the top KPIs for the month. Each slide: headline metric, trend vs prior 6 months, 2 key drivers, 1 recommended decision. Assume audience is functional VPs (Sales, Ops, Finance, Supply)."
Cautions
- Always verify AI-written SQL and formulas against a known subset of data before publishing
- Beware confident misinterpretation. AI may misread trend direction or mislabel red/green
- Data quality first. A dashboard built on bad data is a faster way to be wrong
Key Takeaways
- Cover 12 core supply chain KPIs across customer service, inventory, cost, and operations
- AI writes SQL, Excel, and DAX formulas well — verify against a known sample before publishing
- Every dashboard needs a narrative layer; AI drafts it from the KPI table
- Use AI for structured root-cause diagnosis when a KPI moves red
- Build and maintain a metric dictionary so teams don't disagree on how a KPI is calculated