Track key financial and operational KPIs with year-over-year comparisons
✓ Free to download · ✓ Excel 2016+ and Google Sheets · ✓ Custom version in 24h
An annual review breaks down when each department reports on a different basis. This workbook fixes the shape of the input — one row per metric per month — so the year-end roll-up is a calculation rather than a negotiation.
The actual columns, with sample rows
| A | B | C | D | E | F | G |
|---|---|---|---|---|---|---|
| 1 | Metric | Owner | Target | Actual | Var % | YTD |
| 2 | Revenue | Sales | 1,200,000 | 1,318,400 | +9.9% | 1,318,400 |
| 3 | Gross margin % | Finance | 62.0% | 59.4% | -4.2% | 59.4% |
| 4 | Churn % | CS | 3.0% | 2.4% | +20.0% | 2.4% |
| 5 | Headcount | People | 48 | 51 | +6.3% | 51 |
Why each one is written the way it is
=IF([@Target]=0, "", ([@Actual]-[@Target])/ABS([@Target]))
Variance against target. Dividing by ABS keeps the sign meaningful when the target itself is negative, which happens on cost and burn lines.
=SUMIFS(Metrics[Actual], Metrics[Metric], [@Metric], Metrics[Month], "<="&[@Month])
Year-to-date without a running-total column, so inserting or reordering months cannot break it.
=IFS([@[Var %]]>=0.05,"Ahead", [@[Var %]]>=-0.05,"On track", TRUE,"Behind")
A banded status rather than a raw percentage — reviewers act on the band, not the third decimal.
=XLOOKUP([@Metric]&[@Month], Prior[Key], Prior[Actual], "n/a")
Prior-year comparison by composite key, returning a readable fallback instead of #N/A for metrics that did not exist last year.
A worked example with real numbers
A 50-person software business enters twelve months of actuals against targets set in January. Revenue closes 9.9% ahead but gross margin lands 4.2% behind, and the YTD column shows the gap opening in Q3 — the quarter that support headcount grew. Because every figure sits on the same grain, the board pack is a pivot over the metrics table rather than five people reconciling five decks the week before.
Tab by tab
| Tab | Contents |
|---|---|
| Metrics | The one input table: metric, owner, month, target, actual. |
| Departments | Owner-to-department mapping so any metric can be rolled up by team. |
| Prior year | Last year's actuals, keyed identically, for like-for-like comparison. |
| Review | Pivot-driven summary by quarter and department, ready to present. |
| README | Which cells to type in, and what to change when the fiscal year rolls over. |
What is included, and what to look at next
Specific to this workbook, not generic
Yes. The month column is just a period key; entering Q1–Q4 works identically and every formula aggregates the same way. Keep one grain throughout — mixing months and quarters in the same column breaks the YTD calculation.
Add rows to the Metrics table from the month it starts. The YTD formula sums what exists, and the prior-year lookup returns "n/a" rather than an error for the months before it existed.
Yes, but enter the target as the direction you want. For churn and cost lines, set the target and let the variance formula report the sign; the status bands then read correctly without a separate rule.
"This annual performance dashboard has transformed how we track our business progress. The year-over-year insights have been invaluable for strategic planning."