Hardest modeling test / case study

Hi everyone,


Simple thread here that could be useful for most (juniors, job seekers...) 


Based on modeling tests you've worked on, what's the most difficult things you've faced?


Several debt tranches? / Multi-tiers promote / Rent roll with convoluted break/renewal options? / timing?


Facing a 4-hour one tomorrow for a very nice opportunity and feel like I've came across everything regarding modeling test shenanigans but always discovering new surprises...


Happy to hear about your experiences and perhaps let's build a thread where we break down these difficulties!

Thanks

 

Went well I believe, among the hardest I came across:

Acquisition of an existing building with an access to data room

Mandatory defensive Capex to charge ERV vs. Contracted Rent once achieved 

Then Value-add Capex to be modelled but only switched "on" if making sense from a return perspective (they would trigger an ERV increase)

Waterfall with catch-ups 

 

This does not sound standard at all even at the associate level - what kind of role was this for? Sounds like a very high bar for a modelling test

 

Agreed. Sadly I don't have the kind of CV that will be considered by big names that send "easy" modelling tests...

It's a small shop, newly founded, already very well capitalized by huge LPs. Thing is as you're supposed to report directly to senior management I suspect they want top-notch modelling & presentation skills (basically IC is you and the heads).

 
Most Helpful

Let's imagine you've got your waterfall in place whatever the hurdles / pref return levels / promote...

Simple example (Call it percentage of distribution total rather than promote on invested capital for the ease of that exercise): 

Tier 1: LP gets everything until it makes 8%

Tier 2: Between 8% and 12% -> LP=85% / GP=15%

Tier 3: After 12% -> LP=80% / GP=20%

Here you can have a catch-up after each tiers / some tiers / one tier / nothing -> basically GP will get X% of what the LP has made at this stage in total or during previous tier

Tier 1: LP is making £100 GP is making £0

Catch-up mechanism "100% to GP until distributions to the GP equals 15% of all distributions made pursuant to tier 1"

GP is getting 15% of £100=£15

Then Tier 2

Hope that's clear 

 

Recently had to do one for a large industrial owner that used probably every possible ARGUS input and requested that you build a DCF (no debt assumptions, solving to unlevered IRR).

Pain in the ass having to build out a cash flow with existing tenant roll/renewal probability and then layer all the MLA's and escalation/inflations. Was expecting it to be far simpler. Every REPE multi case I've come across has been easier IMO

 
CRE Anon

Pain in the ass having to build out a cash flow with existing tenant roll/renewal probability and then layer all the MLA's and escalation/inflations.

I think I'm missing something, but isn't this standard?
Input everything into Argus, then build out the cash flow from the Argus output?

Or did you have to link all the MLAs and probabilities within the Excel itself via formulas?

 

Did one just recently for a MM PERE fund:

- Acquisition of commercial asset with multiple tenants, difference size units (I.e. different market rents and comps),different rent types, lease rollover, etc.

- Multiple financing options and you had to pick best option amongst them. Options included multiple tranches of debt, variable rate debt, with hedging in there too

- Multi-tiered waterfall

- PPT Investment Memo

TBH, quite a difficult model to build from scratch. They gave 48 hours for the whole thing.

 

Can you share a little bit on how you approached the debt portion of the case study? How’d you choose what type of debt and how did you model it?

 

Had to do one recently where the test itself wasn’t too hard, they just required only 90 min time for it. Full model including value-add renovation plan, debt financing, 3-tier equity waterfall. Again not hard but was a lot of formulas to write in 90 minutes to make a dynamic model. This was for AM role too. 

 
laffer

For modeling value add deals in an interview, and anyone who's had to do this please chime in, did they expect you to do cost estimation for the value add plan? Or did they give you a development budget to use as an input?

It's usually a mix of 50/50 and those who don't provide it are probably testing how experienced you are. With enough deal exposure you'll know rough costs. 

 

Ea nostrum necessitatibus quae et dolor. Necessitatibus quod repellendus quasi consectetur. Quasi non id voluptatibus rem tempora modi autem. Explicabo modi quis consectetur velit qui.

Eos aperiam sunt et perspiciatis qui nisi molestiae. Nam officia et sapiente nisi aliquid quia architecto minus.

Ea assumenda aut dolor maxime consequatur. Quo quia cumque omnis quia sit. Enim deleniti amet dolores pariatur quos consequuntur repellat. Neque numquam asperiores eos excepturi culpa non ut. Excepturi blanditiis adipisci aut repellat rerum quas non quae.

 

Hic magnam consequuntur explicabo voluptatem ducimus optio. Dolor aut qui rem et ut facere quod quisquam. Aperiam impedit quo sit hic adipisci ut sit ad. Libero ullam sit fuga et voluptatem iusto.

Omnis nobis recusandae nobis laudantium aut voluptas. Sit quaerat molestiae perspiciatis velit vel aliquid eos. Ea repudiandae ullam explicabo totam cumque aliquid. Qui consequatur voluptatem dolor a voluptas consequatur deserunt sit. Et corrupti quia velit ut ullam voluptas rerum. Dolore quia dolorem natus accusantium ut veniam.

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 04 97.1%

Overall Employee Satisfaction

March 2024 Investment Banking

  • Harris Williams & Co. 18 99.4%
  • JPMorgan Chase 11 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 06 97.7%
  • Lincoln International 04 97.1%

Total Avg Compensation

March 2024 Investment Banking

  • Director/MD (5) $648
  • Vice President (19) $385
  • Associates (81) $263
  • 3rd+ Year Analyst (12) $184
  • Intern/Summer Associate (32) $172
  • 2nd Year Analyst (60) $169
  • 1st Year Analyst (193) $159
  • Intern/Summer Analyst (142) $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
BankonBanking's picture
BankonBanking
99.0
3
Betsy Massar's picture
Betsy Massar
99.0
4
Secyh62's picture
Secyh62
99.0
5
GameTheory's picture
GameTheory
98.9
6
kanon's picture
kanon
98.9
7
CompBanker's picture
CompBanker
98.9
8
dosk17's picture
dosk17
98.9
9
Jamoldo's picture
Jamoldo
98.8
10
Linda Abraham's picture
Linda Abraham
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...”