Last week, I was caught in a vortex of executive reviews, publishing deadlines, and other miscellaneous family stuff. (Note the placement and miscellaneous status given to “family stuff”. This is why I’m going to die alone.)
In any case, during the executive review prep, we had a bit of a debate on how a certain set of data should be represented.
The basic idea is that we have a FY 2010 goal. Each month, we add values to get us to that goal. The question is how to show this on a chart.
Attempt #1
Attempt #1 was pathetic. Anyone can look at this chart and see what’s wrong with it.
Although the monthly values are represented, the annual goal line is out of place. The scales are too different, making the monthly values tiny. With this version, the monthly values will never reach the annual goal line.
Attempt #2
In Attempt #2, we went for a “how much is left” concept. That is, each month, we would show how much of the annual goal is remaining. In theory, this should give the reader a clear sense of performance against annual goal. But there are a couple of problems. First, the actual monthly values are hidden. For example, it’s not easy to tell what the value for July was.
Also, if the annual goal is exceeded, the chart goes to negative territory. This is obviously counterintuitive and confusing.
Attempt #3
In Attempt #3, we tried out the Thermometer style chart. In this version, we have the monthly values add up until we climb the goal. On the plus side, if we exceed the annual goal, the chart shows that effectively. But like Attempt #2, the actual monthly values are hidden. So it’s not easy to tell what the value for each individual month is.
Attempt #4
Finally, I said “enough is enough”. Let’s just use two charts – one to show the monthly values, and one to show cumulative values vs. goal.
With this version, you can not only clearly see each the performance of each individual month, but you can easily tell how the cumulative values stack up against the annual goal.
The Moral of this Story
The moral of this story is that you don’t have to push everything into one chart. Sometimes two charts tell the story better. I’m not sure why we naturally try to show everything with one chart. I think it’s the same reason we create huge formulas in one cell vs. splitting the formula up into several cells. It gives us a sense that everything is encapsulated in one neat package.
Well, I’m here to say that you don’t have to be afraid of going to multiple charts. It doesn’t add that much overhead and maintenance load to your workbook. And as you can see in Attempt #4, you can format your charts to look like one cohesive component.
Showing your data in multiple different charts is easy and it often allows you to show your data more effectively.
Upcoming Visulization Bootcamp
Since I’m on the topic of Visualizations, I’ll take this time to mention that there is only one more week left to sign up for our
Excel Dashboard and Visualization Bootcamp
(Apr 27 – Apr 29 | Dallas, TX)
Microsoft MVPs Jon Peltier (Peltier Technical Services) and Mike Alexander (DataPig Technologies) are joining together again to bring you our acclaimed Excel Dashboard and Visualization Bootcamp!
This 3-day boot camp is designed for Excel users who need to more effectively synthesize data into meaningful dashboards, charts, and visualizations. The topics presented during this boot camp will introduce you to advanced techniques that will help you build and manage better reporting mechanisms. Going beyond simple tables and charts, you will learn to:
-
Synthesize data in meaningful views with advanced charting techniques
-
Create reports and dashboards that communicate and get noticed
-
Create interactive reporting mechanisms
-
Implement macro-charged reporting
-
Automate the creation of PowerPoint slides directly from Excel
-
Integrate external data into your reports
I played around with this and was wondering what people thought of this idea. I created two sets stacked bars and essentially created a bullet graph of the two.
To see the chart, look here: http://dl.dropbox.com/u/749941/GoalVsActuals_Chart.jpg
Actual spreadsheet is here: http://dl.dropbox.com/u/749941/GoalVsActuals.xlsx
David
I think a better alternative for the 2nd chart in #4 would be a chart with two ascending lines:
1) cumulative Actuals, shown as a dark solid line
2) cumulative Goal, shown as a light dotted line.
By comparing cumulative actuals to cumulative goal, you’d see if you’re on pace and you’d be able to eyeball your full year total. That seems like an essential inference but I don’t think your example charts help you figure that out.
I was just going to suggest a chart with 2 cumulatives lines – actuals, and goal, evenly divided by months, but of course, Jon had been there already
The only limit I see with this approach is that if you have strong seasonal effects, you may get a false sense of comfort or urgency by dividing evenly – but then, re-injecting some seasonality isn’t too hard, if you have data.
Jon/Mathias: I get what you’re saying. The one drawback to your suggestion is that you would be suggesting monthly goals where there are none to be had. That is to say, you woud be forced to break your FY Goal line into monthly chunks (either flat-lining the Goal or applying a seasonal adjustment).
This leaves too much room to make the Goal line look the way you need it, in order to show the best performance.
Like Mathias said, this can give the user a false sense of comfort or urgency.
You might try a stacked column chart. It shows each contributing category stacked on each other for the month (or whatever timeperiod) and the total for the month. IN Excel 03 it’s Column Chart Type, top-middle subtype.
Combined with something like your #3 chart would look pretty nifty.
Mike…how does your annual total give any less sense of comfort or urgency to what Jon and Mathias suggest? At the beginning of the year, you’ve got urgency overload, and towards the end you could get lazy because you figure you’re nearly there.
I prefer the monthly goals myself…gives me less chance to procrastinate (which is what I’m doing right now, in fact).
“The moral of this story is that you don’t have to push everything into one chart. Sometimes two charts tell the story better. ”
Amen to that. The second chart could be a bullet graph (don’t understand the “Dec” label in your chart).
I often add a process behavior chart to the mix (as described by Don Wheeler in Understanding Variation). The process behavior chart shows the trend, but more importantly provides an indication of whether the goal can be met at all, since goals are so often arbitrary.
Finally, if it’s important, monthly goals can be added as “bars” (individual horiziontal lines) in the first chart.
If there’s a FY 2010 goal, then there’s a monthly goal: 175 / 12 = 14.6 (assuming steady business with no seasonality). I agree with Jon…how do you know you’re on pace to achieve 175 if you’re not evaluating month-to-month performance? You need to quickly identify any trends where you are underperforming against goal. By the same token, you need to know when NOT to panic in underperforming months because you overpeformed in the prior months and you net out to the monthly goal of 14.6.
And if you don’t do it by month, then maybe by quarter.
For #3, why not try waterfall chart?