How to Build a Financial Dashboard for Your Small Business

A financial dashboard brings the financial numbers you monitor most often into one place so you can quickly see how your business is performing. In this guide, I’ll build a simple example using Google Sheets, but the same process works with other spreadsheet or reporting tools.

We’ll track revenue, gross profit, cash, accounts receivable, accounts payable, and budget variance. I’ll also show how QuickBooks Online can supply many of the underlying accounting figures instead of calculating or gathering everything manually. By the end, you’ll understand what data your dashboard needs, how the metrics work together, and how to turn them into a useful management view.

Step 1: Decide what your financial dashboard should track

Start with the questions you actually need the dashboard to answer. I prefer this approach over filling the page with every financial metric available because each number then has a clear reason for being there.

For this example, we’ll use six basic questions:

Think of these six metrics as the first version of the dashboard, not a permanent list. Starting small makes it easier to understand what each number contributes before you add more measures later.

You can expand the dashboard later with metrics such as margins, operating cash flow, burn rate, runway, customer concentration, or sales by product. I recommend adding them only when they support another decision you regularly make.

In Google Sheets or your preferred spreadsheet tool, I would start by reserving space for these six figures at the top of the dashboard. The numbers themselves can remain blank until the underlying data and calculations are ready.

How QuickBooks Online can help

If you already use QuickBooks Online, many of these metrics already have a natural accounting source.

This is the relationship I’d maintain throughout the build: QuickBooks Online remains the accounting system of record, while the dashboard reorganizes selected information into a more focused management view.

Before you move on, make a short list of metrics and identify which accounting report supplies each one. That gives the rest of the build a clear boundary: every table, formula, and chart should support one of those selected metrics.

Step 2: Organize the information behind the dashboard

A dashboard needs more than a presentation page. It also needs a place to store the source figures, perform calculations, and check the results.

For a basic Google Sheets version, I’d organize the workbook into four areas separated in each tab:

• Data: Financial figures coming from your records.
• Calculations: Metrics derived from that data.
• Dashboard: KPI cards and visualizations.
• Checks: Comparisons between dashboard figures and the accounting records.

This separation makes the dashboard easier to maintain. If a number looks wrong, you can determine whether the problem comes from the source data, the calculation, or the presentation.

The idea is to keep source data separate from the presentation. When you update a number next month, change it in the data area first, then let the calculations and dashboard reflect that update. This keeps the workbook easier to follow and reduces the chance of changing a displayed number without changing its source.

For our example, the main Data table looks like this:

I’d also keep a separate table for accounts receivable aging:

Notice that these aging buckets total $9,000, matching the March accounts receivable balance. That relationship becomes useful later when we check the dashboard.

Before you move on, make sure every dashboard metric has a clear place in the Data area. Supporting detail, such as accounts receivable aging, should stay in its own small table rather than being mixed into the monthly summary.

Step 3: Gather the financial data

Once the structure is ready, replace the sample values with figures from your own accounting records.

For each reporting period, you need:

• Revenue
• Cost of goods sold
• Cash balance
• Accounts receivable
• Accounts payable
• Revenue budget
• Accounts receivable by aging bucket

Gather one complete reporting period at a time. I would start with the most recent three months so the dashboard can show the latest numbers and a short trend. Use the same month labels and reporting dates throughout so the figures line up when you calculate and chart them later.

Keep the reporting periods consistent. If you are comparing March revenue with March budget, use March figures for both. The same principle applies to the other metrics.

A spreadsheet makes it easy to organize these figures, but the important part is the information itself. You can use the same structure in another spreadsheet program or reporting tool.

How QuickBooks Online can help

QuickBooks Online can reduce much of the data-gathering work.

Use:

• Profit and Loss for revenue and cost of goods sold.
• Balance Sheet for cash balances.
• AR Aging for outstanding receivables.
• AP Aging for outstanding payables.
• Budget vs. Actuals if the budget is maintained in QuickBooks Online.

QuickBooks Online reports can also be exported for downstream spreadsheet analysis.

A simple reporting flow therefore looks like:

QuickBooks Online → financial data → reporting spreadsheet → dashboard

I prefer this over manually maintaining a second accounting record inside the spreadsheet. The dashboard should summarize the books, not become another set of books.

Before moving to calculations, compare the figures you entered or imported with the source reports. You do not need a formal reconciliation yet, but obvious differences are easier to fix before formulas and charts are built on top of them.

Step 4: Calculate the metrics 

The goal of this step is to separate numbers that already exist in your accounting records from numbers that need to be calculated for the dashboard. I would avoid adding formulas just because a spreadsheet can handle them. Every calculation should answer a specific management question and use data from the same reporting period.

For this example, revenue, cash balance, accounts receivable, and accounts payable are source values. Gross profit and revenue budget variance are calculated values. Keeping that distinction clear makes the dashboard easier to audit later because you can trace each number back either to a report or to a simple formula.

