Safaricom, the most admired brand in the telecommunication industry in Kenya has seen yet a more profitable year 2015 with total revenue having a YoY growth of +12.9% to 163.4bn, whereas Free Cash Flow (FCF) grew by +21.3% to 27.5bn (Source: Safaricom Financial Results). What fascinated me though is the use of the waterfall chart to visualize both the revenues and the FCF. But have you ever wondered how to create this chart in Excel? This post will give you the 4 steps you need to create a waterfall chart in Excel 2013.

A waterfall chart, often referred to as bridge or cascade charts, are used in business to show how a value (EBITDA in the above figure) changes from one state to another (FCF value) through a series of intermediate (positive and negative) changes. You can use a waterfall chart to present income statement components, how a product price has changed over the last 3 years, etc. Power BI experts have done a great article on how you create and use a waterfall chart in Power BI (this is a Microsoft data visualization tool available as a desktop as well as a web-based version).

This article labors to take you through 4 simplified steps of creating this non-standard advanced waterfall chart. Download the file  used to follow through. These steps apply for those with MS Excel 2013 and lower versions. For Excel 2016, the good news is that the waterfall chart is one of the standard charts! Share this article with a colleague and let me know in the comments section what data visualization techniques you would want to advance skills in.

The first step is to reorganize the data and introduce new columns as shown in the figure that follows. Given the value of each of the items to be presented in the waterfall chart, we shall introduce the Cumulative, Ends, Invisible, +ve Change and -ve Change columns.

The formulas used for these additional columns are explained below:

Cumulative column: We use the formula =SUM($D$3:D3) to get the cumulative total. Alternatively, in cell E4, type the formula E3+D4.

Ends column: This gives the starting and ending values, in this case EBITDA and FCF respectively. To get the start value type =D3 in cell F3 then copy the formula in cell F8.

Invisible column: The values in this column, often referred to as the base column provide support or the starting point to the floating negative and positive series. The formula used here reads =IF(D4>0,E3,E4). Different Advanced Excel analysts apply varied formulas to generate this column.

The rationale of this column is that if the change is positive (D4>0), then this positive value’s starting point will be the previous total (E3), BUT if there is a decrement, then the absolute value of this negative change will be supported by a series whose value is current row cumulative total (E4).

+ve Change column: This column will comprise of all positive values. We use the formula =IF(D4>0,D4,NA()) to return these values, otherwise return an error. The error is invoked so that the chart doesn’t plot the blank/zero values (article for another day).

-ve Change column: It is also called the Fall column and we’ll place all the negative numbers (the absolute) here using this formula =IF(D4<0,ABS(D4),NA()) or =IF(D4<0,-D4,NA()).

Before inserting the column, ensure you have selected the series labels, and the four columns created in the previous step.

Upon inserting the chart from the Insert menu/tab you will get the following stacked column chart:

The secret here is to make the invisible column have no fill colour and formatting the ups (+ve change series) and downs (-ve change series) in your favorite colour.

• Right click the invisible series (see legend above) on the chart and choose Format Data Series…option from the pop up menu. Under Fill, choose No fill and for the Border, choose No line.

• In a similar manner, format the +ve changes column/series to green and -ve changes series to red.
• For the Start (EBITDA) and End (FCF) series, colour code them using the same shade (say dark blue) following similar steps.
• Select one of the series and right click to format (short cut is CTRL+1). When the “Format Data Series…” dialog box appears, under the Series Options, set the Gap Width to 0% (zero).

This step will involve the following:

• Ensure you delete the gridlines (the horizontal lines on the chart).
• Select the legend and press delete on the keyboard.
• Select the primary vertical axis (y-axis) and delete as well.
• Add a chart title -in this case “FY15 Free Cash Flow“
• Add data labels by right-clicking one of the series and selecting “Add data labels…” Add labels to each of the series apart from the invisible column.

Select the data labels and make them bold, change colour as appropriate. The finished chart should look something similar to the one below.

Download the completed version here.