CHARTING: How to build a basic Waterfall Chart?

Waterfall charts are widely used in financial modeling to show how a value changes from one level to a final level through a series of intermediate changes. The graph allows the user to observe positive and negative variations in values. For example, an analyst can show how accretion/dilution in EBITDA takes place during the projection period.

Waterfall charts are also known as bridge charts as they show a bridge connecting the end points. A simple waterfall chart is shown below:

  

Making a simple Waterfall chart in Excel

Here we want to visually present how EBITDA changes during the 2014–2018 projection period

Part One: Waterfall Chart Data

1.        Populate the data set that results in a change in EBITDA, including the initial and final EBITDA levels, during the projection period in column C

2.        In column D, select the initial and final EBITDA levels for the chart end points. For example, link cell D7 & D13 with C7 & C13, respectively

3.        Column E captures the blank series that support the floaters, i.e., the intermediate values. To find this value, use formula in Cell E8: =D7+IF(F7="",0,F7)-IF(G8="",0,G8). Afterwards, in Cell E9, use this formula to find the value: =E8+IF(F8="",0,F8)-IF(G9="",0,G9). The formula used in E9 is to be filled down to row 12

4.        Column F captures the up intermediate values. To find the up values, use the formula in Cell F7: =IF(C7-D7>0,C7-D7,"")

5.        Column G captures the down intermediate values. To find the down values, use the formula in Cell G7: =IF(C7-D7<0,-C7+D7,"")

Part One: Create a Waterfall Chart

1.        Select the data B6:B13 & D6:G13 and create a stacked column chart

2.        Hide the blank columns by giving them no border and no fill

3.        After adjusting the chart formatting, the final output will look like the following chart:

 

                                               

Variants of Waterfall Chart

Apart from these basic Waterfall charts, there are other variants.

For better visualization of Waterfall charts, connector lines are added to the basic Waterfall charts

Another variant of Waterfall charts is wherein an analyst wants to show intermediate sum along the way before showing the final cumulative sum. In the above example, the analyst wants to show 2018 EBITDA without investments and total EBITDA, including investments.

We will cover the advanced Waterfall charts in our future posts. Keep reading.