Building a KPI Dashboard for Your Small Business in Google Sheets

A KPI dashboard is only as good as two things: whether it shows the right handful of numbers, and whether those numbers are actually driven by formulas reading a real transaction log, rather than typed in by hand and quietly going stale. This guide covers both — which KPIs matter for a small business specifically, why each one earns its place, and how a Google Sheets dashboard can calculate all of them automatically from a single connected system.

The KPIs that actually matter

Revenue and expense totals feel like the obvious starting point, but on their own they're incomplete. A useful small business dashboard needs these, together:

• YTD Revenue and YTD Expenses. Your top-line numbers for the current financial year, filtered automatically so you're never looking at a stale "as of some date I don't remember" total.
• Net Profit and Profit Margin %. Revenue minus expenses, and that figure as a percentage of revenue. Margin matters more than the raw profit number on its own — a business growing revenue while its margin quietly shrinks is heading somewhere worse than the top-line number suggests.
• Outstanding Receivables. The total of everything invoiced but not yet paid or written off. This is money that exists on paper but not yet in your bank account — a real number, but a different kind of real from cash in hand.
• Overdue Amount, specifically. A subset of outstanding receivables: the part that's actually past its due date. Outstanding isn't automatically a problem — overdue is where attention needs to go.
• Average Invoice Value. A quick sanity check on deal size over time, useful for noticing if you're doing more small jobs or fewer large ones than you think.
• Top Client (by revenue). Worth watching for concentration risk — if one client accounts for a large share of your revenue, that's useful to know before they leave, not after.
• Monthly Revenue vs. Expenses, trailing 12 months. A trend line, not just a snapshot. A single month's numbers can be misleading; a rolling year shows the actual direction.
• Expenses by Category, trailing 12 months. Where the money is actually going, broken down rather than lumped into one expense total.
• Accounts Receivable Aging. Unpaid invoices grouped into buckets — Current, 1–30, 31–60, 61–90, and 90+ days overdue. This is the difference between "we're owed some money" and "we're owed money that's been sitting unpaid for three months."

Notice what's not on this list: vanity metrics like total transaction count, or numbers that look impressive but don't drive a decision. Every KPI above answers a specific question a small business owner actually asks. If a number on your dashboard doesn't do that, it's decoration.

How the numbers actually calculate themselves

Underneath a working KPI dashboard is a single transaction log — every income and expense entry, dated, typed as Income or Expense, and categorised. From that one log, standard formulas do all the work: SUMIFS totals revenue and expenses for a date range and transaction type at once, the same pattern repeated with a rolling twelve-month window to build the monthly trend table. Net profit is just revenue minus expenses; profit margin is that figure divided by revenue, wrapped in IFERROR so a month with zero revenue shows a clean result instead of a division error.

The receivables side works the same way but reads from an invoices log instead: outstanding receivables sums every invoice not marked Paid or Written Off, and the overdue figure narrows that further to invoices past their due date specifically. Top client is an INDEX/MATCH lookup against a separate clients tab that itself totals revenue per client from the transaction log. None of this needs a macro or a script — it's the same handful of functions, reused across different filters, reading from data you've already logged once.

A well-built version of this also runs off a small settings block — your business name, currency symbol, and financial year start month, set once — so every formula on the dashboard reads from those same settings cells instead of having a year or currency symbol hard-coded into a dozen different formulas that would each need updating separately.

Accounts receivable aging, worked through

Aging is worth its own explanation because it's the piece that turns a flat number into something actionable. Each unpaid invoice gets a days-outstanding figure calculated from today's date and its due date, and that figure sorts it into a bucket: Current if it's not yet due, then 1–30, 31–60, 61–90, and 90+ days overdue. The dashboard then sums the total owed in each bucket.

A receivables total of, say, $9,000 sounds fine in isolation. Whether it actually is fine depends on the aging breakdown behind it — $7,000 of that sitting Current and $2,000 in 1–30 is a normal, healthy pattern for an active business. The same $9,000 total with $4,000 sitting in the 90+ bucket is a collections problem wearing a number that looks identical from a distance. The aging view is what tells the two apart.

What still needs a human

Days outstanding and aging buckets calculate themselves the moment a due date passes. Whether an invoice is actually marked Paid does not — that's a status you set once the money genuinely lands, because nothing in a spreadsheet checks your bank account to confirm a payment arrived. A dashboard fed by invoices nobody's updated to Paid will overstate your outstanding receivables indefinitely, no matter how good the formulas underneath it are. The same goes for logging transactions in the first place: the dashboard is a live read of whatever you've entered, not a live read of your actual bank balance.

Build it yourself, or start from one already wired up

Everything described above can be built in a blank Google Sheet: a transaction log, an invoices log, a settings block, and a page of SUMIFS formulas reading from both. It takes real time to get the ranges and filters right, and it's easy to get an aging bucket formula slightly wrong in a way that only shows up months later when a total looks off. Where a ready-built system saves the most time is exactly there — formulas that have already been checked end to end against real sample data, not written once and assumed correct.

If you're setting up the underlying budget alongside the dashboard, the annual budget planning guide covers phasing a budget by month rather than dividing by twelve. If cash timing is the bigger worry than the KPIs themselves, the cash flow forecast guide and the scenario planning guide both build on the same transaction data. And if receivables aging specifically is the piece you need most, the accounts receivable aging template guide goes deeper on that one view alone.