This article shows users how to create a waterfall chart in Excel. It will apply the bar or column chart template with “Stacked Column” properties, which will produce an aesthetically pleasing graph of financial performance; based on a specified key performance indicator (KPI) over a selected time period i.e. EBITDA.
Applying eye-catching or intuitive colours to the bar graphs in the chart, will improve the marketing appeal and financial snapshot of corporate performance to executives or stakeholders.
Practical value of a Waterfall Chart in business modelling
A waterfall chart is a powerful way to illustrate key business drivers in a company’s financial performance. A standard Excel graph, such as a bar or line graph, can deliver a high-level overview of a company’s performance across time periods.
However a Waterfall Chart delivers greater analytical and insightful value, because it illustrates the path of financial performance across financial line items (i.e. Cost of Sales), which generates a bottom line result such as EBITDA or Net Income.
This attached financial model is used in the following video to outline the creation of a Waterfall Chart.
Strategize Financial Modelling – Basic Waterfall Chart.xls
[youtube]http://youtu.be/CBPQTAcSzTA[/youtube]
Components of a Waterfall Chart
Building this from scratch, rather than using an Excel add-in, does take a bit more time and attention to detail. Hence, it is vital to follow these steps closely.
The Waterfall Chart is tracking values from the prior year’s KPI (i.e. EBITDA), denoting all items above the line (i.e. Cost of Sales) and concluding with the current year’s equivalent KPI (i.e. EBITDA).
❶ Values
Working downwards from the previous year’s EBITDA, the below schedule sequentially lists the line items, which make up the computed EBITDA value. The first (“2017 (F)”) value will simply reference it’s adjacent columns, which is why this cells is coloured in light blue.
Remember the second value, must subtract the previous year’s EBITDA value – hence the reason for this cell being presented in royal blue.
The other three values are merely referenced from the table of financial results, through the use of a simple VLOOKUP formula to reference the correct value by specific year.
The last value (“2018 (F)”) is the sum of the above cells; it must reconcile to the value in part ❻, otherwise the calculations are incorrect and the Waterfall Chart will not work.
❷ Previous period’s value (i.e. “EBITDA – 2017 (F)”)
This value is sourced from the table, like the other three values in part ❶, through the use of an HLOOKUP formula to reference the correct value for the specific year.
❸ Invisible Column
This column will determine whether the Waterfall Chart is correctly setup. It commences on the second row and ends on the second last row – it excludes the previous and current period’s value
Remember, the invisible column must be graphed on the chart using “No Fill” colour formatting, and it must rank second in the “Legend Entries (Series)” properties in the following – otherwise the Waterfall Chart won’t work.
❹ Accretive column (i.e. “EBITDA Accretive”)
This column will convey any line items (“Graph Inputs”) that are positive. The accretive column will increase the trailing aggregate of the previous period’s value, whenever a financial line item is positive such as “Net revenue”.
In this example, “Net revenue” is the only positive value that will be referenced in this column. In cell AP10, type the following formula and copy down to cell AP13:
=MAX(AM10,0)
❺ Dilutive column (i.e. “EBITDA Dilutive”)
Similar to the “Accretive column” in part ❹, but instead this column will reference any “Graph Inputs” that are negative. Elements such as “Cost of sales” will reduce the trailing aggregate of the previous period’s value, because they have a dilutive effect on the bottom line.
In cell AQ10, enter the subsequent formula and copy down to cell AQ13:
=-MIN(AM10,0)
❻ Current period’s value (i.e. “EBITDA – 2018 (F)”)
This is the same as part ❷ through the use of a HLOOKUP formula to reference the correct value for the current year.
Creating the Waterfall Chart on a graph
Normally creating a normal graph is straight forward in Excel, however a Waterfall Chart requires a little more work. This example requires five series of data to be added to the “Legend Entries (Series)” or y-axis, and the “Graph Inputs” column will represent the “Horizontal (Category) Axis Labels”.
To guarantee the correct presentation of the y-axes, it is advisable to rank the above columns in the following order:
Executive value of a Waterfall Chart
Executives and other stakeholders prefer aesthetically pleasing, graphical presentations of financial performance, as opposed to reams and reams of details spreadsheet printouts and non-uniform graphs.
Present the Waterfall chart with uniform corporate colours of the company, and illustrate the accretive and dilutive columns in a way, which fully illustrates the key drivers of the company’s bottom line.
Present the previous period in a grey font, the accretive bars (i.e. revenue) in an eye-catching colour such as purple or green, the dilutive bars (i.e. expenses) in a bright red, and the closing period bar in a dark colour or blank.