Cash Flow Waterfall Chart - Strategic Finance
ADVERTISEMENT
1
2TECHNOLOGY
EXCEL
By Bill Jelen
Cash Flow Waterfall Chart
Microsoft Excel doesn’t offer a built-in
Figure 1. Cash Flow Waterfall Chart
waterfall chart, but a few extra columns
of formulas added to your data can easily
produce a cash flow waterfall chart. In a
waterfall chart, the column begins with
the previous month’s balance and travels
up for positive amounts or down for neg-
ative amounts (see Figure 1).
To create the chart, you will add sev-
eral quick columns to the original data
set shown in Figure 1. First, add a bal-
ance column. Though this isn’t absolute-
ly necessary, it makes the remaining
formulas much easier. For 10 years, I
built waterfall charts without this extra
column and would beat my head against
my desk as I tried to decode the formu-
las needed for the additional columns.
The first row in the balance column is
simply =Amount. Then each new row
touch the baseline. The formula for the
matter in this example, but the calcula-
adds that month’s amount to the previ-
Grey column is =Balance.
tion time for MAX is a tiny bit faster
ous balance (=Previous Balance +
The Up column needs to pull all of
than IF.
Amount). Figure 2 shows the formula for
the positive amounts over. While you
The Down column needs the absolute
the January cell.
could use =IF(Amount>0,Amount,0), it’s
value of all negative amounts. While you
Now copy the month names to the
quicker to use =MAX(0,Amount). This
might use =IF(Amount<0,ABS(Amount),
next column. Then add four new
clever formula is handy for getting posi-
0), you can also use =MIN(Amount,0)*-1.
columns: Invisible, Down, Up, and Grey.
tive amounts. If the amount is greater
Formulating the
The Grey column is for the values that
than zero, then the amount “wins” in
Invisible Column
need to touch the x-axis. In this example,
the MAX function. If the amount is neg-
the first and last rows (Start and End)
ative, then the zero wins. It will hardly
The Invisible column is the magic that
I
S T R AT E G I C F I N A N C E
D e c e m b e r 2 0 1 1
5 2
ADVERTISEMENT
0 votes
Related Articles
Related forms
Related Categories
Parent category: Business