Free Job Interview Test Setups For Ground-Up Mixed-Use Apartment Building Development Modeling

Free Job Interview Test Setups For Ground-Up Mixed-Use Apartment Building Development Modeling

$0.00

SKU: SKU-735 Categories: ,

Description

These free set-ups simulate the type of assignment you might get in a job interview hands-on test that requires you to build a development Excel model from scratch under a time constraint.

Instructions: Using the assumptions given, build out a 7-year annual projection model for the ground-up development, operation, refinancing (in the case of setup #1 only) and sale of a mixed-use apartment building property.

A complete solution will include the following sections:

  1. Sources and Uses of funds
  2. Cash Flow
  3. Sensitivity Analysis
  4. Equity Joint Venture Cash Flow Waterfall

NOTE: To allow for the use of data tables in the analysis, please do use circular references in your sizing of the sources of funds. In other words, the senior loan will be sized as a given % of the eligible project costs and it will also fund 100% of its own interest reserve amount.

When you are done with the property-level Excel projections, create the following two-way data tables for sensitivity analysis that show the impact to the project-level IRR given simultaneous changes to these sets of variables:

Base Building Hard Costs & Contingency per unit vs. Average Monthly Rent

Hard Costs & Contingency –

  • Base: $200,000
  • +2%
  • +4%
  • +6%
  • +8%

Average Monthly Rent, today’s value (apartments)

  • Base: $4.00 PSF
  • -$0.05
  • -$0.10
  • +$0.05
  • +$0.10

 

Hard Costs & Contingency per unit vs. Asset Sale Capitalization Rate

Hard Costs & Contingency –

  • Base: $200,000
  • +2%
  • +4%
  • +6%
  • +8%

Asset Sale Capitalization Rate –

  • Base: 6.00%
  • +0.25%
  • +0.50%
  • -0.25%
  • -0.50%

 

Questions to answer:

  1. What is the going-in cap rate for this transaction in Elapsed Year 5?
  2. What is the Elapsed Year 5 cash-on-cash return?
  3. Assuming a project annual discount rate of 15%, does the project create value according to the NPV calculation on an Unlevered basis? On a Levered basis?
  4. What is the Debt Service Coverage ratio of the Permanent Loan in Elapsed Year 5?
  5. What is the total profit to the Sponsor?

.

Want to get the unlocked Excel solution set and an illustrative Investment Committee-style 1-page writeup?

Buy the Solution Set

You may also like…

Register now to get FREE e-Books, Excel tools and access to Valuate software REGISTER
left to take 50% off with coupon code: yearend50