In 3 Simple Tricks to Improve Analytical Charting in Excel, I showed you how to shift time periods, index your data, and chart your rate of change. In this article, I’m going to show you additional ways to transform your charts so your can give your readers greater business insight.
Trick 1: Add Context to Your Charts
By adding context to your charts, you help your users find additional meaning in what you show them.
In the last figure in 3 Simple Tricks to Improve Analytical Charting in Excel, for example, I provided context in two ways. First, I showed two lines, which helped readers to understand Massachusetts’ employment performance in the context of US employment.
Second, I showed the two US recessions during the years shown in the chart. By doing so, I displayed both measures of performance in the context of the US economy.
That particular recession indicator is the most-common one used in the US. It relies on recessions as defined by the National Bureau of Economic Research (NBER). However, I sometimes display the other definition of recessions, which is used by the Organisation for Economic Co-operation and Development (OECD). In the US, at least, the OECD version of recessions is very useful, because it tends to include downturns, and not merely recessions.
The following chart illustrates the use of both these tricks. First, it shows that US Real GDP grew at an increasing rate from about 2016, while total employment grew at a decreasing rate during that time. That “scissor” pattern suggests that good employees are getting more difficult to find as the economy continues to grow. That is, we appear to be nearing full employment.
Without both lines, this chart would be like the sound of one hand clapping.
Second, the light blue shading uses the OECD’s definition of recession to indicate two downturns in the US since The Great Recession. We see that the growth rate of our Real GDP fell during both those downturns. But unlike during the recession, the real GDP in the US never actually fell during those two downturns…that is, its growth rate never dropped below zero.
There are additional ways to add context to your charts.
The following chart, for example, compares the results of four investment strategies since 1980. Charting merely the top two or the bottom two strategies wouldn’t provide nearly as much insight as charting all four.
The two best strategies shown are investments in the stock market and investments in Real Estate Investment Trusts (REITs). With REITs, returns are reinvested so that real estate profits compound.
In contrast, the two worst strategies shown are that of investing in real estate without reinvestment (the Home Price Index), and in gold.
Trick 2: Show Ratios in Your Charts
I noticed that the price of regular gasoline in my neighborhood jumped by a dime last week. So when I got home, I checked the price of crude oil. Sure enough, the price of West Texas Intermediate crude oil has risen from about $42.00 per barrel in June, 2017, to a recent price of more that $65.00.
And that got me to wondering, just how IS the price of gasoline affected by the price of crude oil?
If you were interested in answering this question, you could take at lest three approaches with an Excel chart. First, you could display the price of crude oil in one chart and the price of gasoline in another…and then compare the two visually.
Second, you could use an XY plot to show a meandering line like the one used for Beveridge Charts, with crude oil prices on one axis and gasoline prices on the other. But as one reader in a senior financial position for a major car company wrote me recently, most senior executives would never take the time to figure out the insights that the chart might reveal.
So, third, you could display the ratio of gasoline to crude oil, as shown in the following figure.
As you can see, back in about 1998 the price of gasoline rose to nearly 9% of the cost of crude. But since then, the price of gasoline has fallen relative to crude…until it’s now slightly more than 4% of crude.
We also can see that recessions and slowdowns seem to have no consistent effect on the ratio of gasoline to crude. Specifically, we can see that the ratio dropped during the Great Recession, rose during the last slowdown, and merely jumped around during the other two downturns.
This chart is useful for another purpose. The national news frequently mentions the price of crude oil when it rises or falls for some reason. The next time you hear such news, multiply the crude-oil price by 5%, which will tell you about what the average price of gasoline will be in the US if the crude oil price stays at its headline-grabbing rate.
Of course, because your state probably taxes gasoline at a different rate than the national average, you might need to add or subtract some amount to adjust your estimate.
Trick 3: Include Small Tables When Needed
As a general rule, it’s a bad idea to add numeric data to charts. This is because the benefit of charts is that they allow us to step back from the many details in our business and see the patterns of performance that are hidden in all that detail.
At times, however, it’s useful to add small tables to your chart figures to provide small, easily consumable detail your figures.
For example, Just before the anniversary of President Trump’s first year in office, a Bloomberg.com email newsletter used a simplistic chart to illustrate that nearly 40% of the gain in the Dow Jones Industrial Average had come since his tax-cut plan was announced.
Because I didn’t like their chart, I decided to create one of my own:
I added the table to this figure for several reasons. First, many people follow the Dow Jones Industrial average, and they want to know the specific values on the specific dates that the chart in this figure displays.
Second, because the Dow fell about 2,000 points during the first third of February, 2018, we can put the figure in better perspective when we can mentally compare the current price to the one on January 19.
Third, the chart divides performance into three stages, and without the table, it’s difficult to discern the information needed to estimate the growth rates at each stage.
In short, if you know that your audience is going to demand key pieces of information, it’s okay to provide the data in small, simple tables with your chart figure.
One thing that’s not obvious about any of these charts is that you need to set up the proper plumbing, which flows your data from the source to your staging areas in your workbook, and then to your charts. Without that plumbing, your charts would be WAY too much work to maintain.
In the months ahead, I plan to tell you more about that plumbing.
And also, of course, these aren’t the only ways to transform your data to bring new insights to your reports and analyses. You could, for instance, use Beveridge Charts, Fishbone Charts, and other transformations.
But the other transformations are for another time.
