Budget vs actual report in Excel: who is over budget and why, with an AI agent

Short answer: A budget vs actual report sets each month's spend against the budget by department, office and account, with the variance in dollars and percent. Combine the monthly actuals, merge them with the budget on the same keys and add an office lookup. Before you trust it, check for duplicated lines, mismatched account names, spend with no budget line and offices missing from the lookup. An AI agent can do the update and explain who is over budget.

A budget vs actual report is simple arithmetic, but the files behind it rarely line up: lines exported twice, an account renamed, a new office nobody added to the lookup.

Here is how to build one, from a recent tutorial, then an agent updating a firm's October report from four Excel files, and the follow-up it needed.

The tutorial this is based on

Automate an Actuals vs Budget Report in Excel by Kenji Explains, published 4 October 2026 (in English). A 14-minute build: Power Query combines the monthly actuals and merges them with the budget and an office lookup, and a PivotTable shows the variance.

How do you build a budget vs actual report in Excel?

  1. Put each month's actuals export in one folder and combine them with Data > Get Data > From Folder.
  2. Set the date column to a date and add the start of the month.
  3. Group by month, office, department and account, summing the amount, so the keys match the budget's.
  4. Load the budget and merge it with the actuals on those four keys.
  5. Drop the months with no actuals yet and add a variance column: budget minus actual.
  6. Merge an office lookup to add each office's name, region and country.
  7. Load it to a PivotTable with departments and accounts in rows, add a variance % and slicers for month, office and region. Next month, add the new file and refresh.

Where it usually goes wrong

How an AI agent does it from Slack

We gave the agent four files for a made-up analytics firm, Halverson & Quill Analytics: the July to September actuals, the October actuals export, the July to December budget and the office lookup. Every company, name, tax number and amount in these files is made up. The agent works in Slack, where it shows up as Mnemio AI, because Accountant AI is built on Mnemio AI.

The message in SlackOctober actuals are in for Halverson & Quill. Update budget vs actuals for October and the year to date, and tell me who is over budget and why.
Slack request to update budget vs actuals for October and the year to date from four Excel files, and the start of the agent's reply
The request with the four files. The agent also saved a workbook with its tables.

What the agent did

  1. Found seven September lines at the top of the October export ($28,057.64) that were already in the July to September file, and dropped them.
  2. Reported October at $358,316.64 against a $303,100.00 budget, $55,216.64 (18.2%) over, and the year to date $35,994.43 over, by department and office.
  3. Traced Engineering's overspend to one invoice, a $39,800 annual cloud commitment booked in full to October, and suggested booking it as a prepayment ($3,316.67 a month).
  4. Traced most of Marketing's overspend to an $18,500 sponsorship with no budget line, and kept it in the report instead of dropping it.
  5. Mapped October's "IT Software" to the budget's "Software" and kept a $2,340 airline refund as a credit.
  6. Got three things wrong in its first reply: its office table left out the new Phoenix office, which isn't in the office lookup; it gave Boston's cloud budget as $28,000 instead of $31,000; and it blamed Seattle's overrun on cloud hosting.
  7. Asked why the office rows didn't add up and to recheck the cloud budgets, it found Phoenix ($5,500.00 budget, $5,455.45 actual), gave the right budgets and showed Seattle's overrun came from contractors and travel.
The agent's October and year-to-date totals and department table, after dropping seven September lines repeated in the October file
It dropped seven September lines repeated in the October export ($28,057.64), then gave October and the year to date by department. Plus means over budget.

What it found

What the agent found, after one follow-up
What the agent foundAmount ($)Where
October over budget, after dropping duplicates55,216.64Whole firm, 18.2% over
Annual cloud commitment booked in one month39,800.00Engineering, Boston
September lines repeated in the October file28,057.647 lines, dropped
Sponsorship with no budget line18,500.00Marketing, New York
New office missing from the office lookup5,455.45Phoenix (OF-108)
Contractors over budget1,981.11Engineering, Seattle
The agent's suggestion to book a $39,800 annual cloud invoice as a prepayment, and the $18,500 New York sponsorship with no budget line
Engineering's overspend is mostly one annual invoice booked in October; Marketing's is mostly a sponsorship with no budget line.
A follow-up asking why the office rows don't add up to the totals, and the agent's answer: the Phoenix office, OF-108, is missing from the office lookup
Its first office table left out the new Phoenix office. Asked why the rows didn't add up, it found it. It put Phoenix in West; the lookup has no row for it, so the region needs confirming.
The agent's office table for all eight offices, October and year to date, tying to the totals
The corrected office table ties to the totals.
The agent's recheck of Boston and Seattle cloud hosting against the budget, and Seattle's overrun broken down into contractors, travel and supplies
Its first reply had Seattle's cloud budget wrong. Rechecked, Seattle's cloud hosting was under budget; contractors and travel drove the overrun.

What you still check

Questions accountants ask

What does a negative variance mean?

It depends on the convention, so state it on the report. The tutorial uses budget minus actual, so negative means overspend. The agent used actual minus budget and labelled every overspend "over budget".

Does the agent replace the Power Query model?

No, they do different jobs. The model refreshes the same report when you add a month's file. The agent reads the files, checks them for duplicates, mismatched names and missing offices, explains the variances and saves a workbook. You can keep the model and have the agent check each month before the report goes out.

Can it run every month?

Yes. Agents can run on a schedule, so the report and the list of who is over budget can be ready when the month's actuals arrive. It asks before changing anything in your files.

Hand this job to an agent

Bring one of your own files to a 30-minute call and watch an agent do it.

Book a 30-min demo