For business users of Microsoft Excel Free guides and templates

Insight from charts

Excel Charts, Seasonality, & Analysis:
Five Lessons from the WSJ

An article in the Wall Street Journal about winter babies offers some great advice about charts for Excel users in business.


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

A 2009 article in the Wall Street Journal offered some great advice for Excel users in business. The article, New Light on the Plight of Winter Babies, didn’t discuss Excel at all. Instead, it discussed some fascinating data about babies and their mothers.

“Study after study,” the article began, “has shown that winter babies test poorly, don’t get as far in school, earn less, are less healthy, and don’t live as long as children born at other times of year. Researchers have spent years documenting the effect and trying to understand it.”

A key assumption of that research was that the backgrounds of children born in the winter are the same as the backgrounds of children born at other times of the year. Therefore, the researchers assumed, something must be happening to those winter-born children that accounts for their faring poorly.

Seasonal charts showing that women who give birth during the winter months tend to have fewer means. Source: The Wall Street Journal

The article said that people who first wrote about this phenomenon in 1991 attributed it to how school-attendance laws affect children born at different times of the year. But the article reported that economists Kasey Buckles and Daniel Hungerman at the University of Notre Dame have discovered some surprising new insights about this topic from the old data...by looking at some charts.

The chart figure above, which I repeat below, are from the Wall Street Journal.

A Columbia University economist who the economists' paper for a conference, says that what strikes her is how the pattern the economists found shows up even in simple charts. "You can take a look at those graphs and see the clear pattern and that it's remarkably stable over time," she says. "It speaks for itself -- you don't have to put a lot of interpretation on it."

Notice, that is, that the women who give birth to babies in winter months are disproportionately unmarried teenagers with low education. And researchers have known for a long time that "children of children" tend to do poorly.

In other words, these charts explain the winter-baby phonomon that has stumped economists for more than twenty years.

What does this article have to do with Excel and business data? Here are five Excel-related things that occurred to me:

1. Notice the Y-axis values in the figure. As a general rule, it’s a bad idea to manually set the minimum Y-axis value to a number that's near your data values. The reason: Doing so can make small variations seem very large. In fact, fiddling with the Y axis like this is a common way to “lie” with charts. But in this instance, there's no problem. This is because we’re interested in the fact that a seasonal pattern exists at all, and we care less about its magnitude.

2. The original paper about Winter births was written more than 20 years ago, but these findings are new. This should remind all of us, in any profession, in any department, that what we know to be true about our business data might not be the complete truth, or might no longer be true. So as you create new reports and analyses, always be on the alert for unexpected patterns in your data.

3. “Honestly, when we first saw these patterns, we were so stunned we wondered if we made some mistake,” one of the researchers told the Journal’s reporter. “We weren’t even excited, we were like, ‘Is that right?’” This is exactly the correct response for Excel users. Surprising results often are, indeed, incorrect results.

Seasonal charts showing that women who give birth during the winter months tend to have fewer means. Source: The Wall Street Journal

4. Notice that the researcher refered to the importance of patterns in the data. Few management reports, unfortunately, are designed to help managers find patterns. Instead —in both Excel reports and in Business Intelligence displays—managers tend to receive only the ending values for the current period. This display technique makes patterns of performance nearly impossible to discover. I invented spreadsheet dashboard reports in the early 1980s precisely so that I could find patterns in my data more easily, and I think you’ll find this approach useful today.

5. This is a good-looking figure, which you could duplicate almost exactly in Excel. In fact, this is just what I did to create Charley's Swipe File #67, shown below. You can learn more about this figure at the link.

When you compare this figure with the WSJ version above, notice that I didn't attempt to duplcate the WSJ version exactly. Instead, I adapted the figure to my data, using the general design that the publication used. When you try to match a professional design for your own data, be sure to follow the same approach.

Charley's Swipe File #67...Chart showing employment seasonality of male workers by age group

 



Tags: #excel, #charts, #seasonality, #wsj, #wall street journal

Create Cycle Plots in Excel to Chart Seasonal Sales Data

How to Create a Rolling Forecast of Seasonal Sales in Excel

Free Excel Dashboards

Click to see testimonials from readers

Charley's SwipeFile charts

Click to see who uses Excel dashboards.