I would like to get help with Case20, Coral Bay Hospital Traditional project analysis.

book is 5th addition Louis C.Gapenski

Cases in Healthcare Finance, 5th Edition Copyright 2014 Health Administration Press CASE 20 QUESTIONS CORAL BAY HOSPITAL Traditional Project Analysis 1. What are the NPV, IRR, MIRR, and payback of the proposed ambulatory surgery center? Do the measures indicate acceptance or rejection of the proposed ambulatory surgery center? 2. Inflation is one of the most difficult factors to deal with in project analysis. a. Complete the inflation impact table shown in Exhibit 20.2. b. What management information is provided by the inflation impact table? 3. 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? 4. a. Perform a scenario analysis. b. What management information is provided by the scenario analysis? c. Why is the expected NPV obtained in the scenario analysis different from the base case NPV? 5. A board member is interested in the utilization breakeven of the Center. a. What are the breakeven values of the three input variables that are highly uncertain? b. What management information is provided by the breakeven analysis? 6. To help with the risk-incorporation phase of the analysis, Jules consulted with Mark Hauser, the hospital's CFO, about both the risk inherent in the hospital's average project and how the hospital typically adjusts for risk. a. What is the project's differential risk-adjusted NPV? b. Assess the corporate risk of the project. (No calculations are required. Think about correlation of the surgery center and hospital cash flows.) 7. Jules Bergman is aware that there are some qualitative factors that are relevant to the surgery center decision. a. What qualitative factors might support project acceptance? b. What qualitative factors might preclude project acceptance? c. Can you think of any costs that might be associated with the project that have not been included in the analysis? d. Are there any potential benefits that have not been included? e. What additional data would you seek from other hospital staff members to conduct a more thorough analysis? 8. Considering all points, would you build the ambulatory surgery center? 9. In your opinion, what are three key learning points from this case? 12/6/2013 CASE 20 11/16/2015 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 NPV $875,020 $200,000 IRR 12.9% compare to cost of capital $10,000,000 MIRR 11.8% $5,000,000 Payback 4.1 Years to getting profit break even 20 can be tried with 25,and 15 $1,000 $918,000 $50,000 $36,000 $200 3.0% 3.0% tray 6% for different scenario analysis 40.0% $1,000,000 $500,000 10.0% tray 14% and 4%for different scenario analysis MODEL-GENERATED DATA: Depreciation Schedule: Year 1 2 3 4 5 6 MACRS Factor 0.20 0.32 0.19 0.12 0.11 0.06 Deprec. Expense $2,000,000 3,200,000 1,900,000 1,200,000 1,100,000 600,000 End of Year Book value $8,000,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 3 4 5 $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 $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 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 (For payback calculation) ($10,200,000) ($7,902,400) ($5,079,872) ($2,731,068) ($614,600) $4,930,962 Profitability and Breakeven Measures: Net present value (NPV) Internal rate of return (IRR) Modified IRR (MIRR) $875,020 12.9% 11.8% Payback 4.1 END CASE 20 11/16/2015 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 $150,000 Land opportunity cost (and salvage valu $200,000 Building/equipment cost ### Build/equipment salvage value $5,000,000 Procedures per day 20 Average net patient revenue per proced $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% NPV IRR MIRR Paybac $875,020 12.9% compare to cost of capital 11.8% 4.1 Years to getting profit break even MODEL-GENERATED DATA: Depreciation Schedule: Year 1 2 3 4 5 6 MACRS Deprec. End of Year Factor Expense Book value 0.20 $2,000,000 $8,000,000 0.32 3,200,000 4,800,000 0.19 1,900,000 2,900,000 0.12 1,200,000 1,700,000 0.11 1,100,000 600,000 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 ### 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 ### $2,297,600 $2,822,528 $2,348,804 $2,116,468 $5,545,562 Cumulative net cash flow (For payback calculation) ### ### ($5,079,872) ($2,731,068) Profitability and Breakeven Measures: Net present value (NPV) Internal rate of return (IRR) Modified IRR (MIRR) $875,020 12.9% 11.8% ($614,600) $4,930,962 Payback 4.1 END CASE 20 11/21/2015 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 3,200,000 1,900,000 1,200,000 1,100,000 600,000 $875,020 12.9% 11.8% 4.1 End of Year Book value $8,000,000 4,800,000 2,900,000 1,700,000 600,000 0 MODEL-GENERATED DATA: Depreciation Schedule: Year 1 2 3 4 5 6 MACRS Factor 0.20 0.32 0.19 0.12 0.11 0.06 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 3 4 5 $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 $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 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 (For payback calculation) ($10,200,000) ($7,902,400) ($5,079,872) ($2,731,068) ($614,600) $4,930,962 Profitability and Breakeven Measures: Net present value (NPV) Internal rate of return (IRR) Modified IRR (MIRR) $875,020 12.9% 11.8% Payback 4.1 END Level of net patient revenue inflation 0% 1% 2% 3% 4% 5% 6% 0% 724,065 893,501 1,066,160 1,242,091 1,421,339 1,603,952 1% Level of cost inflation 557,808 495,296 661,553 830,988 1,003,648 1,179,578 1,358,826 1,541,440 2% 431,588 597,845 767,280 939,940 1,115,870 1,295,119 1,477,732 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 Impact of uncertain future inflation on NPV of propsed Ambulatory Surgery Center