negative waterfall chart

Hello:

Anyone have any ideas how to make a waterfall chart that can break below the
Y-axis? Jon Peltier has a good example of an automatic waterfall chart, but
I can't figure out how to make it go negative.

Thanks!

Pablo,

I can understand why you might have a component of the variance go negative
but in theory the start and end points should always be positive. As a
result, couldn't you just set your data up similar to this and then just
create a simple clustered column chart?

2003 Rev. $3,000
Volume $2,000
Price $1,500
Acuity -$800
Payor Mix -$1,000
Other -$500
2004 Rev. $4,200

In the example total gross revenue increased by $1,200. Volume and price
make up a positive variance of $3,500. However acuity, payor mix, and other
factors have resulted in negative variances and thus reduce gross revenue by
$2,300. If you create the bar chart, the positive variances appear above the
Y axis and the negative variances appear below it. Because you include the
2003 start and 2004 end amounts, the entire chart will crossfoot if you show
the values in the data labels.

You can make the chart even more meaningful by staggering the data to
include three sets of columns. The first set is for the 2003 start and 2004
end values, the second set is for positive variances, and the third set is
for negative variances. The revised setup would look like this:

Balances Pos Var Neg Var
2003 Rev. $3,000
Volume $2,000
Price $1,500
Acuity -$800
Payor Mix -$1,000
Other -$500
2004 Rev. $4,200

When you create a simple clustered column chart with the data set up like
above, the three groups of columns will reflect different colors accrding to
what category the data falls into i.e Balances, Pos Var, or Neg Var.

If this does not work for you, can you provide a sample of your data and an
explanation of what the data is being used for?

----
Regards,
John Mansfield
http://www.pdbook.com


"Pablo" wrote:

> Hello:
>
> Anyone have any ideas how to make a waterfall chart that can break below the
> Y-axis? Jon Peltier has a good example of an automatic waterfall chart, but
> I can't figure out how to make it go negative.
>
> Thanks!
>
>
>

Pablo -

You need to add more series to account for negative values. I have
another page describing stacked column charts that have to cross the
axis, which you can combine with the waterfall charts technique:

http://peltiertech.com/Excel/Charts/...eAndBelow.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Pablo wrote:

> Hello:
>
> Anyone have any ideas how to make a waterfall chart that can break below the
> Y-axis? Jon Peltier has a good example of an automatic waterfall chart, but
> I can't figure out how to make it go negative.
>
> Thanks!
>
>

"John Mansfield" <[email protected]> wrote in
message news:[email protected]...
> Pablo,
>
> I can understand why you might have a component of the variance go
> negative but in theory the start and end points should always be
> positive. As a result, couldn't you just set your data up similar
to
> this and then just create a simple clustered column chart?
>

Hi John,

Just as a note, this is correct if you are looking at, say, revenue.

However, what about the case where the chart is reconciling, say,
EBIT. The start point might be $1.2m profit, and the end point
perhaps $0.7m loss.

In that situation, we have to be able to cope with a variance bar
crossing the axis I think?

Alan.