Small Business KPI Dashboard in Google Sheets

A dashboard is only valuable if it shows metrics you actually act on. Most small business dashboards show too much data, none of which drives decisions. Here’s a tight, high-signal approach.

The Metrics That Matter for Small Business

Revenue metrics:

• Monthly revenue (vs. prior month, vs. same month last year)
• Revenue by category or product line
• Revenue per customer (average)
• Largest customers as % of total (concentration risk)

Profitability metrics:

• Gross margin % (Revenue − COGS) ÷ Revenue
• Net margin % (Net income ÷ Revenue)
• Operating expenses as % of revenue

Cash metrics:

• Cash on hand
• Accounts receivable balance (what customers owe you)
• Accounts payable balance (what you owe vendors)
• Days Sales Outstanding (DSO): average days to collect receivables

Leading indicators (vary by business type):

• New leads this month
• Proposals outstanding
• Repeat purchase rate (if product business)
• Utilization rate (if service business)
• Customer satisfaction or NPS score

Dashboard Layout

Design the dashboard to be readable in 30 seconds. One page. Big numbers. Color coding.

Section 1: Revenue Snapshot (top of page)

• This month’s revenue: large font, colored green if above budget/prior period, red if below
• Prior month revenue (for comparison)
• Year-to-date revenue vs. YTD budget

Section 2: Profitability (middle)

• Gross margin %: threshold is your specific business benchmark (service businesses typically 40-80%, product businesses often 25-50%)
• Net margin %: positive = profitable, watch trend
• Key expense ratios: payroll %, marketing % of revenue

Section 3: Cash Position (always visible)

• Cash balance
• Incoming receivables (expected in next 30 days)
• Outgoing payables (due in next 30 days)
• Projected ending cash (current + receivables − payables)

Section 4: Leading Indicators

• 3-5 leading indicators specific to your business
• Each with a mini sparkline (Google Sheets SPARKLINE function) showing the trend

Building the Sparklines

Google Sheets SPARKLINE function creates tiny trend charts in a single cell:

=SPARKLINE(B2:M2, {"charttype","line"; "color","#2196F3"})

B2:M2 is a row of 12 months of data. The sparkline fits in one cell and shows whether the metric is trending up or down.

Put sparklines next to each key metric for instant visual trend reading.

Color-Coding with Conditional Formatting

For each key metric:

• Green background: At or above target
• Yellow background: Within 10% below target
• Red background: More than 10% below target

Formula for conditional formatting rule (Revenue cell is B5, budget is C5):

• Green: =B5>=C5
• Yellow: =AND(B5<C5, B5>=C5*0.9)
• Red: =B5<C5*0.9

The color coding means you don’t have to read every number — you scan for red and investigate.

Keeping the Dashboard Current

A dashboard that’s 2 weeks out of date is nearly useless. Schedule a regular update:

Weekly (10 minutes):

• Update cash balance
• Update accounts receivable (invoices outstanding)
• Update leading indicators (new leads, proposals, etc.)

Monthly (30 minutes):

• Update revenue and expense actuals
• Recalculate margins
• Add the new month’s column to all trend calculations

Data source tip: Link your dashboard to your existing spreadsheets rather than re-entering data. If your revenue is tracked in another sheet, use =SheetName!B5 to reference it directly.

What to Do with Dashboard Information

The point of a dashboard isn’t to know numbers — it’s to make decisions.

Revenue below target → look at pipeline, accelerate outreach, check if a key customer churned

Gross margin declining → investigate material costs or pricing; both can compress margins silently

DSO rising → clients are paying slower; follow up on overdue invoices immediately

Cash declining despite profitability → check if receivables are building up (profitability doesn’t equal cash until invoices are collected)

Build the minimum viable version this week — just revenue, cash, and two leading indicators. You can add complexity later. The habit of looking at a dashboard weekly is more valuable than having a perfect one you never use.