Lesson 5: Waterfall Charts


Introduction

Waterfall Charts are a really handy instrument. It is however unfortunately not that easy to create them in Excel, unless you're using Office 2016.

One step back please. Waterfall Charts?

Oh sorry! You're right, it can indeed be useful to introduce Waterfall Charts first. A Waterfall Chart (or Cascading Chart, Bridge Chart, etc.) is a nice tool to visualize positive as well as negative changes to a value over e.g. a certain period of time. It shows a running total as values are added or subtracted. It's useful for understanding how an initial value is affected by a series of positive and negative variations. It looks like this:


As you can see, the columns are color coded so you can quickly tell positive from negative numbers. The initial and the final value columns often start on the horizontal axis, while the intermediate values are floating columns.

And how do I read it?

In our example above, we're comparing the initial project budget with the real cost. The initial project budget was � 2.500.

Now that's a big project.

'Sigh'. Now, you can as well tell from the very last bar that the current estimated project cost is � 4.675. As a project leader, you want to know where the difference comes from.

Oh, I see. The floating bars show the variation in budget vs costs item per item.

That is indeed what they do. As you can see, the analysis cost was � 500 higher than budgeted. However, the design cost was � 1.000 lower, etc. It allows identification of the changes at a glance.

If I understand it well, you've a start situation and an end situation and you visualize changes between start and finish.

Yes, and you do that according parameters you define yourself: you can monitor differences over time, differences per product, per project items etc.

And this isn't standard Excel?

Yes it is, but only as of Office 2016. And as a lot of us are still using older versions of Excel, I thought it could be useful to dedicate a lesson to it. Of course, you'll still use standard Excel tools to build these charts, and you'll need to transform your data a little in order to create a Waterfall Chart. There're as well plenty of examples online on how to build these charts. However, very few of them are working in all cases. Now, I'm not pretending the model we'll use is the best and the most concise, but it is easily understandable and it always works, even when combining positive as well as negative values, which is something you don't find a lot online.

Waterfall Charts

The only difficulty is to understand how to create those "floating" bars in the chart.

Put the quotes away, they really are floating, aren't they?

Not at all! They just look like they are floating. In reality, they are just the top bars of a stacked bars chart.

Hold on! Where did the other bars go?

They are still there, but they have no fill color, no glow, no border, no shade, nothing. So you don't see them. You can see the difference below. On the left hand side, the bars you don't need are invisible, on the right hand side, we gave them a stripe pattern fill so as to make them appear again.


Both charts are identical, we just played with the color of the bars.

But still, you need to tell Excel how high the "invisible" bars need to be.

For sure, and you need to tell Excel plenty of other things as well. This is the data table behind our stacked bars chart:


Again, I'm not pretending this is the most concise way to do it, but at least it is understandable and works in all cases. Let's now have a closer look at our data:

  • One data series just contains our start value (in our example here above, the budget). Sounds obvious as we need to know what our start position is;

  • Per analogy, one data series is for our end value (again in our example, this is the estimated cost). Indeed, we ultimately want to know what we achieved.


That was the easy part. Without being rocket science, the other ones are slightly more complicated:

  • We then have two data series for the green bars going down: one for the bar going down above zero (Up Green), and one going down under zero (Down Green);

  • Similarly, we also have two data series for the red bars going up: one for the bar going up above zero (Up Red), and one going up under zero (Down Red);


Why is green going down and red going up?

Because red is everything that is above budget and green is everything executed below budget. Red is destroying value while green is improving your margin.

  • Finally, we have two data series for the "invisible" bars: again, one for positive and the other one for negative values.


What follows next is the most difficult part. You need to fill in the data table with correct data. Let me take the analysis part as an example. The start budget is � 2.500 and you know that the analysis part adds � 500 to that amount. So you want to show that after the analysis part is completed, the project cost is now � 2.500 + � 500 = � 3.000.

Hmmm, let me think. I guess you would need an invisible bar of � 2.500 and then on top a red bar of � 500.

Correct! Please go on with the engineering phase.

Well, after analysis, my project cost is now � 3.000, so I need an invisible bar of that amount and on top of that a red bar of � 750.

Correct again. So basically, your "invisible" bar as you call it is always equal to the previous cumulative value.

Yes it is!

Gotcha! That isn't always true. Have a look at the design phase.

You're right... If the previous cumulative value was positive and I'm adding some positive value, then the invisible bar's height should be equal to the previous cumulative value. However, if I'm subtracting some amount, my invisible bar should take that into account.

See, the formulas are not that easy. By the way, the download link with the correct formulas can be found here.

Should you require any assistance to use the file, don't hesitate to let us know.

Good to know, and to remember...

  • Set the order of your data series correctly! You definitely want the invisible bars to be drawn below the other ones. You can do so by using the two arrows in the Data series selection form:


  • Use these charts wisely. They look fancy but as with any other chart, they'll only tell the story you want them to tell if they're used in the right context;

  • You'll need to change the standard colors Excel sets to your data bars in order to create the right effect.

  • For the ones to which every tiny details is important: we deliberately added a logical error in our project management example. This however allowed us to show the model's validity for all cases. Found it? Let us know...

We appreciate your feedback!


About Cintellis

Let us surprise you with how much more you can achieve by using Excel and VBA based products.

Are you looking for an on the level, reliable and trustworthy company that will surprise you with how much more you can achieve by using Excel and VBA based products? Learn more about our philosophy, products, services or contact us for more info.





About Numis

Flexible, cheap and very short implementation time, discover Numis, our Excel based Cash Management and Management Accounting Software and experience a totally new way to analyse and report on your cash positions.

At Numis, we help you to get more out of your financial data than you thought possible. From enhanced reporting functionalities and detailed analysis to providing enterprise budgeting and forecasting solutions, we will help you to achieve your business objectives. When using Numis, you can share your analysis and strategy with your business partners, suppliers or employees as needed.

Numis is a product from Cintellis bvba & FS&P bvba.

CONTACT Us

You can contact us by phone, mail or by filling out the form below.

Corporate address:
Alsembergsesteenweg 588
1653 Dworp
Belgium

Phone Numbers:
+32 (0)471 65 19 66

E-Mail: info@cintellis.com