Coral Bay Case Spreadsheet With Questions
xlsx
keyboard_arrow_up
School
Georgia State University *
*We aren’t endorsed by this school
Course
8550
Subject
Medicine
Date
Dec 6, 2023
Type
xlsx
Pages
24
Uploaded by bxzzzz
Coral Bay Hospital is a 250-bed, investor-owned hospital located in Coral Bay, Florida, which is kno
was founded in 1946 by Dr. Rob Winslow, a prominent Florida physician, on his return from service
1967 while it was still small and in a relatively quiet setting. However, in recent years, South Florida
fostered high economic growth and the need for more healthcare services. Today, under a succession
leading healthcare providers in the area. Coral Bay’s management is currently evaluating a proposed
on ambulatory surgery, see the Ambulatory Surgery Center Association website at www.ascassociatio
performed by specialists in gastroenterology, gynecology, ophthalmology, otolaryngology, orthopedi
requires an average of about one and a half hours to complete; minor procedures take about one hour
hours. About 60 percent of the procedures are performed under general anesthesia, 30 percent under anesthesia. In general, operating rooms are built in pairs so that a patient can be prepped in one room
room. The outpatient surgery market has experienced significant growth since the first ASC opened i
more than 25 million. This growth has been fueled primarily by three factors. First, rapid advanceme
historically performed in inpatient surgical suites to be offered in outpatient settings. This shift was c
endoscopic, and arthroscopic technologies. Second, Medicare has been aggressive in approving new
Medicare patients using outpatient surgery services has grown substantially. Third, patients prefer ou
third-party payers prefer them because they are less costly. Because of these factors, the number of in
past 20 years, whereas the number of outpatient procedures has continuously grown more than 10 pe
surgeries has been accompanied by a corresponding growth in the number of outpatient facilities nat
competition in many areas has become intense. Somewhat surprisingly, no ASC exists in Coral Bay’
immediate service area. Coral Bay currently owns a parcel of land adjacent to its facility that is a per
five years ago for $300,000, and this amount is reported as an asset on the balance sheet. Last year, $
utility lines, and this amount was reported as an operating expense on the hospital’s income statemen
value of the land on the balance sheet). If sold in today’s market, the land would bring in $400,000, n
been extremely volatile, so Coral Bay’s standard procedure is to assume a salvage value equal to the depreciated for either book or tax purposes. The ASC building, which would house four operating su
an additional $5 million, for a total of $20 million. Assume that both the building and the equipment
system) five-year class for tax depreciation purposes. (In reality, the building would have to be depre
the equipment.) The project will probably have a long life, but Coral Bay typically assumes a five-ye
approximates the value of the
being performed at stand-alone ASCs, and by 2021, the number had grown to more than 25 million. First, rapid advancements in technology have enabled many procedures that were historically perform
settings. This shift was caused mainly by advances in laser, laparoscopic, endoscopic, and arthroscop
approving new minimally invasive surgery techniques, so the number of Medicare patients using out
patients prefer outpatient surgeries because they are more convenient, and third-party payers prefer t
the number of inpatient surgeries has remained more or less flat over the past 20 years, whereas the n
more than 10 percent annually. Rapid growth in the number of outpatient surgeries has been accomp
outpatient facilities nationwide. The number currently stands at about 5,300, so competition in many
exists in Coral Bay's immediate service area. Coral Bay currently owns a parcel of land adjacent to it
The hospital bought the land five years ago for $300,000, and this amount is reported as an asset on land and put in sewer and utility lines, and this amount was reported as an operating expense on the h
thus not included in the asset value of the land on the balance sheet). If sold in today’s market, the la
and taxes. Land prices have been extremely volatile, so Coral Bay’s standard procedure is to assume
course, land is not depreciated for either book or tax purposes. The ASC building, which would hous
equipment would cost an additional $5 million, for a total of $20 million. Assume that both the build
accelerated cost recovery system) five-year class for tax depreciation purposes. (In reality, the buildi
than the equipment.) The project will probably have a long life, but Coral Bay typically assumes a fi
approximates the value of the cash flows beyond Year 5 by including a terminal, or salvage, value in
typically uses the market value of the building and equipment after five years, which for this project land value. (Note that taxes must be paid on the difference between an asset’s salvage value and its ta
cost $10,000 has been depreciated down to $5,000 and then sold for $7,000, the firm owes taxes on t
The expected volume at the ASC is 20 procedures a day. The average charge per procedure is expect
plan discounts, and other allowances lower the net patient revenue amount to $2,000. The ASC woul
250 days a year. Labor costs to run the ASC are estimated at $3,200,000 per year, including fringe be
would add another $100,000 in annual costs. If the ASC were built, the hospital’s cash overhead cost
housekeeping and buildings and grounds maintenance. In addition, the ASC would be allocated $50,
overhead costs. On average, each procedure would require $400 in expendable medical supplies, inc
receivables would increase slightly if the center is constructed, its accruals and payables would also expected to be small
and hence not material to the analysis. Coral Bay’s marginal federal-plusstate tax rate is 30 percent. W
staff meeting, several questions were raised. Coral Bay’s chief of medicine expressed concern over t
surgeries. This concern prompted an analysis by the head of the surgery department, who reported th
cash revenues annually. When pressed, the department head estimated that such a reduction in volum
reduction in annual cash expenses. The CEO has asked DeShawn Williams, Coral Bay’s director of c
DeShawn was invited to the last board of directors’ meeting, where several concerns about the propo
was the impact of potential inflation on project profitability. Both input costs and charges in the heal
overall inflation. Furthermore, inflationary pressures have been highly variable. For the proposed AS
for costs (except depreciation) and 2 percent for net patient revenue. In previous capital budgeting de
inflation on project profitability with a table such as the one shown in exhibit 21.1. The second conce
impact on profitability if actual outcomes differ from the expected ones. Recently, the board was forc
when analyzed two years ago but turned out to be a big money loser. The board does not want a repe
thought Coral Bay was putting too much faith in the numbers. “After all,” she pointed out, “that is w
start worrying more about how projects fit into our strategic vision and how they affect the services w
sensitivity, scenario, and break-even analyses are an integral component of the risk analysis that DeS
risk analysis were obtained from the department heads of surgery, marketing, and facilities. Three in
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
Number of procedures per day. If another entity enters the local ASC market, the number of procedu
acceptance is strong and no competing ASCs are built, the number of procedures could be as high as
day.
• Average net patient revenue per procedure. The most likely amount of $2,000 per procedure is a fu
amount of managed care penetration. If surgery severity were high (i.e., if a higher number of compl
managed care penetration remained low, then the average amount per procedure could be as high as expected and managed care penetration increases, the average amount per procedure could be as low
• Building and equipment salvage value. If real estate and medical equipment values stay strong, the
$14 million, but if the market weakens, the salvage value could be as low as $10 million, compared w
DeShawn estimates that the probabilities of the worst case, most likely case, and best case scenarios
DeShawn consulted with Sophia Garcia, Coral Bay’s chief financial officer, about both the risk inher
the hospital typically adjusts for risk. Sophia told DeShawn that historical scenario-analysis data sho
net present value in the range of 3.5 to 4.0. If a proposed project has an estimated coefficient of varia
percentage points are added to the 7 percent corporate cost of capital to adjust for higher project risk
years ago, the board has insisted on this adjustment for projects judged to be high risk and has consis
risk-adjusted corporate cost of capital. The final concern raised at the last board of directors’ meeting
specifically, what if a competitor uses an ASC to lure the hospital’s surgeons away? What if the hosp
director believes that a missed opportunity for such a venture could precipitate a severe reduction in and productive surgeons and polarizing the medical staff. The ASC may be a way of preserving the s
productive surgeons, and growing market share and geographic penetration. However, it is also poss
to participate in the ASC because of their surgical specialty or safe harbor compliance, may not supp
ASC. What should DeShawn recommend to the board of directors?
Model
Page 5
CASE 21 CORAL BAY HOSPITAL
Traditional Project Analysis
Student Version
This case illustrates a complete capital budgeting analysis, including cash flow analysis, and
profitability measures. Note that the model extends to Column I.
The model consists of a complete base case analysis--no changes need to be made to the exis
spreadsheet have been replaced by zeros. Students must select appropriate input values and from the MODEL-GENERATED DATA section.
INPUT DATA:
KEY OUTPUT:
Land initial cost
$0 NPV
$0 Land opportunity cost (and salvage value)
$0 IRR
Err:523
Building/equipment cost
$0 MIRR
10.0%
Build/equipment salvage value
$0 Payback
999.0
Procedures per day
0 Average net patient revenue per procedure
$0 Labor costs
$0 Utilities costs
$0 Incremental overhead
$0 Supply cost ($/procedure)
$0 Tax rate
0.0%
Inflation rate on net patient revenue
0.0%
Inflation rate on costs
0.0%
Revenues lost from inpatient surgeries
$0
Reduction in inpatient surgery costs
$0
Cost of capital
0.0%
MODEL-GENERATED DATA:
Depreciation Schedule:
MODEL-GENERATED DATA section. However, values in the I
NPUT DATA section of the stude
them into the cells with values colored red
. After this is done, any error cells will be corrected a
the base case solution will appear. The KEY OUTPUT section includes the most important outp
Model
Page 6
MACRS
Deprec.
End of Year
Year
Factor
Expense
Book value
1
0.20
$0 $0 2
0.32
0 0 3
0.19
0 0 4
0.12
0 0 5
0.11
0 0 6
0.06
0 0 Net Cash Flows:
Project Cash Flows
0
1
2
Land opportunity cost
$0 Building/equipment cost
0 Net patient revenue (including inpatient loss)
$0 $0 Less: Labor costs
0 0 Cost savings on inpatients
0 0 Utilities costs
0 0 Supplies
0 0 Incremental overhead
0 0 Depreciation
0 0 Income before taxes
$0 $0 Taxes
0 0 Project net income
$0 $0 Plus: Depreciation
0 0 Plus: Net land salvage value
Plus: Net building/equipment salvage value
Net cash flow
$0 $0 $0 Cumulative net cash flow
$0 $0 $0 (For payback calculation)
Profitability and Breakeven Measures:
Net present value (NPV)
$0 Internal rate of return (IRR)
Err:523
Modified IRR (MIRR)
10.0%
Payback
999.0
Copyright © 2022 Foundation of the American College of Healthcare Executives. Not for sale.
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
Model
Page 7
sting
enter
ent
and
put
Model
Page 8
3
4
5
$0 $0 $0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 $0 $0 $0 0 0 0 $0 $0 $0 0 0 0 0 0 $0 $0 $0 $0 $0 $0 END
Question 1
What are the NPV, IRR, MIRR, and payback of the proposed ambulatory surgery center
measures indicate acceptance or rejection of the proposed ASC? (Use Coral Bay Hosp
cost of capital without applying a risk premium.)
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
r (ASC)? Do the pital's corporate
Question 2
Inflation is one of the most difficult factors to deal with in project analysis.
a.
Complete the inflation impact table shown in exhibit 21.1.
b.
Does inflation risk appear to be a potential reason for management to not pro
The What-If Analysis in Excel can be used to produce a table that shows the impact of i
-
In cell H25, enter “=F25”
-
In cells I25 to O25, enter 0%, 1%, 2%, 3%, 4%, 5% and 6%, resp
-
In cells H26 to H32, enter 0%, 1%, 2%, 3%, 4%, 5% and 6%, res
-
Highlight cells H25 to O32
-
Click on What-If Analysis and then Data Table
-
Row Input Cell: D36
-
Column Input Cell: D37
-
Click OK
oceed with the ASC?
inflation on the project's NPV. Here are the steps:
pectively
spectively
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
Question 3
a.
Perform a sensitivity analysis for the three variables identified as highly unce
input values can range from -30 to +30 percent of base case.
b.
How sensitive is NPV to changes in each of the three variables identified as c.
If the ASC proceeds, how might information about the sensitivity of NPV to e
variables identified as highly uncertain be used by management? That is, w
focus on to increase the chances of success?
ertain. Assume that highly uncertain?
each of the three hat might management
Question 4
a.
Perform a scenario analysis using the three variables identified as highly unc
b.
Is the ASC an average-risk or high-risk project?
c.
Why is the expected NPV obtained in the scenario analysis different from the
d.
In the worst-case scenario, what should management be most concerned ab
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
certain.
e base case NPV?
bout?
Question 5
a.
Perform a break-even analysis for each of the three variables identified as hi
b.
If the ASC proceeds, how might the break-even values be used by managem
ighly uncertain.
ment?
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
Question 6
To help with the risk-incorporation phase of the analysis, DeShawn consulted with the h
about both the risk inherent in the hospital's average project and how the hospital typica
risk.
a.
What is the project's NPV using the risk-adjusted corporate cost of capital?
b.
Assess the financial risk of the project. How would the correlation between th
hospital cash flows affect the financial risk of the project? (No calculations ar
hospital CFO
ally adjusts for he ASC and re required.)
Question 7
DeShawn is aware that there are some qualitative factors that are relevant to the ASC decision.
a.
What qualitative factors might support project acceptance?
b.
What qualitative factors might preclude project acceptance?
c.
What other costs might be associated with the project that have not been inc
d.
What additional data would you seek from other hospital staff members to co
analysis?
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
cluded in the analysis?
onduct a more thorough
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
Question 8
Considering all points, would you build the ASC?
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
Question 9
In your opinion, what are three key learning points from this case?
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help