Question

AJ BC Northeast Hospital is analyzing a potential project for a new outpatient center K L M 3 Create a Sensitivity Analysis of your Income Statement, Cashflow, Resulting NPV, IRR and MIRR assuming the following Best Case, Most Likely Case and Worst Case usinf the followin Most Likely Worst | Total Visits 60000 55000 50000 Revenue per Visit $ 90.00 $ 75.00 $ 65.00 Salvage Value $ 800,000.00 $750,000.00 $ 650,000.00 Year 3 Year 4 9 Total projected Visits 10 Average Revenue per visit 11 Average Variable Cost per Visit 50.00 12 Total Fixed Costs $ 500,000.00 13 Purchase price for Equipment $ 4,500,000.00 14 Monthly Rental Cost to Occupy the New Site 15 Salvage values of the Equipment (end of Year 7) 16 Corporate Tax Rate 17 Cost of Capital 000.00 18 19 Other Assumptions will be the same for Best, Most Ukely and Worst Case 20 ) Projected Visits are Expected to increase by 10% in Year 2,5% in Year 3 and each year thereafter 21 2) Negotiation with payers indicate that revenue rate (le payment per visits) will increase by each year and 5% in year then thereafter 22 3] Variable costs are expected to rise at a rate of 2% per year 234) Fixed Costs are expected to rise at a rate of 1% per year 24 6) Rentrates will be increased by 2.5% at the end of each year 25 6) The equipment will depreciate based on the straight line method of depreciation, a 7-year estimated life and the equipment will be sold at salvage value at the end of year? e t @ 112820

Answer 1

BEST CASE			Increase over previous year
Total visits		60000	10%	5%	3%	3%	3%	3%
Revenue/visit		90	2%	2%	2%	5%	2%	2%
Variable cost /visit		50	2%	2%	2%	2%	2%	2%
Fixed costs		500000	1%	1%	1%	1%	1%	1%
Rent (5000*12)		60000	2.50%	2.50%	2.50%	2.50%	2.50%	2.50%
Salvage value								800000
Year	0	1	2	3	4	5	6	7
Total projected visits		60000	66000	69300	71379	73520	75726	77998
Revenue per visit		90	91.8	93.64	95.51	100.28	102.29	104.34
1.Total revenues		5400000	6058800	6488975	6817317	7372928	7745998	8137946
Less:Operating expenses:
Variable cost /visit		50	51	52.02	53.06	54.12	55.20	56.31
2.Total variable costs		3000000	3366000	3604986	3787398	3979041	4180380	4391907
3.Total fixed costs		500000	505000	510050	515151	520302	525505	530760
4.Rental costs		60000	61500	63038	64613	66229	67884	69582
5. Depn.(4500000-800000)/7		528571	528571	528571	528571	528571	528571	528571
6.EBT(1-2-3-4-5)		1311429	1597729	1782330	1921583	2278785	2443657	2617126
7.Less: tax at 40%(6*40%)		524571	639091	712932	768633	911514	977463	1046850
8.EAT(6-7)		786857	958637	1069398	1152950	1367271	1466194	1570275
9.Add back:depn.		528571	528571	528571	528571	528571	528571	528571
10.annual opg. cash flow(8+9)		1315429	1487209	1597969	1681521	1895843	1994766	2098847
11.Initial invetsment	-4500000
12 Salvage(BV=SV,no gain/no loss)								800000
13.Net annnual cash flows(10+11+12)	-4500000	1315429	1487209	1597969	1681521	1895843	1994766	2898847
14.PV F at 8%(1/1.08^Yr.n)	1	0.92593	0.85734	0.79383	0.73503	0.68058	0.63017	0.58349
15.PV at 8%(13*14)	-4500000	1217989	1275042	1268520	1235968	1290279	1257041	1691449
16.NPV(Sum of Row 15)	4736288
17.IRR (of row 13)	31%
18.MIRR(of row 13)	20%

MOST LIKELY CASE			Increase over previous year
Total visits		55000	10%	5%	3%	3%	3%	3%
Revenue/visit		75	2%	2%	2%	5%	2%	2%
Variable cost /visit		50	2%	2%	2%	2%	2%	2%
Fixed costs		500000	1%	1%	1%	1%	1%	1%
Rent (5000*12)		60000	2.50%	2.50%	2.50%	2.50%	2.50%	2.50%
Salvage value								750000
Year	0	1	2	3	4	5	6	7
Total projected visits		55000	60500	63525	65430.75	67394	69415	71498
Revenue per visit		75	76.5	78.03	79.59	83.57	85.24	86.95
1.Total revenues		4125000	4628250	4956856	5207673	5632098	5917082	6216486
Less:Operating expenses:
Variable cost /visit		50	51	52.02	53.06	54.12	55.20	56.31
2.Total variable costs		2750000	3085500	3304571	3471782	3647454	3832015	4025915
3.Total fixed costs		500000	505000	510050	515151	520302	525505	530760
4.Rental costs		60000	61500	63038	64613	66229	67884	69582
5. Depn.(4500000-750000)/7		535714	535714	535714	535714	535714	535714	535714
6.EBT(1-2-3-4-5)		279286	440536	543483	620413	862399	955963	1054515
7.Less: tax at 40%(6*40%)		111714	176214	217393	248165	344960	382385	421806
8.EAT(6-7)		167571	264321	326090	372248	517439	573578	632709
9.Add back:depn.		535714	535714	535714	535714	535714	535714	535714
10.annual opg. cash flow(8+9)		703286	800036	861804	907962	1053154	1109292	1168424
11.Initial invetsment	-4500000
12 Salvage(BV=SV,no gain/no loss)								750000
13.Net annnual cash flows(10+11+12)	-4500000	703285.7	800035.7	861804.4	907961.9	1053154	1109292	1918424
14.PV F at 8%(1/1.08^Yr.n)	1	0.92593	0.85734	0.79383	0.73503	0.68058	0.63017	0.58349
15.PV at 8%(13*14)	-4500000	651190.5	685901.7	684128.1	667379.1	716758.7	699042.3	1119382
16.NPV(Sum of Row 15)	723782
17.IRR (of row 13)	12%
18.MIRR(of row 13)	10%

WORST case

6.EBT(1-2-3-4-5)		-360000	-275000	-221841	-182914	-14987	34904	87573
7.Less: tax at 40%(6*40%)		-144000	-110000	-88736	-73166	-5995	13962	35029
8.EAT(6-7)		-216000	-165000	-133105	-109749	-8992	20942	52544
9.Add back:depn.		550000	550000	550000	550000	550000	550000	550000
10.annual opg. cash flow(8+9)		334000	385000	416895	440251	541008	570942	602544
11.Initial invetsment	-4500000
12 Salvage(BV=SV,no gain/no loss)								650000
13.Net annnual cash flows(10+11+12)	-4500000	334000	385000	416895.4	440251.4	541007.5	570942.3	1252544
14.PV F at 8%(1/1.08^Yr.n)	1	0.92593	0.85734	0.79383	0.73503	0.68058	0.63017	0.58349
15.PV at 8%(13*14)	-4500000	309259	330075	330945	323598	368201	359791	730847
16.NPV(Sum of Row 15)	-1747284
17.IRR (of row 13)	-3%
18.MIRR(of row 13)	1%

ANSWERS:	Scenarios
Summary	Best	Most likely	Worst
NPV	4736288	723782	-1747284
IRR	31%	12%	-3%
MIRR	20%	10%	1%