How to create waterfall chart in excel in 2 minutes
Posted October 8, 2008 1:10am UTC by Ajay
A waterfall chart is used to represent a set of figures when they all impact the same derived number. A waterfall chart helps to link the individual values to a whole. A profit and loss statement is a good example where the magnitude of each figure has an impact on a derived number (which happens to be Profit in this case).
A colleague of mine recently had a budgeting exercise assigned to him where he had to project the P&L figures for the next year and then show them as a waterfall chart. The existing template, to start with, had probably around 7 or 8 series, each being used to generate a set of graphs. (As you may have already guessed, a waterfall chart in excel uses the native stack graphs.) Now we did not particularly like what we were given and we did a bit of googling to see if the wheel had been invented before……it had been and on numerous occasions but we were looking for an actual example in excel and to our disappointment, that was something we did not find. What we did find, however were a large number of ‘part-waterfall-part-bar’ charts which would work well with a specific set of values but devolve into normal bar charts (or sometimes even simply break down) as soon as the values were moved around too much.
One such example is shown here.
So what is a dimwit to do……well……we made our own version of the waterfall chart.
To create the waterfall chart, we started off with a dummy set of numbers for the P&L.
We then added four additional set of formulas – one each for the actual number, the padding (or offset), for crossover (when the numbers move across the X-axis and finally the reference point – which I called the datum.). The actual waterfall chart actually uses only the first three as a series while the datum is just for the developer to keep track. (At the cost of added complexity, you can actually dissolve the datum in to the first three values and completely drop it.)
You may actually skip the rest if all you need to grab is a quick copy of the waterfall chart in excel .
Four distinct components of the Waterfall Chart:
1. The Padding. Since each of bars in a waterfall chart do not begin at 0, you have to offset them (elevate or depress) by a certain margin. How much to offset is determined by the datum.
2. The Plot. The value to plot. The trick here is to ensure that if the actual value to plot is -ve but it still does not go below zero (because the previous figure was way too positive), the figure used for actually plotting the bar has to be above the axis but moving southwards. And vice-versa. In such cases, the padding needs to be adjusted accordingly.
3. The crossover. Sometimes we will encounter a situation where the plot value is such that it moves across the X-Axis. For example, the first figure was 1000 and the second figure (the one we need to plot) is -2000. In such a case, some part of the bar will be above X-axis and some below. The workaround is to use the plot value (in Point 2) to plot half of the actual value and use the crossover value to plot the remaining portion. This is a crucial test for a waterfall chart – the cross over series should adjust automatically when the next value causes the movement across the axis, from the lower half to the upper or vice-versa.
4. The datum. The datum is the level which becomes the starting point for the next value to being from. The trick here is to make sure that when two successive values are 100 and 10, the third bar has to be from 110. But if the values are 100 and -10, the third bar has to begin from 90.
The formulas that we put for each one of the above components were:
Once you have the set of the four formulas, just extend them to the entire series that you want to plot. This now becomes the source data for the waterfall chart.
What remains now if to simply pick up the first three- the padding, the plot and the crossover – and insert a stacked graph in Excel. The resulting will be something similar to this one:
Remove the chart clutter. Double click on the series that represents padding and make it transparent and VIOLA…..your waterfall chart is ready to flow.Source: www.databison.com