Charts are great at revealing trends, patterns and relationships in your data. We love how they enable us to glean information in the blink of an eye, but beware of the line chart because it can lie.
Let’s take the line chart below that plots the trend of revenue and profit over time. It looks like revenue is growing faster than profit; after all revenue shows a steady incline whereas profit is quite flat.
Present a chart like this and you’ll be inundated with questions from the CEO who is irate that either COGS or overheads, or both have increased, and all the extra revenue they’ve worked so hard to generate appears to be eaten up by increased costs.
To be fair, it’s not the line chart’s fault. The first line chart is plotting the data correctly. It isn’t a bug in the chart. It’s a case of relativity; a 10% increase on $1m is always going to be a bigger number than a 10% increase on $100k, and when you plot disparate data that continues to grow over time, even at the same rate, the gap is going to widen.
The Truth Chart 1 – Log Scale
However, if we look at the data plotted on a log scale we can see that revenue and profit are actually growing at similar rates.
Phew, the production and operations managers have been spared, but now the CEO is after you for producing a misleading chart!
Using a Log scale is one option to prevent line chart lies, but some people find these a bit confusing to interpret so let’s look at another Truth Chart that will also spare your life and job.
The Truth Chart 2 – Index Numbers
The chart above plots the change in revenue and profit figures over time relative to the starting position in 2001. These values are known as index numbers (more on that in a moment).
It tells a completely different story to the original line chart. Here we can clearly see that the growth rate of revenue and profit have followed a fairly similar pattern, and by 2013 they were almost the same.
Calculating the Index
We index numbers by saying that in 2001 (our base period) both revenue and profit were 100, or 100% and from there we calculate the change for each year since 2001.
It’s tricky to explain in words alone so let’s take a look at this table which shows the indexed results in columns D and E (remember 1 = 100%):
In cell D3 we can see that the indexed revenue is 1.14, which is the same as saying that the revenue in 2002 is 14% higher than it was in 2001. Likewise the profit in 2002 is 7% less than it was in 2001.
Each subsequent year is also compared to 2001, our base year, to come up with the indexed value for that year.
The formula in D2 is =B2/$B$2 which is then copied down the column so that D3 contains =B3/$B$2 and so on.
If you liked this, or know someone who might inadvertently be lying with line charts ;-), click the buttons below to share it with your friends on LinkedIn, Google+, Facebook and Twitter.