An interactive Video Tutorial that teaches:
- Typical line item set-up for office properties
- Cash flow projection modeling basics
- Building a multi-tenant property-level operating projection (also applies to single-tenant properties) assuming initial vacancy that is leased up, and no subsequent expirations during the analysis timeline
- Conducting a property-level acquisition equity returns screening analysis
You will model more than 130 rows across the two parts of the tutorial. This will likely take you 3 to 4 hours including watching the video tutorial.
Product includes the following items, which never expire:
Online access to an easily navigated 90-minute Video Tutorial, playable on any device including PC, Mac, phones and tablets
Accompanying fully-unlocked, annotated Excel file, compatible with both PC and Mac, which you can repurpose for future analyses
Excel file tabs are:
- Office Cash Flow Set-Up
- Projection Formulas
- Projection Exercise
- Projection Solution
- Screening Analysis Exercise
- Screening Analysis Solution (see Bonus Content included below)
First, you will first learn the generic 20-line item set-up for an existing operating commercial office property, starting at Base Rental Revenue (Gross Potential Rent) and ending at Before-Tax Levered Cash Flow (cash flow to equity).
Then, a primer on projection formula construction is provided to get you ready to construct the 10-year annual property operating projection.
Next, the assumptions for projection modeling exercise are explained.
There are three Suites in the property. The assumptions to be used are that leases for Suites 100 and 200 were put in place at the start of the trailing twelve months, and that Suite 300 is vacant currently and will remain vacant through the end of Year 1. Suite 300 rent will commence at the start of Year 2 with Year 2 Base Year.
Suite 100 is a triple-net (NNN) lease, and Suites 200 and 300 are Gross leases (Base Year stop).
The following assumptions inputs are provided for you to key into the Exercise tab:
- Property-level Base Year (BY) operating expenses, real estate taxes and utilities PSF
- Base Year rents for all three suites
- general vacancy and credit loss
- Annual growth rates for rents for all three suites
- % of reimbursable and non-reimbursable expenses that are fixed
- Annual growth rates for:
- parking and miscellaneous revenue
- reimbursable expenses
- real estate taxes
- non-reimbursable expenses
- capital reserves
- Management fees as a % of EGR
- Tenant Improvements and Leasing Commissions PSF for Suite 300
Projection formula mathematical descriptions are provided to guide your Excel formula construction.
After a couple of examples are performed for you, you will fill in the 30+ line items in the exercise. You can check your work in real-time against the fully-unlocked Solution set tab provided in the Excel file. Solutions for the lines are explained in the video.
The second part of the tutorial takes the NOI line from the Part 1 Solution set tab, and weaves it into an acquisition analysis screening tab that integrates purchase, sale, debt and equity elements.
The sections of this analysis are:
- Uses of Funds
- Sources of Funds (Sponsor equity, Third Party Investor equity, Senior Acquisition Loan)
- Unlevered Projection Analysis
- Debt Schedule
- Levered Cash Flow
- Sensitivity Tables
- Levered Summary
After a few examples are performed for you, you will model more than 100 rows. You can check your work in real-time against the fully-unlocked Solution set tab provided in the Excel file. Solutions for the lines are explained in the video.
Data tables are used to provide a spectrum of outcome possibilities given simultaneous changes in two key inputs. A levered summary table is also provided to evaluate returns for multiple hold durations side-by-side.
BONUS EXCEL CONTENT INCLUDED! *
- Annual equity joint venture partnership waterfall between Sponsor and Investor
- JV partnership structure: (Equity goes in 10% from Sponsor, 90% from Investor.) All equity gets a Preferred Return of 10%; after Preferred Return is distributed and Equity investment is returned, then the Sponsor gets a 20% promote, and the residual 80% goes to ALL Investors, pro-rata to cash investment.
* Bonus content is annotated only; there is no video tutorial footage associated with it.