Preview of Office Property Operating Projection and Acquisition Screening Analysis Modeling Tutorial

Overview

In this lesson, you will learn how to model the acquisition and value-add capital investment associated with lease-up of a commercial office building.

Instructions

Download the Excel from the Materials tab above. Then watch the videos, and follow along and perform the exercises in the Excel.

Video Sample

• Office Cash Flow Set Up
• Projection Formulas
• Office Projection Exercise
• Office Projection Solution
• Acquisition Screening Analysis Exercise

Part 1

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.

Part 2

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.