Do Only Break even Analysis as follows:

You need to solve breakeven volume for number of procedures,break even price foraverage revenue per procedure and bld/salvage value.These are three separate analyses each of which will drive the NPV of your base case model spreadsheet to $0.

CASE19 CASE 20 4/20/2017 Student Version Copyright 2014 Health Administration Press 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 Land opportunity cost (and salvage value) Building/equipment cost Build/equipment salvage value Procedures per day Average net patient revenue per procedure Labor costs Utilities costs Incremental overhead Supply cost ($/procedure) Inflation rate on net patient revenue Inflation rate on costs Tax rate Revenues lost from inpatient surgeries Reduction in inpatient surgery costs Cost of capital $150,000 $200,000 $10,000,000 $5,000,000 20 $1,000 $918,000 $50,000 $36,000 $200 3.0% 3.0% 40.0% $1,000,000 $500,000 10.0% NPV IRR MIRR Payback Deprec. Expense $2,000,000 End of Year Book value $8,000,000 MODEL-GENERATED DATA: Depreciation Schedule: Year 1 MACRS Factor 0.20 Page 1 $875,020 12.9% 11.8% 4.1 CASE19 2 3 4 5 6 0.32 0.19 0.12 0.11 0.06 3,200,000 1,900,000 1,200,000 1,100,000 600,000 4,800,000 2,900,000 1,700,000 600,000 0 Net Cash Flows: Land opportunity cost Building/equipment cost Net patient revenue (including inpatient loss) Less: Labor costs Cost savings on inpatients Utilities costs Supplies Incremental overhead Depreciation Income before taxes Taxes Project net income Plus: Depreciation Plus: Net land salvage value Plus: Net building/equipment salvage value Project Cash Flows 0 ($200,000) (10,000,000) 1 2 $4,000,000 918,000 (500,000) 50,000 1,000,000 36,000 2,000,000 $496,000 198,400 $297,600 2,000,000 $4,120,000 945,540 (515,000) 51,500 1,030,000 37,080 3,200,000 ($629,120) (251,648) ($377,472) 3,200,000 Net cash flow ($10,200,000) $2,297,600 $2,822,528 Cumulative net cash flow (For payback calculation) ($10,200,000) ($7,902,400) ($5,079,872) Profitability and Breakeven Measures: Net present value (NPV) Internal rate of return (IRR) Modified IRR (MIRR) Payback $875,020 12.9% 11.8% 4.1 Page 2 CASE19 yright 2014 Health ministration Press Page 3 CASE19 3 4 5 $4,243,600 973,906 (530,450) 53,045 1,060,900 38,192 1,900,000 $748,006 299,203 $448,804 1,900,000 $4,370,908 1,003,123 (546,364) 54,636 1,092,727 39,338 1,200,000 $1,527,447 610,979 $916,468 1,200,000 $4,502,035 1,033,217 (562,754) 56,275 1,125,509 40,518 1,100,000 $1,709,270 683,708 $1,025,562 1,100,000 180,000 3,240,000 $2,348,804 $2,116,468 $5,545,562 ($2,731,068) ($614,600) $4,930,962 END Page 4