Waterfall ChartThis follow-up article to “How to create a Waterfall Chart in Excel”, adds a dynamic drop-down list to enable users to change the source time series period of the Waterfall Chart. A dynamic Waterfall Chart delivers flexibility, which a static Waterfall Chart is unable to offer the users of a financial model.

 

Practical added value of a dynamic Waterfall Chart

A basic Waterfall Chart may deliver analytical insight of a company’s drivers of KPIs, a dynamic Waterfall Chart permits users to change the period plotted on the chart. Financial model users can quickly comprehend varying financial performance, by changing the sourced times series period.

The following YouTube video outlines the steps to create a dynamic Waterfall Chart, which utilises this attached Excel spreadsheet:

Strategize Financial Modelling – Waterfall Chart.xlsWaterfall Chart

[youtube]http://youtu.be/cUOyZpKKtTs[/youtube]

 

Components of a dynamic Waterfall Chart

These components are identical to the steps illustrated in a standard Waterfall Chart. The only difference is the application of the hybrid INDEX MATCH, instead of the VLOOKUP function, which can enable flexible referencing of source financials from the dashboard.

Waterfall Chart

 

 

Values Column

This column is the basis for the computed values in the other waterfall chart columns. However, it is not included in the plotted series ranges for the chart. Remember the first line item must subtract the previous year’s bottom line number, in order for the Values Column to calculate the correct bottom line number for the current year.

 

Previous Period Column

This column will merely reference from a dashboard, the prior period’s bottom line number i.e. EBITDA in 2017. This data series will be ranked first, and be positioned in the far left-hand side of the Waterfall Chart.

 

Invisible Column

Arguably the most pivotal data series. This column will guarantee whether the line items are correctly plotting, the progression of the current period’s bottom line number. The invisible column data series must be positioned second, which will ensure its values are located beneath the accretive and dilutive columns.

 

Accretive Column

Applying the MAX function in Excel, the column will reference only positive financial statement items such as revenue or interest. Its values will be plotted on top of the invisible column values.

 

Dilutive Column

Alternatively this column references negative line items such as expenses via the use of the MIN Excel function. Like the accretive column, the dilutive column values will be plotted above the values for the invisible column.

 

Current Period Column

Similar to the Previous Period Column, the current period value is sourced from the dashboard summary, and will be plotted on the far right-hand side of the chart.

 

Error Check in the Waterfall Chart

The essence of best practice financial modelling is safeguarding an error-free modelling solution to financial model stakeholders. The error check will deliver surety and confidence in the computed outputs in the waterfall data, which is the source for the various data series plotted in the Waterfall Chart.