Gross profit

Gross profit shows how much revenue remains after subtracting cost of goods sold. It is useful because revenue alone does not show how much of those sales remain after the direct costs tied to producing the goods or services sold.

The gross profit formula is:

Revenue - Cost of goods sold

In March, our example shows:

$45,000 - $18,000 = $27,000

That means the business generated $45,000 in revenue and used $18,000 of that amount to cover direct costs, leaving $27,000 in gross profit. I would place that $27,000 figure on the dashboard because it gives more context than revenue by itself.

When you calculate this metric, make sure revenue and COGS cover the same period. Comparing March revenue with February COGS would produce a number, but it would not accurately represent March gross profit.

Revenue budget variance

Budget variance shows the difference between what happened and what you planned. For this dashboard, I am using a revenue variance, so the calculation compares actual revenue with budgeted revenue.

The formula is:

Actual revenue - Budgeted revenue

Using March:

$45,000 - $42,000 = $3,000

The positive $3,000 result means actual revenue finished $3,000 above the budgeted amount for March. If actual revenue had been $40,000 instead, the variance would be negative $2,000, showing that revenue came in below target.

The direction of the variance matters. A positive revenue variance is favorable because actual revenue exceeded budget. Expense variances need different interpretation because spending more than budget can be unfavorable. I would therefore label the metric clearly as revenue budget variance rather than using a generic variance label.

Metrics that can flow directly to the dashboard

Some dashboard values do not need another formula because they are already reported as balances or totals. For March, those values are:

I would carry these figures into the dashboard from the latest reporting period rather than recalculate them. Revenue comes from the period's accounting results, while cash, AR, and AP are balances as of the reporting date. This is another reason to keep the dashboard date visible and consistent.

In a spreadsheet, these values can reference the source data area directly. The exact cell reference is less important than making sure each dashboard figure points to the correct source and reporting date.

Step 5: Build the headline dashboard

With the calculations ready, bring the six current metrics into the dashboard.

Our March example now looks like this:

Treat this as the dashboard's at-a-glance layer. A reader should be able to open the page and understand the current month before looking at any chart.

Keep each label close to its value and use the same reporting period for all six cards. In this example, every card reflects March, so the dashboard is not mixing current balances with older revenue or budget data.

I’d keep these headline numbers at the top because they answer the quickest financial questions first. The visual design does not need to be complicated. Use clear labels, consistent number formatting, and enough space for the numbers to stand out.

The important question is whether someone can look at the dashboard and quickly understand the current financial position. Before adding charts, check that the six headline figures match the source values and calculations from the earlier steps. At this point, the top of the dashboard should already give you a reliable current-period snapshot. The charts will add context rather than introduce new headline numbers.

Step 6: Add the charts

The KPI cards tell you where the business stands now. The charts add context by showing whether results are moving, staying on target, or becoming older. For this simple dashboard, three charts are enough: revenue vs. budget, cash balance trend, and accounts receivable aging.

Compare revenue with budget

A single revenue figure shows the current result, but it doesn't show whether it matched the plan. Comparing actual revenue with budget across several periods gives the dashboard a clear performance benchmark and shows whether the business is consistently meeting its target.

Use the same reporting periods for both series. If the revenue figure is for March, compare it with the March budget rather than a different month.

For our example:

Plot Month on the horizontal axis and show actual revenue and budget as separate series. A line chart or column chart works well because the two values can be compared at a glance without adding more calculations.

What the chart should tell you

Look for whether actual revenue stays above or below the budget line and whether the gap changes over time. In this example, actual revenue remains above budget in January, February, and March, with March finishing $3,000 above target.

The chart provides the trend, while the budget variance metric from Step 4 provides the current numerical difference. Keeping both gives the reader a quick visual comparison and a clear current-period result.

Show the direction of cash

The current cash balance is a point-in-time number, so one month by itself gives limited context. Comparing the balance across several reporting dates makes it easier to see whether cash is generally rising, falling, or holding steady.

Use comparable reporting dates for each period so the trend reflects a consistent view of cash.

Our example has:

A line chart works well here because it emphasizes direction rather than a single balance. In the example, cash rises from $27,000 in January to $31,000 in March, an increase of $4,000 across the three-month period.

The chart should make changes in cash easy to spot. It does not explain why cash moved, so treat a noticeable rise or drop as a prompt to review the underlying cash activity rather than as a conclusion by itself.

Show how old your receivables are

Total accounts receivable tells you how much customers owe. Aging tells you how long those balances have remained outstanding.

Our $9,000 receivable balance is split this way:

Turn these buckets into a bar or column chart.

Aging deserves a separate visualization because the age of the receivables adds information that the $9,000 total cannot provide by itself.

For example, $9,000 consisting mostly of current invoices presents a different collections picture from $9,000 concentrated in the 90+ day bucket.

