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?

 

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?

 

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.

I come from down in the valley, where mister when you're young, they bring you up to do like your daddy done
 
Most Helpful
BBA18:
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

"Who am I? I'm the guy that does his job. You must be the other guy."
 

Praesentium aspernatur sed necessitatibus quia recusandae fugiat. Reprehenderit est sed provident aliquam.

Quo iusto sed et eius placeat quia consequatur. Quia nam consequatur mollitia ut occaecati. Voluptas iure non sed. Recusandae sit sint in in et eius possimus. Exercitationem et mollitia reiciendis excepturi aspernatur voluptatem consequatur.

Career Advancement Opportunities

March 2024 Investment Banking

  • Jefferies & Company 02 99.4%
  • Goldman Sachs 19 98.8%
  • Harris Williams & Co. (++) 98.3%
  • Lazard Freres 02 97.7%
  • JPMorgan Chase 03 97.1%

Overall Employee Satisfaction

March 2024 Investment Banking

  • Harris Williams & Co. 18 99.4%
  • JPMorgan Chase 10 98.8%
  • Lazard Freres 05 98.3%
  • Morgan Stanley 07 97.7%
  • William Blair 03 97.1%

Professional Growth Opportunities

March 2024 Investment Banking

  • Lazard Freres 01 99.4%
  • Jefferies & Company 02 98.8%
  • Goldman Sachs 17 98.3%
  • Moelis & Company 07 97.7%
  • JPMorgan Chase 05 97.1%

Total Avg Compensation

March 2024 Investment Banking

  • Director/MD (5) $648
  • Vice President (19) $385
  • Associates (86) $261
  • 3rd+ Year Analyst (13) $181
  • Intern/Summer Associate (33) $170
  • 2nd Year Analyst (66) $168
  • 1st Year Analyst (202) $159
  • Intern/Summer Analyst (144) $101
notes
16 IB Interviews Notes

“... there’s no excuse to not take advantage of the resources out there available to you. Best value for your $ are the...”

Leaderboard

1
redever's picture
redever
99.2
2
Secyh62's picture
Secyh62
99.0
3
BankonBanking's picture
BankonBanking
99.0
4
Betsy Massar's picture
Betsy Massar
99.0
5
kanon's picture
kanon
98.9
6
dosk17's picture
dosk17
98.9
7
GameTheory's picture
GameTheory
98.9
8
CompBanker's picture
CompBanker
98.9
9
DrApeman's picture
DrApeman
98.9
10
Jamoldo's picture
Jamoldo
98.8
success
From 10 rejections to 1 dream investment banking internship

“... I believe it was the single biggest reason why I ended up with an offer...”