For business users of Microsoft Excel Free guides and templates
Home >  Training > 

Strategic Reporting

When Will the Next U.S. Recession Strike?
You Can Predict It with this Excel Dashboard

This cutting-edge Excel dashboard uses early symptoms of recession to warn you when the next recession will occur.


by Charley Kyd, MBA
Microsoft Excel MVP, 2005-2014
The Father of Spreadsheet Dashboard Reports

On August 14, 2019, the Drudge Report web page displayed this headline:

YIELDS FLASH RECESSION WARNING!
STOCKS DIVE -800
BANKS HAMMERED

That headline linked to a CNBC article with this headline: Main yield curve inverts as 2-year yield tops 10-year rate, triggering recession warning

The S&P 500 lost about 3% that day. And since its market cap was about $25 trillion, that loss cost investors about $750 billion.

But what's really sad about that loss is that it never should have happened!

This is because the 2-year / 10-year inversion that caused so much panic that day first occurred 24 months before the 2007 recession and 34 months before the 2001 recession. Therefore, a more-accurate headline about the same event could have been: Recession likely delayed until 2001 or later...which might have caused investors to gain $750 billion, rather than losing it.

To be fair, Drudge and CNBC certainly aren't alone in their use of panicked reporting about a coming recession. When I checked Google News recently, it returned 2,200,000 results for the search term: recession fears rise. And those millions of articles predicted recessions for many different reasons.

So let's take a deep breath and actually think for a moment...

It's certainly true that the next U.S. recession is coming. But most economists admit that they have no idea when it will come. So there was no need to panic.

Here's a great illustration of the inability of economists to predict recessions...

The last U.S. recession—our Great Recession—began in December 2007.

During most of 2007, the Wall Street Journal asked about 60 top economists each month to submit their probability that the U.S. would have a recession within the next 12 months.

In December 2007, just a few days before the Great Recession began, those top economists gave us their monthly recession guesstimate.

We now know that the recession was going to start in just a few days. So what do you think was their average prediction that December? Was it 85%? Was it 95%? Or perhaps even 99%?

Not hardly! Their average guess was only 38%!

Excel Morning Update report.That's right. Just days before the U.S. recession began, those top economists guessed that the chance of a U.S. recession in the next 12 months was only 38%!

On average, they had no clue!

On the other hand, if you had been using this Recession Watch Excel dashboard in December 2007, you could have told them and your company's managers that a U.S. recession would strike very soon. And you could have told them the reasons for your prediction!

Also, if you had been using this dashboard on the day that investors lost $750 billion, you could have told your managers that there's no chance that a recession will strike any time soon. And you could have told them the reasons for your prediction!

In a few minutes, I'll show you that a recession was imminent in late 2007, that it was not imminent in August 2019, and why.

But first, here's a brief introduction to the Recession Watch dashboard...

The Recession Watch Excel Dashboard

The Recession Watch dashboard is very much like a sophisticated management dashboard. Specifically...

...It reports from an Excel Table with more than 100,000 rows of data.

...You use Power Query to update the Table with one command, as new data becomes available every few weeks.

...Each PQ update takes about 15 seconds to process, depending on the speed of your internet connection.

...The charts are designed so that busy managers can understand them at a glance.

...You can "tune" the dashboard to improve the clarity of your presentation. To do so, you use Excel's slicer controls shown here, on the right:

Recession watch dashboard

To help you understand both the economics and the Excel methods that this workbook uses, the dashboard includes a 50-page training manual.

The Recession Watch Prediction Methodology

I know of two ways to predict a recession. We can rely on triggers, or on early symptoms.

Triggers

Most of the articles posted or printed about the next recession rely on supposed triggers of recession. That method is based on each writer's opinion about conditions or events that could cause a recession.

Those writers tell us, for example, that we'll have a recession because any one of these conditions could trigger it real soon now...

  • An inverted yield curve.
  • A trade war
  • A falling stock market.
  • Falling or rising oil prices.
  • A no-deal Brexit.
  • A drop in productivity.
  • Fears about a recession.
  • A housing-market "swoon."
  • A world-wide recession.
  • Climate change.
  • A crackdown on cannabis (no kidding!).
  • And so on.

