The Scenario Analysis tool is a great way to quickly assess the impact of variations in key Assumptions on your project profitability (often called sensitivity analysis or scenario analysis).
Our spreadsheet offers scenarios built around 4 Assumptions: Purchase Price, After-Repair Value, Repair Estimate and Holding Period. Profitability impact is estimated based on 4 Financial Ratios: Project Profit ($ amount), Return on Investment (ROI), Annualized ROI and Internal Rate of Return (IRR).
With this tool, it becomes very easy to assess how your project profit, ROI or IRR is impacted by a change in your purchase price or a delay in your rehab project for instance. When such analysis is performed, all other parameters in your model are assumed constant.
To run the analysis, you need to input 2 parameters for each of the 2 assumptions displayed in the table (x- and y-axes):
Once your parameters have been set, you can run the analysis by pressing F9 on your keyboard. This will populate or refresh all tables.
Important: Your parameters’ values must be plugged and cannot be linked directly to another sheet of this workbook (like the Dashboard) or it will create circularities which will prevent Excel from running the analysis properly.
Please also note that your Repair Estimate Method in the Dashboard must be set as “Lump Sum” in order for the Holding Period / Repair Estimate Scenario analysis to run properly.
The exemple below shows how decreasing or increasing you ARV by $10,000 impacts your project profit, ROI, annualized ROI and IRR. We set the mid-range at $610,000, which is our expected After-Repair Value but you can set that parameter to any desired value. Each of the below tables also shows the impact on profitability of a $5,000 variation in Purchase Price (with a mid-value of $400,000)
How to read results:
Terms & Conditions
© 2021 RealEstateSpreadsheets.com – All rights reserved.