SPECIALISTS IN FINANCIAL MODELLING

Creating a Splash with Simple Waterfall Charts

Waterfall charts, also called bridge or stepped charts, have become popular in recent years, particularly when displaying the output of a financial model. A waterfall chart displays very effectively the incremental excel chartimpact of each period of time, or unit. It is a type of bar chart where the value of the second bar generally begins where the first one finished.

Extracted from Chapter 12 of Using Excel for Business Analysis, Second Edition.  Download the accompanying workbook. 

Despite their popularity, waterfall charts are not yet a standard chart type in Excel and to create one can get very complicated.  Many modellers have their own templates for creating them, as – let’s face it – most dashboard and board papers nowadays normally include some kind of waterfall chart!  In this article, we will cover two methods of creating these kinds of charts:  

1. Firstly the “dummy stack” method which is simple to create, but fairly limited in the data it can show as it’s restricted to positive values only.

2. A superior method is to use up/down bars which are a little trickier to build, but far more versatile in the data it can display.

If the incremental cash amounts for each month are always positive this makes a waterfall chart very simple so it will only take a few minutes to build, as there are no values below the x‐axis. First, we’ll take a look at creating a very simple waterfall chart using the Dummy Stack Method, then we’ll tackle some more complex charts using Up/Down Bars.

Creating a Basic Waterfall Chart Using the Dummy Stack Method

Let’s say you have a forecast for incoming cash for the next six months (see the data list that follows).

We’d like to see the incremental contribution of each month individually, which will add a new dimension to the analysis. Contrary to popular belief, you don’t need fancy software to build a basic waterfall chart. They are really not that difficult to build in Excel and are achieved by a few little tricks, as shown in the following list of 11 steps.

1. Open a blank worksheet and start by entering the dates and your cash amount in columns A and C as shown, leaving column B blank. Alternatively, you can download the accompanying workbook.

2. In column B, we need to create a dummy series, or “dummy stack,” which will be used to push each cash series bar up to where the last one ended. So column B will contain the cumulative balance from the previous period. You’ll see what I mean in a minute.

3. We need to make cell B3 a cumulative balance, so the formula in cell B3 should be =B2+C2. Copy B3 down to B7. Let’s label it the Base column because it’s effectively where the bottom of each column starts. It should now look something like Figure 2.

Figure 2

3. We can now create a stacked bar chart from this data. The easiest way to do this is to highlight the whole range A2:C7 and create the chart.
 
4. Select the stacked column from the column section on the Insert tab (or the Charts tab in Excel for Mac 2011) and the chart will appear automatically. 
The chart should now look something like this (although the colors and background might look a little different if you are using an older version of Excel). See Figure 3.

Figure 3

6. Click on the series legend and delete it.
7. Change the title to “Cash Forecast Waterfall”
8. Click on the bottom series and change the series fill color of the bars to white or no fill by right-clicking and selecting Format Data Series. You may also like to edit the color of the chart series that is showing on the chart.

 

Figure 4

9. You can remove the background gridlines by clicking on them and deleting.

10. If there is a border around the white series, you can remove this by going into Format Data Series again and selecting the No Border option.

11. Your waterfall chart should look something like Figure 5.

Figure 5

The method described above is a very simple method of creating a waterfall chart, but it only works when the incremental cash amount is positive. Let’s look at some more complex examples.

Creating a Waterfall Chart Using Up/Down Bars

What if your cash forecast changed, however, and in April you are expecting a poor month, with a loss amount of $500? See the following list of data:

 

Simply changing the data on your existing waterfall chart model will not display the chart correctly. Entering a negative value will cause the series to display below the x-axis, which is not correct. See Figure 6.

Figure 6

There are a few ways of correcting this, and most methods can get very complicated and difficult to maintain.  If we continue with the stacked column method for this example, the formulas get quite complicated. Below is a completely different method of 10 steps to create a waterfall chart using Line charts and Up/Down Bars, which is a far simpler and more robust method.

1. Set up your data in vertical rows like we did in the last example, except this time we don’t need to leave column B blank for the dummy stack.

