Waterfall Chart

[FM1]

Hi guys. I need help in creating a waterfall chart.

I have a baseline figure, incremental adds and a final figure. I just need to know how to set up a table so I can make a waterfall chart from the numbers I have.

Sample sheet attached.

Thanks.

[Andy Pope]

See,
http://peltiertech.com/Excel/Charts/Waterfall.html

[FM1]

Thanks Andy.

[seena]

Strategy: Use a Profit Waterfall Chart. This chart has three solid bars, representing List Price, Net Revenue, and Profit. Between those bars are floating bars that show how each individual expense item eats away the profit of the deal, as shown in Fig. 1110.
The trick to make the middle bars float is to use a stacked bar chart. The first series of bars will be changed into an invisible color without any lines in order to make the bars in the second series appear to float. The second series is that of the actual bars that will appear on the chart.1) Set up a new range that will be used to create the chart. Copy the labels from column A to this range.2) There are three bars that will need to touch the x-axis. For these three bars, the invisible series needs to be zero. The height of the bar is the same as the value in column B, as shown in Fig. 1111.3) The goal for Trade Discount is to have a floating bar that extends from 62,280 down to 34,254. In order to have the bar float at this level, you will need an invisible bar that is 34,254 tall. In cell E3, enter the formula of =F4, as shown in Fig. 1112.4) The height of the floating bar needs to extend from 34,254 to 62,280. Set the formula in F3 to =F2–E3, as shown in Fig. 1113.5) After the Net Revenue bar come all the SG&A expenses. The height of each floating bar will be the amount of the expense. In F5 enter a formula of =B5. Copy this down to cells F6:F9, as shown in Fig. 1114.6) The formula for the invisible portion of the bars always seems hard to figure out. In this case, if you start at the final bar, it might be easier. The Travel expense bar, representing $600, needs to float just above the profit level of 9441. Thus, the formula in E9 will be =F10, as shown in Fig. 1115.7) The Royalties bar of $4700 needs to float just above the level of the Travel bar. The height of the Travel bar is the height of the invisible bar (9441 in E9) and the height of the visible bar (500 in F9). The formula for E8 is =E9+F9, as shown in Fig. 1116.8) You now have a formula that can be copied. Copy E8 to the blank cells in E7:E5.9) Select the range of data from D1 to F10, as shown in Fig. 1117.10) From the menu, select Insert – Chart. Choose a Column chart and then in the Chart Sub-type box choose a Stacked chart, as shown in Fig. 1118.11) Choose Finish to accept the remaining default settings. The following chart, as shown in Fig. 1119, will appear. The point of the Profit Waterfall Chart is to illustrate to your VP of sales that, although this is a $60K deal, the net profit is less than $10K.

[Allen F Clark]

Hi, try it, http://www.fincontrollex.com/?page=p...s&id=1&lang=en It's very easy and allow make templates even by trial version. You can see how it works

[FDibbins]

THis thread s at best, over 3 years old, I doubt anyone is still monitoring it