Executive KPI Dashboards for Demand & Supply Risk

01. The problem

Reporting was manual, slow, and inconsistent. Different teams pulled different numbers, KPIs were recomputed by hand each cycle, and by the time a report reached leadership it was both stale and hard to trust. Capacity and pricing calls were being made on intuition rather than evidence — and there was no shared view of supply risk.

02. Approach

03. What I built

• A consolidated reporting pipeline (SQL + ETL) producing consistent, auditable KPI definitions.
• Self-serve Power BI dashboards for volume, margin, and supply-risk, with drill-downs by product and corridor.
• Demand forecasts integrated into the dashboards to support capacity and pricing decisions.
• A handoff structure so non-technical stakeholders could explore the data independently.

04. Impact

The dashboards shifted leadership conversations from "what do we think happened" to "what does the data show". Demand forecasts informed a major capacity investment, reporting cycles got faster and more trustworthy, and product managers and executives could finally self-serve — the exact kind of leverage that frees an analytics team to work on higher-value questions.

05. Data model & forecasting methodology

Under the dashboards sits a small star schema: a fact_trip table (one row per shipment — revenue, distance, on-time flag, empty kilometres) joined to dimension tables for corridor, date, and load type. A semantic layer defines the KPIs once — revenue, gross margin, margin rate, on-time %, supply-risk index — so every visual reads from a single, governed definition rather than ad-hoc spreadsheet formulas.

The demand forecast that informed the capacity decision is an OLS linear-trend model with a 95% prediction interval, fit on the weekly revenue and volume series. It is deliberately simple and transparent — the band, not just the point estimate, is what made it decision-grade.

def forecast(y, h=8):
    """OLS linear-trend forecast with a residual-based 95% prediction band."""
    t = np.arange(len(y)); A = np.column_stack([np.ones_like(t), t])
    coef = np.linalg.lstsq(A, y, rcond=None)[0]
    resid = y - A @ coef; s = resid.std(ddof=2)
    tf = np.arange(len(y), len(y) + h); Af = np.column_stack([np.ones_like(tf), tf])
    pred = Af @ coef
    se = s * np.sqrt(1 + 1/len(y) + (tf - t.mean())**2 / np.sum((t - t.mean())**2))
    return pred, pred - 1.96*se, pred + 1.96*se      # forecast + 95% band

Fig 3. Weekly revenue with an 8-week trend forecast and 95% band — the view behind the capacity call.

Fig 4. Margin pool by load type — perishable carries the richest rate, bulk the thinnest.

Fig 5. Supply-risk index by month (empty-capacity exposure proxy).

06. Interactive — the executive KPI explorer

A self-serve slice of the production dashboard. Switch the metric and filter by load type to watch the weekly trend, the trend forecast, and the headline KPI cards recompute — the same "answer your own question" experience the executive build delivered.