# Budget vs actual: variance formula, report layout and the double-counting trap

> How to build and read a budget vs actual report: variance and variance % formulas, SUMIFS in Excel or Google Sheets, why lookups inflate the budget, and asking an AI agent across both sheets.

Source: https://datatomcp.com/guides/budget-vs-actual · By Killian · Updated 2026-11-03

A budget vs actual report puts what you planned to spend (or earn) next to what really happened, line by line, and shows the difference: **variance = actual − budget**, and **variance % = variance ÷ budget**. For a cost line, a positive variance means you overspent (unfavourable); for a revenue line, a positive variance is good news (favourable). The work is in getting the two sides to line up: a budget has one row per account per month, actuals arrive as many transaction lines, and the totals have to be summed before they are compared. Below: the layout, the spreadsheet formula, the mistake that inflates the budget, and how to ask an AI agent the same question across both sheets.

## The layout

One row per account (or cost centre) and period. Four numbers per row:

| Account | Budget | Actual | Variance | Variance % |
|---|---:|---:|---:|---:|
| Payroll | 126,000 | 128,800 | +2,800 | +2.2% |
| Marketing | 24,000 | 23,950 | −50 | −0.2% |
| Software | 10,500 | 10,420 | −80 | −0.8% |
| Travel | 7,500 | 3,200 | −4,300 | −57.3% |
| **Total** | **168,000** | **166,370** | **−1,630** | **−1.0%** |

That's the third quarter (July to September 2026) of the small example used on this page: costs, so Payroll is the one line over budget. The numbers come from the two sheets in the next section.

**How to read it.** Look at the large percentages first, then the large amounts. Travel is 57% under budget, which is either a saving or a sign that planned trips moved to next quarter. Payroll is only 2.2% over but is the biggest amount, and a new hire in September (see the month view below) means it will stay over. A total that's close to budget (−1.0%) can hide lines that are far off in both directions.

## The two sheets behind it

**Budget**: one row per account and month (12 rows: 4 accounts × 3 months). Columns `account`, `month`, `budget`. Payroll 42,000, Marketing 8,000, Software 3,500 and Travel 2,500 a month.

**Actuals**: one row per booked cost (30 rows), as exported from accounting. Columns `entry_id`, `date`, `month`, `account`, `vendor`, `amount`.

<details>
<summary>See all 30 actuals rows</summary>

| entry_id | date | month | account | vendor | amount |
|---|---|---|---|---|---:|
| 1 | 2026-07-31 | 2026-07 | Payroll | July payroll | 42,000 |
| 2 | 2026-07-03 | 2026-07 | Software | Google Workspace | 840 |
| 3 | 2026-07-05 | 2026-07 | Software | Slack | 610 |
| 4 | 2026-07-12 | 2026-07 | Software | Figma | 450 |
| 5 | 2026-07-20 | 2026-07 | Software | HubSpot | 1,200 |
| 6 | 2026-07-08 | 2026-07 | Marketing | LinkedIn Ads | 3,900 |
| 7 | 2026-07-22 | 2026-07 | Marketing | Google Ads | 2,750 |
| 8 | 2026-07-15 | 2026-07 | Travel | Train Paris | 420 |
| 9 | 2026-07-16 | 2026-07 | Travel | Hotel Paris | 690 |
| 10 | 2026-08-31 | 2026-08 | Payroll | August payroll | 42,000 |
| 11 | 2026-08-03 | 2026-08 | Software | Google Workspace | 840 |
| 12 | 2026-08-05 | 2026-08 | Software | Slack | 610 |
| 13 | 2026-08-12 | 2026-08 | Software | Figma | 450 |
| 14 | 2026-08-20 | 2026-08 | Software | HubSpot | 1,200 |
| 15 | 2026-08-25 | 2026-08 | Software | Notion | 380 |
| 16 | 2026-08-09 | 2026-08 | Marketing | LinkedIn Ads | 4,100 |
| 17 | 2026-08-23 | 2026-08 | Marketing | Google Ads | 3,300 |
| 18 | 2026-08-27 | 2026-08 | Marketing | Trade fair stand | 2,400 |
| 19 | 2026-08-18 | 2026-08 | Travel | Flight Berlin | 310 |
| 20 | 2026-09-30 | 2026-09 | Payroll | September payroll | 44,800 |
| 21 | 2026-09-03 | 2026-09 | Software | Google Workspace | 900 |
| 22 | 2026-09-05 | 2026-09 | Software | Slack | 650 |
| 23 | 2026-09-12 | 2026-09 | Software | Figma | 450 |
| 24 | 2026-09-20 | 2026-09 | Software | HubSpot | 1,200 |
| 25 | 2026-09-21 | 2026-09 | Software | Notion | 640 |
| 26 | 2026-09-10 | 2026-09 | Marketing | LinkedIn Ads | 4,400 |
| 27 | 2026-09-24 | 2026-09 | Marketing | Google Ads | 3,100 |
| 28 | 2026-09-14 | 2026-09 | Travel | Flight Madrid | 560 |
| 29 | 2026-09-15 | 2026-09 | Travel | Hotel Madrid | 980 |
| 30 | 2026-09-16 | 2026-09 | Travel | Client dinner | 240 |

</details>

Made-up data, small enough to check by hand. The same shape works for a real general-ledger export with thousands of lines.

## The formula in Excel or Google Sheets

Sum the actuals onto the budget rows, not the other way round. In the budget sheet, with `account` in column A, `month` in B and `budget` in C, add:

- **Actual** (D2): `=SUMIFS(Actuals!$F:$F, Actuals!$D:$D, $A2, Actuals!$C:$C, $B2)` sums every actuals line with the same account and month.
- **Variance** (E2): `=D2-C2`
- **Variance %** (F2): `=IF(C2=0, "", E2/C2)`

Then a pivot table (or `SUMIFS` again) rolls the monthly rows up to the quarter. Keep `month` as text such as `2026-07` in both sheets, so the two match exactly.

## The mistake that inflates the budget

The tempting shortcut is the reverse: start from the actuals, look up each line's budget (a `VLOOKUP` or `XLOOKUP` on account and month), then total both columns. Every actuals line then carries its month's full budget, so a month with five software invoices counts the software budget five times.

On the example, that shortcut gives a quarterly budget of **246,000** instead of **168,000**: Software 49,000 instead of 10,500, Marketing 56,000 instead of 24,000, Travel 15,000 instead of 7,500. Payroll looks right only because it has one line a month. The same thing happens in a SQL join, and in an AI chat that merges the two sheets before summing.

The rule: each budget row must be counted once per group, however many actuals lines point at it.

## Ask an agent instead of rebuilding the report

When the question changes every month ("and by month?", "only September?", "which vendor drove Marketing up in August?"), rebuilding formulas gets slow, and pasting two sheets into a chat stops working once the ledger has thousands of lines (the [token cost calculator](/guides/spreadsheet-ai-token-cost-calculator) shows how fast a pasted sheet fills the context window). datatomcp keeps both sheets in one workspace and lets any agent (Claude, ChatGPT, Cursor, an n8n flow) query them, so the rows stay with us and only the question and the answer go through the chat.

**1. Add both sheets.** Sign in at [app.datatomcp.com](https://app.datatomcp.com/login), open **Add data**, and upload the two CSVs or paste their Google Sheets links. A Google Sheet stays in sync, so next month's actuals show up without a re-upload.

**2. Confirm the link.** datatomcp checks the columns the sheets share and finds that `account` + `month` in Actuals matches every `account` + `month` pair in Budget (100% of values found). A two-column link is only **suggested**: open the relationship map and confirm it. The workspace activity log records who confirmed it.

**3. Connect your agent** ([any MCP client](/guides/add-remote-mcp-server), or [Claude](/guides/connect-google-sheets-to-claude) step by step) and ask in plain words.

We ran these questions through datatomcp's query engine on the two sheets above. The totals are counted by datatomcp (each budget row once per group); the variance is the agent subtracting two numbers.

**"Budget vs actual by account for Q3, with the variance."** One `aggregate` call on Actuals, grouped by `account`, summing `amount` and the linked `budget.budget`: the table at the top of this page. Total 166,370 against 168,000, not 246,000.

**"And by month?"**

| Month | Budget | Actual | Variance |
|---|---:|---:|---:|
| 2026-07 | 56,000 | 52,860 | −3,140 |
| 2026-08 | 56,000 | 55,590 | −410 |
| 2026-09 | 56,000 | 57,920 | +1,920 |

**"Only September: where did we go over?"**

| Account | Budget | Actual | Variance |
|---|---:|---:|---:|
| Payroll | 42,000 | 44,800 | +2,800 |
| Software | 3,500 | 3,840 | +340 |
| Marketing | 8,000 | 7,500 | −500 |
| Travel | 2,500 | 1,780 | −720 |

September is the first month over budget: the payroll step from 42,000 to 44,800 and higher software bills (Google Workspace 900 instead of 840, Notion 640). From there the agent can list the September Software lines with `query_rows` to show the invoices behind the 3,840.

When an actuals line has an account or month with no budget row (a typo such as "Softwre", a new account), the result says how many rows found no match instead of hiding them; the agent is told to mention it. The [linked test set](/guides/test-set-linked-sheets) has a deliberate example of that.

One limit to know: a budget line with no spending at all has no actuals line, so it doesn't show up when you ask from the actuals side. Ask for the budget total from the Budget sheet as well (168,000 here) and compare.

## FAQ

**What is the budget vs actual formula?** Variance = actual − budget. Variance % = (actual − budget) ÷ budget. For costs, a positive variance is overspending (unfavourable); for revenue, a positive variance is favourable.

**What is a good budget vs actual variance?** There is no universal number. A practical rule is a percentage and a minimum amount together, set per line: 3% on payroll can matter more than 30% on a small travel budget.

**How do I do budget vs actual in Excel?** Put `SUMIFS` on the budget sheet to pull in the actuals for each account and month, then subtract (formulas above). Avoid looking up the budget from each actuals line and summing, which counts the budget once per transaction.

**Why is my budget total too high in a budget vs actual report?** Almost always because the budget was joined onto transaction lines and then summed. Each budget row is counted once per actuals line. Sum the actuals first, or use a tool that totals each linked budget row once per group.

**Can ChatGPT or Claude do a budget vs actual analysis?** Yes, if they compute the totals rather than read them. With two small files uploaded, the chat runs code and can be right. With a full ledger, or every month again, connect the sheets so the agent sends a query and gets back counted totals: [how datatomcp works with Claude](/guides/connect-google-sheets-to-claude).

[Create a free account](https://app.datatomcp.com/login) and try it on your own budget and actuals. Free covers 3 datasets of up to 100,000 rows each.

More: [Test an AI data tool on linked sheets](/guides/test-set-linked-sheets) · [Why AI chats get spreadsheet totals wrong](/guides/ai-spreadsheet-totals-wrong) · [What pasting a sheet costs per question](/guides/spreadsheet-ai-token-cost-calculator)