2. This time, we need to calculate the “Upper” and “Lower” points for the boundaries of our Up/Down Bars. To create the Upper number, create a cumulative formula in column C. Your formula in cell C3 should be =SUM($B$2:B3). Copy it down the column and your model should look something like Figure 7 so far.

 

Figure 7

3. Now we can calculate the Lower number by subtracting the Cash amount from the Upper number. Your formula in cell D2 should be =C2-B2. Copy it down the column.

4. Next, create a line chart using the Upper and Lower numbers. We don’t need to show the cash amount on the chart. The easiest way to do this is to hold down the control key to highlight nonconsecutive ranges, and then create the line chart. Your model should look something like Figure 8.

Figure 8

5. Click on the series legend and delete it.

6. Change the title to “Cash Forecast Waterfall”

7. Now add the Up/Down bars to the line chart by clicking on the chart, and select Up/Down bars from the Chart Elements option that appears, if you are using Excel 2013. For previous versions of Excel, then choose Up/Down Bars from the Layout tab on the Ribbon that appears when the chart is selected. For Excel 2011 on Mac, choose the Chart Layout tab to access the Up/Down Bars in the analysis section of the Ribbon.

8. The Up/Down bars will appear between the line series, as shown in Figure 9.

Figure 9

9. Click on each line, right-hand click and select Format Data Series. Under Line or Line Color, select No Line. On Excel 2011 for Mac, Control+Click each line, go to Format Data Series, and under the Line option on the dialog box change Color from Automatic to No Line.

10. Change the color of the bars (they automatically default to black and white) if you wish. This cannot be done through the color scheme, you need to select each series and manually change the color, because they are not ordinary series; rather they depict the difference between the two lines. Although this looks like a column chart, it is still in fact a line chart! Your chart should look something like Figure 10.

Figure 10

Creating a Complex Waterfall Chart (with Positive and Negative Values Crossing the X-Axis)

You may have noticed that the charts we created above all remain as positive total values. The Dummy Stack method will simply not work if the columns are below the x-axis. To create a waterfall chart that will handle both positive and negative values, as well as a balance that crosses the x-axis using the Dummy Stack method, we’d need to create much more complex calculations. Fortunately, the Up/Down bar method is much simpler.

Using exactly the same method as described above, we can create a more complex waterfall chart that includes both positive and negative values, and crosses the x-axis. See Figure 11 for the completed chart.

Figure 11

Podcast – Excel Tips for Best Practice Modelling

Some of you will know that ever since Indian Excel guru and blogging sensation Chandoo visited Australia in 2012 to run his extremely popular Advanced Excel and Dashboard courses, I’ve been trying to convince him to come back Down Under and run another series.  We are hoping that he’ll find time to make another visit in 2015 and we’ll certainly keep you posted and hope to announce it soon.  He hasn’t agreed to come just yet, but as we were chatting last week, he did talk me to into joining
(more…)

Tool Selection: Why do we Use Excel?

We often hear it said that Excel is the “second-best solution” to a problem. There is usually a better, more efficient piece of software that will also provide a solution but we usually default to the “Swiss army knife” of software, Excel, to get the job done.
(more…)

Deficit Levy Tax Calculator

If you’re interested in how the new deficit levy will impact your tax in the new financial year, then take a look at our latest Excel Income Deficit Levy Tax Calculator.  Our tax calculators have always been our most downloaded posts on the site and I’m not sure if it’s because you’re all so interested in financial modelling tax tier calculations, or you just want to know how much of your salary goes in tax!

(more…)

Improving Excel Memory and File Performance

If you have sluggish Excel models, it might not just be the file size that is slowing things down.  Often it’s the memory being used and when things get really bad, you might even get a “Not enough Memory” or “Not Excelenough system resources to display completely” error message. When you get this frustrating message, the only solution is to close Excel and restart it, so let’s look at some ways to improve the performance of your Excel models.
(more…)

Using Excel in the Age of Big Data

We are constantly being told that we are living in the Information Age.  The Industrial Revolution marked the beginning of the Industrial Age and similarly, the Digital Revolution started the Information Age.
(more…)

Is a Power Pivot Training Course Right for Me?

