With normal charts, you find data and then you plot it. But with analytical charts, you transform your data in various ways to discover hidden insights when you plot it.
For example, plotting the Rate of Change (ROC) of your data can reveal underlying strength or weakness in your company, information that your original data hides. The chart below is a case in point.
This unusual transformation of the data reveals a weakness in the US economy, a weakness that I’ve never seen discussed before…and I read articles about business and the economy every day.
Unlike typical ROC charts, this chart plots the ten-year, annualized ROC. Its underlying data is the real (that is, inflation-adjusted) GDP in the U.S. As you can see, the ten-year growth rate of the U.S. GDP has fallen in two stair-steps over the past 50 years. And particularly, that growth rate has plummeted since the Great Recession.
So that I could be as productive as possible when I created this chart, I use the Excel-Friendly Database Strategy, which consists of four steps:
- You stow your data in an Excel-Friendly Database, This is a database to which you can link your worksheet formulas. In this case, I used Excel Tables as my database.
- You flow your data to an Excel template that’s set up as a springboard for quick and easy development.
- You show your data in your reports and analyses.
- You grow your Excel system by continually improving it.
Several months ago, I set up an Excel template that uses Power Query to download data from the Federal Reserve Economic Database (FRED). Therefore, to create this chart, I first entered the two series IDs I needed into a Table and downloaded the data in less than five seconds to my data Table. And because the template was linked-enabled, my key formulas already were set up to return data to my report, which, in this case, was a single chart.
The note at the bottom of the chart tells you the two FRED data series I used, which you can see at these URLs:
https://fred.stlouisfed.org/series/USARECM
https://fred.stlouisfed.org/series/GDPC1
Notice the pattern of the URLs here. Links to all of FRED’s 500,000+ data series add the series IDs as the last section of the URL.
The first series contains 1 for each quarter that was in recession, and zero otherwise. The second series shows real GDP in billions of 2009 Dollars.
Once this data was in my Table, I could work with it easily.
The Staging Table
With my data easily available, I set up a staging Table to contain just the data I wanted to chart, which was from 1965 forward. The first column contained the dates for each quarter beginning in Jan-1965.
Another column in the staging Table used a SUMIFS formula to return the GDP value for each date. Another column used SUMIFS and EDATE to return the GDP value ten years earlier than each date in the Table.
A third column calculated the annualized 10-year growth rate for each date. That is, it divided the current value in its row by its ten-year value to find one plus the growth percentage over ten years. Then it took that ten-year growth number to the one-tenth power, and subtracted one, to find the annualized growth rate for that period over then years.
To illustrate, the last point in the chart is for October-December, 2017, and has the value 17,271.702. The equivalent value from 2007 is 14,991.784.
Dividing the first number by the second gives us approximately 1.152…which equals one plus the 15.2% growth amount over that ten-year period. To find the annual growth rate over those ten years, I took 1.152 to the one-tenth power, and then subtracted one. And that gave me the value 1.4%, which is plotted in the chart.
As a formula, this calculation is: (17271.702/14991.784)^(1/10)-1 = 1.4%
In contrast, the first point on the chart represents a growth amount of 44.3% over ten years. Taking 1.443 to the one-tenth power, then subtracting one, gives us an annualized growth rate of 3.7% over those 10 years.
Digging Into the Chart
Here’s the chart again:
The blue line shows series 1, which is a plot of the annualized growth rates. To add the dotted trendline, I first selected series 1. Then, in the Chart Tools, Design, Chart Layouts group, I chose Add Chart Element, Trendline.
Series 2 indicates recessions, using the technique I described in How to Show Recessions in Excel Charts.
Series 3 uses Excel’s new charting feature that I described in Two Business Uses for Excel’s New Chart Feature.
If you don’t have that feature in your version of Excel, you could set up a Table that plots each of the three lines in a different series. But doing so requires clever formulas and dynamic range names, which I won’t go into here.
Finally, the three red labels are returned by data labels attached to specific points in the red line. The faint red lines above each label are leader lines, which you have the ability to include when you set up your data labels.
