| CASE 20 | | Student Version | | | Copyright 2014 Health Administration Press |
| 8/4/18 |
| | | 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 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. |
| INPUT DATA: | | | | KEY OUTPUT: |
| Land initial cost | | | $0 | NPV | $0 |
| Land opportunity cost (and salvage value) | | | $0 | IRR | ERROR:#NUM! |
| Building/equipment cost | | | $0 | MIRR | ERROR:#NUM! |
| Build/equipment salvage value | | | $0 | Payback | 999.0 |
| Procedures per day | | | 0 |
| Average net patient revenue per procedure | | | $0 |
| Labor costs | | | $0 |
| Utilities costs | | | $0 |
| Incremental overhead | | | $0 |
| Supply cost ($/procedure) | | | $0 |
| Inflation rate on net patient revenue | | | 0.0% |
| Inflation rate on costs | | | 0.0% |
| Tax rate | | | 0.0% |
| Revenues lost from inpatient surgeries | | | $0 |
| Reduction in inpatient surgery costs | | | $0 |
| Cost of capital | | | 0.0% |
| MODEL-GENERATED DATA: |
| Depreciation Schedule: |
| | | MACRS | Deprec. | End of Year |
| | Year | Factor | Expense | Book value |
| | 1 | 0.20 | $0 | $0 |
| | 2 | 0.32 | 0 | 0 |
| | 3 | 0.19 | 0 | 0 |
| | 4 | 0.12 | 0 | 0 |
| | 5 | 0.11 | 0 | 0 |
| | 6 | 0.06 | 0 | 0 |
| Net Cash Flows: |
| | | | Project Cash Flows |
| | | | 0 | 1 | 2 | 3 | 4 | 5 |
| Land opportunity cost | | | $0 |
| Building/equipment cost | | | 0 |
| Net patient revenue (including inpatient loss) | | | | $0 | $0 | $0 | $0 | $0 |
| Less: Labor costs | | | | 0 | 0 | 0 | 0 | 0 |
| Cost savings on inpatients | | | | 0 | 0 | 0 | 0 | 0 |
| Utilities costs | | | | 0 | 0 | 0 | 0 | 0 |
| Supplies | | | | 0 | 0 | 0 | 0 | 0 |
| Incremental overhead | | | | 0 | 0 | 0 | 0 | 0 |
| Depreciation | | | | 0 | 0 | 0 | 0 | 0 |
| Income before taxes | | | | $0 | $0 | $0 | $0 | $0 |
| Taxes | | | | 0 | 0 | 0 | 0 | 0 |
| Project net income | | | | $0 | $0 | $0 | $0 | $0 |
| Plus: Depreciation | | | | 0 | 0 | 0 | 0 | 0 |
| Plus: Net land salvage value | | | | | | | | 0 |
| Plus: Net building/equipment salvage value | | | | | | | | 0 |
| Net cash flow | | | $0 | $0 | $0 | $0 | $0 | $0 |
| Cumulative net cash flow | | | $0 | $0 | $0 | $0 | $0 | $0 |
| (For payback calculation) |
| Profitability and Breakeven Measures: |
| Net present value (NPV) | | | | $0 |
| Internal rate of return (IRR) | | | | ERROR:#NUM! |
| Modified IRR (MIRR) | | | | ERROR:#NUM! |
| Payback | | | | 999.0 |
| | | | | | | | | END |
eloez.
4.excelsheet.xlsx
