[Semi-Urgent] Case Study from Top RE Fund [Modelling Help]

EDIT 1: Thanks for all the replies below and fair comments. I should have mentioned I joined in global markets (illiquid sales for 4+ years) but looking to move to RE but experience on RE modelling is severely lacking. Got the interview after bare networking given that my background is not RE and can't screw this up. Barely having time to prepare and was looking for a shortcut - not sure where to start but will give it a go and get back to you with specific question. Cheers!


Can some provide me with the excel solution to the below. I have an interview coming up in 10 days.

Intro:
Contemplating the acquisition of a vacant, 3-unit, 800,000 rentable-square-foot industrial property for $40.0 million. The business plan entails leasing the property to three tenants and selling after five years. Mr RE plans to acquire the property alongside an Operating Partner who will not contribute any equity to the transaction, run day-today operations and receive a 20% incentive promote once the project has achieved a 9% cumulative IRR.

You have 60 minutes to prepare an annual, dynamic, and well formatted cash flow model that calculates unlevered and levered returns to Mr RE

Acquisition Assumptions:
• 100% purchased and owned by Mr RE
• 800,000 sf vacant industrial building
• Bought on January 1, 2019
• Gross purchase price of $40.0mm
• 1% closing costs upon acquisition

Financing Assumptions:
• Mortgage proceeds based on 65% loan-to-cost
• 30-year amortizing loan with a 4.0% fixed interest rate

Revenue Assumptions:
• No existing tenants in the building
• 10-year lease is signed in January 1, 2019 for 50% of the building @ $4.00 per SF per year
• 10-year lease is signed in January 1, 2020 for 25% of the building @ $4.50 per SF per year
• 10-year lease is signed in January 1, 2021 for 25% of the building @ $5.00 per SF per year
• No rent escalations

Operating Expense Assumptions:
• Annual non-recoverable operating expenses represent 3% of annual gross revenue

Capital Expenditure Assumptions:
• Tenant improvements totaling $3.00 per SF per at each lease inception (one-time expense)

Exit Assumptions:
• Sell in year 5
• 6.5% capitalization rate based on forward net operating income
• 1% sales costs upon exit

Waterfall Assumptions:
• 100% of cash flow goes to Metropolitan until the project achieves a 9% cumulative IRR
• 20% promote paid to GP thereafter

 

I’ve found that a lot of groups do “speed tests” rather than super complicated models. Like they’d give the above but you’d have thirty minutes to do it. For example, Starwood’s is longer than the above, less time, but about the same level of complexity

Array
 
Most Helpful

To be honest this is about the most complicated a modeling tests should be. If you have been in the industry for 4 years, it should be a cakewalk or something that maybe needs 1 week to study for. As an example I know how to do development draws but it would take me 5-10 to make a dynamic draw, but 5 seconds of copy paste from a previous model I have built out.

And, I'm just curious. ... How hard is it to bring a flash drive into one of these modeling tests?

10 minutes to model the shell 10 min to model the cash flows/TI.etc 10 min to model amort tabale 20 min for the waterfall.

No real curve balls, I couldn't finish in 30 min. But I could give a good product in 1 hour.

I may just test myself on a slow day.

To help out, the test is set up to be modeled yearly. for cash flows you should have a row of cells referring to year 0,1,2,3,4,5.etc. below that is the date year 2019,2020,2021 etc. Have the revenue correspond (if greater than 2019 then 4 psf * pct square footage.

I'm not sure if you have any exact questions, but you should know how to do an amortzation table, cash flows and a shell.

 

Iure velit et consequatur. Facilis reiciendis vero non saepe nisi. Quibusdam ratione occaecati esse aliquam at. Veniam vero aut voluptatum ad quam.

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
Betsy Massar's picture
Betsy Massar
99.0
4
BankonBanking's picture
BankonBanking
99.0
5
kanon's picture
kanon
98.9
6
CompBanker's picture
CompBanker
98.9
7
dosk17's picture
dosk17
98.9
8
DrApeman's picture
DrApeman
98.9
9
GameTheory's picture
GameTheory
98.9
10
bolo up's picture
bolo up
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...”