Paired Waterfall Chart

The Paired Waterfall Chart

I came across a new (for me) style or waterfall chart last month, which I call the paired waterfall chart. It is designed to show how an investment fund’s total holdings, comprised of stocks or other vehicles and cash, changes during a time period, based on changes in value of its stocks and on proceeds from the sale of some stocks.

Here is an example of such a chart. The holdings of the fund totaled 1350 in 2015: 1100 in stock plus 250 in cash. The holdings of the same fund in 2016 totaled 1450: 675 in stock and 775 in cash. Stock A didn’t change in value. Stock B appreciated by 50 in value. Proceeds from the sale of some Stock C were 275, while the fund’s total value of Stock C declined by 250; the difference of 25 must have been additional appreciation of Stock C. Sales of some Stock D resulted in proceeds of 250 and corresponding loss in value of 250. Stock E went down 25 in value, while Stock F went up 50.

Paired Waterfall Chart

There is no provision for taking cash and buying more of a stock already in the portfolio or a new stock, so the upper set of floating bars are green only, that is, you can only gain cash. The lower bars can be red or green, but the value of your stock holdings can never drop below zero, because at worst the stock is worth nothing. These constraints make the chart slightly easier to build than a standard waterfall, though the horizontal and slanted lines are trickier than in the standard one.

For want of a better name, I call this the “Paired Waterfall Chart”. In hundreds of images of waterfall charts that Google found for me, none had this same structure. I tried to crowd source the chart’s name on Twitter, but the only suggestions that weren’t lame attempts at humor were “stacked waterfall” and “cascade”, and these refer to other chart types. If you know what this chart is really called, or if you have some examples you can share, please let me know in the comments below this article.

Stacked Waterfall and Cascade Charts

How To…?

Since I’ve never seen this kind of waterfall, and since nobody asked me how to make one, I have not prepared a tutorial. But someone did ask whether my charting software, Peltier Tech Charts for Excel 3.0, could make these charts. The answer was, not yet, but with the thought of perhaps selling a few licenses, I took on the task.

The Advanced Edition of Peltier Tech Charts for Excel 3.0 now includes a Paired Waterfall Chart feature, and that’s what I used to make the chart at the top of this article. The data consists of three columns of data, with labels for the horizontal axis, entries for value of the holdings, and entries for proceeds due to any sales. The top row consists of labels, and the totals in the bottom row are omitted, because the program can add better than you can.

If you are a licensee of the Advanced Edition of Peltier Tech Charts for Excel 3.0, shoot me an email and I’ll send you the link to download the latest build, which includes the Paired Waterfall Chart. If you are a licensee of the Standard Edition, let me know and I’ll send a coupon code so you can upgrade to the Advanced Edition for the difference in price. If you are a licensee of an older Peltier Tech utility, let me know and I’ll send a discount coupon code so you can upgrade. And you should upgrade, because Peltier Tech Charts for Excel 3.0 works in Excel 2016, and the same add-in file works on both Windows and Mac.

Like all of my utility’s charts, it is easy to make the Paired Waterfall Chart. Here I’ve selected the data, clicked the Waterfall dropdown, then clicked the Paired Waterfall Chart button.

Data and Ribbon Button for Paired Waterfall Chart

This very simple dialog came up.

Paired Waterfall Chart Dialog

I clicked OK, and the program generated a new worksheet with a bunch of formulas in a nice Excel Table, and the Paired Waterfall Chart (click on the image to see it full-sized).

Paired Waterfall Chart Output

I’m gradually transitioning as many of my program’s chart output sheets to use Excel Tables, because they make it easier for users to adjust the chart’s data after they’ve already built the chart. Tables will also make it easier when I finally come out with Peltier Tech Charts for PowerPoint and Word. The structured referencing in the tables can be a challenge, but Zack and Kevin’s book about Excel Tables helped a lot.

 

Peltier Tech Charts for Excel

Comments

  1. Hi,
    Based on other postings both from you and Chandoo I created a stacked waterfall chart almost exactly as you show here I use a dual Axis to achieve the correct height however and have not been able to master automation of the Maximum.. some time ago I requested assistance with writing into the code you provide for Axis Max you advised I should consider hiring someone. Please consider adjusting the Macro code you use for this functionality to apply to your waterfalls or a dual axis waterfall…

  2. Sharon –

    I can’t seem to find that earlier comment. What exactly is the problem you’re having with the maximum? Do you need different scales on two vertical axes? If not, delete one axis and the other will fit to all the data.

  3. Ian wrote to me offline:

    I’m not a huge fan of waterfall charts but was drawn to yesterday’s article on paired waterfall charts. Very interesting and well done with the quick update to your utility.

    But I don’t get the x-axis: in the example you show, surely the two green boxes shouldn’t be aligned with values greater than 1450? Just looks odd. I think it works well without the x-axis.

    Just a thought

    I’m not sure why the X axis is the axis you have the problem with.

    The way the chart works is by taking two components of a total, and making a waterfall chart of each.

    Ordinarily you might end up with something like this, with the final bars both starting at the horizontal baselines defined by their respective initial bars. There is a gap because much of the decrease in the lower bar was captured by gains in the upper bar.

    We can duplicate the two separated final bars in the next category, but reset the baseline of the upper bar to the top of the lower bar, eliminating the gap and showing the two final bars in a combined total bar.

    The paired waterfall simply skips the totals with the top bar floating well above the bottom bar, and goes straight to the view with the upper bar seated atop the lower bar.

Speak Your Mind

*

Peltier Tech Charts for Excel 3.0