Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
"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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Editing a Chart Directly?? | Charts and Charting in Excel | |||
Problem with xlusrgal.xls file | Charts and Charting in Excel | |||
Impedding/Overlaying Charts | Charts and Charting in Excel | |||
Labels on Chart with Negative Value Axis | Charts and Charting in Excel | |||
pivot table multi line chart | Charts and Charting in Excel |