As an analyst, I often need to keep track of the change in position of various players in the industry. More often than not, the position change needs to be tracked between two specific points in time – say quarter 1 vs. quarter 2 or perhaps year 1 vs. year 2. In this article we take a look at a few alternative approaches to plotting position changes and find out which one serves us best. For this example let’s take some freshly “cooked” up data.
The first alternative is a line chart. Not the best of ways to represent the data at hand. Representing multiple categories using a single line is never a good idea especially when they don’t share a relationship. Although one must acknowledge the fact that a line chart does try to provide a sense of the proportion of companies whose position went up versus the number of companies that went down. But then again, that may be entirely due to the data set used for this example … (or is it just me ).
The second alternative is the ubiquitous bar chart. Surprisingly in this case, the ever helpful bar chart also does not offer much help – it simply gets bogged down by the sheer number of categories that it has to handle.
With the line and the bar gone, lets turn our focus on the radar chart. Naah … that doesn’t look good either. Neither the value of the data points nor the proportion of companies moving up vs. down can be established without spending a disproportionate amount of time deciphering the chart.
Let’s look at some charts outside the usual assortment provided by excel. The first one is the bump chart.
This does look line what we were looking for? So well … ladies and gentleman, let me introduce you to our hero of the day … the bump …hey ..wait a minute … its a rank order chart masquerading as a position change chart. DISQUALIFIED ! Just for the sake of curiosity, what would this bump chart have looked like if we had used the absolute numbers to show positional changes rather than the rank.
That’s not half as good as the previous one, is it. Looks like we’ll have to search a bit more for this one.
Here’s something that I picked up from the Economist. The bars give the reader an idea of the quantum of change with the arrows pointing in the direction of the change. You might’ve already guessed – its a stacked bar chart. With a bit of color coding (red for decrease, blue for increase), this does look like a reasonable alternative. A visual comparison of the number of arrows of each color, allows the reader to get a fair idea of the proportion of companies that went up versus those that went down.
In case you were wondering how to get this one going – here are the steps:
1. Transform the basic data into four series. The first series represents the smaller of original (two) data points. The third series represents the larger of the two. The second and the third series are simply static portions of the – each one either having a small values (say 50 in this case) or #n/a (nothing) depending on whether (of the two original points) the first one was greater than the second or not.
2. Create a stacked bar chart using the charting interface in Excel.
3. Make the first series disappear by removing the fill.
4. Create three shapes from the drawing toolbar – two arrows and a rectangle and fill them with appropriate colors.
5. Select and copy the first shape (ctrl + c). Now go to the chart and carefully select the second series in the chart – the one shown in Red color and paste (ctrl + v) the shape over it. This step will transform the bar into an arrow.
6. Repeat the same step for the second and the third series in the chart.
If you are interested, you can download the worksheet with all these chart here.