KPI design, plan vs fact, variance and sales analysis

KPI design — the KPI tree

Definition. A KPI (key performance indicator) is a metric tied to an objective, with a target and an owner. Business purpose. Focus attention and decisions on what drives results. Required data. Transactions, plan/targets, definitions agreed with owners.

Build a KPI tree: decompose the top metric into multiplicative or additive drivers until each leaf is something a team can act on.

Net revenue
├── Orders = Visitors/footfall × Conversion rate
└── Average order value = Units per order × Average net price
                                         └── List price × (1 − Discount %)

At Orion Retail, 2025 revenue grew 12.5%: orders +12.7%, AOV −0.2% (Module 1). The tree tells the Commercial Director where growth came from (more orders, flat baskets) and where to look next (conversion or footfall?).

Plan vs fact

Formula. Variance = Fact − Plan; Variance % = Variance ÷ Plan; Attainment % = Fact ÷ Plan. Worked example (real 2025 data):

WITH fact AS (
  SELECT o.store_id, SUM(oi.quantity * oi.unit_price * (1 - oi.discount_pct)) AS fact
  FROM orders o JOIN order_items oi ON oi.order_id = o.order_id
  WHERE o.status = 'completed' AND o.order_date >= '2025-01-01' AND o.order_date < '2026-01-01'
  GROUP BY o.store_id),
plan AS (SELECT store_id, SUM(target_revenue) AS plan FROM sales_targets WHERE year_month LIKE '2025%' GROUP BY store_id)
SELECT s.store_name, ROUND(p.plan) AS plan, ROUND(f.fact) AS fact,
       ROUND(f.fact - p.plan) AS variance, ROUND(100.0 * f.fact / p.plan, 1) AS attainment_pct
FROM plan p JOIN fact f ON f.store_id = p.store_id JOIN stores s ON s.store_id = p.store_id
ORDER BY attainment_pct;

Interpretation. Network attainment ≈ 101%, but four stores are below 100%. Ankara has the largest shortfall in % terms; Online the largest in absolute terms after Ankara? — read the variance column: decisions should weigh both % (performance) and absolute size (money). Decisions. Review Ankara's plan assumptions (first full year — was the plan too aggressive?), not only its execution.

Variance decomposition (volume vs price/mix)

Definition. Splitting a revenue difference into the part caused by volume (more/fewer units or orders) and the part caused by price/mix (value per unit/order). Formula (order-based):

• Volume effect = (Orders₁ − Orders₀) × AOV₀
• Rate (AOV) effect = (AOV₁ − AOV₀) × Orders₁
• Volume effect + Rate effect = Revenue₁ − Revenue₀ (exactly)

Worked example — Orion 2025 vs 2024: Orders₀ = 11,277, AOV₀ = 90.24; Orders₁ = 12,713, AOV₁ = 90.03.

• Volume = (12,713 − 11,277) × 90.24 = 1,436 × 90.24 ≈ +129,600
• AOV effect = (90.03 − 90.24) × 12,713 ≈ −2,700
• Total ≈ +126,900 = 1,144,527 − 1,017,608 ✓

Interpretation. All growth came from more orders; slightly smaller baskets cost ~2.7k. Common mistake. Computing both effects at old (or both at new) values leaves an unexplained "interaction" remainder; use the sequential method above so the parts add up exactly.

Sales analysis structure

A professional sales analysis moves top-down:

1. Headline: revenue, growth, vs plan (one sentence).
2. Trend & seasonality: monthly YoY.
3. Decomposition: orders × AOV; units × price; discount impact.
4. Breakdowns: channel, store (LFL vs new), category, customer segment.
5. Concentration: top products/customers share (Pareto).
6. Drivers & anomalies: campaigns, stock-outs, price changes.
7. So what: 3 actions with expected impact.

Practice

Summary

• KPI trees connect results to actionable drivers; every KPI needs a decision.
• Variance = fact − plan; report % and absolute size; state the convention.
• Volume/rate decomposition explains why revenue changed and must add up exactly.
• Structure sales analysis from headline to drivers to actions.