How to use a waterfall chart with Excel template

We love a waterfall chart, because they help us tell a story.

What does every great story have?

A beginning, middle and end.

A table of numbers will only show you the beginning and the end.

Imagine watching Finding Nemo in a table:

There’s a fish called Nemo and he swims away.

Nemo comes back and his father learns that love depends on trust.

That’s not much of a story is it.

We’ve missed out the middle!

How did we get from the start to the end?

What were all the twists and turns?

That’s the beauty of  a waterfall chart.

You have a beginning, a middle and an end.

It tells the story visually and in words of how we moved from budget/last year to actual.

Now imagine watching Finding Nemo as a waterfall chart:

Once upon a time there was a fish called Nemo, and every day his father Marlin warned him of the dangers of swimming in open water.

Until one day Nemo swam away, got caught by a diver, and ended up in a fishtank in Sydney.

Because of that Marlin went on an all or nothing quest to save his only son, meeting lots of friends along the way.

Until finally he saved Nemo, and learned that love depends on trust.

Waterfalls tell the story behind the numbers

Waterfalls are our best friend when it comes to bringing numbers to life and telling the story behind the numbers.

Any table of numbers with last year, budget, actual is crying out to be turned into a waterfall chart.

You can make a waterfall chart for every line on the P&L, balance sheet or cashflow statement to show people the movements over the year.

You can use them to show absolute £, %, headcount. Pretty much any metric.

When I was finance director I would present my entire budget and forecasts in waterfalls, following the P&L.

Slide 1 was volume. Slide 2 gross sales value. Slide 3 rebates & allowances. Slide 4 net sales. Slide 5 gross margin £. Slide 6 gross margin %…and so on.

Each slide a simple waterfall chart following these 3 rules:

1. Rule of 5-7

The easiest way to ruin a waterfall chart is to have too many columns.

It completely defeats the object because you loose track of the story.

The simple rule is 5 to 7 bars maximum, including start and finish.

2. Up and down

Part of telling the story is to make sure all the ups are together and all the downs are together (either way, ups first or downs first).

It’s up to you which way round – whether you want the bad news or good news first.

Never have up and down all over the place because the flow of the story is lost.

3. Buckets

Question: What makes the difference between a poor waterfall and a great one?

Answer: The buckets we choose.

Over 90% of the waterfall charts I see choose buckets from the system.

The movements relate to product groups, regions, cost centres, account codes or customers.

This is because it is easy to go into the system and find out the product group breakdown of the sales movement year on year.

That’s ok, and better than showing it as a table.

But if you want to take your insight and impact to another level, you’ll want to dig deeper.

Rather than show the movements by product group, show the underlying drivers.

For example: market movements, impact of competitor pricing, new listings, store number movements.

That’s getting far closer to insight.

The very best waterfall charts use root cause drivers of performance, and not what is in the system.

And yes, that means you can’t just press a button and generate it automatically.

Here’s an example of a power waterfall chart.

How do I create a waterfall

A waterfall is just a stacked bar chart with a hidden series of data (the hidden series of data creates a bar that you can’t see ‘supporting’ the bars you can see).

The newest versions of Excel & PowerPoint do it for you, it’s pretty intuitive.

Otherwise, here’s a free Excel template to download and use. Simply follow the instructions. If you have any questions drop me and email.

Read our latest posts

The least commercial thing you can ever say…

The least commercial thing you can ever say…

Ever wondered why you’re left out of the important discussions❓…this could be the reason 👇🏻 Commercial decisions are rarely, if ever, made based on a budget. Commercial decisions are based on: ✅ what’s happening in the market NOW, not what we budgeted for ✅ what our...