Most of the time, at least one of the major trading countries is in recession. The bottom row of this Excel figure summarizes this sad situation.
This figure has an important story to tell, whether or not your company trades internationally. Here’s why…
First, recessions are fairly common in every country. The US, for example, has been in recession for 39% of the time since 1960. Canada is 41%. The four European countries, as a group, are at 49%. And the five Asian countries are at 67%.
Therefore, your managers need to know precisely how recessions affect performance so they can more easily find ways to succeed in spite of a current recession.
Second, recessions are averages. Not only are they averages of several regions, they’re averages of many economies within regions. So your target market could be booming even though your country is in recession. Or it could be the other way around.
No matter which situation you face, your managers must find products and services that help your company grow under current conditions. Recession is no excuse.
Therefore, if you’re going to understand and adapt to the economies and regions in which you operate — or in which you could operate — you need to track their health.
The figure above shows one way to summarize recessionary performance by region.
Creating the Figure
The figure above doesn’t use a chart; it uses a spreadsheet with conditional formatting, large fonts, and narrow columns. To see how this works, take a look at this figure:
Cell D5 contains a formula that returns 0 (zero) if there wasn’t a recession that month, and 1 if there was a recession. I assigned the custom number format “;;;” so that neither value would be displayed. (I’ll discuss the formula in a minute.) Then I copied this formula to the right and downward as necessary.
For US data, I used conditional formatting to display the blue color if the value is 1. To do so in New Excel, choose Home, Styles, Conditional Formatting, Highlight Cell Rules, Equal To…, and then complete the dialog. For other regions, I used the same approach but assigned a different color when the value is 1.
Cell D4 contains the text “60″ to show the year. The cell beneath the date for January, 1962 contains the text “62″. And so on. I couldn’t use a formula here because there’s no way to write a formula to return a code that Excel will interpret as an empty cell. We need those unused cells in row 4 to be empty so that the label can extend into adjacent cells. (Excel doesn’t interpret a null string, “”, as an empty cell for this purpose.)
Finally, when I had the formatting set up, I set the width of the data columns to 0.08, which is the narrowest I could make them.
I made the fonts large because the first figure on this page is roughly half the actual size of the original figure. I therefore needed to use large fonts so they could be read easily when I reduced the size of the image. If you don’t intend to reduce the image, you can use smaller fonts.
The Data and Formulas
The data I used for this figure came from the FRB, which published it for the first time last week. You can find this data at…
http://research.stlouisfed.org/
…where you can search for these Series IDs: US: USARECM, Canada: CANRECM, Big Four European Countries: 4BIGEURORECM, Australia: AUSRECM, Five Major Asian Countries: MAJOR5ASIARECM, Mexico: MEXRECM, Brasil: BRARECM, and the Russian Federation: RUSRECM.
In total, the FRB currently has “Turning Point” data for 44 countries and regions.
For each data series, you could download a spreadsheet and then copy the data to your report workbook. To update the data, you would need to repeat the process.
However, there is an easier way, as shown in this figure, which eliminates all formatting from the previous figures:
The key formula here is:
D5: =KydWeb_Actual(“FRB”,$A5,D$1)
This formula uses the KydWeb_Actual function to return the data directly from the FRB’s web site. To update the workbook when new data is available, I merely need to open and recalculate it.
Part 2 of the series will explain how to display recessions in your charts of performance history.
