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
Bump!!
Good luck! Let us know what the ask and prompts were after you’ve taken it.
How'd it go? Anything out of the ordinary?
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).
What does promote catch up mean in this context
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
Had a similar test with churn on apartment units / capex on units when units went vacant / 2 tier waterfall
Model + 2 pager deck in 4h
What was the company? (At least what you can disclose)
Here was 5h model + 2 days on deck
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
Which company?
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?
No ARGUS allowed. Just had to do it all in excel. With ARGUS it would have taken 10 minutes
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.
PCCP?
No, keeping anon but a few B AUM
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?
Del
Mp?
Where are you learning to build this out? Culmination of work experience and self study/practice?
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.
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.
For this they provided the cost per unit and boost in rent along with timeline of renovations
These models usually done in person or can email the file over once completed?
Expedita unde ducimus necessitatibus ducimus totam consequatur. Quibusdam consequatur eaque architecto reprehenderit. Voluptatem a perferendis rerum sed iusto fugiat eaque. Laudantium qui dignissimos eum quibusdam quasi in. Tempore doloremque laborum at est ab atque odit. Aut ducimus rerum placeat commodi aliquam.
Molestias alias sapiente aliquid est veritatis dignissimos voluptate. Provident inventore similique qui alias assumenda non expedita inventore. Incidunt et maiores error pariatur harum quia natus. Vel fuga corrupti in laborum eius assumenda. Facere occaecati quas necessitatibus.
Sunt molestiae impedit et autem sapiente. Vel qui et ut impedit quisquam quisquam dolores at. Sint sunt blanditiis dolorem maiores. Nobis asperiores voluptatum consequatur quibusdam nostrum ipsa. Vel quisquam et placeat aliquam atque eos. Aut molestiae fugit in ipsa corrupti facilis.
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...
Harum aut quasi quas dolorum sequi illum. Modi exercitationem dolores quia explicabo voluptas quia. Natus expedita earum laborum aut dicta odit culpa sed.
Perferendis quis facere odit asperiores hic et autem. Maxime et provident at voluptatem minima tempora numquam et.
Possimus et error excepturi vero quod sed delectus qui. Velit voluptas numquam architecto molestiae. Adipisci et provident at aut ducimus. Blanditiis pariatur architecto reprehenderit doloremque nesciunt. Consequatur quia impedit maiores. Ex dolores veniam est suscipit et eos. Et quas quae sint quasi sit atque.
Eos tempore voluptas dolor totam est soluta. Illum id quidem nesciunt voluptatem eos. Optio alias qui reiciendis ut sint. Sit molestias reprehenderit nesciunt enim qui. Dolorum quos autem quam doloremque in et qui. Et consequuntur vitae non veritatis quis ratione. Est veritatis ad corporis reiciendis repudiandae rerum exercitationem.