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 can't seem to open any of the links, but i'm happy to take a look at and see. i work for a private equity shop, and have u/w quite a bit (used to work for a high profile brokerage team and did a lot of big ticket deals)
You do realize that the link is cut in half, right? Have you used DropBox before?
Simply copy/paste the entire link to a web browser and remove the space in "c om." (My account is too new to link the full address.)
If you are willing to share your feedback with me over message I'd appreciate it. Going to take a stab at the test myself.
Sadly the only feedback I've received so far is what is in the original post.
i'm curious, how long did they give you to put this together? i haven't dug into it yet to check the calculations but it looks like you didn't skimp on effort and seems like you responded to all the points.
I was given maximum one week. It's the calculations I'm more worried about. Structure 1 was classic waterfall through pref and hurdles, structure 2 was worded a bit strangely, structure 3 used multiples for hurdles which was new to me, but my result "felt" right. Then again, my experience is pretty limited, so I'm wary of hubris here.
Something that was strange to me: the chart axes are specified. If you sensitize around the provided 180,000,000 exit, the net cash flow IRR's plot outside of the chart from the get-go. That seems really weird to me from a case study stand point. Any time I was given a value in a case study and told to sensitize, I always did assumptions above and below that amount. If you do that here, the optimistic values plot way outside of the chart.
Side note: to get the exact formatting I used, open in Excel desktop -- dropbox's reader is distorting things here and there.
I've only taken a quick glance (will take a better look later) but the first thing I seen was that the IRR tests on each tier are not hitting the exact value. Off the top of my head I think instead of (1+Structure1Pref)^(1/12)-1) you would need to get something along the lines of the below;
(1+Structure1Pref)^(x/y)-1) Where; x = days since last accrued payment y = days in year (accounting for leap years)
Or something along those lines, someone feel free to correct me.
Your formatting is pretty good and easy to follow.
That's correct. For x, you'd subtract the two dates. An XIRR check would prove this out too.
You also could use the days function in excel to accomplish the same thing:
=Days(end date, beginning date)/360 *Hurdle Rate
I also feel like hurdle IRRs don't always tie out exactly when using XIRR? Just a thought....
*Correction: it would tie out with 365 days. It depends on how interest is compounded, which I don't think is spelled out for the sake of the case study, but would be a think on a live deal.
Thanks very much for this -- I implemented your suggestion in this way: ((1+Structure1Pref)^((H2-G2)/(DATE(YEAR(H2),12,31)-DATE(YEAR(H2),1,1)+1))-1)
My Preferred IRR is coming out to 7.99% instead of exactly 8. Any ideas what may be missing?
I'm not sure, I know I've ran into this problem in the past and fixed it but it's buried somewhere on one of my old models and can't seem to find it (disappointed I haven't kept this fix running with my newer models!). If I manage to find it I'll post it here.
beyond me but appreciate the candor in sharing. would be interested to see everyone else's thoughts. and see what additional feedback the firm gives you.
Only thought I have is maybe putting the assumptions on the actual waterfall pages, having built-in tests, and putting in sums/totals on the left side for the waterfall.
If I want to print those tabs, and just those tabs, I have no idea what the assumptions/hurdles are and I want to see sums/bottom line IRRs and multiples. Also, we all know you think there are no errors, but everyone likes to see a "check" line-- it gives them a warm fuzzy feeling.
Again, these are very nit comments, and different people have different styles, but if you forced a nit-pick, those would be my comments.
Looks like you did a good job conceptually but you solutions were not precise.
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...
How do you normally account for acrrual of preference over time to have it tie out exactly? I have implemented some of the advice (see above) for very careful accrual that more accurately counts days, but XIRR reflects just shy of the required 8% (7.993).
late to this...but for any compounding...ill typically use the effect formula in excel. this takes the annual rate, incorporates the compounding schedule, and returns the equivalent percentage. I found this is the most accurate and easy way to tie out the numbers exactly. Once you have the effective rate, I make the formula based on number of days...meaning take the rate / 365 (make sure it's the exact number of days in the year you are calculating) * number of days between periods. I also like to have my waterfalls built out this way incase another deal comes by and someone says "we want it modeled out to show quarterly distributions per how it's outlined in the OA.". Changing my model from monthly CF to qtly CF is easy, because my formula just calculates a larger number of days between dates.
hope that makes sense.
some things I've found happen a lot when seeing other company models on deals is the coming leap here having an impact...but that's getting a little too cute IMO.
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.
Dolor veritatis ut suscipit minus voluptatem et. Ullam cumque et quis vel natus cupiditate itaque.
Itaque eaque eaque vel facilis quasi. Debitis vel dolore molestiae occaecati. Consequuntur cumque asperiores odit aut aspernatur.
Et ea et totam. Qui qui ipsa autem. Eaque pariatur deleniti id consequatur. Voluptatem rerum quam corporis eligendi. Perspiciatis vel eius sit sapiente.
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...
Reprehenderit repellat quidem ipsum. Voluptatum reprehenderit autem omnis aut eligendi sit. Ipsam modi qui enim harum.