Question

The managerial board at a Hospital are thinking to plan a capital growth project. To analyse 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 the expected growth rate of 8% per year. Based on the current reimbursement, it is estimated that each patient provides and 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 cost 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. a) Define and run three reasonable scenarios (e.g. Best case, Worst case, most likely case) having number of patients and the fixed cost in the first year as variables and NPV as output.

Answer 1

Year	1	2	3
Patient	1500	1620	1750
Billing per patient	1,50,000.00	1,54,500.00	1,59,135.00
Total Billing Amount	22,50,00,000.00	25,02,90,000.00	27,84,22,596.00
Collection by hospital	7,87,50,000.00	8,76,01,500.00	9,74,47,908.60
Drugs cost	2,70,00,000.00	2,86,20,000.00	3,03,37,200.00
Salaries	2,00,00,000.00	2,12,00,000.00	2,24,72,000.00
Total Cost	4,70,00,000.00	4,98,20,000.00	5,28,09,200.00
Total Cash flow	3,17,50,000.00	3,77,81,500.00	4,46,38,708.60
	0.9346	0.8734	0.8163
Discounted cash flow	2,96,72,897.20	3,29,99,825.31	3,64,38,483.06