Reference no: EM132432421

The hospital has 512 doctors. The new COO at the hospital has been told that in the past, any physician with a quality rating higher than 83.5, received a $10,000 bonus. Physicians with a rating of 90 or higher, received a $25,000 bonus. (Note that a physician with a 90 or higher ranking received the first $10,000 bonus for being above 83.5 and then another $15,000 on top of that for a total of $25,000. )

This year, the budget for physician bonuses has been set at $2 million. The COO knows that this budget is not sufficient to meet last year's bonus structure, but wants to keep rewarding the 90+ rated physicians with $25,000 bonuses. Thus, the COO must decide what cutoff to use in place of 83.5 so that the total bonuses handed out stays within the $2 million budget.

All indications are that average quality score of 83 and standard deviation of 4.8 will be the same for this year.

Calculate your Bonus cost using as many steps as you need.

Hint: if you've calculated the bonus cost correctly, you should find that the total cost for last year's bonuses (using the lower cutoff of 83.5) would have been $2,903,449.90. Clearly exceeding this year's budget.

Once you have a model, use the Excel Data Table function to test various values for the Lower Cutoff. Test values to the nearest tenth of a point (i.e., 83.5, 83.6, 83.7 ...) It should be sufficient to check values between 83.5 and 89.0.

Determine the cutoff to use to replace the 83.5 lower cutoff for the $10,000 bonus so that the sum of all bonuses is less than or equal to $2,000,000, but use as much of this bonus budget as possible.