Chapter 9
In This Chapter
Using variance displays
Using progress bars
Creating bullet graphs
Showing performance against a range
Hopefully, this is an easy one to grasp. Someone sets a target, and someone else tries to reach that target. The target can be anything from a certain amount of revenue to a number of boxes shipped or to phone calls made. The business world is full of targets and goals. Your job is to find effective ways to represent performance against those targets.
What do I mean by performance against a target? Imagine that your goal is to break the land speed record, which is now 763 miles per hour. That makes the target 764 miles per hour, which will break the record. After you jump into your car and go as fast as you can, you will have a final speed. That number is your performance against the target.
In this chapter, I explore some new and interesting ways to create components that show performance against a target.
The standard way to display performance against a target is to plot the target and then plot the performance. This is usually done with a line chart or a combination chart, such as the one shown in Figure 9-1.
Although this chart allows you to visually pick the points where performance exceeded or fell below targets, it gives you a rather one-dimensional view and provides minimal information. Even if this chart offered labels that showed the actual percent of sales revenue versus target, you’d still get only a mildly informative view.
A more effective and informative way to display performance against a target is to plot the variances between the target and the performance. Figure 9-2 shows the same performance data you see in Figure 9-1 but includes the variances (sales revenue minus target) under the month label. This way, you see where performance exceeded or fell below targets, but you also get an extra layer of information showing the dollar impact of each rise and fall.
The target you use to measure performance doesn’t necessarily have to be set by management or organizational policy. In fact, some of the things you measure may never have a formal target or goal set for them. In situations in which you don’t have a target to measure against, it’s often helpful to measure performance against some organizational statistic.
For example, the component in Figure 9-3 measures the sales performance for each division against the median sales for all the divisions. You can see that divisions 1, 3, and 6 fall well below the median for the group.
Here’s how you’d create a median line similar to the one you see in Figure 9-3:
Start a new column next to your data and type the simple MEDIAN formula, as shown in Figure 9-4.
Note that this formula can be any mathematical or statistical operation that works for the data you’re representing. Just make sure that the values returned are the same for the entire column. This gives you a straight line.
Copy the formula down to fill the table.
Again, all numbers in the newly created column should be the same.
A thermometer-style chart offers a unique way to view performance against a goal. As the name implies, the data points shown in this type of chart resemble a thermometer. Each performance value and its corresponding target are stacked on top of one another, giving an appearance similar to that of mercury rising in a thermometer. In Figure 9-5, you see an example of a thermometer-style chart.
To create this type of chart, follow these steps:
Go back to the chart and delete the new vertical axis that was added.
It’s the vertical axis to the right of the chart.
A bullet graph is a type of column/bar graph developed by visualization expert Stephen Few to serve as a replacement for dashboard gauges and meters. He developed bullet graphs to allow you to clearly display multiple layers of information without occupying a lot of space on a dashboard. A bullet graph, as shown in Figure 9-6, contains a single performance measure (such as YTD [year-to-date] revenue), compares that measure with a target, and displays it in the context of qualitative ranges, such as Poor, Fair, Good, and Very Good.
Figure 9-7 breaks down the three main parts of a bullet graph. The single bar represents the performance measure. The horizontal marker represents the comparative measure. The background color banding represents the qualitative ranges.
Creating a bullet graph in Excel involves quite a few steps, but the process isn’t necessarily difficult. Follow these steps to create your first bullet graph:
Start with a data table that gives you all the data points you need to create the three main parts of the bullet graph.
Figure 9-8 illustrates what that data table looks like. The first four values in the dataset (Poor, Fair, Good, and Very Good) make up the qualitative range. You don’t have to have four values — you can have as many or as few as you need. In this scenario, you want the qualitative range to span from 0 to 100%. Therefore, the percentages (70%, 15%, 10%, and 5%) must add up to 100%. Again, this can be adjusted to suit your needs. The fifth value in Figure 9-8 (Value) creates the performance bar. The sixth value (Target) makes the target marker.
Select the entire table and plot the data on a stacked column chart.
The chart that’s created is initially plotted in the wrong direction.
Right-click the Target series and choose Change Series Chart Type from the menu that appears.
Doing so calls up the Change Chart Type dialog box.
Use the Change Chart Type dialog box to change the Target series to Line with Markers and to place it on the secondary axis; see Figure 9-10.
After your change is confirmed, the Target series appears on the chart as a single dot.
Go back to your chart and delete the new secondary axis that was added to the right of your chart; see Figure 9-12.
This is an important step to ensure that the scale of the chart is correct for all data points.
All that’s left to do is change the color for each qualitative range to incrementally lighter hues.
At this point, your bullet graph is essentially done! You can apply whatever minor formatting adjustments to the size and shape of the chart to make it look the way you want. Figure 9-13 shows your newly created bullet graph formatted with a legend and horizontal labels.
After you’ve built your chart for the first performance measure, you can use the same chart for any additional measures. Take a look at Figure 9-14.
As you can see in Figure 9-14, you’ve already created this bullet graph with the first performance measure. Imagine that you add two more measures and want to graph those. Here’s how to do it:
Hover the mouse pointer over the blue dot in the lower-right corner of the blue box.
The cursor turns into an arrow, as shown in Figure 9-14.
Click and drag the blue dot to the last column in your expanded dataset.
Figure 9-15 illustrates how the new data points are added without one ounce of extra work!
Before wrapping up this introduction to bullet graphs, I discuss two final thoughts on formatting:
First, if the qualitative ranges are the same for all performance measures in your bullet graphs, you can format the qualitative range series to have no gaps between them. For instance, Figure 9-16 shows a set of bullet graphs in which the qualitative ranges have been set to 0% Gap Width. This creates the clever effect of qualitative bands.
Here’s how to do it:
For those of you waiting on the section about horizontal bullet graphs, I have good news and bad news. The bad news is that creating a horizontal bullet graph from scratch in Excel is a much more complex endeavor than creating a vertical bullet graph — one that doesn’t warrant the time and effort it takes to create them.
The good news is that there is a clever way to get a horizontal bullet graph from a vertical one — and in three steps, no less. Here’s how you do it:
Create a vertical bullet graph.
For how to do this, see the “Creating a bullet graph” section, earlier in this chapter.
Use Excel’s Camera tool to take a picture of the bullet graph.
After you have a picture, you can rotate it to be horizontal. Figure 9-18 illustrates a horizontal bullet graph.
The nifty thing about this trick is that because the picture is taken with the Camera tool, the picture automatically updates when the source table changes.
In some businesses, a target isn’t one value — it’s a range of values. That is to say, the goal is to stay within a defined target range. Imagine that you manage a small business selling boxes of meat. Part of your job is to keep the inventory stocked between 25 and 35 boxes in a month. If you have too many boxes of meat, the meat will go bad. If you have too few boxes, you’ll lose money.
To track how well you do at keeping the inventory of meat between 25 and 35 boxes, you need a performance component that displays on-hand boxes against a target range. Figure 9-19 illustrates a component you can build to track performance against a target range. The gray band represents the target range you must stay within each month. The line represents the trend of on-hand meat.
Obviously, the trick to this type of component is to set up the band that represents the target range. Here’s how you do it:
Set up a limit table in which you can define and adjust the upper and lower limits of the target range.
Cells B2 and B3 in Figure 9-20 serve as the place to define the limits for the range.
Build a chart feeder that’s used to plot the data points for the target range.
This feeder consists of the formulas revealed in cells B8 and B9 in Figure 9-20. The idea is to copy these formulas across all data. The values you see in the Feb, Mar, and Apr columns are the results of these formulas.
Add a row for the actual performance values, as shown in Figure 9-21.
These data points create the performance trend line.
Right-click the Values series and choose Change Series Chart Type from the menu that appears.
Doing so calls up the Change Chart Type dialog box.
Using the Change Chart Type dialog box, change the Values series to a line chart and place it on the secondary axis, as shown in Figure 9-22.
After your change is confirmed, the Values series appears on the chart as a line.
Go back to your chart and delete the new vertical axis that was added.
It’s the vertical axis to the right of the chart.
In the Format Series dialog box, adjust the Gap Width property to 0%.
That’s it. All that’s left to do is apply the minor adjustments to colors, labels, and other formatting.
3.12.74.189