|
The term waterfall chart means different things to
people in different industries. Search
www.google.com or www.yahoo.com for
an extensive list. Two types commonly used in business analysis are
charts that look like a vertical waterfall or a horizontal multi-stage
waterfall. For more on the horizontal waterfall chart see the tutorial
on floating bar charts and the
Gantt Chart add-in.
The vertical waterfall
chart
 |
Figure 2
 |
Figure 1
A typical waterfall chart is shown in Figure 1.
A somewhat modified version of the chart, leveraging Excel's
capabilities to enhance the visual display of the chart, is shown in
Figure 2. This tutorial explains how to create the latter chart.
The example demonstrates one hypothetical financial statement of an
organization -- the Cash Flow Statement for the year 2004 illustrated
in Table 1.
|
|
B |
C |
|
2 |
Waterfall Chart Example from Finance |
|
3 |
|
|
|
4 |
|
Amount |
|
5 |
Net cash flow from operating activities |
2,336 |
|
6 |
Depreciation and amortization |
3,098 |
|
7 |
Net cash for/from investing activities |
(4,200) |
|
8 |
Net cash for/from financing activities |
1,752 |
|
9 |
Beginning Cash Balance (Dec. 31, 2003) |
1,248 |
|
10 |
Dividends paid |
(733) |
|
11 |
Ending Cash Balance (Dec. 31, 2004) |
3,501 |
Table 1
Numbers in red are outflows. The two Cash Balances are net
positions at the end of two consecutive fiscal years and, as such,
are neither cash inflows or outflows.
The building blocks
Putting it all together |
|
Before creating the waterfall for the data
above, one must understand the three 'building blocks' that go
into the creation of the desired chart. These building blocks
are:
- Negative values in a
stacked chart
- A floating chart
- A chart with
'conditional formatting'
|
 |
Figure 3
 |
Figure
4
Typically, a stacked chart shows two series so
that one series is plotted "as is" and the 2nd series is stacked on
top of the first. Consider the example data in Table 2.
The first data point of series 1 is 10 and the corresponding data
point of the 2nd series is 8. So, Excel will show the first
series as a bar from zero to 10, and the 2nd series as a bar from 10
to 18 (see Figure 3).
However, if the value in the 2nd series is
negative as shown in Table 3, Excel does not start from the top of the
first series and subtract the negative value, but it starts the bar
for the data point for the 2nd series at zero as in Figure 4.
| 10 |
-8 |
| 12 |
10 |
| 14 |
12 |
Table 3
 |
Figure 5
Consequently, if one wants to 'stack' the
negative value such that it starts at the top of the first series and
effectively subtracts from it, one must do some additional work.
Note that to get Excel to stack two points one must plot only a
positive value. So, we create a dummy series that has absolute
values from the 2nd series. In addition, the value of the first
series should be adjusted so that it is now the subtracted value
itself. We do this with another dummy series.
|
B |
C |
D |
E |
| 29 |
10 |
-8 |
2 |
8 |
| 30 |
12 |
10 |
12 |
10 |
| 31 |
14 |
12 |
14 |
12 |
Cell D29 contains the formula
=IF(C29<0,B29+C29,B29) and E29 =ABS(C29). D29:E29 is copied down
to rows 30:31. |
 |
Figure 6
Given the stacked chart in Figures 5, the
easiest way to create a floating effect is to make the first series
transparent. Double-click the first (lower) plotted series, then
in the Format Data Series dialog box, select the Patterns
tab. In there, set the Border to None and the
Area to None.
Also, select the legend. Pause, then
select the legend entry for Series 1. Delete it with the Delete
key.
The result should be as in Figure 6. |
|
One can simulate a wide variety of conditional
formatting of charts. In this case, we want to make it appear as
though negative numbers are one color and positive numbers another.
The easy way to do this is to create two series, one containing only
those numbers that are positive, the other for the negative numbers.
The example above becomes:
|
B |
C |
D |
E |
F |
| |
Series 1 |
Series 2 |
Modified Series 1 |
Positive Series 2 values |
Negative series 2 values |
| 45 |
10 |
-8 |
2 |
#N/A |
8 |
| 46 |
12 |
10 |
12 |
10 |
#N/A |
| 47 |
14 |
12 |
14 |
12 |
#N/A |
 |
