Analyst Test and Sensitivity

Tbh, don't really get what the confusion is about.

• I'd compare Cap Rate to Equity Multiple; to Rent Assumptions; possibly Capex/TI

• Standard 5 x 5 with basic assumption in the middle.

Do a 2 variable data table displaying the different IRRs achievable, by changing acquisition prices and exit cap rates.

Since each market will have its own cap rate, growth rates etc, build in a global assumptions sheet with a ratchet on sensitivity. Eg cap rate ratchet of +25bps

Asset A has a cap rate of 4% Asset B has a cap rate of 7%

You cant sensitise a blended cap rate as that would make no sense. You could have however build your sensitivity against a bps shift per market.

I'm assuming that's the question you were asking as opposed to how to actually build a sensitivity table?

It was more how to best set it up. A preferred excel function. I know what variables to play with.

1) Have an operating and valuaiton drivers tab. Run an offset function for each line item that pulls from a toggle on another tab then link your pro-formas to the drivers tabs so that you can run multiple scenarios.

Should look something like this: The cell being referenced is the same cell you're entering the formula in (X11), the row offset is the cell containing the case toggle number on the "Controls and Summary" tab, there's no column offset.

2) Manually constructed data tables for every return metric other than IRR. Just pick the two biggest drivers and do the math to manually make a dynamic sensitivity table showing a range of outputs. Doing it manually makes the model run faster and, in my opinion, is generally more malleable than inserting a data table.

Would recommend you not doing an actual 'data table' by way of using the table function embedded in Excel. This confuses a lot of people who aren't very familiar with it. If he wants something easy to audit/without macros don't know if you can make the assumption he will understand data tables.

Easier way to do it is just to have a table set up with toggles for not only drivers/variables as mentioned above, but also with toggles for the increments you want to incorporate into the sensitivity (i.e., toggle whether it's driving off of pricing, cap rate, IRR). You can use if statements in combination of conditional formatting for this. You can also

I would recommend run purchase price against exit date/exit cap. And in your data table, it'll show "IRR/EM". It'll make things look more simple.

Thanks everyone. Went with data table. Did month sold and various cap rates for IRR and EM.