In fact, after about ten minutes of searching, I found web posts and articles that opined about how nearly two dozen different conditions or events could trigger the next recession.

Many of these triggers—but certainly not all—could lead to an eventual recession.

But even so, pointing at those triggers with profound alarm—and using red text in all caps to emphasize how significant the alarming condition is—offers no help in predicting when a recession actually will occur.

A recession is like getting a very bad cold, and a trigger is like learning that you were exposed to a sick person in the elevator. Will you get a very bad cold? Probably. Will it happen next week? Or 20 years from now? You have no way of knowing.

Instead, the only accurate warning you'll receive about the onset of your cold will be a sore throat, followed by other cold symptoms.

Early Symptoms 

Recessions don't arrive like a meteor from the sky. They arrive slowly, just as winter eventually follows spring.

And as recessions develop, a few economic measures—measures that reliably show early symptoms of a recession—begin to signal warnings.

Therefore, you must compare the patterns for the current trends of key measures with their patterns of economic deterioration before earlier recessions. A recession will be likely when most of the current patterns match the historical patterns before those earlier recessions.

For example, the best-known early symptom of a recession is when the headline unemployment rate (U3) stops falling, flattens out for a time, and then starts to rise. That is, it "troughs," as shown here with the three bright squares before the three most-recent recessions:

The unemployment rate troughs before a recession, as shown in this Excel chart.

There's a perfectly logical connection between recessions and unemployment rates: The economy deteriorates during a recession, and when that deterioration begins, the unemployment rate begins to rise.

Therefore, if the unemployment rate has not begun to rise, then we're not on the cusp of a recession.

You won't find the previous chart in the Recession Watch dashboard, however. This is because its format provides little help when we need to compare the current (black) pattern with the three pre-recession patterns. Instead, we use charts like the ones below.

The Dashboard Charts

The legend for the figures below shows the starting dates of the recessions for each of the charts' bright lines. And each black line shows the most-current pattern. The colors of the lines below correspond with the areas marked in the chart above.

Excel chart of unemployment rate based on LINEST functionChart A shows a close-up view of the four sections marked in the previous chart. This chart shows 25 months of original data, without smoothing.

(As the subtitle does tell us, however, all lines show a three-month centered moving average (CMA) of performance, which reduces random variations somewhat.)

Although the black line shows a different pattern than the bright lines in chart A, a hurried reader (which many managers are) might see only four squiggly lines in the chart. That's why we need to tune the chart.

Chart B shows my first (failed) attempt to tune the chart for greater insight—by using smoothing levels of one, as you can see in its nearby slicers. Honestly, I've never actually used straight lines in my recession charts. Perhaps you'll find a reason to use them in your own charts.

xxxChart C does a good job of showing that when I captured these images, the current unemployment rate (the black line) had a completely different pattern than we had prior to the past three recessions (the bright lines).

Chart D shows a smoothing level of five for both the black and the bright lines, which is the highest-level smoothing that the Recession Watch slicers allow. In this version, the bright lines shouldn't use such a high smoothing level, because the lines start to get too squiggly again. In this case, on the other hand, there's no apparent difference between level 4 and 5 smoothing of the black line.

When I say that you have the ability to tune your dashboard, this is what I mean. As you move from chart to chart and month to month, you'll occasionally need to tweak your smoothing levels so that each chart provides the greatest insight to your readers in a single glance at each figure.

Today's Recession Picture

The Excel Recession Watch dashboardNow that you understand what the black and bright lines represent, look again at the full dashboard.

Not one of these charts shows a hint that the black and the bright patterns are ready to converge.

