8 ways to show budget vs actuals

When leadership asks how the business did against budget, what do you show them?

A summarized P&L with every variance laid out? A donut chart with one big percentage in the middle? Or a waterfall that shows exactly what pulled you away from budget?

There's no single right answer, because each one has its time and place.

So instead of picking one, I built eight different ways to show budget vs actuals in Excel.

I know, eight sounds like a lot…

But each one answers a different question leadership might ask.

They start with a simple table and end with a trend chart that gets into what I'd call serious Excel black belt territory.

Alright, so let's start with the comparison financials, the report most of the others pull their numbers from.

It's your P&L summarized into categories, so you're not looking at every account until you need to.

Each line compares actuals against budget, the prior period, and the prior year, in both dollars and percentages.

The dollar tells you how much you missed by… and the percentage tells you how significant that miss actually was.

Now, one rule runs through the whole table.

A favorable variance is always positive and an unfavorable one is always negative, whether you're looking at revenue or expenses.

Revenue is actual minus budget, but for expenses, you flip it to budget minus actual.

Now, this next one zooms out to just eight key metrics, and it's what I like to call the KPI view.

It gives you a high level picture of the business on a single page.

Each metric gets the same comparisons as the P&L: budget, prior period, and prior year.

But now good variances turn green, bad ones turn red, and a little arrow shows whether each number went up or down.

And those arrows come straight from custom number formatting.

Hold Alt and type 30 for the up arrow, or 31 for the down arrow.

Okay, so those first two are great for detail, but they're missing one crucial piece.

A visual that tells you what's happening at a glance.

That's where the donut chart comes in, and it puts your performance against budget front and center as a percentage.

And the color changes on its own, depending on whether the variance is favorable or unfavorable.

So how does a chart change color by itself?

The trick is four chart series instead of two, so one pair shows up for a hit and the other pair for a miss.

Alright, and then there's probably the easiest one of the eight to build.

A column shows what you actually achieved, and a little dash marker shows your budget.

At a glance, you can see whether you're above or below target, and by how much.

But stay with me, because this chart has one blind spot…

It doesn't tell you whether being above budget is good or bad.

Revenue above budget is great.

Expenses above budget aren't.

The fifth report fixes that by showing budget, actual, and variance side by side.

And the variance turns green when it's favorable and red when it isn't.

After that come the ranked variances, the waterfall, and the trend chart I mentioned earlier.

You'll find the other four in the video, along with exactly how I built all eight.

All eight of these reports depend on a budget and actuals that stay current every single month.

That's exactly why I built Model Wiz, and it's where the budget in the video came from.

It connects to your accounting software, builds your financial model in Excel, and rolls it forward each month.

So what about you?

When you report against budget, do you lead with a table like the comparison financials, or a chart like the donut?

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