Tulsa Memorial Hospital (Break-Even Analysis, Case #6, pp. 47-53)
Download the Case 6 excel spreadsheet .This spreadsheet contains a model that will assist you in answering the following questions. You should replace the cells in red with the "appropriate" numbers, which may depend on your assumptions.
1. Using the historical data as a guide, construct a pro forma (forecasted) profit and loss statement for the clinic's average month for all of 2014 assuming the status quo. Explain why you choose the numbers you did. With no change involume (utilization), is the clinic projected to make a profit?
[HINT: Complete the "Pro Forma Average Month" section of the excel spreadsheet.]
2. Now consider the clinic's situation without the new marketing program. How many additional daily visits must be generated to break even? Construct a breakeven graph that can be included in your report.
[HINT: Fill out the "Incremental Monthly Costs" portion of the excel spreadsheet. Use the numbers generated by the model to graphically show where the breakeven point is.]
3. Repeat the Question 2 analysis, but now assume that the new marketing program is implemented.
[HINT: If you have finished Q2, the analysis for Q3 and Q4 are already provide by the spreadsheet.]
4. Now focus solely on the expected profitability of the proposed marketing program. How many incremental daily visits must the program generate to make it worthwhile? Construct a breakeven graph. Is the clinic profitable at this point?
5. What is your final recommendation concerning the future of the walk-in-clinic? Consider both the numerical analysis you just conducted and the whether the clinic has any value to the hospital beyond the numerical analysis (i.e. Do the actions by Baptist Hospital have any bearing on the final decision regarding the clinic)?
Book being used is:
Louis C. Gapenski and George H. Pink, Cases in Healthcare Finance, 5th Edition (Washington DC: Health Administration Press), 2014. ISBN: 978-1567936117.
Pages 47-53
Attachment:- Case-6 Excel.rar