Question

Please use Excel and include all formulas in different cells so that it is clear

H A B C D E F G 1 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 4 Year 1 Year 2 Year 3 Year 4 Year 5 8 Total projected Visits 52500 9 Average Revenue per visit 75.00 10 Average Variable Cost per Visit $ 50.00 11 Total Fixed Costs $ 500,000.00 12 Purchase Price for Equipment $ 4,500,000.00 13 Monthly Rental Cost to Occupy the New Site $ 5,000.00 14 Salvage Values of the Equipment (end of Year 5) $ 750,000.00 15 Corporate Tax Rate 40% 16 Cost of Capital 17 18 Other Assumptions 19 1) Projected Visits are Expected to increase by 10% in Year 2,5% in Year 3 and 3% each Year thereafter 20 2) Negotiation with payers indicate that revenue rate (ie payment per visits) will increase by 2% each year and 5% in year 5 21 3) Variable Costs are expected to rise at a rate of 2% per year 22 4) Fixed Costs are expected to rise at a rate of 1% per year 23 6) Rent rates will be increased by 2.5% at the end of each year 24 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 25 7) Tax rate will remain constant for the entire 5-year Period and do not assume any tax loss carryforward 8%

Answer 1

Based upon the assumptions the projected numbers are calculated as shown below:

F3 D E F Н - J K L M N a 7 8 52500 Year o 9 $ $ 10 Total projected visits Average Revenue per visit Average Variable costs per visit Total Fixed Cost Purchse price for Equipment Monthly rental cost to occupy the new site Salvage Values of the equipment (end of year 5) Corporate tax rate Cost of capital 75 50 5,00,000 45,00,000 5,000 7,50,000 40% 8% Year 1 52500 $ 75 $ $ 50 $ $ 5,00,000 $ $ 60,000 $ $ $ Year 2 Year 3 57750 60637.5 77 $ 78 $ 51 $ 52 $ 5,05,000 $ 5,10,050 $ 61,500.0 $ 63,037.5 $ Total projected visits Average Revenue per visit Average Variable costs per visit Total Fixed Costs Yearly rental After-tax Salvage Value Depreciation 12 Year 4 Year 5 62456.625 64330.32375 80 $ 83.57 53 $ 54 5,15,151 $ 5,20,302 64,613.4 $ 66,228.8 $ 4,50,000 7,50,000 $ 7,50,000 $ $ 13 14 15 16 $ 7,50,000 $ 7,50,000 $ 7,50,000 $ 17 Year o 18 19 20 21 Income Statement Total revenue Total Variable Costs Total Fixed Costs Yearly rental Depreciation Operating Profit Tax (40%) Net Profit Year 1 Year 2 Year 3 Year 4 Year 5 $ 39,37,500 $ 44,17,875 $ 47,31,544 $ 49,70,960 $ 53,76,094 $ 26,25,000 $ 29,45,250 $ 31,54,363 $ 33,13,974 $ 34,81,661 $ 5,00,000 $5,05,000 $ 5,10,050 $ 5,15,151 $ 5,20,302 $ 60,000 $ 61,500 $ 63,038 $ 64,613 $ 66,229 $ 7,50,000 $ 7,50,000 $ 7,50,000 $ 7,50,000 $ 7,50,000 $ 2,500 $ 1,56,125 $ 2,54,094 $ 3,27,223 $ 5,57,902 $ 1,000 $ 62,450 $ 1,01,638 $ 1,30,889 $ 2,23,161 $ 1,500 $ 93,675 $ 1,52,456 $ 1,96,334 $ 3,34,741 22 23 24 25 26

