When you look at a chart of long-term business performance, showing where recessions occurred often can help to explain the results…as this chart illustrates:
Here, Dell’s revenue fell considerably during the last two recessions. But Dell’s revenue problems pale in comparison to Caterpillar’s, as this chart shows:
As you can see, Caterpillar’s revenues dropped nearly by half during the most-recent recession…and then recovered very quickly.
Finally, in contrast to Dell and Cat, Microsoft’s revenues dropped slightly for only a few months, then also recovered:
Notice that all three of these charts use estimated monthly revenues rather than quarterly revenues. I took this approach because I wanted to give you a better idea of what your charts might look like when you show recessions in a chart of your company’s long-term month performance.
When you create charts like this, you’re not limited only to your company’s total sales, of course. You could chart sales by product line or region, or you could chart profits, spending, cash flow, and so on. Any of these could help to show how your company performed during past recessions, and how it might perform during the next one.
These charts contain two data series, a line plot for the measure and a column plot for the recession shading. But first, let’s set up the data that the chart displays.
How to Set Up Your Data
You can download the recession-chart workbook here if you don’t want to create it from scratch. Here’s the top of its Data section:
If you have the KydWeb add-in, you can enter a ticker symbol in cell B1 and then recalculate to display 12 years of estimated monthly values from the company’s actual quarterly financial history.
You also can reference one of the Series IDs in column G to display the periods of recession for any of 44 countries or regions in the list provided by the Federal Reserve Bank of St. Louis. Then, when you want, you can enter your own company’s monthly financial performance in column D.
Otherwise, enter performance data from whatever company you have available in column D. And for the recession data, search for the Series ID you want at http://research.stlouisfed.org/ and then copy your data into column E. Then repeat the process to update the data
Here are the key formulas for this workbook:
B3: =KydWeb_Info(“NAS”,Ticker&”-QI-Tot*Rev*”,”Comp*”)
Cell B3 returns the company name for the ticker symbol entered in cell B1.
A8: =KydWeb_Actual(“NAS”,”MSFT-QI-Tot*Rev*”,-50,2)
A9: =DATE(YEAR(A8),MONTH(A8)+1,1)
The formula in cell A8 returns the earliest date for which data is available. And the formula in cell A9 returns the month that follows.
B8: =KydWeb_Actual(“NAS”,Ticker&”-QI-Tot*Rev*”,$A8)/3
Cell B8 divides the quarter’s Total Revenue by three to find the preliminary estimate of the monthly sales.
C8: =TEXT(A8,”mmm”)&CHAR(13)&TEXT(A8,”yy”)
This formula inserts a carriage return character between the month and the year to force the chart to display this information on two lines.
D8: =B8
D9: =AVERAGE(B8:B10)
After the first cell, the remaining formulas in this column estimate sales for the current month by returning the three-month centered moving average of the sales in column B. This doesn’t return a completely accurate estimate, but it’s close enough for our purposes. And it’s easy to calculate.
E8: =IF(KydWeb_Actual(“FRB”,B$2,$A8),1,NA())
Here, the KydWeb_Actual function returns 1 during a period of inflation, and 0 (zero) otherwise. The IF function converts any zeros to NA()
Copy the formulas in row 8, or in row 9 if provided, down their columns as needed.
Columns G and H contain text from the FRB web site.
How to Set Up the Recession Plot
To create this chart, first create it as a line chart. After you do so, you’ll probably find it easier to select the line for sales rather than the line for recession. So select the sales line then press the up-arrow key to select the recession line.
Choose Chart Tools, Design, Change Chart Type; then choose the first column chart option, which is titled Clustered Column. Then choose OK.
Select the recession series again then press Ctrl+1 to launch the Format Data Series dialog.
In the Series Options tab:
- Choose Secondary axis.
- Set the Series Overlap value to 100%.
- Set the Gap Width to 0%
In the Fill tab, choose Solid Fill. Then choose a Fill Color and set its transparency to 50%. (Doing so will allow you to see the gridlines through the column plot. It also makes the column plot look like background information rather than data.)
Select the right vertical axis, then press Ctrl+1 to launch its properties dialog. In the Axis Options tab:
- Set the Minimum value to the Fixed value of 0 (zero).
- Set the Maximum value to the Fixed value of 1 (one).
- Set Major Tick Mark Type to None.
- Set Minor Tick Mark Type to None.
- Set Axis Labels to None.
After you close the dialog, your chart should show actual performance with the recession shading as in the figures above.
If you ever need to adjust the Secondary Vertical Axis, you first must select it. But this won’t be easy to do while it’s hidden. So here’s what you do: With the chart selected, choose Chart Tools, Layout. Then in the drop-down list box directly under “File”, choose “Secondary Vertical (Value) Axis”. You now can press Ctrl+1 to launch the Format Axis dialog and format the axis as you want.
Finally, to learn more about KydWeb you can follow the link.
