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
- Users added per month
- Inventory Purchasing Frequency
- Inflation Rates
- Depreciation (useful life input)
The only trick is that the tab that you make the sensitivity table on must be the same tab that all the relevant inputs are on. It is ok that the other calculations may be happening on other tabs, but the inputs you are sensitizing must be on the same tab as where you make the table.
You will see me utilizing these data tables in more of the templates I build going forward as well as integrating them into some of the more popular models I've done. In nearly every single bottom-up financial model I built, the minimum equity required (after startup costs and burn are accounted for) is solved for. This would also be an awesome result to see sensitized with key variables as it would give a range of expected investment needed. Running the same variables on IRR would also then give the resulting change in expected returns on the same apples-to-apples comparison.
Article found in Accounting and Finance.
