The managerial board at a Hospital is thinking to plan a capital growth project. To analyze their decision, they need to forecast the profitability of the project over the next three years. The estimated number of patients in the first year is 1500 patients with an expected growth rate of 8% per year. Based on the current reimbursement, it is estimated that each patient provides an average billing of $150,000, which will grow by 3% each year. However, the hospital can only collect 35% of billings. The variable cost of supplies and drugs is estimated to be 12% of billings, and the fixed cost for salaries and utilities is estimated to be $20,000,000 in the first year. Both variable and fixed costs are estimated to increase by 6% per year. 

a) Use excel to develop a spreadsheet model to calculate the net present value (NPV) of profit over a three-year period, assuming a 7% discount rate. 

b) Define and run three reasonable scenarios (e.g. Best case, worst case, most likely case) having a number of patients and the fixed cost in the first year as variables and NPV as output.

Answer rating: 100% (QA)

answer Year 1 2 3 Patient 1500 1620 1750 Billing per patient 15000000 15450000 15913500 To... View the full answer