SPECIALISTS IN FINANCIAL MODELLING
Select Page

Waterfall charts, also called bridge or stepped charts, have become popular in recent years, particularly when displaying the output of a financial model. A waterfall chart displays very effectively the incremental impact of each period of time, or unit. It is a type of bar chart where the value of the second bar generally begins where the first one finished.

Extracted from Chapter 12 of Using Excel for Business Analysis, Second Edition.  Download the accompanying workbook.

Despite their popularity, waterfall charts are not yet a standard chart type in Excel and to create one can get very complicated.  Many modellers have their own templates for creating them, as – let’s face it – most dashboard and board papers nowadays normally include some kind of waterfall chart!  In this article, we will cover two methods of creating these kinds of charts:

1. Firstly the “dummy stack” method which is simple to create, but fairly limited in the data it can show as it’s restricted to positive values only.

2. A superior method is to use up/down bars which are a little trickier to build, but far more versatile in the data it can display.

If the incremental cash amounts for each month are always positive this makes a waterfall chart very simple so it will only take a few minutes to build, as there are no values below the x‐axis. First, we’ll take a look at creating a very simple waterfall chart using the Dummy Stack Method, then we’ll tackle some more complex charts using Up/Down Bars.

## Creating a Basic Waterfall Chart Using the Dummy Stack Method

Let’s say you have a forecast for incoming cash for the next six months (see the data list that follows).

We’d like to see the incremental contribution of each month individually, which will add a new dimension to the analysis. Contrary to popular belief, you don’t need fancy software to build a basic waterfall chart. They are really not that difficult to build in Excel and are achieved by a few little tricks, as shown in the following list of 11 steps.

1. Open a blank worksheet and start by entering the dates and your cash amount in columns A and C as shown, leaving column B blank. Alternatively, you can download the accompanying workbook.

2. In column B, we need to create a dummy series, or “dummy stack,” which will be used to push each cash series bar up to where the last one ended. So column B will contain the cumulative balance from the previous period. You’ll see what I mean in a minute.

3. We need to make cell B3 a cumulative balance, so the formula in cell B3 should be =B2+C2. Copy B3 down to B7. Let’s label it the Base column because it’s effectively where the bottom of each column starts. It should now look something like Figure 2.

Figure 2

3. We can now create a stacked bar chart from this data. The easiest way to do this is to highlight the whole range A2:C7 and create the chart.

4. Select the stacked column from the column section on the Insert tab (or the Charts tab in Excel for Mac 2011) and the chart will appear automatically.
The chart should now look something like this (although the colors and background might look a little different if you are using an older version of Excel). See Figure 3.

Figure 3

6. Click on the series legend and delete it.
7. Change the title to “Cash Forecast Waterfall”
8. Click on the bottom series and change the series fill color of the bars to white or no fill by right-clicking and selecting Format Data Series. You may also like to edit the color of the chart series that is showing on the chart.

Figure 4

9. You can remove the background gridlines by clicking on them and deleting.

10. If there is a border around the white series, you can remove this by going into Format Data Series again and selecting the No Border option.

11. Your waterfall chart should look something like Figure 5.

Figure 5

The method described above is a very simple method of creating a waterfall chart, but it only works when the incremental cash amount is positive. Let’s look at some more complex examples.

## Creating a Waterfall Chart Using Up/Down Bars

What if your cash forecast changed, however, and in April you are expecting a poor month, with a loss amount of \$500? See the following list of data:

Simply changing the data on your existing waterfall chart model will not display the chart correctly. Entering a negative value will cause the series to display below the x-axis, which is not correct. See Figure 6.

Figure 6

There are a few ways of correcting this, and most methods can get very complicated and difficult to maintain.  If we continue with the stacked column method for this example, the formulas get quite complicated. Below is a completely different method of 10 steps to create a waterfall chart using Line charts and Up/Down Bars, which is a far simpler and more robust method.

1. Set up your data in vertical rows like we did in the last example, except this time we don’t need to leave column B blank for the dummy stack.

2. This time, we need to calculate the “Upper” and “Lower” points for the boundaries of our Up/Down Bars. To create the Upper number, create a cumulative formula in column C. Your formula in cell C3 should be =SUM(\$B\$2:B3). Copy it down the column and your model should look something like Figure 7 so far.

Figure 7

3. Now we can calculate the Lower number by subtracting the Cash amount from the Upper number. Your formula in cell D2 should be =C2-B2. Copy it down the column.

4. Next, create a line chart using the Upper and Lower numbers. We don’t need to show the cash amount on the chart. The easiest way to do this is to hold down the control key to highlight nonconsecutive ranges, and then create the line chart. Your model should look something like Figure 8.

Figure 8

5. Click on the series legend and delete it.

6. Change the title to “Cash Forecast Waterfall”

7. Now add the Up/Down bars to the line chart by clicking on the chart, and select Up/Down bars from the Chart Elements option that appears, if you are using Excel 2013. For previous versions of Excel, then choose Up/Down Bars from the Layout tab on the Ribbon that appears when the chart is selected. For Excel 2011 on Mac, choose the Chart Layout tab to access the Up/Down Bars in the analysis section of the Ribbon.

8. The Up/Down bars will appear between the line series, as shown in Figure 9.

Figure 9

9. Click on each line, right-hand click and select Format Data Series. Under Line or Line Color, select No Line. On Excel 2011 for Mac, Control+Click each line, go to Format Data Series, and under the Line option on the dialog box change Color from Automatic to No Line.

10. Change the color of the bars (they automatically default to black and white) if you wish. This cannot be done through the color scheme, you need to select each series and manually change the color, because they are not ordinary series; rather they depict the difference between the two lines. Although this looks like a column chart, it is still in fact a line chart! Your chart should look something like Figure 10.

Figure 10

## Creating a Complex Waterfall Chart (with Positive and Negative Values Crossing the X-Axis)

You may have noticed that the charts we created above all remain as positive total values. The Dummy Stack method will simply not work if the columns are below the x-axis. To create a waterfall chart that will handle both positive and negative values, as well as a balance that crosses the x-axis using the Dummy Stack method, we’d need to create much more complex calculations. Fortunately, the Up/Down bar method is much simpler.

Using exactly the same method as described above, we can create a more complex waterfall chart that includes both positive and negative values, and crosses the x-axis. See Figure 11 for the completed chart.

Figure 11