Concept
1. Three numbers for every target
Variance = Actual − Target
Variance % = Actual / Target − 1
Achievement % = Actual / Target
Capstone FY2025-26 by region (targets = last year × 1.15, rounded):
| Region | Target | Actual | Variance | Achievement |
|---|---|---|---|---|
| East | 5,63,30,000 | 5,45,36,450 | −17,93,550 | 96.8% |
| North | 9,69,00,000 | 9,42,56,153 | −26,43,848 | 97.3% |
| South | 7,13,80,000 | 7,14,32,223 | +52,223 | 100.1% |
| West | 8,08,90,000 | 7,47,71,585 | −61,18,415 | 92.4% |
| Total | 30,55,00,000 | 29,49,96,410 | −1,05,03,590 | 96.6% |
Story: only South hit target; West is the biggest gap in rupees and percent — and it was also the slowest-growing region (+6.3% YoY).
2. RAG logic
Agree thresholds before the period starts:
| Status | Rule (higher is better) |
|---|---|
| 🟢 Green | Achievement ≥ 100% |
| 🟠 Amber | 90% ≤ Achievement < 100% |
| 🔴 Red | Achievement < 90% |
Formula (B = Target, C = Actual):
=IF(C2/B2 >= 1, "Green", IF(C2/B2 >= 0.9, "Amber", "Red"))
Or with IFS / LET:
=LET(a, C2/B2, IFS(a >= 1, "Green", a >= 0.9, "Amber", TRUE, "Red"))
For all 48 region-months of FY2025-26 in the capstone: 19 Green, 13 Amber, 16 Red — even though the year total is Amber at 96.6%.
Keep thresholds in cells (e.g. F1 = 100%, F2 = 90%) and refer to them, so management can change policy without editing formulas.
3. When lower is better
Costs, returns %, days to deliver, overdue receivables: Green when at or below target.
=IF(C2 <= B2, "Green", IF(C2 <= B2 * 1.1, "Amber", "Red"))
(Amber = up to 10% over target.) Label which direction each KPI runs; mixing them up turns good news red.
4. Show it
- Conditional formatting on the status column: Format only cells that contain → text "Red" → red fill, etc.
- Or an icon set on Achievement % (Module 5): green ≥ 1, amber ≥ 0.9, red otherwise — use "Number" type thresholds, not percent-of-range.
- Add a symbol or word with the colour (▲ ● ▼ or "Red") for colour-blind readers and printing.
5. Interpreting variance well
- Cumulative vs month: a region can be Red in two months and still Green YTD. Show both month and YTD status.
- Target quality: if everyone is Red, the target may be wrong (the capstone's "+15% on last year" was above the 11% growth achieved).
- Explain, don't just colour: each Red line needs a one-line reason and an action owner.
- Price vs volume: a revenue gap can come from fewer orders or lower order value — split it (capstone: average order value was flat, ₹11,213 both years, so growth came entirely from more orders).
Common mistakes
Thresholds changed after the results are in. Same RAG rule for "higher is better" and "lower is better" KPIs. Colour without explanation. Only monthly status (noisy) without YTD.