Advanced Graphing – Step / Waterfall Charts
Download Excel file
This is the second tutorial in a series that discusses how the graphing functions in Excel can be used to enhance your financial model. This tutorial focuses on the ‘Step’ or ‘Waterfall’ charts and how they can be developed and incorporated into a financial model.
A waterfall chart effectively displays variances and how they change from one point to another. In the examples below the chart on the left shows how various scenarios effect the base case IRR in a project finance model. The chart on the right is a visual way of displaying the projects sources and uses table.
Step 1 – Create the data table
A waterfall chart is a Stacked Column Chart containing fives series. By calculating which bars should be included, the positions in the stack and the bars heights, the chart can be created. Five series are used to cater for bars both above and below the X-Axis.
Set up the Table below with the formulae in the marked cells. This table will then calculate all data required to produce the waterfall chart.
| Column Label | Formula / Input |
| Total Bar(1=Yes 0=No) | Input |
| Value | Input |
| X-axisLabel 1 | Input |
| X-AxisLabel 2 | Input |
| Total Value | =A3*B3 |
| Blank Bar | =IF(A2=1,IF(AND(E2<0,B3>0),IF(B3+E2>0,0,E2+B3),MAX(0,E2+(B3*(B3<0)))),F2+G2+I2-J3-H3)*NOT(A3) |
| Positive Bar Above Zero | =(B3>0)*(B3+K2>0)*(B3+MIN(K2,0))*(1-A3) |
| Negative Bar Above Zero | =IF(AND(K2>0,B3<0),MIN(-B3,K2),0)*(1-A3) |
| Negative Bar Below Zero | =(B3<0)*(K3<0)*MAX(B3,K3)*(1-A3) |
| Positive Bar Below Zero | =IF(K2<0,MIN(MAX(-B3,K2),0),0)*(1-A3) |
| Cumulative | =IF(A3=1,B3,K2+B3) |
The formulae in Row 3 can then be copied downwards as required. If more than 1 chart is required leave a blank line in the table then continue to copy downwards. A couple of simple adjustments have to be made to the fist row of formulae of a new charts data table.
The table is set out in this way so that the graphs can be created using one data set rather than having to create individual series.
Step 2 – Create the Chart
- From the chart menu select the “Stacked Column Chart”, click next
- In “Data Range” select the data in columns C to J, then select the “Columns” radio button, click next
- Complete the remaining chart options as required
- Insert the chart in the current sheet
At this stage you should have something that looks like this:
Step 3 – Format the Chart
Follow the steps below to format the chart
- Change the colours of the series per the table below
| Series Title | Colour |
| Blank Bar | No Colour / No line |
| Positive Bar – Above Zero | Blue |
| Positive Bar – Below Zero | Blue |
| Negative Bar – Above Zero | Red |
| Negative Bar – Below Zero | Red |
| Total | Grey (any) |
- Double click on a data series. Format Data Series – Options – Gap Width = 0
- Right click on plot area. Format plot area – Border = None ; Area = None
- Double click on a gridline. Custom – Style = Small dashed, Colour = 40% grey
- Click on chart and set text size to 10
The Finished Chart
Once the formatting above has been applied the finished result should look something like the chart below.
Further OptionsSave the chart as a custom chart so it is easy to replicate:
Right click on the chart and select Chart Type from the pop up menu.
- Right click on the chart and select Chart Type from the pop up menu
- Click on the Custom Types tab.
- Select the User-Defined option located below the list of chart types.
- Click the Add button.
- Type a name and description for your chart.
- Click the Add button
Link the values in column (B) to excel data tables which drive the variances. This will allow the charts to automatically update post any changes to the base case.
