Here’s a great way to discover new insights in your company’s data:
Always watch for new methods of looking at data…even if you care nothing about the data that the method uses. Then, when you understand the technique, think of ways you might apply it to data that you DO care about.
The Beveridge Chart is a case in point.
Some time ago, I stumbled across a blog post by Christian Zimmermann, an economist working for the Federal Reserve Board of St. Louis. His post was about the Beveridge Chart, my more-recent version of which is shown below.
He wrote that the “classic scatter plot used in economic analysis is the Beveridge curve, which describes the dynamics of the labor market through the business cycles, with the unemployment rate on the horizontal axis and the job openings rate on the vertical axis.
“Thus, every point corresponds to the values of those two rates on a particular date, with the dates connected by a line.”
“As one would expect, when the unemployment rate is high, the job openings rate is low (and vice versa).”
This curve, he wrote, typically generates “a scatter plot that looks like a banana, as [labor] markets react sluggishly to changing conditions.”
You’ll find the updated version of his blog post here.
Even if you don’t care about US unemployment trends, this is an amazing chart, for two reasons.
First, it compares how two measures have varied in tandem over more than ten years, with periods of recession displayed as a red line. The Wikipedia topic about this curve tells you more than you probably want to know about the information that economists can derive from it.
Second, this curve would have been much more difficult to create without Excel 2016’s new ability for formulas to set gaps in your line and scatter charts
That’s fine for economists. But what if all you really care about is your own industry?
Suppose, for example, that you’re in construction, or your company sells to that industry. Compared to the general economy, how has the health of your industry trended over the past ten years?
To start to answer that question, let’s apply the Beveridge analytical charting technique to construction employment.
Here, you can see that Total Nonfarm Payrolls fell from 2007 to 2011 as the recession came and went.
But look what’s happened since 2011.
Total US employment has recovered nicely since then.
Before the recession, as you can see, total employment was at about 138 million. But by mid-2017, total employment had jumped to about 146 million people.
On the other hand, construction jobs haven’t done nearly so well. In fact, at about 6,800 thousand jobs, they’re about a million jobs less than they were ten years ago.
Why might that be?
In part, could it be that the demand for homes has decreased for some reason? If so, house prices would still be low.
However, according to data from the S&P/Case-Shiller index of house prices, that’s not the problem.
Today, in 2017, as you can see, housing prices are back nearly to where they were in 2007.
On the other hand, at about 760 thousand persons, residential construction jobs are significantly less than they were ten years ago.
Let’s take one last look to see if we’re missing something. Perhaps the construction industry has become highly automated in the past 10 years. So let’s look at total construction spending.
The previous two charts used different units of measure. Although that’s perfectly okay, that does make it difficult to compare relative changes in the data.
In contrast, this chart compares total construction spending to total personal consumption by comparing indexes of both measures, rather than their dollar values.
To see why this is useful, notice that over the past ten years, total personal consumption never fell below its 2007 level of 100, even during the Great Recession. Its growth merely slowed.
Specifically, it grew by about 10 points in the four years between 2007 and 2011. And then it grew by more than 30 points in the last six years.
By mid-2017, personal consumption was nearly 40% above where it was in 2007.
But construction spending has followed a completely different path. Between 2007 and 2011, total construction spending fell by 35 points—that is, by 35%. And by mid-2017, construction spending was only about 7% above where it was in 2007.
So any way you look at it, companies that work or sell in the US construction industry have faced more than their share of challenges over the past ten years.
More analysis probably could explain the underlying reasons.
Apply the Beveridge Chart to Your Company
But your company probably doesn’t work in construction. How could YOU apply Beveridge charts?
Here’s one thing to keep in mind…
As you look at these charts, notice that the units of measure on each axis don’t have to correspond at all. You could compare dollars to donuts if that could help you to understand the relationship between two measures as they’ve changed over time.
You could, for example, create a Beveridge chart that compares your long-term monthly sales in units or dollars to any measure that’s somehow relevant to your company’s performance. That could be…
- Economic data.
- The sales of your publicly traded customers or competitors.
- The number of visitors to your web site.
- The employment rates in your target markets.
- Profit margins for your products or your competitors.
- Commodity prices.
- Whatever.
However, as the previous chart illustrates, if you want your readers to be able to compare the relative performance of each measure easily, it helps to display each axis as an index.
As you analyze company performance with Excel, keep Beveridge charts in mind. I’m sure you’ll find many uses for them.
Now What?
In Excel, Beveridge charts are merely scatter (XY) charts with data labels that display values from cells. The red section of the line displays a second scatter plot that takes advantage of Excel’s new power to give formulas the ability to set gaps in our line and scatter charts.
You might have noticed, however, that I’ve provided no instructions about how to set up the charts. This is because a lot is involved with the charts I’ve shown you, and I’d have to write a short book to describe it all.
For example, I’d have to explain…
- How to find free online sources of data that’s relevant to your own company or industry.
- Quick and easy ways to download the data into Excel.
- How to set up an Excel database with both your internal and external data for analysis.
- How to scale up when your database grows too large for Excel, but when you still want to use workbook formulas for your reporting and analysis.
- How to set up worksheet formulas that connect your data with your charts.
- How to set up Beveridge charts for your reporting and analysis, including the ability to display line segments in different colors.
- How to make your charts interactive.
Instead of a book, I’m thinking about offering an online training course that covers these issues. But I’ll only do it if enough people are interested.
So if you’d like to use Beveridge charts in your own company, please email me and tell me how you might want to apply them in your own company.
