So what is a dimwit to do……
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.)
Four distinct components of the Waterfall Chart:
- 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.
- 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.
- 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.
- 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 !!!