I got a workbook from Dick Kusleika that he thought would be a good candidate for Chart Busters. Basically it’s his sales staff, with their sales and commissions figures. He uses a couple pie charts to compare the salesmen, in terms of sales, commissions, and relative commissions (normalized by sales). Dick describes the process:
1. I look at the Sales chart and look for anomalies in sales by guy. For example, I know Syd has good territory so I’m not surprised that he has a larger slice. David, however, has a crappy territory but I know he had a big order, so again no surprise. Bob also has a bad territory and he didn’t have any large sales, so that would induce me to look into the details and see if there is an error.
2. I look at the Sales chart compared to the Commission chart. They will never be perfectly proportional because guys have different commission rates. Also, some of the sales are non-commissionable. However, I want a chart that points out anomalies so I can investigate for errors (mistyped percentages, misapplied commission, etc).
Here is Dick’s data, and the two pie charts he uses.
Well, aside from the tiny labels (which may just be that way in this hastily hacked together example), the charts are pies, which as we know are generally not a particularly effective way to display data. I’ve decided to try this data in a panel chart.
The first thing I did was convert Dick’s data to a List (or a Table in Excel 2007), to allow easy sorting by any of the relevant columns. I added a column for Ratio, which was simply Commission divided by Sales. I formatted the Ratio column as a percentage and the Sales and Commissions as thousands. I added five more columns, one for each of the columns so far, and two spacer columns. The additional Sales column has this formula in cell E2:
This gives the largest Sales number a value of 0.95, and the rest are proportionally smaller. The additional Commission and Ratio columns have similar formulas. The spacer columns simply have the amount to bring the cumulative total to 1 or 2. For example, the formula in F2 is:
Start building the chart by selecting the data. Select A1:A7, then hold CTRL while selecting A1:I7, so both areas are highlighted. Insert a stacked horizontal bar chart.
Let’s do a little clean-up. Change the horizontal axis scale maximum to 3 and spacing to 1, remove the gray plot area fill, hide the two spacer series (no fill, no border). Format the vertical axis so the categories are plotted in reverse order (this puts the list in the same order as in the wheet), and so the axis crosses at the maximum category.
We can add a dummy series to add labels above each panel of the chart. Put this helper data into a range someplace:
Copy the data, select the chart, and use Paste Special with the following settings to add the data to the chart as a new series in the proper orientation.
The result is another bar chart series stacked on the rest. it’s not visible, since its values are all zero. Select the series by selecting one of the other series, and press the up or down arrow until this series is selected. Change this series to a Column chart type. Excel automatically put this series onto the secondary axis, and uses the labels on the secondary category axiss along the top of the chart.
Hide the column series by formatting it with no borders and no fill. Hide the primary value axis tick labels (lower horizontal axis).
Finally, if desired you can add value labels to the bars. Since the bars actually plot fractions, we will use Rob Bovey’s Chart Labeler, a free add-in which interfaces smoothly with Excel. Select the chart, click on the XY Chart Labels item on the Tools menu, then Add labels, and make the appropriate selections for the Sales series, then repeat for the Commision and Ratio series.
Now it’s an easy task to check the relative sales of each employee. You can sort by Sales, and see whether the Commission data is similarly sorted, or note whether there may be an error in the figures. The Ratio data is a further check that commissions are appropriately calculated.
So how would you analyze this data?