Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Pablo
 
Posts: n/a
Default 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!


  #2   Report Post  
John Mansfield
 
Posts: n/a
Default

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!



  #3   Report Post  
Jon Peltier
 
Posts: n/a
Default

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!


  #4   Report Post  
Alan
 
Posts: n/a
Default

"John Mansfield" wrote in
message ...
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.


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Editing a Chart Directly?? Charisma Riley Charts and Charting in Excel 3 January 26th 05 02:09 PM
Problem with xlusrgal.xls file Alfred S C Lee Charts and Charting in Excel 2 December 29th 04 06:54 PM
Impedding/Overlaying Charts Phil Hageman Charts and Charting in Excel 4 December 17th 04 08:25 PM
Labels on Chart with Negative Value Axis David F. Schrader Charts and Charting in Excel 6 December 17th 04 07:25 PM
pivot table multi line chart souris Charts and Charting in Excel 2 December 7th 04 04:56 AM


All times are GMT +1. The time now is 11:11 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"