Look for where the receivable balance is concentrated. In this example, $7,000 of the $9,000 total is current or 1–30 days old, while smaller amounts sit in the older buckets. The goal is not to explain every overdue invoice on the dashboard. It is to make older balances visible enough that you know when to investigate further.

How QuickBooks Online can help

QuickBooks Online's AR Aging reports already organize open receivables by how long they have been outstanding. Due dates matter here because aging depends on how long an invoice has been past due. Accurate invoice dates and due dates therefore help make the dashboard more useful.

If you use QBO, I would use its aging information rather than recreate the aging logic manually in the spreadsheet. Once these three charts are in place, the dashboard shows both the current position and the direction behind it. The next step is to verify that the displayed figures agree with the accounting records.

Step 7: Check the dashboard against your accounting records

I consider reconciliation part of building the financial dashboard, not an optional final touch. A dashboard can look polished while still showing the wrong numbers.

Start with the few control totals that matter most. You do not need to reconcile every line in the spreadsheet. For this dashboard, revenue, cash, accounts receivable, and accounts payable are enough to confirm that the headline numbers tie back to the books.

Create a simple comparison like this:

Calculate:

Dashboard value - Accounting-record value

Ideally, the result is zero. If it is not, check:

• Reporting dates
• Reporting period
• Accounting method
• Missing data
• Duplicated data
• Outdated information
• Calculation errors

When you see differences, that doesn’t immediately mean that something is wrong. Investigating these differences is necessary, especially if they meet a certain threshold or materiality level. Use your professional judgment in determining how much is material or immaterial. 

How QuickBooks Online can help

Use QBO as the control source for these checks.

Compare:

• Dashboard revenue with Profit and Loss.
• Dashboard cash with Balance Sheet.
• Dashboard AR with the AR report.
• Dashboard AP with the AP report.

Use the same reporting dates and settings when comparing them. Different report configurations can produce different figures even when both reports are functioning correctly.

Do not move on to the recurring update process until unexplained differences are resolved. A repeatable dashboard depends on a clean starting point, so this check is what turns the build from a one-time spreadsheet into something you can rely on each month.

Step 8: Create an update process and automate later

Once the dashboard is built and reconciled, the next goal is to make it repeatable. A simple update routine keeps each reporting period consistent, while automation can reduce manual work after the process is stable and you know how every number moves from the accounting records to the dashboard.

Update the dashboard with new data

After the initial build, the recurring process is straightforward. Treat each refresh as the same short routine: update the source records, bring in the new period, refresh the calculations and charts, then run the checks. Repeating the same order makes the dashboard easier to maintain because you always know what should happen next.

Suppose April produces these figures:

The new gross profit is:

$48,000 - $19,000 = $29,000

The new revenue budget variance is:

$48,000 - $46,000 = $2,000

Add April to the underlying data, refresh the dashboard figures and charts, update the AR aging information, and rerun the reconciliation checks.

This is also why I prefer keeping the dashboard logic simple. A reporting process you understand is easier to update and troubleshoot than one built from dozens of calculations you rarely inspect.

Automate only when the manual process works

Automation should remove repetitive data transfer, not remove your understanding of the dashboard. I would only automate a process after I can explain how the source data moves into each metric and how the checks confirm the result.

A dashboard does not need to update automatically to be useful.

The manual workflow is:

QuickBooks Online → report/export → dashboard data → calculations and charts

An automated setup changes the data-transfer portion:

QuickBooks Online → automated connection → dashboard data → calculations and charts

Reporting connectors or more advanced integrations can bring QBO data into Google Sheets on a schedule. Direct QBO API integrations provide additional control but also require more technical setup and maintenance.

I would automate after the dashboard definitions and reconciliation process are stable. Otherwise, automation can make incorrect assumptions and refresh too quickly.

Keep the checks in place even after automation. An automatic refresh does not guarantee that the resulting dashboard is correct.

Once the manual update process has worked consistently, you can automate the repetitive parts while keeping the same calculations, visuals, and checks. The goal is to make the dashboard easier to maintain without losing the ability to trace where each number came from.

What your finished financial dashboard should tell you

A useful financial dashboard does not need dozens of metrics.

Our finished example tells us:

• Revenue reached $45,000 in March.
• Gross profit was $27,000.
• Cash stood at $31,000.
• Customers owed $9,000.
• The business owed vendors $5,000.
• Revenue exceeded budget by $3,000.
• Revenue stayed above budget during the three-month example.
• Cash increased over the same period.
• Most accounts receivable remained current or less than 30 days overdue.

Those are the kinds of observations a dashboard should make easy to find.

The specific metrics can change as the business grows. The basic process remains the same: decide what questions matter, identify the right accounting data, calculate only what needs calculating, visualize useful trends, and verify the result against the books.