## Places Of Interest

### Sensitivity Data Table Usage in Financial Models

If you deal with financial models, use them, or build them, one of the most interesting things you can do is data table creation through Excel's Data ribbon under 'what-if' analysis. It can be done with all sorts of assumptions and it is one of the reasons why it is so important to have all inputs called out in their own cells rather than trying to hard code in numbers into formulas. For example, if your expense growth rate is 0.03 and you just put that number into the formula rather than putting it into an input cell and referencing it with a formula, then you can't run a sensitivity analysis on expense growth.

Relevant Template:

In the model linked aboved, I built a sensitivity table that shows the resulting IRR if the occupancy changed, hold time changed, and if the exit cap rate changed. In order to properly allow Excel to do this, the occupancy rate, exit cap rate, and exit cap rate all must be input cells. The output (IRR) can then be sensitized so you can see how it changes without building multiple versions of the same model.

This is so cool because Excel will run the model for each change in inputs and show how a resulting output formula result changes if the inputs were to change.

It gets a bit tricky if you have never done this before. The formatting and style in which to run the data table is not super intuitive and it even took me a few attempts before I got it down. Once you understand it, the uses are broad and powerful. If you have any financial model that includes inputs and you want to see how the IRR changes (or ROI, cash-on-cash, margin, or any output calculation) when a certain input or multiple inputs were to be a different value, this is the best way to do it.

The problem people run into is you have a beautiful robust template that has all sorts of inputs and calculations. Then, you want to see what happens when various assumptions change and you want to see side-by-side results without having to duplicate all the calculations 6 or 7 times. Use Excel data tables to do this.

Another metric that you can use this analysis tool for is a business valuation (DCF or EBITDA multiple or whatever business valuation methodology you want to use. It because very easy to see how all kinds of assumptions effect the end valuation calculation. You could run it on:

• Price
• Volume
• Interest Rates
• Growth Rates
• Occupancy (real estate)
• Rent
• Retention (big one for SaaS modeling)
• Cost of Goods Sold Percentage or Cost per Unit