Posted: July 23rd, 2016

How many visits would have to occur before RRC starts to lose money?

Happy Healthcare (a large MCO) is negotiating with Roger s Rehab Center (RRC) to be the sole local provider of physical and occupational therapy to its members. Currently, Happy Health Care has 25,000 covered lives. Roger s Rehab Center has the capacity to handle 500 new patients within the current cost structure (no additional fixed costs). RRC has variable costs of $45 per visit. RRC has collected data that leads them to believe that 1 percent of Happy Healthcare s covered lives will require rehab services during a typical year. In addition, RRC assumes that Healthy Healthcare s covered lives are similar t their current patients and will, on average, require 25 rehab sessions per patient. Happy Healthcare has offered RRC $25 per covered life per year.
a) Based on RRC s assumptions, should they take Happy Healthcare s offer?
b) How many visits would have to occur before RRC starts to lose money?
c) Using the scenario manager (see instructions for Sensitivity Analyses in the Topic 8 files), develop the following sensitivity analyses for part a of this problem
1) What if 2 percent of the covered lives needed rehab services (still 25 sessions each)?
2) What if those who required rehab services averaged 38 visits per year (but 1 percent required rehab services)?
3) Combine the above two scenarios (2% need rehab services and they average 38 sessions each) for a worst case scenario.
4) Using the summary function to develop a summary table of the three sensitivity analyses.
You should also submit the mathematical formulas or calculations completed to answer the questions. You will receive a maximum of 75 points for correctly completing all the questions and providing the correct calculations.
Need this done in an excel spreadsheet