N47 fx D E F G H J K L M N 0 26 27 Year o 28 $ Year 1 Year 2 1,500 $ 93,675 $ 7,50,000 $ 7,50,000 $ Year 3 1,52,456 s 7,50,000 $ Year 4 1,96,334 $ 7,50,000 $ Year 5 3,34,741 7,50,000 29 $ Cash Flows Net Profit Add: Depreciation Less: Purchase of machinery Add: After-tax Cash Flows Net Cash Flows 30 $ -45,00,000 31 $ 9,46,334 $ 4,50,000 15,34,741 32 $ -45,00,000 $ 7,51,500 $ 8,43,675 $ 9,02,456 $ 33 34 35 NPV IRR MIRR 36 37 $ -6,24,349.75 3.11% 2.04% Year 0 Year 1 Year 2 Year 3 Year 4 $ -45,00,000 $ 7,51,500 $ 8,43,675 $ 9,02,456 $ 9,46,334 $ $ -45,00,000 $ -37,48,500 $ -29,04,825 $ -20,02,369 $ -10,56,035 $ 4.69 years 38 Year 5 15,34,741 4,78,706 Net Cash Flows Cumulative Net Cash Flows Payback Period 39 40 41 42 43 44

Formulas are calculated as below:

N47 G H J K L M N 7 8 Year o Year 1 ear 1 9 =F8 =F9 10 Year 2 = 19*(1+10%) =J10*(1+2%) =J11*(1+2%) =)12*(1+1%) =J13*(1+2.5%) 11 =F10 Total projected visits Average Revenue per visit Average Variable costs per visit Total Fixed Costs Yearly rental After-tax Salvage Value Depreciation Year 3 Year 4 Year 5 =K9*(1+5%) =L9*(1+3%) =M9*(1+3%) =K10*(1+2%) =L10*(1+2%) =M10*(1+5%) =K11*(1+2%) =L11*(1+2%) =M11*(1+2%) =K12*(1+1%) =L12*(1+1%) =M12*(1+1%) =K13*(1+2.5%) =L13*(1+2.5%) =M13*(1+2.5%) =F14*(1-F15) = ($F$12-$F$14)/5 = ($F$12-$F$14)/5 =($F$12-$F$14)/5 12 =F11 =F13*12 13 14 15 =($F$12-$F$14)/5 =($F$12-$F$14)/5 16 Year o Year 3 Year 4 17 18 19 Year 1 =J10*19 =J11*19 Year 5 =N10*N9 =L10*L9 =M10*M9 =L11*L9 =M11*M9 =N11*N9 =J12 20 21 22 Income Statement Total revenue Total Variable Costs Total Fixed Costs Yearly rental Depreciation Operating Profit Tax (40%) Net Profit Year 2 =K10*K9 =K11*K9 =K12 =K13 =K15 =K18-SUM(K19:K22) =K23*40% =K23-K24 =J13 =J15 = J18-SUM(J19:J22) =J23*40% =J23-J24 =L12 =M12 =N12 =L13 =M13 =N13 =L15 =M15 =N15 =L18-SUM(L19:L22) =M18-SUM(M19:M2 =N18-SUM(N19:N22 =L23*40% =M23*40% =N23*40% =L23-L24 =M23-M24 =N23-N24 23 24 25 26

Function Library Derined Names Formula Auditing Calcu N48 X - J K L N Year 5 27 Year o Year 1 Year 2 Year 3 M Year 4 =M25 =M22 28 Н Cash Flows Net Profit Add: Depreciation Less: Purchase of machinery Add: After-tax Cash Flows Net Cash Flows =J25 =J22 =K25 =K22 =L25 =L22 =N25 =N22 29 30 =-F12 31 =N14 =SUM(N28:N31) 32 =SUM(128:131) =SUM(128:131) =SUM(K28:K31) =SUM(L28:131) =SUM(M28:M31) 33 34 35 NPV IRR MIRR 36 37 =NPV(8%,J32:N32)+132 =IRR(132:N32) =MIRR(132:N32,F16,0) Year 0 =132 =138 =4+(-M39)/N38 Year 2 Year 3 Year 4 Year 5 =K32 Year 1 =J32 =139+J38 =M32 38 39 =N32 Net Cash Flows Cumulative Net Cash Flows Payback Period =L32 =K39+L38 =J39+K38 =L39+M38 =M39+N38 40 years 41 42