Finance KPI dashboard

You give the owner a single sheet that tells them, in under a minute, whether the business is healthy and where to look. That means few KPIs, each tied to a decision, defined so precisely that two people would calculate the same number, fed from the ledger and bank, with targets the owner set and a trend that shows direction, not just a point.

When to use

• "I want a dashboard of my key numbers."
• "What KPIs should we track?"
• "Can you build me a monthly KPI sheet for the board?"
• "Update the dashboard for September." Schedule with cronjob after each month-end close.

What you need

• Monthly figures for at least the last 13 months: P&L by line, balance sheet (receivables, payables, stock, cash), and operational counts (customers, orders, staff, hours) where the KPIs need them.
• Access, best first: connected apps (Xero, QuickBooks, Stripe) on the Connections page; your own browser signed in by the owner to the accounting software, billing system or bank (read only); or exports from the owner. If none is there, ask with clarify.
• The owner's goals for the year and the decisions they make monthly (hiring, pricing, spending, chasing).
• Targets for each KPI, set or approved by the owner.

Method

1. Start from decisions, not metrics. Ask what the owner decides each month and what would change their mind. Each KPI must map to a decision; a metric that would not change any action is a diagnostic, not a KPI.
2. Choose 6 to 10 KPIs from references/kpi-library.md for the business model: a few universal ones (cash, runway or headroom, gross margin, net margin, debtor days) plus the model-specific drivers (MRR and churn for subscriptions; orders, AOV and contribution after marketing for e-commerce; utilisation and realisation for services). Mix lagging results with one or two leading indicators.
3. Define each KPI precisely on a Definitions sheet: name, formula, exact source (report and line), unit, frequency, owner, direction (higher or lower is better), target and the reason for the target, and the amber band (how far off target before it turns red).
4. Lay out the workbook (tested code in references/dashboard-build.md):
   • Data: raw monthly inputs, one row per input, months as columns, typed or pulled, never calculated.
   • KPIs: one row per KPI, formulas only, reading from Data.
   • Dashboard: first sheet; for each KPI this month, last month, change, target and status (On track, Watch, Off track) by formula, coloured by conditional formatting; then a 13-month line chart per KPI.
   • Definitions, and Checks.
5. Status rules come from the definitions: for a higher-is-better KPI, On track when at or above target, Watch when within the amber band below it, Off track beyond that; mirror it for lower-is-better.
6. Fill and tie out. Pull the month's inputs. Recompute every KPI in Python and compare with the evaluated workbook formulas. Check inputs against source: revenue equals the P&L, cash equals the reconciled bank balances, receivables equals the aged receivables total.
7. Read it before handing it over. For anything on Watch or Off track, add one line: why (with the number) and the suggested action. Note any KPI whose trend has moved the wrong way three months in a row even if still on target.
8. Keep it stable. Do not change a definition mid-year without restating history and noting it. Add a KPI only if one comes off.

Choosing the set: starting points by business model

Model	Universal	Model-specific	Leading
Subscription	Cash, runway, gross margin %, net margin %	MRR, net new MRR, logo churn %, net revenue retention	Trials started, pipeline value
E-commerce	Cash, headroom, gross margin %, stock days	Orders, average order value, contribution after marketing, returns rate	Sessions, conversion rate
Services	Cash, debtor days, net margin %	Utilisation %, realisation %, effective hourly rate, lock-up days	Backlog cover in months
Six to ten in total. Definitions and formulas for each are in references/kpi-library.md.

Worked example: one definition, start to finish

Debtor days.

• Formula: trade receivables at month end / revenue for the month x days in the month; use 3-month average revenue when the month is unusual.
• Source: balance sheet "Trade receivables"; P&L "Revenue". Owner: the owner.
• Direction: lower is better. Target: 35 (terms are 30 days; last year averaged 38). Amber band: 5 days, so Watch up to 40, Off track above.
• This month: 104,000 / 79,600 x 30 = 39.2, Watch; last month 39.1.
• Commentary: "Debtor days 39 against a target of 35, unchanged on the month. Acme's 9,600 at 64 days overdue adds nearly 4 days on its own; a formal chase is recommended (see receivables-chasing)."

Reading the dashboard before you hand it over

KPI	This month	Last month	Target	Status	One line
Gross margin %	64.9%	61.8%	62%	On track	Better mix: licence revenue up
Net margin %	4.6%	2.6%	8%	Off track	Overheads up 3,050: recruitment fee, one-off
Debtor days	39	39	35	Watch	Acme overdue; see above
Cash	64,000	66,500	60,000	On track	Down 2,500: receivables rose 5,000
One line for every Watch or Off track item: the number, the cause, the action. Add a line for any KPI that has moved the wrong way three months running even if still On track; that is where next quarter's problem is.

Updating each month

1. Close the month (month-end-close) and reconcile cash first; a dashboard on unclosed numbers will be restated.
2. Add the new month's column to Data; extend the KPI formulas by one column.
3. Point the Dashboard's "this month" and "last month" at the new columns and move the chart ranges forward.
4. Re-run the Python twin and the tie-out; fix anything that does not match before anyone sees it.
5. Write the commentary for every Watch and Off track item. Never type a KPI value into the KPIs or Dashboard sheets; a figure that must be entered by hand goes in Data as an input with its source.

Output

• finance-kpi-dashboard.xlsx with sheets Dashboard, KPIs, Data, Definitions, Checks.
• A show_card with each KPI: value, change, target, status.
• Three to five lines of commentary on what needs attention.

Checks before you finish

• Every KPI has a written definition, source, owner, direction and target.
• Python recompute matches the workbook for every KPI and month.
• Revenue, cash and receivables inputs agree to the P&L, bank reconciliations and aged receivables.
• Statuses match the rules (spot-check one of each).
• Partial months are not compared with full months; month lengths are handled in day-based ratios.
• No more than 10 KPIs on the Dashboard.

Pitfalls

• Twenty KPIs. Nobody reads them. Fewer, sharper.
• Vanity numbers (followers, total sign-ups) on a finance dashboard. Keep to what drives cash and profit.
• Vague definitions. "Churn" means nothing until you say logo or revenue, monthly or annual, and how new customers in the month are treated.
• Targets you invented. The owner sets them; you can propose with evidence.
• Point-in-time only. Without the trend, one bad month looks like a crisis and a slow decline looks fine.
• Charts with a truncated axis that exaggerate small moves. Start rate axes sensibly and label them.

See also

• management-accounts-pack, unit-economics-model, financial-statements-explained.