Failed Real Estate Acq Assoc Candidate Case Study
I recently sat for a second round interview for the role above with a very large US-based developer's London office. It was shortly followed by the recruiter "expediating the process" by sending me a case study that they have all of their hires do. I returned my product and received radio silence for a week. Upon initiating follow-up, I received feedback from the recruiter stating that "the team felt that the modeling was not quite up to associate level." I'm a recent MBA grad with limited analyst experience, but I felt my math was good. I'm fairly certain I won't get the role now, so will someone please critique the hell out of my submission so I can be better prepared later?
Remove spaces:
Here's the prompt:
www.dropbox.co m/s/ndxl0s6cong2l7a/Test%20Overview.docx?dl=0
Here are the provided cashflows:
www.dropbox.co m/s/qld7r3qwxkraeot/Test%20Cashflows.xlsx?dl=0
Here is my response:
www.dropbox.co m/s/3lkgw99rm4bn5rn/Test%20Submission.xlsx?dl=0
I think all the above are interesting points. With that being said, I think you are very ahead of the curve as far as where other analysts are. In a prior life I worked with an analyst who was fresh out of his MSRE from a top school...thought he know everything...and struggled consistently with any type of waterfall modelling. the format and layout is one thing, but you'd be surprised how many analyst level people don't understand the concepts behind the math they are using.
Definitely echo the point about making sure your test return %'s tie out EXACTLY to the hurdle. I have mine tie out 100% so that if you enter in a cell your test value = input value excel should return TRUE.
Some advice I would have is how you laid out the waterfalls. It did take a bit of reviewing before I finally understood the flow from A-Z. This can ding you hard on these tests, because the grader mentally checks out soon as he had to work to understand the math and results. Instead, I've found that the below logic and illustration resonates well with a 3rd party who doesnt know your model/template from working in it every day. I find that this way allows you to quickly and easily identify when you start promoting, when the pref is paid off, etc...
1) Capital Contributions 2) NCF 3)Distributions 4) Sale Price 5) Total NCF
6) for your PREF... - beginning pref balance (either compounded and accrued or simple) - preferred return $ (i.e. your rate of return * the beg bal) - distributions - ending balance
7) Return of Equity... - beg balance - contirbutions - distirbutions - ending balance
8) IRR/MOC hurdles... - beginning balance - cumulative distribution needed to reach hurdle - actual distribution for that period - ending balance
9) RESIDUAL SPLITS... - simply a line for remaining cash flow, and all investors below that showing their split based on the %.
AGAIN - all in all keep your head up, i think you did good for your level. Let's also remember this is a job interview, and lies are very often told. Could be a million and 1 reasons why they went a different direction -- that's just the reason they gave you...
Unless I'm missing something, I think this is a shitty prompt TBH for several reasons:
As a junior, you'd be more responsible for structuring asset-level deals/promotes, which would 99% of the time involve some skin in the game and PP returns up to the first hurdle. Would very much doubt that they'd be having a new analyst try to model out internal partnership distributions/true CI.
Somewhat related to point above, but I don't know of any firm that routinely does 0% GP money up front. Most LP's want you to have skin in the game. So this is a little bit odd to me the way they structured it.
Surprised that someone would want you to include a data table in something like this. To me, it only muddies the waters. This screams to me that whoever came up with the prompt doesn't really know a whole lot about excel/UW and just wants to use buzz words to make them look smart.
But it is what it is I guess.
With respect to your model, think the only things I noticed after brief review were: 1. As others mentioned, your IRR checks don't tie. This is important and probably why they dinged you after cursory review. 2. Not sure if anyone else saw this, but structure 2 looks wrong to me. Unless I'm missing something, not sure how your deal level IRR could be 19.89% and both your Ordinary and Carried interest IRR's are both below deal level IRR. 3. Why do you have your exit coming from 2 different places in the model? (i.e., you have capital return sensitivity as a different input than proceeds from exit at top of the first tab. Don't see a reason for this, but let me know if there is something I'm missing). EDIT: I re-read the prompt and see why you did this, but again, think this is redundant so I question the competence of the prompt writer.
Other than that, pretty good attempt though.
Hate to be that guy but this shit is so stupid and is a poor indication of how successful a candidate would be.
There are internal models everywhere that would take legit one day to master.
So idiotic.