Question

Northeast Hospital is analyzing a potential project for a new outpatient center

Please use the following facts to create a 5-year projection of cash flow for the proposed center. Please create your full income statement first to include all cash and non-cash expenses
Calculate the projects NPV, IRR, MIRR, Payback Period (not discounted). Using these calculations, do you recommend that they should proceed with this project? Explain your answer


Year 1 Year 2 Year 3 Year 4 Year 5
Total projected Visits 52500
Average Revenue per visit $75.00
Average Variable Cost per Visit $50.00
Total Fixed Costs $500,000.00
Purchase Price for Equipment $4,500,000.00
Monthly Rental Cost to Occupy the New Site $5,000.00
Salvage Values of the Equipment (end of Year 5) $750,000.00
Corporate Tax Rate 40%
Cost of Capital 8%

Other Assumptions
1) Projected Visits are Expected to increase by 10% in Year 2, 5% in Year 3 and 3% each Year thereafter
2) Negotiation with payers indicate that revenue rate (ie payment per visits) will increase by 2% each year and 5% in year 5
3) Variable Costs are expected to rise at a rate of 2% per year
4) Fixed Costs are expected to rise at a rate of 1% per year
6) Rent rates will be increased by 2.5% at the end of each year
6) The equipment will depreciate based on the straight-line method of depreciation, a 5-year estimated life and the equipment will be sold at salvage value at the end of year 5
7) Tax rate will remain constant for the entire 5-year Period and do not assume any tax loss carryforward

Answer 1

brazil 2 - Microsoft Excel Page Layout Formulas Data Review View Home Insert * Cut La Copy Paste Format Painter Clipboard E DA 3 AutoSum" A Conditional Format Formatting as Table Styles Cell Styles Insert Delete Format 2 Clear Sort & Filter Editing Find & Select Cells F38 K L M N O P Q R S T A B 2017 28.55744 Calibri - 11 - A A = = = Wrap Text Percentage BI U - ->-A Merge & Center Font Alignment 0 Number fic IRR(E34:34) IDE G H I J rent expense 5000 5125 5253.125 5384.453 5519.064 depreciation expense 750000 750000 750000 750000 750000 operating profit 45000 198475 296857.5 370671 448435.5 less taxes-40% 18000 79390 118743 148268.4 179374.2 after tax profit 27000 119085 178114.5 222402.6 269061.3 add depreciation 750000 750000 750000 750000 750000 add scrap value of equipment 750000 net opera - 4500000 777000 869085 928114.5 972402.6 1769061 present v 1 0.925926 0.857339 0.793832 0.73503 0.680583 present v -4500000 719444.4 745100.3 736767.2 714744.9 1203993 NPV =sum -379949.7418 IRR =Using IRR function in MS excel IRR(E34:J34) 5.11% MIRR =Using MIRR function in MS MIRR(E34:34, excel 8%,8%) 6.11% 38 40 A1

O x Campo- D *- AutoSum" AT A Eg Copy Cell Styles Insert Delete Format 2 Clear Sort & Filter Editing Find & Select Cells F39 N O P Q R S T brazil 2 - Microsoft Excel Home Insert Page Layout Formulas Data Review View * Cut Calibri 11 - A A Wrap Text Percentage Paste Format Painter B I U P A E Merge & Center - $ % , ,00 Conditional Format Formatting as Table Clipboard Font Alignment Number Styles Fax =MIRR(E34:134,8%,8%) A B C | IDE F G H I J K L M 2017 28.55744 rent expense 5000 5125 5253.125 5384.453 5519.064 depreciation expense 750000 750000 750000 750000 750000 operating profit 45000 198475 296857.5 370671 448435.5 less taxes-40% 18000 79390 118743 148268.4 179374.2 after tax profit 27000 119085 178114.5 222402.6 269061.3 add depreciation 750000 750000 750000 750000 750000 add scrap value of equipment 750000 net opera - 4500000 777000 869085 928114.5 972402.6 1769061 present ve 1 0.925926 0.857339 0.793832 0.73503 0.680583 present vé - 4500000 719444.4 745100.3 736767.2 714744.9 1203993 NPV =sum -379949.7418 IRR =Using IRR function in MS excel IRR(E34:34) 5.11% MIRR =Using MIRR function in MS MIRR(E34:34, excel 8%,8%) 6.11% 38

Year	1	2	3	4	5
projected visits	52000	52000*1.1	57200*1.05	60060*1.03	61862*1.03
projected visits	52000	57200	60060	61861.8	63717.86
revenue per visit	75	75*1.02	75*1.02^2	75*1.02^3	75*1.02^4
revenue per visit	75	76.5	78.03	79.5906	81.18241
Variable cost per visit	50	50*1.02^1	50*1.02^2	50*1.02^3	50*1.02^4
Variable cost per visit	50	51	52.02	53.0604	54.12161
Fixed cost	500000	500000*1.01^1	500000*1.01^2	500000*1.01^3	500000*1.01^4
Fixed cost	500000	505000	510050	515150.5	520302
rent expense	5000	5000*1.025^1	5000*1.025^2	5000*1.025^3	5000*1.025^4
rent expense	5000	5125	5253.125	5384.453	5519.064
annual depreciation	(4500000-750000)/5	750000
annual depreciation	750000	750000	750000	750000	750000
Year	0	1	2	3	4	5
cost of equipments	-4500000
revenue = visits*revenue per visit		3900000	4375800	4686482	4923618	5172770
variable cost =visits*variable cost per visit		2600000	2917200	3124321	3282412	3448513
total fixed cost		500000	505000	510050	515150.5	520302
rent expense		5000	5125	5253.125	5384.453	5519.064
depreciation expense		750000	750000	750000	750000	750000
operating profit		45000	198475	296857.5	370671	448435.5
less taxes-40%		18000	79390	118743	148268.4	179374.2
after tax profit		27000	119085	178114.5	222402.6	269061.3
add depreciation		750000	750000	750000	750000	750000
add scrap value of equipment						750000
net operating cash flow	-4500000	777000	869085	928114.5	972402.6	1769061
present value factor at 8% =1/(1+r)^n r =8%	1	0.925926	0.857339	0.793832	0.73503	0.680583
present value of cash flow = cash flow*PVF	-4500000	719444.4	745100.3	736767.2	714744.9	1203993
NPV =sum of present value of cash flow	-379949.7418
IRR =Using IRR function in MS excel	IRR(E34:J34)	5.11%
MIRR =Using MIRR function in MS excel	MIRR(E34:J34,8%,8%)	6.11%
No project should not be accepted as it results in negative NPV and an IRR and MIRR less than minimum required rate of return of 8%