Here are five of the easiest to create and cleanest budget vs. actual (target) Excel charts you might need as a consultant. I spent three hours searching through different Excel techniques and methods on the web and picked these five for being easy to create and cleanly showing the differences between budget and actual values.
When you present your consulting findings to management you want to insure you communicate clearly.
The world uses Excel’s budget vs. actual charts to monitor how far ahead or behind they are on budgets and projects. These charts are invaluable for prodding us to meet our goals.
I hope this list saves you time and boosts your productivity.
This is the basic actual vs. budget (target) chart we published in Critical to Success years ago. You should master this one before trying any others. It’s the basic black of Excel charts.
This is one of our most popular articles and ranks first on a couple of Google search result pages. It is quick and easy to create and people understand it naturally; the blue columns are actual values and the red markers are the budgeted or target values.
Click here to learn how to create a basic budget vs. actual chart in Excel.
Click here to get 21 Excel budget templates and tips on creating budgets in Excel.
When you want to show the variance between actual and budget (target) then use this Excel chart. This chart shows the difference between actual and budget (target), but with just a few mouse clicks it colors the negative values with a different color so they pop out.
Here’s how to show color alerts for budget vs. actual Excel charts.
Chandoo has one of the most awesome all-around websites for Excel. He did a great job here showing six different ways of charting actual vs. budget in a limited amount of space. If you need column reports to show a lot of data these in-cell charts or icons allow managers to quickly see which line items are not meeting target. Chandoo uses Excel’s Sparklines to fit a chart in a cell.
Techniques he includes are,
As usual Chandoo, you’ve done a very thorough job!
Here’s how to show budget vs. actual Excel charts within a cell.
I had never been to Jon Acampora’s site until I came across his method of putting the variance between budget and actual in an appropriately sized box. It’s a very straightforward and puts the variance box and numeric value right where your eye would expect to find it.
This is a quick and easy tip that can be used with columns or bars. I think a lot of managers would like to see this. Thanks Jon.
Here’s how to show variance on a clustered column or bar chart.
This is one of those “So cool” tricks that is a real head slapper. It looks like it takes some complex work, but it’s actually very easy. Although Chandoo is comparing productivity of a company to the national average in this example, his technique works just as well to compare budget vs. actual. In this example, Acme Inc.’s productivity is the black line. Above national average productivity produces green shading and below national average productivity produces red shading.
At the end of the same article Chandoo hints at four other ways of making similar budget vs. actual charts that use color to pinpoint the variance.
Here’s how to make a shaded Excel line chart that shows budget vs. actual and more.