Simple Rollover Calculation

In healthcare, the term "rollover" can refer to the amount of volume it would take to offset a change in revenue. A typical question might be "if we sign a contract with a certain insurance company and they agree to a 30% discount, how much more additional volume will we need to see to offset that discount?" The term "volume" refers to billed charges for office visits, surgery procedures, pharmacy prescriptions, etc.

For example, if we currently have $600,000 in patient charges, a bad debt rate of 5%, and 7,500 units of volume, how much more volume do we need to see if the discount rate goes from 5% to 30%? In the screenshot below, the $600,000 of gross revenue appears in line 1, the 5% discount rate in line 2, and 7,500 units of volume in line 5. The net revenue is calculated by multiplying the gross revenue by the discount rate.

The first step to estimating the rollover impact is to calculate the gross revenue per unit for the data you're given. That calculation appears in line 6 below. Next, carry the gross revenue per unit ($80), the net revenue ($570,000), and the new discount rate (30%) into the second column of calculations.

Calculate the gross revenue by dividing the net revenue by 1 - the discount rate. The formula in cell F4 is:

=F8/(1-F10)

Finally, calculate the volume required at a 30% discount by dividing the gross revenue by the gross revenue per unit. In the screenshot below, the formula in cell F12 is:

=F4/F14

In summary, the analysis shows that volume needs to increase 36% to recover the net revenue lost by increasing the discount rate from 5% to 30%.