A B C D E F G H K L M N O Create a THREE separate worksheets of income statement, cashflow, Resulting NPV, IRR and MIRR assuming the following Best Case, Most Likely Case and Worst Case usint the following key indicators Best Most Likely Worst Total Visits 68000 63000 57000 Revenue per Visit in 90.00 $ 76.00 $ 68.00 Salvage Value $ 850,000.00 $ 800,000.00 $ 750,000.00 Base Year Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7 9 Total projected Visits 63000 Average Revenue per visit 76.00 1 Average Variable Cost per Visit S 53.00 2 Total Fixed Costs $ 550,000.00 Purchase Price for Equipment $ 4,800,000.00 Monthly Rental Cost to Occupy the New Site $ 5,500.00 Salvage Values of the Equipment (end of Year 7) 6 Corporate Tax Rate 40% Cost of Capital 8.50% 8 Other Assumptions will be the same for Best, Most Likely and Worst Case 10 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% beginning in Year 2-4, 5% in year 5 then 2% thereafter 3) Variable Costs are expected to rise at a rate of 2% per year beginning in Year 2 and each year thereafter 4) Fixed Costs are expected to rise at a rate of 1% per year beginning in Year 2 and each Year thereafter 5) Rent rates will be increased by 2.5% beginning in Year 2 and each year thereafter 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 7 6 7) Tax rate will remain constant for the entire 7-year Period and do not assume any tax loss carryforwardDerived Cash Flow Year 0 Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Purchase Equipment Revenues Variable Unit Costs Fixed Costs Rental Costs Depreciation Total Expenses Income Before Taxes Taxes Profit (Loss) After tax Cash flow Add-back Depreciation Cash flow Salvage Value Total Cashflow per Year Cummulative Cashflow Net Present Value IRR MIRR Payback PeriodCreate a THREE separate worksheets of income statement, cashflow, Resulting NPV, IRR and MIRR assuming the following Best Case, Most Likely Case and Worst Case usinf the following key indicators Best Most Likely Worst Total Visits 68000 63000 57000 Revenue per Visit 90.00 $ 76.00 $ 68.00 Salvage Value $ 850,000.00 $ 800,000.00 $ 750,000.00 Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7 Total projected Visits 68000 Average Revenue per visit 90.00 to to Average Variable Cost per Visit 53.00 Total Fixed Costs $ 550,000.00 Purchase Price for Equipment $ 4,800,000.00 Monthly Rental Cost to Occupy the New Site S 5,500.00 Salvage Values of the Equipment (end of Year 7) Corporate Tax Rate 40% Cost of Capital 8.50% Other Assumptions will be the same for Best, Most Likely and Worst Case 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% beginning in Year 2-4, 5% in year 5 then 2% thereafter 3) Variable Costs are expected to rise at a rate of 2% per year beginning in Year 2 and each year thereafter 4) Fixed Costs are expected to rise at a rate of 1% per year beginning in Year 2 and each Year thereafter 5) Rent rates will be increased by 2.5% beginning in Year 2 and each year thereafter 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 7 Tax rate will remain constant for the entire 7-vear Period and do not assume any tax loss carryforwardDerived Cash Flow Year 0 Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Purchase Equipment Revenues Variable Unit Costs Fixed Costs Rental Costs Depreciation Total Expenses Income Before Taxes Taxes Profit (Loss) After tax Cash flow Add-back Depreciation Cash flow Salvage Value Total Cashflow per Year Cummulative Cashflow Net Present Value IRR MIRRCreate a THREE separate worksheets of income statement, cashflow, Resulting NPV, IRR and MIRR assuming the following Best Case, Most Likely Case and Worst Case usint the following key indicators Best Most Likely Worst Total Visits 68000 63000 57000 Revenue per Visit S 90.00 $ 76.00 $ 68.00 Salvage Value $ 850,000.00 $ 800,000.00 $ 750,000.00 Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7 Total projected Visits 57000 Average Revenue per visit $ 68.00 Average Variable Cost per Visit S 53.00 Total Fixed Costs S 550,000.00 Purchase Price for Equipment $ 4,800,000.00 Monthly Rental Cost to Occupy the New Site S 5,500.00 Salvage Values of the Equipment (end of Year 7) Corporate Tax Rate 40% Cost of Capital 8.50% Other Assumptions will be the same for Best, Most Likely and Worst Case 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% beginning in Year 2-4, 5% in year 5 then 2% thereafter 3) Variable Costs are expected to rise at a rate of 2% per year beginning in Year 2 and each year thereafter 4) Fixed Costs are expected to rise at a rate of 1% per year beginning in Year 2 and each Year thereafter 5) Rent rates will be increased by 2.5% beginning in Year 2 and each year thereafter 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 7 ) Tax rate will remain constant for the entire 7-year Period and do not assume any tax loss carryforwardDerived Cash Flow Year 0 Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7 Purchase Equipment Revenues Variable Unit Costs Fixed Costs Rental Costs Depreciation Total Expenses Income Before Taxes Taxes Profit (Loss) After tax Cash flow Add-back Depreciation Cash flow Salvage Value Total Cashflow per Year Cummulative Cashflow Net Present Value IRR MIRR Payback Period