As a follow-up to the last two posts, I just thought of a way to compare performance to multiple Boolean (yes-no) conditions.
In an international company, for example, how can we visually interpret a chart of sales that could be affected by recessions in several major market areas?
Similarly, how could we visually evaluate the effects of several ad campaigns that often overlap?
This figure shows one way to do this:
This chart definitely gives us more information to think about. Notice that:
- Dell’s revenues fell in 2000 just about the time that the Big 4 European countries (France, Germany, Italy, and the UK) started their recession.
- Revenues started to rise when the recessions ended in the US and the Major 5 Asian countries (China, Japan, India, Indonesia, and Korea).
- When the Big 4 European recession ended about July of 2003, Dell’s revenues jumped.
- About December of 2004, in the middle of the Asian recession, Dell’s revenues flattened out, and stayed that way until the Asian recession ended.
- In 2008, just after all three areas had started their recessions, Dell’s revenues fell sharply.
- In 2009, when the recessions ended, Dell’s revenues rose sharply.
Of course, I might have over-interpreted the chart, but it definitely provides more information to work with.
The Source Data
The recession data comes from the Federal Reserve Bank of St. Louis, Series IDs USARECM, 4BIGEURORECM, and MAJOR5ASIARECM. These series return 1 for months with recessions, and 0 otherwise. If you want, you can download their data and paste it into your workbook.
On the other hand, you could use KydWeb to download the data directly from the web. When I did so, the top of my data worksheet looked like this:
Columns A-D are the same as in yesterday’s recession post, except for the Regions cell. The value in this cell specifies how many regions we’re tracking in the chart.
The remainder of this data area has only two key formulas:
E8: =IF(KydWeb_Actual(“FRB”,E$6,$A8),1,NA())/Regions
This KydWeb_Actual function in this formula returns 1 for months with inflation for the region specified in cell E6; otherwise it returns 0. Then this formula divides the result by the number of Regions.
F8: =IF(ISNA(E8),1,NA())/Regions
This formula switches the value in cell E8. If E8 is #N/A, this formula returns a number; if E8 is a value, it returns #N/A.
I copied the formulas down their columns as needed, then copied and pasted the pair of columns to the right for the other two regions. Finally, for easy reference, I filled their areas with colors similar those used in the chart.
Creating the Chart
I won’t provide step-by-step instructions for creating the chart, for two reasons. First, I provided similar instructions in yesterday’s blog. Second, you can download the workbook here.
Here are the five key things you need to know to create this chart:
- Each area that marks a recession requires two data series, not just one.
- The first series of each pair shows when the recession occurred; the second series shows when the recession did not occur.
- The second series of each pair has a fill color of No Fill.
- The chart type for all recession indicators is Stacked Column.
- As in yesterday’s chart, the recession bars have 100% overlap, 0% gap width, and are plotted on the Secondary Axis.
To see why we must include the second series for each pair, look at the US and European recessions in 2001 and 2002. The US recession ended around January of 2002. If we had used only one stacked bar for each region, the European recession after January, 2002, would have dropped beside the blue recession bar for the US. This would create a jumbled set of colors.
To fix this problem, we add a second series — an invisible one — that provides a platform on which the continued European recession could rest. And the hidden European series, combined with the US series, provides a platform on which the Asian series can rest.
Finally, to create the legend, I just set up four text boxes, added the text, shaded them, and moved them into position as shown.
