Let's say you want to track the performance of four critical areas of your business versus previously defined targets: sales volume, operating margins, fixed costs and cash flow. Because these indicators use different measures and you need a quick snapshot of your performance, you have to normalize the data first in order to make it comparable and then use a common chart layout.
So, if sales volume is measured in units, operating margins as a percentage of sales, fixed costs and cash flow in dollars, you may convert the actual figures as percentages of respective target values to assess performance. Have a look at the following example:
As you can see, we listed the four indicators in a table comparing actual vs. target figures and divided the actual by the targeted numbers to reach a common sized performance evaluation. Of course, in the case of fixed costs, the target has to be seen in the opposite way of the remaining indicators since the objective is to minimize them. In this case, we divide target by actual results and reach 105% because fixed costs were lower than expected.
You can also see in the table above that fixed costs were actually the only target met. The other three indicators show a performance lower than one, i.e., below the target. However, analyzing the different degrees of attainment, we can note that sales margins are closer to the target than fixed costs. This leads us to the question of how to classify results: what is a bad, acceptable and an outstanding performance?
The answer, of course, depends a lot on the culture of each company, especially on how serious targets are seen but also on past performance. In most cases, even if results are not reached at 100%, it is acceptable to be at least at 85%. If actual results are above 95%, most managers accept them as a fully reached target. Considering this, we can start designing our performance bands:
- Poor: below 85%
- Acceptable: between 85% and 95%
- Good: between 95% and 100%
- Excellent: above 100%
Because we will use a stacked column chart to display performance (you will see how later), data for designing the performance bands could be something like this:
The data adds to 100%, which corresponds to the target.
Now, we can highlight this data and use it in a stacked column chart. Simply, select this range and go to: Insert -> Stacked Column Chart. You will get a chart like this:
We need to pile up all the data in a vertical format to display our bands. An easy way to do this is to switch columns with rows. Right click on any of the columns above and click "Select Data...". You will see the Data source in a screen like this:
Now click the "Switch Row/Column" button above -> OK and you will get the first draft of your performance bands:
It's also a good idea to simplify the chart design by removing all unnecessary information.
- Go to the ribbon, select "Chart Tools" -> "Layout" and remove the X-axis Label
- In the same menu, remove the Legend on the right;
- You can also replace the colors by something more discrete. Choose a design with different tones of blue, from the "Chart Tools" -> "Design";
- Format the vertical axis defining the minimum value to 0 and the maximum to 1,20;
- Narrow the chart width.
You will obtain something like this:
It looks more like a bullet to me. Now all we are missing is the actual result. This will be the information we want to see in the chart to compare with the performance bands that will tell us how well we are doing.
To do this, we need to add another data series. Right click on one of the columns and choose "Select Data...". Then press "Add". Choose the cell that contains the Actual results (in the sales example that would be 83%) and include it in the range "Series Values:" Click OK.
Now we will get another column on top of the 3 we already had. This is because we are using a Stacked Column Chart, so Excel will just pile this series as it does with all the others. This will go out of our scale because we had defined the maximum of 120%.
Right click on the fourth series (the one at the top) and choose "Format Data Series...". To segregate this column from the other three we will have to use another axis. Select "Secondary Axis" , from "Plot Series on" to do that. Now your chart will look like all details are gone:
This is because the fourth series plotted on the secondary axis is overlapping the other three. We need to separate it from the others. Right click on it, select "Format Data Series..." and drag the Gap Width selector to the maximum, i.e., 500%.
From the chart above, it seems that the actual result is above 100%, which is not correct. This is because the secondary axis scale was automatically defined. In order to correct the problem, we will need to define the same range for the scale: a minimum of 0 and a maximum of 1,2.
- Right click on the secondary axis;
- Choose "Format Axis...";
- Set the minimum to 0 and the maximum to 1,2.
At this stage, the chart looks more realistic. All we need to do now is to configure the chart design to make it more appealing. Since we are using soft colors for the performance bands, we can now use a strong red for the actual result. This will highlight the current performance. We can also remove the right axis as it is redundant.
Now we can just copy and paste this chart and use it with the other 3 indicators. All we need to do is to replace the actual numbers.
Adding a title to each of the charts and aligning them would produce this simple, well designed Dashboard.