Figure 7
The formula in E45 is =IF(C45>0,C45,NA()); that
in F45 is =IF(ISNA(E45),ABS(C45),NA()). Copy E45:F45 down to
rows 46:47.
Now, plot three series, those represented by
columns D, E, and F. As above, format the first series (column
D) to be transparent. Format the 2nd series (the positive values
in column E) to have a Green area and no border. Next, format
the last series (the negative values plotted as absolute quantities in
column F) as red.
Also, as above, delete the extraneous legend
entry.
The result should be as in Figure 7.
|
|
Given the financial data in Table 1 and the
result in Figure 2, three things becomes apparent.
- Because at least one of the values is
negative, the technique demonstrated in the 'negative values in a
stacked chart' must be used.
- Given the floating nature of the bars,
there is need for one dummy series to represent the transparent
series as in the second building block above.
- Finally, because the result chart has three
differently colored series, three additional series are needed --
one for positive cash flows, another for negative flows (though
converted to positive values), and the third for the 'net
position' values.
Putting it all together, the result is:
|
B |
C |
D |
E |
F |
G |
| 2 |
Waterfall Chart Example from Finance |
|
|
|
|
| 3 |
|
|
Columns for charting |
|
|
| 4 |
|
Amount |
Dummy |
Inflows |
Outflows |
Net Position |
| 5 |
Net cash flow from operating activities |
2,336 |
|
2,336 |
|
|
| 6 |
Depreciation and amortization |
3,098 |
2,336 |
3,098 |
|
|
| 7 |
Net cash for/from investing activities |
(4,200) |
1,234 |
|
4,200 |
|
| 8 |
Net cash for/from financing activities |
1,752 |
1,234 |
1,752 |
|
|
| 9 |
Beginning Cash Balance (Dec. 31, 2003) |
1,248 |
2,986 |
|
|
1,248 |
| 10 |
Dividends paid |
(733) |
3,501 |
|
733 |
|
| 11 |
Ending Cash Balance (Dec. 31, 2004) |
3,501 |
|
|
|
3,501 |
and the formulae in columns D through G are:
| Columns for charting |
|
|
|
| Dummy |
Inflows |
Outflows |
Net Position |
| |
=IF(C5>=0,ABS(C5),"") |
=IF(E5="",ABS(C5),"") |
|
| =IF(C6<0,SUM($C$5:C6),SUM($C$5:C5)) |
=IF(C6>=0,ABS(C6),"") |
=IF(E6="",ABS(C6),"") |
|
| =IF(C7<0,SUM($C$5:C7),SUM($C$5:C6)) |
=IF(C7>=0,ABS(C7),"") |
=IF(E7="",ABS(C7),"") |
|
| =IF(C8<0,SUM($C$5:C8),SUM($C$5:C7)) |
=IF(C8>=0,ABS(C8),"") |
=IF(E8="",ABS(C8),"") |
|
| =IF(C9<0,SUM($C$5:C9),SUM($C$5:C8)) |
|
|
=IF(C9>=0,ABS(C9),"") |
| =IF(C10<0,SUM($C$5:C10),SUM($C$5:C9)) |
=IF(C10>=0,ABS(C10),"") |
=IF(E10="",ABS(C10),"") |
|
| |
|
|
=C11 |
Now, plot columns B, D:G. By default,
column B will be the X (or category) values. Format the first
plotted series (column D) as transparent, the 2nd (inflows in column
E) as green, the third (outflows in column F) as red, and the last
(net positions in column G) in light blue to get the result in Figure
2.
|
|
