How to create a McKinsey-style waterfall chart

The "water fall" chart is an effective way to summarize the quantitative impact of a number of drivers. For example, you need to put the following story in a chart: "Our profits went up by 7, the positive effect of higher prices and lower cost was offset by a lower sales volume." A waterfall chart would look something like this: For illustration purposes I left the light grey color and data labels of the supporting series in so you can see how to make the chart: it is basically a stacked bar chart with 3 series:
  • A "white" series to support the drivers
  • One series for the drivers
  • One series for the (sub)totals
The data table for this chart (Powerpoint 2007): For a final touch, make the color of the light grey series white, take out the data tables and that's it. There is the temptation to make automated tools (in Excel) that do the work for you. Like almost all my charts, I start with a piece of paper and make my waterfalls manually, to make sure that they
  • Are correct (negative numbers can make these charts a bit tricky to get right sometimes)
  • The chart tells the story I want it to tell (what subtotals to use, in what order to list factors, etc.).
(Click on images for a larger picture)