Sensitivity Analysis
Sensitivity Analysis
Maybe one day you are reviewing a model you underwrote and are wondering what would happen if you changed the cap rate and exit date of the project. You need 5 scenarios all in the next 20 minutes or your deal will be dead. What do you do? The answer is to create a sensitivity analysis.
What is Sensitivity Analysis?
A sensitivity analysis, also known as a what-if analysis, is a technique in Excel used to determine how a set of independent variables will affect a specific dependent variable for a particular set of assumptions. Sensitivity analysis is used quite often in the business world, particularly with economists and financial analysts. It is a decision-making tool that allows you to run different scenarios without having to change every assumption.
Why do we use Sensitivity Analysis?
Instead of changing multiple assumptions to show various situations, we can use a sensitivity analysis to predict the outcome of multiple scenarios at one time while also comparing the new set of assumptions to the original set. A sensitivity analysis can provide multiple benefits for decision-makers.
Sensitivity Analysis in Excel
In Excel, a sensitivity analysis can be utilized by creating a data table. A data table is an array of cells where you can change certain variables and the other cells will populate with different answers depending on the new assumptions. Data tables are not limited to just one output. They allow you to view multiple outputs at any given time depending upon the way the table is built. At Top Shelf Model’s we utilize one variable and two variable data tables regularly in all our models.
One variable data table – a one variable data table allows you to see multiple results at a time by changing one value. Below is an example of a one variable table. The blue font indicates the variable that can be changed in the table. The table shows three scenarios in which a different cap rates impact the exit value, IRR, peak equity, multiple, and capital of a project. You can see how various cap rates affect certain aspects of a deal.
