Monitor revenue, expenses, and profit trends with automated financial metrics
✓ Free to download · ✓ Excel 2016+ and Google Sheets · ✓ Custom version in 24h
A KPI sheet is only useful if every number has a defined denominator. This template fixes the definitions in one place so that "gross margin" means the same thing in March as it did in January.
The actual columns, with sample rows
| A | B | C | D | E | F |
|---|---|---|---|---|---|
| 1 | KPI | Definition | Target | Actual | Status |
| 2 | Gross margin % | (Rev − COGS) / Rev | 62.0% | 59.4% | Behind |
| 3 | Current ratio | Current assets / current liabilities | 1.50 | 1.82 | Ahead |
| 4 | DSO (days) | AR / revenue × days | 45 | 51 | Behind |
| 5 | Burn multiple | Net burn / net new ARR | 1.50 | 1.21 | Ahead |
Why each one is written the way it is
=IF(Revenue=0, "", (Revenue-COGS)/Revenue)
Gross margin from named cells rather than positional references, so inserting a row above cannot silently repoint it.
=AR/Revenue*DaysInPeriod
Days sales outstanding. Using the period length as an input rather than a hard-coded 365 keeps monthly and quarterly views consistent.
=IFS([@Actual]>=[@Target]*1.05,"Ahead", [@Actual]>=[@Target]*0.95,"On track", TRUE,"Behind")
A ±5% tolerance band, so a KPI does not flip status on rounding.
=(EndValue/StartValue)^(1/Periods)-1
Compound growth rate, for the trend column. The arithmetic average of monthly growth rates overstates growth and is the most common error on these sheets.
A worked example with real numbers
A services business reported gross margin at 62% in board packs and 54% in the finance review. Both were arithmetically correct: one excluded contractor cost from COGS, the other included it. Writing the definition into the sheet next to the number ended a recurring argument in one meeting, and the reconciled figure — 54% — is what the pricing review was finally run against.
Tab by tab
| Tab | Contents |
|---|---|
| KPIs | Definition, target, actual and status for each measure. |
| Inputs | The raw financial figures every KPI is derived from. |
| Trend | Twelve-month history with compound growth. |
| Chart | Sparkline view for the board pack. |
| README | Where each input comes from in your accounting system. |
What is included, and what to look at next
Specific to this workbook, not generic
Start with four: gross margin, current ratio, DSO and runway. Everything else is a diagnostic you reach for when one of those moves. A sheet with forty KPIs gets read by nobody.
Monthly, on the same day relative to close. KPIs updated irregularly are worse than none, because trend comparisons across uneven periods are misleading.
Because averaging percentage growth overstates it. Growing 50% then falling 50% is a 25% loss, but averages to zero — the compound formula reports it correctly.
"This financial KPI dashboard has transformed how we track our business performance. The automated metrics give us instant visibility into our financial health."