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

A$.$ Using the data in the Green Highlighted Area, please complete the Yellow highlighted sections of this worksheet. Must use Excel Formulas to complete each cell

B$.$ Calculate the projects NPV$,$ IRR, MIRR, Payback Period $($not discounted$).$

C$.$ Using these calculations, do you recommend that they should proceed with this project? Explain your answer

Part A$--$Projected Volumes

Projected Volumes, Per Item and Total Costs Data

Prior Year Data Year $1$ Year $2$ Year $3$ Year $4$ Year $5$

Total projected Visits $63000$

Average Revenue per visit $$76\mathrm{.}00$

Average Variable Cost per Visit $$53\mathrm{.}00$

Total Fixed Costs $$550,000\mathrm{.}00$

Purchase Price for Equipment $$4,800,000\mathrm{.}00$

Monthly Rental Cost to Occupy the New Site $$5,500\mathrm{.}00$

Salvage Values of the Equipment $($end of Year $5)$ $$800,000\mathrm{.}00$

Corporate Tax Rate $40\%$

Cost of Capital $8\mathrm{.}50\%$

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\mathrm{.}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

Part A $/$Continued $----$ Income Statement and Cashflow

Income Statement and Cashflow$---$Use yellow highlighted answers from Part A Volumes above to create this Financial Statement$---$Should have formula for each yellow highlighted cell to show your work and cell references

Base Year Year $1$ Year $2$ Year $3$ Year $4$ Year $5$

Equipment Purchase Price

Total Patient Revenues

Total Variable Costs

Total Fixed Costs

Total Rent Costs

Depreciation

TOTAL EXPENSES

Profit Before Taxes

Taxes

Profit After Tax

Addback Depreciation

Salvage Value

Total Cashflow by Year

Cummulative Cashflow

Part B

NPV

IRR

MIRR

Payback Period

Part C

Do you Recommend proceeding with this purchase.....be sure to explain your answer?