Waterfall Charts

Anaplan definition

Waterfall charts help to convey how an initial value is affected by a series of intermediate positive or negative values. They are particularly useful for understanding the gradual transition in value of an item that is subject to increment or decrement. Whole columns represent the initial and the final values, while the intermediate values are expressed as floating columns. The columns are colour-coded to distinguish between positive and negative values: green for positive values; red for negative; and blue for totals (these colours can be customised in Chart Options).

Waterfall charts are very flexible and can be useful for viewing product earnings, company profit, cost sheets, the number of contracts in hand, the structure of a sales pipeline, cash flow statements, or monitoring how divisional profit and loss is formed. If you compare the same data, presented as a bar chart and then as a waterfall chart, you notice that the waterfall chart gives a clearer view of how values changed over the period.

Typical use cases

Generate a waterfall chart

You can create a waterfall chart from data in a simple list or from line items. Seven points to consider before selecting data:

Case study 1

I have created a simple module with employee costs made up of the following lists: Time and Line Items. I would like to generate a waterfall chart showing me how the different elements of the payroll make up the whole.

Remember that waterfall charts will only work on one column and the list or line items must always be in rows as per screenshot (right).

Please note: All the line items that have parents are included in the waterfall chart and because Gross Salary is the first occurring valid aggregation (i.e. it is a total of ALL the line items that precede it in the top-down order in the module), it appears as well. Anaplan automatically generates the End Total because the parent of the line items in question varies (see blueprint of the module here).

What if we change the module so that all the line items have one aggregation line item?

This screenshot shows the blueprint of the same module, now with a new line item ‘Total Employee Costs’ which is an aggregation of all the line items (excluding the summary line items ‘Gross Salary’, ‘Benefits’, ‘Bonus’ and ‘Total Other Costs’).

When we repeat steps to generate the waterfall chart on the amended Employee Costs module we get the below result.

Please note: Anaplan no longer automatically generates an ‘End Total’ because one now exists in the module (i.e. ‘Total Employee Costs’ is the parent of all the relevant line items).

Case study 2

The previous case study only shows increments; however, the true value of Anaplan waterfall charts is that they automatically show decrements as well as increments without the tricky formulas that Excel models require and case study 2 is an example of this.

I have created another simple module (below), this time with a starting total value of ‘Total Employee Costs F1’ (i.e. Forecast 1) and an ending ‘Total Employee Costs F2’ (i.e. Forecast 2). The aim is to understand variances between these two forecast versions, so you will notice that the line items now show variances between forecast versions F2 and F1 rather than totals (see blueprint, also below).

Please note: the parent for the line items is ‘Total Employee Costs F2’, which contains an aggregation formula of all the line items as per Case Study 1, above. However, line item formulas have been added to pick up a variance between forecast versions F1 and F2. The new line item ‘Total Employee Costs F1’ is picking up Total Employee Costs for forecast version F1. This will be our starting point for the waterfall with increments and decrements (i.e. variances calculated in the line items) leading to the final ‘Total Employee Costs F2’ aggregation line item. However, we won’t focus on the detailed workings of these formulas in this case study.

When you step through the process to generate the waterfall chart you get the result here.

You can now see increments (green) and decrements (red) between F1 and F2. From this example, a reduction in salaries is largely behind the decrease in employee costs from F1 to F2. This gives us a better understanding of what is driving changes in our forecast values.