Description
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):
- Mid-Value: represents the central value of that parameter in the table (i.e. central row for Purchase Price and Holding Period / central column for ARV and Repair Estimate)
- Interval: defines the increment / reduction in value from one row (or column) to another for that particular parameter
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.
Exemple
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:
- Assuming a Purchase Price of $400,000 and an ARV of $610,000 (both being our mid-range values), we can expect a project profit of $41,402 and a ROI of 22.9% (as shown in the center of each table). Should the ARV decrease by $10,000, our projected profit would decrease to $31,402 and our ROI to 17.4%.
- Conversely, an increase to our ARV to $630,000 associated with an increase to our Purchase Price to $410,000 would lead to a project profit of $51,034, a ROI of 27.9%, an annualized ROI of 83.8% (assuming an holding period of 4 months) and an IRR of 111.6%