Have you even been on a training course and found you were completely out of your depth?  Or have you been on a “advanced” course and discovered that half the participants haven’t a clue and the instructor spends most of the day on the basics?  We want to make sure that this doesn’t happen to you!

If you’re thinking about coming on a Power Pivot training course, then you need to make sure that you have the right skills already in order to get the maximum benefit from your training.

Here are some questions that will help you to determine if this course is right for you:  You should:

  1. Understand and have used Excel’s SUMIF function
  2. Have a working knowledge of filtering data in Excel (e.g. Auto or Advanced Filters)
  3. Know how to deal with multiple criteria (e.g. SUMIFS, SUMPRODUCT or DBASE functions)
  4. Be able to import data from third party databases and / or files (e.g. Access, SQL, MIS systems)
  5. Regularly use, adapt and modify PivotTables
  6. Have created calculated fields in PivotTables
  7. Created and / or modified an Excel Table (a structured reference table, not a data table)
  8. Have access to either Excel 2010 (all versions) or Excel 2013 Professional Plus

Give us a call if you’d like to talk through your options prior to booking on a course.  If you think you’ll need a bit more help, consider booking an in-house private training course for your company.  The consultant will come your offices, and we can go as quickly or slowly as suits your needs.

If you need to brush up on your skills prior to coming on the course, here are some online resources that will help.

Download the Power Pivot add-in here.  There are also some videos you can watch to get you started.

  • Chandoo has a paid online Power Pivot course as well as a lot of free online resources on Power Pivot & Excel
  • To brush up on Pivot Tables, here’s an article from SumProduct.
  • Here’s another one on structured reference tables
  • Take a look at the Free Stuff section.  We have links to free tutorials, and a more                                                                extensive list of other online  resources

There are some amazing resources online – so spend a little bit of time reviewing the content and making sure you’re up to speed with all of the techniques in Excel.  The same goes for any of our Excel-based training courses, not just Power Pivot.  You’ll get a lot more out of your training course!

Learn to Love your Excel Errors

As I was scrolling through the index for my book recently, I noticed the #REF error jump out at me.  Why is it that the #REF strikes such fear into a modeller’s heart?  Probably because out of all common Excel errors, it is the one which is the most difficult to fix.  It means that your formula is referring to a cell that doesn’t exist anymore!  If someone has saved a model with a #REF error in it, it’s not very easy to correct because the formula that it’s referencing has gone; so the formula needs to be completely rebuilt.

As annoying as they are, I actually like seeing Excel errors in my formulas believe it or not, as it means that something isn’t working, and therefore I can fix it.  As I always tell my training course attendees – the only thing worse than finding a mistake in your model is NOT finding a mistake in your model and your model being wrong!  Worse than that is your boss finding the mistake in your model.  There’s no faster way to lose credibility as a modeller than for others to find formula mistakes in your model, and there are a number of strategies to reduce error that you can employ reduce this possibility in your modelling.

If you’ve attended one of my courses, you’ll know I’m quite fond of the SUMIF function is it’s much more versatile and robust than other popular functions such as a VLOOKUP.  One of my issues with the SUMIF though is that it does not warn you if you use mismatching ranges.  In the example below, the result in cell C13 will not return an error; it will simply give a wrong answer without any warning!

Excel Errors

Source:  Fig 6.6 on page 96 of Using Excel for Business Analysis

The thorough modeller in this example will check the totals as they work, and then put an error check below in cell C17 to make sure that any incorrect entry in the future is quickly identified.

Even better than the SUMIF is its big brother, the SUMIFS function which can handle multiple criteria and  –  critically –will warn the user if the ranges are mismatching, unlike the SUMIF.  It’s one of SUMIF’s annoying little quirks that it allows mismatching ranges and exactly why SUMIF allows this, is something I’ll never understand.   It’s highly unlikely that we’d deliberately want mismatching ranges and far more likely that it’s been done inadvertently (although I have been known it use it for staggering periods).

So instead of cringing at a horrid #VALUE! error value, or supressing it with an IFERROR function, we should instead embrace, use and perhaps even love our Excel error values!  Figure out what’s wrong, and get to the bottom of it.  An Excel error value is far more helpful than the alternative which is to have deceivingly innocent number values which are in fact incorrect.