Excel – Creating a Basic Waterfall Chart

Waterfall charts are used to show how you got from one figure, let’s say at the start of the year, to how you ended up at the figure at the end of the year, but instead of showing a total value for each period, it will show you the change in value from one period to another.

Let’s take a simple example…

Standard column chart

Standard column chart

Above we can see the cash amount left over at the end of each month. If we chart this with a basic column chart it shows those values as solid columns. We can see that there is a change from month to month but we can’t see by how much unless we work it out in our heads.

The waterfall chart (sometimes called a flying bricks, bridge or Mario chart) will display the change in value whether positive or negative as a floating bar. This type of chart is used in a number of ways but examples might include showing cash flow over time, or changes to inventory or staff levels.

Instead of a standard column chart we want to create this;

Waterfall chart

Waterfall chart

There are add-ins you can buy to create a variety of waterfall charts and I will mention this again later. If all you want though is a basic waterfall chart then it can be achieved relatively simply using a handful of formulas and a bit of formatting.

First we need to modify our data table.

Insert four new columns. You can call them anything you like. In this example I have named them;

  • Invisible
  • Increase
  • Decrease
  • Variance

We also need to add a couple of new rows to the table. We need an opening balance (i.e. how much had in the bank at the end of December 2014) and a closing balance at the end of the year.

This will now give us the following layout;

Modified table required to create the waterfall data

Modified table required to create the waterfall data

Next, calculate the change from one period to another.

Simple bit of maths, one cell minus another

=F3 – F2

…and copy down.

Variance calculation

Variance calculation

Now we need to create what will become the floating bars in our waterfall chart. We need one calculation to identify positive values and one for negative values.

In D3 I need the following formula;

=IF(E3>0,E3,0)

This tests the value in the VARIANCE column. As we are looking for positive variances we test for values greater than 0. If the value is negative display a 0 (zero).

Increases calculated

Increases calculated

Now for the negative values. In cell E3 enter the following formula;

=IF(E3<=0,ABS(E3),0)

This time show which values are negative, but as we need these values to be displayed as floating bars, the values need to be converted to positive ones, hence the use of the ABS (absolute) function.

Decreases calculated

Decreases calculated

As a quick check, in each pair of cells representing an INCREASE and a DECREASE, you should find one cell with the value 0 and the other with a positive value in it.

The final calculation we have to do now is work out the INVISIBLE value. We need this to create a value to calculate the start value of our DECREASE/INCREASE bars.. The close up below of the bars in the final waterfall chart shows what we are trying to calculate.

Calculating where bars have to start from

Calculating where bars have to start from

To work these out we need to add the INVISBLE value to the INCREASE minus the DECREASE. Copy across the OPEN and CLOSE values into your INCREASE column and enter the formula as shown below.

Calculating the height of the supporting invisible series

Calculating the height of the supporting invisible series

Looking at the numbers, the sum of the INVISBLE and the DECREASE values in one line should add up to the INVISBLE value in the line above.

Quick check to ensure values are correct

Quick check to ensure values are correct

Copy your formula down and we now have a full set of data from which to build the chart.

Select cells A1 to the bottom of your INCREASE column.

Selecting your data for the chart

Selecting your data for the chart

Go to the INSERT tab and select the 2-D STACKED COLUMN chart.

Ready to apply final formatting

Ready to apply final formatting

All we need now is a bit of formatting to get the final effect.

Select the INVISIBLE series and format the bars to have NO FILL. Then remove INVISBLE from the LEGEND as it is no longer required.

If you want, colour code the INCREASE/DECREASE bars. In this example I will make INCREASE bars green and DECREASE bars red.

To differentiate your OPEN and CLOSE balances from the variance bars, colour these differently. You may have to click twice on the end bars to select them separately from the rest of the series. Pick any colour you like.

As a finishing touch, you can add the variance values to the floating bars using Rob Bovey’s free add-in – X Y CHART LABELER from www.appspro.com

Completed waterfall chart

Completed waterfall chart

And there we have our final chart.

If you don’t fancy doing all those calculations then you might want to consider buying Jon Peltier’s PELTIER TECH CHART UTILITY add-in. The full advanced edition sells currently (April 2015) for $99 (http://peltiertech.com/Utility20/). At that price it may not be for everyone, but if you have to build complex waterfall charts (as well as some other types included in the add-in) on a regular basis then it’s worth investing in.

Advertisements

Posted on April 23, 2015, in Charts and tagged , , , , , , . Bookmark the permalink. 2 Comments.

  1. There is one more add-in for creation waterfall charts. It is Waterfall Chart Studio http://fincontrollex.com/?page=products&lang=en
    How it works you can see here https://youtu.be/Z1pavJW8_e8

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: