How I build budget vs actuals in Excel
It's budget season, so I want to show you how I build my favorite budget vs actuals report in Excel.
It runs on a pivot table, which is just a report you can filter and drill into.
Pick a department, pick any time period, and click from a summary line all the way down to a single account.
Build it once, hit refresh when each month's numbers come in, and it saves you hours of reporting.
But you can't build it straight from your accounting software…
The data that comes out of it isn't set up for a pivot table.
Accounts run down the side, dates run across the top, and the values sit in the middle.
Easy to read.
Really hard to work with.
So we'll fix that layout first, and then I'll show you a sign column: a plus one or a negative one next to every account.
It makes every good variance show up as a positive number, so revenue above budget and an expense below budget both read as good news.
Alright, so that export layout, with accounts down the side and dates across the top, has a name. It's called semi structured data.
It's great for understanding what's happening in a business.
But it's very difficult to manipulate, and it needs to be reshaped before it goes anywhere near a pivot table.
A pivot table wants structured data instead. One column for the account, one for the date, one for the amount, and every row is a single account in a single month.
And the tool that does the reshaping is Power Query, which you'll find under the Data tab in Excel.
It can look overwhelming the first time you open it.
But it's really just a list of steps, like using the first row as headers or renaming a column. You set them up once, and it runs them all again every time you hit refresh.
The example file in the video has actuals for four departments, covering 2025 through September 2026. Each department also has a budget covering all of 2026.
I start with the general and administrative actuals.
I give the data a named range, load it into Power Query, and unpivot the date columns, so every account and date ends up on its own row.
Instead of repeating every step for the other three departments, I duplicate the query and open the advanced editor, which shows those steps written in a language called M.
And you don't need to know how to read it.
I'll admit… I can't read it either.
I just find the first department's range name in the code and swap in the next one. Every step carries over.
Before combining anything, I add a department column to each query.
Otherwise, once all four are stacked together, there's no telling which numbers belong to which department.
Then I append all four, which stacks them into one query called actuals.
Now that query goes into a pivot table. Accounts go in the rows, and the amounts go in the values area.
I add a timeline for the date and a slicer for the department.
Click any month or any department, and every number updates.
But right now it's just a long list of raw accounts.
I want it to read like a real P&L. Sections like revenue and operating expenses should open up into summary groupings, and then into individual accounts.
For that, I build a mapping table.
It lists every account in the chart of accounts and the section it belongs to. Each account also gets a summary grouping one level above it, like nonrecurring income.
I leave the subtotal rows blank in the mapping on purpose, since the pivot table doesn't need them.
Then I merge the mapping into the actuals in Power Query.
If you've never done a merge, it works pretty much like a lookup, only inside Power Query.
The subtotals come through blank, so I filter them out.
And now every account knows exactly where it belongs in the P&L.
There's still one problem though.
The sections show up in the wrong order. A P&L should run revenue, cost of goods sold, gross profit, and on down to net income.
You could drag each section into place by hand.
But there's a better way.
Under File, Options, Advanced, you can create a custom list in the exact order of your P&L.
Then sort the pivot table A to Z, and instead of going alphabetically, Excel follows your list.
The budget goes through the same steps, so I duplicate the query again and point it at the budget's named ranges.
When I did… Power Query threw an error.
The budget only covers 2026, so it has fewer date columns than the actuals. And the query was looking for a column that didn't exist.
Once I removed the references to those extra columns, the error went away.
Next, I merge the budget into the actuals, and this is where it's easy to slip.
Because matching on the account alone isn't enough.
The budget has to match on the account, the date, and the department. That way you pull the budget for that exact month and that exact department.
A lot of rows still come through with a blank budget, since there's no budget for 2025 and some accounts were never budgeted.
I replace those blanks with zero, so every row has a number for the variance math.
Now budget and actual sit side by side.
Now for the variances, which is where that sign column comes in.
What does a good variance look like?
Well… it depends on the account.
Revenue above budget? That's good.
Expense above budget? That's bad.
In the mapping table, I add a sign column.
Revenue, gross profit, net income, and any other line where higher is better all get a plus one.
And every expense account gets a negative one.
Because the sign lives in the mapping, it comes along with the merge. Then I add a variance column in Power Query: actual minus budget, multiplied by that sign.
Now a positive variance is always good news, and a negative one is always bad, no matter what kind of account it is.
For the percentage, I add a calculated field in the pivot table that divides the variance by the absolute value of the budget. I wrap it in IFERROR in case the budget is zero.
That absolute value matters.
Without it, a bad variance on a negative budget would show a positive percentage.
Last comes the variance chart.
The chart needs the data shaped differently, with operating expenses only and one bar per department. So I copy the pivot table, park the copy off to the right where nobody needs to see it, and set it up that way. Then I build a bar chart from that copy.
In the bars' fill settings, turn on invert if negative.
Now the bars go yellow for good variances and blue for bad ones.
Pick a department, pick a month, and the whole report updates, chart included.
Now look.
Building this from scratch takes real work.
A query for every department, and a mapping and sign for every account.
That's a big part of why I built Model Wiz, the tool I wish I had when I started my fractional CFO firm.
It connects to your accounting software and builds a CFO grade financial model in Excel, budget vs actuals included.
So what about you?
How are you building budget vs actuals right now, and how many hours does it take each month?
Hit reply and let me know. I read every single one.
Josh (Your CFO Guy)
Thanks for reading my article. My goal is to help you grow in your career with proper Finance and Accounting.
If we're not already connected on Social media, let's get connected and send me a message to say hi. I promise to respond
