Blog · Board and management reporting · Finance and FP&A teams
Build a Google Sheets financial dashboard from actuals, budget, and cash movements. Use a small workbook layout, exact formulas, and reconciliation checks before sharing the figures.
A financial dashboard in Google Sheets should compare actual revenue and costs with budget, then show cash on a separate basis. Build the calculations from controlled source tabs and reconcile them before adding charts. The layout below gives finance a monthly view that managers can reproduce from its rows.
Use four source and calculation areas: Actuals, Budget, Cash, and Dashboard. Keep a note above the dashboard showing entity, USD reporting currency, reporting month, source filenames, and the time the export was approved. The workbook owner should also identify which budget version is being used.
For metric definitions and the broader finance layout, use the existing financial dashboard template. This article concentrates on Sheets ranges and formulas. A general KPI dashboard in Google Sheets covers the wider cross-functional layout.
For this example, a US distributor reports September 2026. Actuals and Budget each have three columns: A is Period, B is Reporting line, and C is Amount. Store period as a real month-start date, displayed below in US format.
| Source tab | Row | Period | Reporting line | Amount (USD) |
|---|---|---|---|---|
| Actuals | 2 | September 1, 2026 | Revenue | 120,000 |
| Actuals | 3 | September 1, 2026 | COGS | 72,000 |
| Actuals | 4 | September 1, 2026 | Operating expenses | 24,000 |
| Budget | 2 | September 1, 2026 | Revenue | 125,000 |
| Budget | 3 | September 1, 2026 | COGS | 75,000 |
| Budget | 4 | September 1, 2026 | Operating expenses | 23,000 |
Expenses are stored as positive amounts here. If your ledger exports credit-negative revenue, map and normalize signs first; the financial pivot workflow explains that control. Do not paste a ledger subtotal beside its underlying account rows and sum both.
Put the selected month in Dashboard!E1:
=DATE(2026,9,1)
Google's DATE documentation defines this year, month, day construction. A real date avoids relying on visually similar month labels that are actually different text values.
Set A2:E2 to Metric, Actual, Budget, Variance, and Variance %. Enter Revenue in A3, COGS in A4, and Operating expenses in A5. In B3, use:
=SUMIFS(Actuals!$C$2:$C$4,Actuals!$B$2:$B$4,$A3,Actuals!$A$2:$A$4,$E$1)
In C3, use:
=SUMIFS(Budget!$C$2:$C$4,Budget!$B$2:$B$4,$A3,Budget!$A$2:$A$4,$E$1)
Fill both formulas down through row 5. SUMIFS sums a range using multiple criteria, so each result requires both the reporting line and the selected month to match. These bounded ranges fit the worked example; extend all corresponding ranges together when adding source rows.
Put Gross profit in A6, Operating profit in A7, and Gross margin in A8. The actual formulas are:
B6: =B3-B4
B7: =B6-B5
B8: =IF(B3=0,"",B6/B3)
Repeat in column C using the budget cells. For dollar metrics in rows 3 through 7, D3 is =B3-C3; fill down. E3 is =IF(C3=0,"",D3/ABS(C3)); fill down for the same rows. A blank percentage means the budget denominator is zero, not that the variance is zero.
The resulting amount block is:
| Metric | Actual (USD) | Budget (USD) | Actual minus budget (USD) |
|---|---|---|---|
| Revenue | 120,000 | 125,000 | -5,000 |
| COGS | 72,000 | 75,000 | -3,000 |
| Operating expenses | 24,000 | 23,000 | 1,000 |
| Gross profit | 48,000 | 50,000 | -2,000 |
| Operating profit | 24,000 | 27,000 | -3,000 |
Actual gross profit is 120,000 - 72,000 = $48,000. Operating profit is 48,000 - 24,000 = $24,000. Budget operating profit is 125,000 - 75,000 - 23,000 = $27,000.
Revenue is 5,000 / 125,000 = 4.0% below budget. Gross margin is 48,000 / 120,000 = 40.0%, equal to budget's 50,000 / 125,000 = 40.0%. Amounts are exact dollars; percentages are rounded to one decimal place. Stable gross margin alongside lower gross profit points first to the revenue shortfall.
Keep the arithmetic variance label explicit. Lower COGS produces a negative amount variance here, but whether that is favorable depends on sales volume and cost performance. Use financial analytics for the interpretation beyond the dashboard's calculations.
On Cash, column A contains the item and column B its signed amount:
| Row | Cash item | Signed amount (USD) |
|---|---|---|
| 2 | Opening cash | 40,000 |
| 3 | September receipts | 30,000 |
| 4 | September payments | -45,000 |
| Closing | Opening plus movements | 25,000 |
The dashboard closing cash formula is:
=SUM(Cash!B2:B4)
The proof is 40,000 + 30,000 - 45,000 = $25,000. Compare it with the reconciled closing ledger cash balance for the same bank accounts and date. September's operating profit does not establish cash: customer collections, supplier payments, and other movements follow their own timing and classification.
Add a compact actual-versus-budget chart only after the calculation block ties. A sales-specific view belongs in the neighboring Google Sheets sales dashboard.
Covirage is not affiliated with Google.
Bring a sample of your own Google Sheet containing actuals, budget, and the supporting cash rows. Covirage maps the columns, its tools compute and check the figures, and the external AI model explains the results with every figure traced to its rows. Create a free Covirage account, then set up your business and bring your workbook.
See Covirage for finance teams for the reporting context.
Start with revenue, gross profit, operating profit, budget variances, and closing cash. Add receivables or other measures only when their source and owner are clear. A smaller dashboard with reconciled figures is more useful than a crowded page containing conflicting definitions.
Yes, if each row includes an explicit scenario field and every formula filters it. Separate tabs are easier to inspect in a small workbook. Either layout needs consistent reporting lines, period dates, currency, and a controlled budget version.
No. A dashboard can display closing cash and summarized movements, but those measures do not replace a classified cash flow statement. Keep cash measures distinct from profit, and reconcile opening cash plus signed movements to the closing balance.