One board member wants to make sure that a complete risk analysis, including sensitivity and scenario analyses, is performed before the proposal is sent to the board.

a. Perform a sensitivity analysis.

b. What management information is provided by the sensitivity analysis?

		CORAL BAY HOSPITAL
		Traditional Project Analysis
This case illustrates a complete capital budgeting analysis, including cash flow analysis
and profitability measures. Note the model extends to Column I.
The model consists of a complete base case analysisno 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.
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
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