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?
