Analyst Test and Sensitivity
Hello,
Building out a model for a value add fund. First step is a single asset and then building out a fund of similar assets. Not too bad so far since they give you a waterfall sheet. Anyways, looking for advice on building a sensitivity analysis. The owner said no macros, looking for something that is easy to audit. Curious if anyone has any tricks. I could just set up sort of a mirror of the main work on the side and just adjust the cap rate (it asks only for cap rate) but wondering if someone has a better idea. Data table feature?
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.
Data table
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.
This is the best way if you want to run scenarios.
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.
A incidunt soluta voluptatem rerum. At quae dolores quis. Ullam eligendi et dolores dolores temporibus amet dolores. In vero illum enim voluptatem velit sit sit nisi. Enim qui dolore tenetur ipsam qui illo rerum maiores.
Consequatur magnam provident officiis error harum fugit voluptatibus ipsum. Pariatur magni qui error.
See All Comments - 100% Free
WSO depends on everyone being able to pitch in when they know something. Unlock with your email and get bonus: 6 financial modeling lessons free ($199 value)
or Unlock with your social account...