I chose the measures displayed in the first seven of these charts because their patterns reliably predicted the onset of the past two or three recessions. (Earlier data wasn't available for charts with only two bright lines.)

And I'll tell you about the 8th chart in a minute.

In short, the trigger-driven headlines on August 14, 2019, were wrong. Because no early symptoms have begun to appear, there's no hint of a recession on the horizon—no matter what one of many possible yield curves may indicate.

Now, let's look at 2007, when a recession was just around the corner... 

Backtesting Your Recession Data

Earlier on this page, I said that if the WSJ's economists had been using the Recession Watch dashboard they never would have predicted such a small chance of recession in December 2007. Now let's backtest the data so I can show you why an imminent recession should have been obvious back then...

An Excel chart that backtests the unemployment data to one month before the 2007 recession.As its subtitle tells us, this version of Chart 1 shows what it would have looked like one month before the 2007 recession began.

Notice in the figure's bottom group of slicers that I turned on the Lags display and set the number of Lag Months to 2. That displayed the chart's dark-gray line, which shows where the black line was two months before where it now is in the figure. And it displayed the light-gray line, which shows where the black line was two months before that.

In other words, this figure shows that a recession was imminent in November 2007, because the black line and the bright lines all show a similar pattern of deterioration. This chart would have given us a 100% certainty that the recession was going to occur within 12 months. There's no excuse for the zero-percent guess that one of the WSJ's guest economists made in November 2007.

The recession watch dashboard three months before the 2007 recession.Before I explain the yield curve, let's take a quick look at the full dashboard. But let's look at it six months before the 2007 recession, not just one month.

You can see that in most of the first seven charts, the black line has started to converge with the bright lines.

This dashboard definitely shows that a recession was in our near future.

Now tap your Page Up key and compare this dashboard to today's dashboard. Do you see the difference?

When you compare the two dashboards, you can safely say that there's no chance of a recession any time soon.

Today's headlines about a near-term recession are wrong. The symptomatic patterns just aren't there yet. 

But What About the Yield Curve?

The recession trigger that we hear so much about these days is the inverted yield curve. Economists explain that normally, interest rates for short-term treasuries are lower than for long-term ones. But when short-term rates are higher than for long-term rates, the yield curve is "inverted," and that usually means that a recession will follow within a year or three.

The inverted yield curve is a trigger; it's not a symptom of a developing recession. But it's not even a logical trigger, like trade wars or recession fears are. Instead, it's a correlation-based trigger. An inverted yield curve, economists tell us, usually is followed by a recession—eventually.

However, even as a trigger, yield-curve performance before recent recessions has displayed a more-distinctive pattern than I've ever seen an economist describe.

And Excel chart of the yield curve in the context of the past three recessions.When economists talk about yield curves, they usually discuss their favorite pair of short- and long-term treasuries—like the 2-year / 10-year treasuries, which caused so much panic on the day that the Dow dropped 800 points.

In contrast, these two charts plot all pairs of short- vs long-term rates that actually inverted each month, as a percentage of all possible pairs of rates that could have inverted.

In the left chart, you can see that the percentage reached 100% before two recent recessions. And it reached 97% before the most-recent recession.

Yield curve inversions, February 2007.From another perspective, this table shows the pairs of inversions for the 97% value in the chart above. Of the 30 pairs of Treasury rates that could have inverted, only the 24-month / 20-year difference failed to invert.

But as you can see from the chart above, on the day of the panic on August 14, 2019, the most-recent monthly inversion percentage was less than 60%.

If the yield-curve pattern continues—and who knows if it will?—the charts above tell us that the U.S. is 12 to 18 months away from a recession, if not more.

Honestly, I included the yield curve in the Recession Watch dashboard because so many people are talking about it, not because I have any faith in what it might tell us.

Remember, the yield curve is just another trigger—a correlation-based trigger at that; it's not an early symptom of recession.

In fact, as the Federal Reserve Bank of St. Louis blog said in October 2018, "[A]lthough yield curve inversions are good predictors of recessions, they’re not perfectly correlated and the exact relationship isn’t completely understood."

The Dashboard's Key Excel Features

I've been using computer spreadsheets in business for more than 40 years, and Recession Watch is the most cutting-edge workbook I've ever created, or ever seen—even though it doesn't use VBA. That's why I wrote a 50-page training manual for it.

Here's what the Recession Watch workbook is about from an Excel perspective...

Power Query. The workbook uses lower-intermediate PQ queries to download 19 data series from secret URLs maintained by the Federal Reserve Economic Database (FRED).

FRED Data. FRED offers the largest collection of free economic data in the world. It currently offers 570,000 data series from 87 U.S. and international data sources. From a business perspective, FRED data can help your managers understand the health of the economies in which your company operates...from many countries all the way down to all U.S. counties and the major cities in each state.

Unemployment chart showing the annual rate of change.Interactive Charts. Never before have I created a dashboard with charts that can respond to interactive settings in so many ways. In addition to changing time periods, backtesting, lag settings, and smoothing levels, the dashboard offers two controls that I haven't told you about yet.

One slicer tells the LINEST functions whether to use their optional zero-intercept setting. And the other slicer switches the display to the data's annual Rate Of Change (ROC).

This chart displays the ROC and does not use a zero-intercept. The chart tells us that the unemployment rate currently continues to fall at just over 5% per year on a year-over-year basis.

And because I set the slicer to allow the lines to display normally, we can see that the bright lines started with a falling unemployment rate and—over a period of 24 months—ended with a rising unemployment rate when the recession began. But the current line started with a faster-falling rate, and—24 months later—still shows a falling unemployment rate.

To be clear, not one of the slicers' settings changes the chart itself. Instead, the settings all change values in cells, which cause formulas in the Figure Data Support (FDS) sheets to return different results. And then the chart responds automatically to those different results.

LINEST. Microsoft tells us that we can use LINEST to fit a straight line using the least-squares method. That is, we can use this equation:
Linear equation supported by LINEST.

However, that's not all that LINEST can do. Earlier, when I explained that we could click a slicer to set a smoothing level from zero (no smoothing) to one (a straight line) to five, the "level" number was referring to the largest exponent that LINEST is using, as in a five-level formula like this...
Smoothing equation solved by LINEST

(By the way, to create a formula like this, in Excel's Insert, Symbols group, choose Equation and then choose an example equation, which you can edit.)

I've successfully tested the LINEST function to a level of 10, which is higher than we'll probably ever need.

Slicers. The workbook uses 45 slicers. I use them to set values in cells—values that control the LINEST and other formulas. Most of the slicers control just one chart, but the Backtest and Lag slicer settings, control all the charts.

PivotTables. The Pivots worksheet contains 45 PivotTables linked to 45 Excel Tables. Each pivot is controlled by one of the 45 slicers. (Yes, you could connect slicers directly to Tables. But my training PDF explains why that's generally not a good idea.)

Workbook Structure. The Recession Watch workbook has 18 worksheets. Each of them is designed to serve a specific purpose. Like most of my workbooks that report and analyze data, the Recession Watch workbook includes these worksheets:

  • The Control sheet contains settings and calculations used throughout the workbook.
  • The Data sheet contains a Table of data, with more than 100,000 rows in this case.
  • The Meta sheet contains a metadata Table, and metadata is "data about data."
  • The Pivots sheet contains all the pivots controlled by slicers, and the small Tables that the pivots reference.
  • The Figure Data Support (FDS) sheets contain Tables that typically use SUMIFS functions, nested in IF functions, to stage summaries of the data for the charts to display.
  • And, of course, there are report sheets, which I call Landscape and Portrait in this case.

The Recession Watch Training

The first page of the Recession Watch training.When you get the Recession Watch workbook, you'll also get my 50-page Recession Watch Short Course as a PDF. You can see the first page of the training here.

This training consists of five parts:

1. The Dashboard’s Analytical Strategies

2. How to Use the Dashboard Workbook

3. The Economic Measures Used in the Charts

4. How to Tune Your Data Patterns

5. The Workbook's Key Formulas, Methods, and Structure

The last section—the section about Excel—is the largest section by far.

The Excel training covers...

  • The Control sheet, which I use in every Excel report or analysis I create.
  • The Range Name strategy, including dynamic range names.
  • The workbook's Figure Data Support (FDS) sheets, which I use in all workbooks with charts and in many workbooks with no charts.
  • Setting up and using the LINEST function.
  • Using array formulas to display labels and numbers.
  • Using array formulas with dynamic array constants.
  • Setting up the charts.
  • Working with the slicers and pivots.
  • And a lot more.

Recession Watch Updates

For a year after you purchase Recession Watch, as I update the workbook or the training, I'll send you the updated versions—for no extra cost.

One area that I want to test, for example, is to calculate one statistic that summarizes all seven of the measures of early symptoms. If I can find a useful method, I'll update everything and send you the new version.

Also, of course, if you happen to find a bug, I'll send the corrected version to everyone.

The Excel for Business Newsletter

I'll also send you my new Excel for Business newsletter by email. I decided to start this newsletter because the Recession Watch workbook deals with an important topic, and I want you to understand it well as time passes.

Until the recession actually strikes, this newsletter will concentrate on additional Recession Watch training, including useful Excel, business, and economic topics. And then, after the recession strikes, we'll shift gears and get into a turnaround mode.

The newsletter will cover areas like...

  • Ideas about how you can use Excel to help prepare your company for a recession, or how to recover from a recession when one eventually does strike.
  • Answers to questions that you and others have about the Recession Watch workbook and the measures I've selected.
  • Productive Excel methods I've used in Recession Watch and other products and projects. (Do you remember that the Recession Watch workbook updates in about 15 seconds? Your own reports and analyses could update about as quickly.)
  • Using FRED data with Excel to help your managers better understand how the U.S. and international economies affect your company's performance.
  • Other measures that might—or might not—have a story to tell about the current health of the economy.
  • How and why to use new features and functions in Excel 365, which is an outstanding product that I highly recommend!
  • Free copies of workbooks that I create to illustrate Excel features and solutions.
  • Etc.

Get Recession Watch Now!

Not only does this workbook illustrate cutting-edge Excel features, it offers the only source that can give your managers a logical way to predict when the next recession will strike...

...so that your managers can find the time to develop strategies to become a stronger company during the recession,

...so that your managers don't act foolishly when another trigger-based panic about the U.S. economy strikes without warning,

...so that your planning and budgeting process takes recession probabilities into account, and,

...so that you can learn advanced Excel methods that you can apply on the job.

Get Recession Watch for $199If everyone on Wall Street had been using the Recession Watch dashboard on August 14, 2019, investors would have been at least $750 billion richer! And that certainly would be worth more than $199.00.

Thanks for your interest in business solutions for Excel!

 

Charley Kyd

P.S.  In case you’re one of those people, like me, who just skips to the end of the message, here’s the deal…

The Recession Watch workbook offers the only practical way to predict when the next recession will strike. It uses Power Query to download updated data in about 15 seconds, from secret URLs maintained by the Federal Reserve Economic Database (FRED).

Not only does this workbook illustrate cutting-edge Excel methods, it includes a 50-page training manual that explains both the economic and Excel contents of the workbook.

And the workbook is designed to warn you when the next recession will strike.

Excel Recession Watch dashboard for one month before the Great Recession.Example 1: Just days before the start of the Great Recession in December 2007, more than 50 top economists guesstimated for the Wall Street Journal that there was only a 38% chance of a recession in the next 12 months.

This backtest version is for November 2007. It shows that the pattern for the black line in each chart followed the same pattern as the bright lines.

And that meant that a recession was imminent!

If you had been giving your managers Recession Watch reports back then, your company would have had the warning it needed to prepare to survive the recession and thrive at the end of it.

The Excel Recession Watch dashboardExample 2: On August 14, 2019, a panic hit the stock market. This was because a widely followed yield curve had inverted, and multiple headlines screamed about the likelihood of a U.S. recession.

That panic caused the DJIA to drop 800 points in one day, and it cost S&P investors to lose about $750 billion.

If you had been using Recession Watch reports then, you could have seen this dashboard, where the black line in each chart showed a completely different pattern than the bright lines.

And that meant you could have reassured your managers that there was no reason to panic, because there was no chance of a recession striking any time soon...probably not for a year or more.

Get Recession Watch for $199Along with this workbook and its 50 pages of instruction, you'll receive my new Excel for Business newsletter by email. This newsletter will cover both the economics and the Excel methods that the Recession Watch workbook represents. It also will explain other Excel features and analytical methods that normal Excel users are never taught.

Get the Recession Watch now so you can help your managers to plan more realistically, so you can learn advanced ways to create interactive Excel dashboards.