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