Healthcare Waterfall
Jon Peltier provides an excellent example of how to create waterfall charts that cross the x axis on his Peltier Technical Services site. These charts are especially useful for illustrating the components of variance.
Adapting Jon's waterfall charting examples to show the components of variance in a healthcare environment (for Hospitals, Clinics, etc.) requires an extra step. Normally, a positive variance would be shown with a green bar moving up and a negative variance would be shown with a red bar moving down. In a healthcare setting there's an extra twist. A positive variance can be shown with a green bar moving up or down. Likewise, a negative variance can be shown with a red bar moving up or down. Given these additional requirements, the formulas that drive the variance bars must be modified to reflect these types of changes.
To further explain, revenue provided by commercial and contract payers allows a greater reimbursement than revenue provided by government payers. As a result, an increase in the revenue provided by a commerical and contract payer would be favorable. A decrease in the revenue provided by a government payer is also favorable. Conversely, a decrease in revenue provided by commercial and contract payers may be viewed as unfavorable while an increase in revenue provided by government payers would also be viewed as unfavorable.
Below is an example of how a healthcare waterfall chart might look:
To allow for the changes in color if the variance is positive or negative, I've added a "Yes" or "No" color switch to the range C29:C33. Rather than the values determining if the color of the bar is positive or negative, the Yes-No switch allows the user to determine whether a change in a bar is positive or negative.
The example shows an overall decrease in volume. However, although the decrease in volume is normally viewed as negative, the reimbursement associated with the decrease may actually increase. The increase in reimbursement is a result of the positive commerical and contract variance combined with a negative government variance. To summarize, even though the volumes are decreasing the net revenue per unit of volume increases which allows for the possibility of greater profitability.
Since the example is somewhat complex, rather than explaining how each of the formulas work I've provided a link to the example file below.
