11. Solving a Real Estate Financial Modeling Test

COURSE DESCRIPTION

This is the final course in the 16-course Accelerator, and the last of three capstone courses. Upon completion of this course and together with completion of the previous 15 core Accelerator courses, you will receive the A.CRE Real Estate Financial Modeling Accelerator – Certificate of Completion. In this case-based course, you will put into practice what you’ve learned in previous courses to build an institutional-quality, apartment value-add model. In this course you will rely heavily on the following concepts learned from previous Accelerator courses:

1. Use the inputs –> calculation modules –> outputs concept to build a fully dynamic real estate model
2. Use the straight-line construction cost forecasting method to calculate investment cash flows related to a heavy renovation project
3. Model operating cash flows, applying appropriate growth rate, recovery percentage, and vacancy assumptions
4. Track rental rates and rental income as units transition from non-renovated to renovated
5. Create a direct cap pro forma to derive a reversion value
6. Layer in two tranches of debt, a senior loan and a mezzanine loan
7. Modeling debt service with two types of interest calculation methods, 30/360 and Actual/365
8. Build a discounted cash flow model, summing to unlevered and levered net cash flow lines
9. Calculate property-level return metrics
10. Model a partnership waterfall to arrive at partnership-level returns
11. Use proper formatting and layout convention
12. Solve for two value-add metrics, untrended yield-on-cost and development spread

CASE DESCRIPTION – A.CRE CAPITAL TECHNICAL INTERVIEW MODELING TEST

After a phone interview with a recruiter and a 1st round interview with a Director at A.CRE Capital, you’ve been asked to fly to A.CRE Capital’s corporate offices in Anytown USA for a full day of interviews and testing.

As part of the day, the recruiter mentioned that you will be required to complete a technical interview test. In that test, you will be given 3-4 hours and a blank Excel Workbook to model a hypothetical investment opportunity. Upon completion of the test, you will be asked to sit with one of the managing directors at the firm and discuss your analysis.

A.CRE Capital is a real estate private equity firm that executes on a wholly opportunistic strategy. The firm identifies under performing real estate assets, improves NOI through either streamlined management or targeted renovations, and then sells the assets at a profit 3 to 5 years later. The firm is largely agnostic to property type and geography, focusing instead on a nimble strategy of seeking out value wherever it can be found.

In preparation for the modeling test, you look at a combination of development and acquisition models, recognizing that a value-add model is largely a fusion of concepts from both. After several weeks of study and practice, you feel prepared for whatever A.CRE Capital might throw at you in terms of a test.

PROPERTY TYPE

• Apartment; most concepts apply to all property types

SOFTWARE RECOMMENDED/REQUIRED

• While we recommend using Microsoft Excel, as that is the industry standard, this course will work with most other spreadsheet software such as Google Sheets and OpenOffice Calc
• This course was built using the Google Chrome browser, thus we recommend you use Google Chrome for optimal view experience
• This course is best taken on a desktop computer, although the platform is compatible with tablet and mobile devices

EXCEL PROFICIENCY REQUIREMENT

• The course assumes you have at least a basic proficiency working with Microsoft Excel