This article is a follow-on from earlier, more basic editorials on creating a Waterfall Chart; How to create a Waterfall Chart in Excel and How to create a dynamic Waterfall Chart. It addresses the needs of an advanced Waterfall Chart, which must chart both positive and negative financial KPIs.
The flexibility of Excel and the added complexity required, in order to chart negative numbers in a Waterfall Chart, means there are a variety of ways to chart both positive and negative values.
The following is covered in this financial model and YouTube video:
Strategize Financial Modelling – Advanced Waterfall Chart – Charting Negatives.xls
[youtube]http://youtu.be/Bnj-GvPk9kU[/youtube]
Practical Considerations of this Waterfall Chart
The key factors to consider are prior period negative results, accretive drivers (i.e. revenue) crossing through the x-axis, and dilutive drivers (i.e. cost of sales) cutting through the x-axis. Clearly a vanilla, basic Waterfall Chart will be unable to chart this correctly or successful; as per earlier articles and videos.
Added or Amended Columns in a Waterfall Chart with Negatives
Aggregate Sum
The creation of an aggregation of values column is required, because it will be a precedent number, which will help to calculate a number of the ancillary columns. These columns will discussed in the following.
=SUM($AM$9:AM10)
Invisible Column (+) (above x-axis)
=IF(MIN(SUM(AM$9:AM9),SUM(AM$9:AM10))>0,MIN(SUM(AM$9:AM9),SUM(AM$9:AM10)),0)
This column triggers a value if the prior year KPI (i.e. EBITDA) was positive; otherwise the computed number will be zero.
Invisible Column (+) (cuts x-axis)
=IF(AND(AN9<0,AO10>0),MIN(SUM($AM$9:AM9),SUM($AM$9:AM10)),0)
Conversely a prior year number in the red (i.e. negative EBITDA) would return a negative value, which is the prior year’s value, because the current year’s revenue amount (assuming it is positive) will cut through by the x-axis. In the following for 2016, Net Revenue must offset 2015’s net EBITDA of ($317,293), before it will cut through the x-axis by the delta of $1,899,462.
Invisible Column (-) (cuts x-axis)
=IF(AND(AO9>0,AO10<0),MIN(SUM($AM$9:AM9),SUM($AM$9:AM10)),0)
This column generates a negative value if a dilutive item (i.e. Cost of Sales) causes the aggregate sum of values to become negative. This is illustrated the below 2014 Waterfall Chart, whereby the Cost of Sales (-$1,673,583) is greater than the Net Revenue ($1,523,220) for the period.
Invisible Column (-) (below x-axis)
=IF(AND(AO9<0,AO10<0),MAX(SUM($AM$9:AM9),SUM($AM$9:AM10)),0)
This column is sparked if the aggregate sum of values is negative (i.e. 2015), whereby additional dilutive items such as Selling & Marketing or General & Admin would force values below the x-axis.
Accretive column
=MAX(AM10+AQ10,0)
This column value accounts for only line items (i.e. revenue), which have earnings that are above the x-axis. As the following illustration for 2015 can testify to; the ($385,907) to fully offset the prior year’s earnings sits below the x-axis – even though it is technically earnings accretive.
Dilutive column
=IF(AND(AO9<0,AO10<0),MIN(AM10,0),-MIN(AM10-AR10,0))
Similarly the revised Dilutive column must disregard any balance, which is below the x-axis because this delta; as illustrated for Cost of Sales in 2014 is accounted for in Invisible Column (-) (cuts x-axis) by negative $150,363. In this 2014 example, the other dilutive opex items are invariably chartered as negative amounts – because they are below the x-axis.
Concluding Remarks
As stated in the above video, it is fundamental these computed columns are 100% correct and order of the Legend Entries (Series); otherwise the Waterfall Chart will not accurately track earnings from prior to current year. The flexible and ubiquitous nature of Excel means there are many alternatives, which are outlined by other peer financial model experts like Liam Bastick.