As a consultant you’ve probably heard the phrase, “What you measure is what gets managed.” to the point you might argue with it. But, it is true.
We know that measuring is important, but measuring is only the start. If you are going to improve what you are measuring you need targets. And before you start changing you need to know how far off the target you are. This Excel trick is an easy way to see the actual value as a column with target value shown as a floating bar, as shown in this figure.
Click here to learn more ways to create budget vs actual charts.
Click here to get 21 Excel budget templates and tips on creating budgets in Excel.
This figure shows a simple Actual and Target column chart. The trouble is that it takes some visual and brain work to compare the Actual and Target columns. We could improve this Actual vs Target chart by creating markers for the Target values.
To create markers we need to first change this dual-column chart into a combination chart with the Target data showing as a line. To do that right-click on a Target column and select Change Series Chart Type. In the Change Chart Type dialog box select Line with Marker for the new Target type. You should now have a chart showing columns for Actual and a line with markers for Target values.
Now, here are the tricks. You need to change the markers into a wide line and change the line color to invisible. To do that, right-click a marker on the line so that the entire line and markers are selected. Choose Format Data Series.
Change Marker Options to Built-In with a line style and size of 10 or 12. This changes the default box marker into a line.
Without closing the dialog box select Line Color and change the line to No Line. Choose Ok.
While you are in the formatting dialog box you can change the colors of the two data series to fit your organization’s color schemes. Switch between formatting the different data series with the Series Options choice in the Format Data Series dialog box.
If your main concern is watching the differences and noticing when actuals are below target, then you may want to use a column chart that shows the amount of difference between actual and target.
Click here to learn more ways to build budget vs actual Excel charts.