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

By Killian, founder · Updated 3 November 2026 · Markdown

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:

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.

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 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, 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, or 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?”

“Only September: where did we go over?”

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 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.

Create a free account 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 · Why AI chats get spreadsheet totals wrong · What pasting a sheet costs per question