Please use Excel and also show all the formulas so that it is clear
F A B D E G H M N 2 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 following key indicators 4 Best Most Likely Worst 5 Total Visits 60000 55000 50000 6 Revenue per Visit $ 90.00 $ 75.00 $ 65.00 7 Salvage Value $ 800,000.00 $ 750,000.00 $ 650,000.00 8 Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7 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 $ 5,000.00 15 Salvage Values of the Equipment (end of Year 7) 16 Corporate Tax Rate 40% 17 Cost of Capital 8% 18 19 Other Assumptions will be the same for Best, Most Likely and Worst Case 20 1) Projected Visits are Expected to increase by 10% in Year 2,5% in Year 3 and 3% each Year thereafter 21 2) Negotiation with payers indicate that revenue rate (ie payment per visits) will increase by 2% each year and 5% in year 5 then 2% thereafter 22 3) Variable Costs are expected to rise at a rate of 2% per year 23 4) Fixed Costs are expected to rise at a rate of 1% per year 24 6) Rent rates 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 7 26 7) Tax rate will remain constant for the entire 7-year Period and do not assume any tax loss carryforward 27 F A B D E G H M N 2 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 following key indicators 4 Best Most Likely Worst 5 Total Visits 60000 55000 50000 6 Revenue per Visit $ 90.00 $ 75.00 $ 65.00 7 Salvage Value $ 800,000.00 $ 750,000.00 $ 650,000.00 8 Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7 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 $ 5,000.00 15 Salvage Values of the Equipment (end of Year 7) 16 Corporate Tax Rate 40% 17 Cost of Capital 8% 18 19 Other Assumptions will be the same for Best, Most Likely and Worst Case 20 1) Projected Visits are Expected to increase by 10% in Year 2,5% in Year 3 and 3% each Year thereafter 21 2) Negotiation with payers indicate that revenue rate (ie payment per visits) will increase by 2% each year and 5% in year 5 then 2% thereafter 22 3) Variable Costs are expected to rise at a rate of 2% per year 23 4) Fixed Costs are expected to rise at a rate of 1% per year 24 6) Rent rates 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 7 26 7) Tax rate will remain constant for the entire 7-year Period and do not assume any tax loss carryforward 27
