Coral bay hospital case study

CASE19
CASE 20 Instructor Version Copyright 2014 Health Administration Press
11/23/16
CORAL BAY HOSPITAL
Traditional Project Analysis
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 existing MODEL-GENERATED DATA section. However, all values in the student
version INPUT DATA section have been replaced with zeros. Thus, students must determine
the appropriate input values and enter them into the model. These cells are colored red.
When this is done, any error cells will be corrected and the base case solution will appear.
Note that the student version does not contain any risk analyses, so students will have to
create their own if required by the case. Furthermore, students must create their own
graphics (charts) as needed to present their results.
The instructor version of the model contains an inflation table, sensitivity analysis,
and scenario analysis, which are not included in the student version. A graph of the sensitivity
analysis is contained on the second sheet.
INPUT DATA: KEY OUTPUT:
Land initial cost $150,000 NPV $875,020
Land opportunity cost (and salvage value) $200,000 IRR 12.9%
Building/equipment cost $10,000,000 MIRR 11.8%
Build/equipment salvage value $5,000,000 Payback 4.1
Procedures per day 20.0
Average net patient revenue per procedure $1,000
Labor costs $918,000
Utilities costs $50,000
Incremental overhead $36,000
Supply cost ($/procedure) $200
Inflation rate on net patient revenue 3.0%
Inflation rate on costs 3.0%
Tax rate 40.0%
Revenues lost from inpatient surgeries $1,000,000
Reduction in inpatient surgery costs $500,000
Cost of capital 10.0%
MODEL-GENERATED DATA:
Depreciation Schedule:
MACRS Deprec. End of Year
Year Factor Expense Book value
1 0.20 $2,000,000 $8,000,000
2 0.32 3,200,000 4,800,000
3 0.19 1,900,000 2,900,000
4 0.12 1,200,000 1,700,000
5 0.11 1,100,000 600,000
6 0.06 600,000 0
Net Cash Flows:
Project Cash Flows
0 1 2 3 4 5
Land opportunity cost ($200,000)
Building/equipment cost (10,000,000)
Net patient revenue (including inpatient loss) $4,000,000 $4,120,000 $4,243,600 $4,370,908 $4,502,035
Less: Labor costs 918,000 945,540 973,906 1,003,123 1,033,217
Cost savings on inpatients (500,000) (515,000) (530,450) (546,364) (562,754)
Utilities costs 50,000 51,500 53,045 54,636 56,275
Supplies 1,000,000 1,030,000 1,060,900 1,092,727 1,125,509
Incremental overhead 36,000 37,080 38,192 39,338 40,518
Depreciation 2,000,000 3,200,000 1,900,000 1,200,000 1,100,000
Income before taxes $496,000 ($629,120) $748,006 $1,527,447 $1,709,270
Taxes 198,400 (251,648) 299,203 610,979 683,708
Project net income $297,600 ($377,472) $448,804 $916,468 $1,025,562
Plus: Depreciation 2,000,000 3,200,000 1,900,000 1,200,000 1,100,000
Plus: Net land salvage value 180,000
Plus: Net building/equipment salvage value 3,240,000
Net cash flow ($10,200,000) $2,297,600 $2,822,528 $2,348,804 $2,116,468 $5,545,562
Cumulative net cash flow ($10,200,000) ($7,902,400) ($5,079,872) ($2,731,068) ($614,600) $4,930,962
(For payback calculation)
Profitability and Breakeven Measures:
Net present value (NPV) $875,020
Internal rate of return (IRR) 12.9%
Modified IRR (MIRR) 11.8%
Payback 4.1
Impact of Uncertain, Future Inflation on NPV:
Level of Net Patient Revenue Inflation
$875,020 0% 1% 2% 3% 4% 5% 6%
0% 557,808 724,065 893,501 1,066,160 1,242,091 1,421,339 1,603,952
1% 495,296 661,553 830,988 1,003,648 1,179,578 1,358,826 1,541,440
Level of Cost 2% 431,588 597,845 767,280 939,940 1,115,870 1,295,119 1,477,732
Inflation 3% 366,668 532,925 702,360 875,020 1,050,950 1,230,199 1,412,812
4% 300,518 466,775 636,210 808,870 984,801 1,164,049 1,346,662
5% 233,121 399,378 568,813 741,473 917,403 1,096,651 1,279,265
6% 164,458 330,715 500,150 672,810 848,741 1,027,989 1,210,602
Sensitivity Analysis:
(Note: This table does NOT automatically recalculate when input values are changed.)
Number of Avg Revenue Bldg/Equip
Procedures per Procedure Salvage Value
-30% (2,006,852) (2,727,320) $316,191
-20% (1,046,228) (1,526,540) 502,467
-10% (85,604) (325,760) 688,744
0% 875,020 875,020 875,020
10% 1,835,644 2,075,800 1,061,296
20% 2,796,268 3,276,580 1,247,573
30% 3,756,892 4,477,360 1,433,849
Scenario Analysis:
(Note: This section does NOT automatically recalculate when values in the INPUT DATA section are
changed. However, the probabilities within the section can be changed and the resulting values
will automatically be recalculated.)
Number of Avg Revenue Bldg/Equip
Case Probability Procedures per Procedure Salvage Value NPV
Worst 25.0% 10 $800 $4,000,000 ($5,501,433)
Most Likely 50.0% 20 1,000 5,000,000 875,020
Best 25.0% 25 1,200 6,000,000 6,651,083
100.0%
Expected NPV $724,922
Standard Deviation $4,299,184
Coefficient of Variation 5.9
END
equipment salvage value
CASE 20 Instructor Version Copyright 2014 Health Administration Press
11/23/16
CORAL BAY HOSPITAL
Traditional Project Analysis
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 existing MODEL-GENERATED DATA section. However, all values in the student
version INPUT DATA section have been replaced with zeros. Thus, students must determine
the appropriate input values and enter them into the model. These cells are colored red.
When this is done, any error cells will be corrected and the base case solution will appear.
Note that the student version does not contain any risk analyses, so students will have to
create their own if required by the case. Furthermore, students must create their own
graphics (charts) as needed to present their results.
The instructor version of the model contains an inflation table, sensitivity analysis,
and scenario analysis, which are not included in the student version. A graph of the sensitivity
analysis is contained on the second sheet.
INPUT DATA: KEY OUTPUT:
Land initial cost $150,000 NPV $0
Land opportunity cost (and salvage value) $200,000 IRR 10.0%
Building/equipment cost $10,000,000 MIRR 10.0%
Build/equipment salvage value $2,651,286 Payback 4.1
Procedures per day 20.0
Average net patient revenue per procedure $1,000
Labor costs $918,000
Utilities costs $50,000
Incremental overhead $36,000
Supply cost ($/procedure) $200
Inflation rate on net patient revenue 3.0%
Inflation rate on costs 3.0%
Tax rate 40.0%
Revenues lost from inpatient surgeries $1,000,000
Reduction in inpatient surgery costs $500,000
Cost of capital 10.0%
MODEL-GENERATED DATA:
Depreciation Schedule:
MACRS Deprec. End of Year
Year Factor Expense Book value
1 0.20 $2,000,000 $8,000,000
2 0.32 3,200,000 4,800,000
3 0.19 1,900,000 2,900,000
4 0.12 1,200,000 1,700,000
5 0.11 1,100,000 600,000
6 0.06 600,000 0
Net Cash Flows:
Project Cash Flows
0 1 2 3 4 5
Land opportunity cost ($200,000)
Building/equipment cost (10,000,000)
Net patient revenue (including inpatient loss) $4,000,000 $4,120,000 $4,243,600 $4,370,908 $4,502,035
Less: Labor costs 918,000 945,540 973,906 1,003,123 1,033,217
Cost savings on inpatients (500,000) (515,000) (530,450) (546,364) (562,754)
Utilities costs 50,000 51,500 53,045 54,636 56,275
Supplies 1,000,000 1,030,000 1,060,900 1,092,727 1,125,509
Incremental overhead 36,000 37,080 38,192 39,338 40,518
Depreciation 2,000,000 3,200,000 1,900,000 1,200,000 1,100,000
Income before taxes $496,000 ($629,120) $748,006 $1,527,447 $1,709,270
Taxes 198,400 (251,648) 299,203 610,979 683,708
Project net income $297,600 ($377,472) $448,804 $916,468 $1,025,562
Plus: Depreciation 2,000,000 3,200,000 1,900,000 1,200,000 1,100,000
Plus: Net land salvage value 180,000
Plus: Net building/equipment salvage value 1,830,772
Net cash flow ($10,200,000) $2,297,600 $2,822,528 $2,348,804 $2,116,468 $4,136,334
Cumulative net cash flow ($10,200,000) ($7,902,400) ($5,079,872) ($2,731,068) ($614,600) $3,521,733
(For payback calculation)
Profitability and Breakeven Measures:
Net present value (NPV) $0
Internal rate of return (IRR) 10.0%
Modified IRR (MIRR) 10.0%
Payback 4.1
Impact of Uncertain, Future Inflation on NPV:
Level of Net Patient Revenue Inflation
$0 0% 1% 2% 3% 4% 5% 6%
0% (317,212) (150,955) 18,481 191,140 367,071 546,319 728,932
1% (379,724) (213,467) (44,032) 128,628 304,558 483,806 666,420
Level of Cost 2% (443,432) (277,175) (107,740) 64,920 240,851 420,099 602,712
Inflation 3% (508,352) (342,095) (172,660) 0 175,931 355,179 537,792
4% (574,502) (408,245) (238,810) (66,150) 109,781 289,029 471,642
5% (641,899) (475,642) (306,207) (133,547) 42,383 221,632 404,245
6% (710,562) (544,305) (374,869) (202,210) (26,279) 152,969 335,582
Sensitivity Analysis:
(Note: This table does NOT automatically recalculate when input values are changed.)
Number of Avg Revenue Bldg/Equip
Procedures per Procedure Salvage Value
-30% (2,006,852) (2,727,320) $316,191
-20% (1,046,228) (1,526,540) 502,467
-10% (85,604) (325,760) 688,744
0% 875,020 875,020 875,020
10% 1,835,644 2,075,800 1,061,296
20% 2,796,268 3,276,580 1,247,573
30% 3,756,892 4,477,360 1,433,849
Scenario Analysis:
(Note: This section does NOT automatically recalculate when values in the INPUT DATA section are
changed. However, the probabilities within the section can be changed and the resulting values
will automatically be recalculated.)
Number of Avg Revenue Bldg/Equip
Case Probability Procedures per Procedure Salvage Value NPV
Worst 25.0% 10 $800 $4,000,000 ($5,501,433)
Most Likely 50.0% 20 1,000 5,000,000 875,020
Best 25.0% 25 1,200 6,000,000 6,651,083
100.0%
Expected NPV $724,922
Standard Deviation $4,299,184
Coefficient of Variation 5.9
END
revenue per procedure
CASE 20 Instructor Version Copyright 2014 Health Administration Press
11/23/16
CORAL BAY HOSPITAL
Traditional Project Analysis
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 existing MODEL-GENERATED DATA section. However, all values in the student
version INPUT DATA section have been replaced with zeros. Thus, students must determine
the appropriate input values and enter them into the model. These cells are colored red.
When this is done, any error cells will be corrected and the base case solution will appear.
Note that the student version does not contain any risk analyses, so students will have to
create their own if required by the case. Furthermore, students must create their own
graphics (charts) as needed to present their results.
The instructor version of the model contains an inflation table, sensitivity analysis,
and scenario analysis, which are not included in the student version. A graph of the sensitivity
analysis is contained on the second sheet.
INPUT DATA: KEY OUTPUT:
Land initial cost $150,000 NPV $0
Land opportunity cost (and salvage value) $200,000 IRR 10.0%
Building/equipment cost $10,000,000 MIRR 10.0%
Build/equipment salvage value $5,000,000 Payback 4.3
Procedures per day 20.0
Average net patient revenue per procedure $927
Labor costs $918,000
Utilities costs $50,000
Incremental overhead $36,000
Supply cost ($/procedure) $200
Inflation rate on net patient revenue 3.0%
Inflation rate on costs 3.0%
Tax rate 40.0%
Revenues lost from inpatient surgeries $1,000,000
Reduction in inpatient surgery costs $500,000
Cost of capital 10.0%
MODEL-GENERATED DATA:
Depreciation Schedule:
MACRS Deprec. End of Year
Year Factor Expense Book value
1 0.20 $2,000,000 $8,000,000
2 0.32 3,200,000 4,800,000
3 0.19 1,900,000 2,900,000
4 0.12 1,200,000 1,700,000
5 0.11 1,100,000 600,000
6 0.06 600,000 0
Net Cash Flows:
Project Cash Flows
0 1 2 3 4 5
Land opportunity cost ($200,000)
Building/equipment cost (10,000,000)
Net patient revenue (including inpatient loss) $3,635,645 $3,744,715 $3,857,056 $3,972,768 $4,091,951
Less: Labor costs 918,000 945,540 973,906 1,003,123 1,033,217
Cost savings on inpatients (500,000) (515,000) (530,450) (546,364) (562,754)
Utilities costs 50,000 51,500 53,045 54,636 56,275
Supplies 1,000,000 1,030,000 1,060,900 1,092,727 1,125,509
Incremental overhead 36,000 37,080 38,192 39,338 40,518
Depreciation 2,000,000 3,200,000 1,900,000 1,200,000 1,100,000
Income before taxes $131,645 ($1,004,405) $361,462 $1,129,306 $1,299,185
Taxes 52,658 (401,762) 144,585 451,723 519,674
Project net income $78,987 ($602,643) $216,877 $677,584 $779,511
Plus: Depreciation 2,000,000 3,200,000 1,900,000 1,200,000 1,100,000
Plus: Net land salvage value 180,000
Plus: Net building/equipment salvage value 3,240,000
Net cash flow ($10,200,000) $2,078,987 $2,597,357 $2,116,877 $1,877,584 $5,299,511
Cumulative net cash flow ($10,200,000) ($8,121,013) ($5,523,656) ($3,406,779) ($1,529,195) $3,770,316
(For payback calculation)
Profitability and Breakeven Measures:
Net present value (NPV) $0
Internal rate of return (IRR) 10.0%
Modified IRR (MIRR) 10.0%
Payback 4.3
Impact of Uncertain, Future Inflation on NPV:
Level of Net Patient Revenue Inflation
$0 0% 1% 2% 3% 4% 5% 6%
0% (270,907) (119,794) 34,208 191,140 351,046 513,966 679,946
1% (333,419) (182,306) (28,305) 128,628 288,533 451,454 617,433
Level of Cost 2% (397,127) (246,014) (92,012) 64,920 224,825 387,746 553,725
Inflation 3% (462,047) (310,934) (156,932) 0 159,905 322,826 488,805
4% (528,197) (377,084) (223,082) (66,150) 93,755 256,676 422,655
5% (595,594) (444,481) (290,480) (133,547) 26,358 189,279 355,258
6% (664,257) (513,144) (359,142) (202,210) (42,305) 120,616 286,595
Sensitivity Analysis:
(Note: This table does NOT automatically recalculate when input values are changed.)
Number of Avg Revenue Bldg/Equip
Procedures per Procedure Salvage Value
-30% (2,006,852) (2,727,320) $316,191
-20% (1,046,228) (1,526,540) 502,467
-10% (85,604) (325,760) 688,744
0% 875,020 875,020 875,020
10% 1,835,644 2,075,800 1,061,296
20% 2,796,268 3,276,580 1,247,573
30% 3,756,892 4,477,360 1,433,849
Scenario Analysis:
(Note: This section does NOT automatically recalculate when values in the INPUT DATA section are
changed. However, the probabilities within the section can be changed and the resulting values
will automatically be recalculated.)
Number of Avg Revenue Bldg/Equip
Case Probability Procedures per Procedure Salvage Value NPV
Worst 25.0% 10 $800 $4,000,000 ($5,501,433)
Most Likely 50.0% 20 1,000 5,000,000 875,020
Best 25.0% 25 1,200 6,000,000 6,651,083
100.0%
Expected NPV $724,922
Standard Deviation $4,299,184
Coefficient of Variation 5.9
END
SENSITIVITY ANALYSIS GRAPH
SENSITIVITY ANALYSIS

-0.3 -0.2 -0.1 0.0 0.1 0.2 0.3 -2.00685216994865E6 -1.04622814252877E6 -85604.115108883 875019.9123110045 1.83564393973089E6 2.79626796715078E6 3.75689199457066E6 -0.3 -0.2 -0.1 0.0 0.1 0.2 0.3 -2.72732019051357E6 -1.52654015623871E6 -325760.121963853 875019.9123110045 2.07579994658586E6 3.27657998086072E6 4.47736001513557E6 -0.3 -0.2 -0.1 0.0 0.1 0.2 0.3 316190.7215577643 502467.1184755117 688743.5153932571 875019.9123110045 1.06129630922875E6 1.2475727061465E6 1.43384910306424E6
Change from Base Case

NPV ($)