The Bedford Coach - Waterfall Charts
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 we compare the same data, presented as a bar chart and then as a waterfall chart, you can see how the waterfall chart gives a clearer view of how values changed over the period.
Typical use cases:
Understanding movements between forecast versions
Understanding YOY movements
Understanding incremental effects of line item values leading to a total value
Generate a Waterfall Chart
You can create a waterfall chart from data in a simple list or from line items. You should consider these points before selecting data:
Composite lists (parent hierarchies) cannot be used to create a waterfall chart.
Any line items that don't have a parent are ignored and will not be included in the waterfall chart.
Only a single column of data is used to generate a waterfall chart. If you select multiple columns, only the data from the first column will be used to generate the chart.
The list or line items must always be on rows and any non-numeric data in the selection is ignored.
Aggregates are only displayed if all the values selected are aggregates of the same value type.
Waterfall charts will always display an end total. If the waterfall chart finishes on a valid aggregate it will take the name of that aggregate as the label for the total. If it does not finish on an aggregate, the chart will automatically generate a total (based on the preceding values) and display End Total as the label.
Although the default colours are green for an Increment, red for a Decrement, and blue for Totals, you can choose different colours in Chart Options when generating the chart.
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 below.
Highlight the column in question
Navigate to charts icon and select Waterfall Chart
Select chart options and publish to a dashboard
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 below).
What if we change the module so that all the line items have one aggregation line item?
Below screenshot shows 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.
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. The below case study 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 2 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).
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 we step through the process to generate the waterfall chart we get the below result.
We 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.
Stay tuned for the next Bedford Coach article in May,