0% Complete
0/8 Steps

Preview of Mixed-Use Office Building Development Modeling Course

Lesson
Materials

A note from instructor Bruce Kirsch, REFAI

This course includes:

  • Chaptered video of 5 lessons totaling 5.7 hours
  • Fully-unlocked Excel file with solution set
  • Lifetime access, even on mobile devices.

Overview

In this course, you will learn how to model the development, operation and sale of a speculative office building with ground-floor retail and income-producing parking. To facilitate learning, a case study is used of an urban office property with a total development cost of approximately $100MM.

Topics covered include:

  • site and building information
  • pre-, during- and post-construction timing elements
  • transaction capital structure
  • formula writing involving conditional statements
  • cash flow projection formula writing
  • uses of funds: land and acquisition costs, hard & soft costs, FF&E and financing costs
  • sources of funds: equity, debt
  • cash flows from office (and parking and retail) or industrial
  • capitalized valuation for sale
  • investment returns
  • residual land valuation.

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

Excel File Tabs

The Excel file is 100% unlocked, and you can re-purpose the tabs and comprising code as you desire.

  1. Roadmap
  2. Profile, Lot and Building Information
  3. Stacking Plan
  4. Construction Hard Costs
  5. Capital Structure 1
  6. Capital Structure 2
  7. Capital Struture 3
  8. Conditional Statements Introduction
  9. Exercise Solution
  10. Development Financing Overview
  11. Construction Loan Sizing Methods
  12. Capital Structure 4
  13. Flow Charts
  14. Sources and Uses of Funds Overview
  15. Sources and Uses of Funds Overview 2
  16. Sources and Uses of Funds Exercise
  17. Sources and Uses of Funds Solution
  18. Construction Bell-Shaped Curve
  19. Cash Flow Set-Up
  20. Cash Flow Projection Timeline
  21. Cash Flow Exercise
  22. Cash Flow Solution
  23. Parking Income Projection
  24. Land & Acquisition Costs Schedule
  25. Hard Costs Budget Schedule
  26. Soft Costs Budget Schedule
  27. Operating Expenses Schedule
  28. Sources and Uses of Funds Exhibit

Course Content

Lesson 1 – Introduction and Modeling Foundations (72 min.)

In this lesson, you will learn the big picture of the developer’s analysis, which always starts with the subject land parcel. You will learn the basics of zoning to determine the allowable building density on the site, which drives the project’s economics. You will also learn about project capitalization using debt and equity, how to harness the power of conditional (IF function-based) statements to model efficiently, and the two ways to size senior construction debt (one with and one without circular references). Last, you will learn about the linear nature of the development business model and get an overview of total development costs modeling.

Video chapter markers:

  • Introduction
  • Roadmap
  • Profile Lot & Building Information
  • Lot & Base Building
  • Parking
  • Stacking Plan
  • Components of Floor Height
  • Construction Hard Costs
  • Capital Structure 1
  • Capital Structure 2
  • Capital Structure 3
  • Conditional Statement Introduction
  • Development Financing Overview
  • Construction Loan Sizing Methods
  • Capital Structure 4
  • Flow Charts
  • Sources and Uses of Funds Overview
  • Sources and Uses of Funds Overview 2

Lesson 2 – Uses of Funds Projection (51 min.)

In this lesson, you will learn how to model the uses of funds (total development costs) for the mixed-use office building.  You will do this on a monthly basis, the standard modeling frequency for serious analysis on a project. All major cost categories will be modeled out, with base building hard costs funded based on a bell-shaped curve, the industry standard practice. Special attention paid to which costs are eligible for funding by the senior construction loan and which are not.

Video chapter markers:

  • Sources and Uses Exercise Overview
  • Control Panel
  • Timeline
  • Task Overview
  • Teaching Approach
  • Land & Acquisition Costs
  • Base Building Hard Costs
  • Tenant Improvements
  • Leasing Commissions
  • Total Hard Costs, TIs and LCs
  • Soft Costs
  • FF&E
  • Subtotal Eligible Costs for Senior Loan Funding
  • Financing Costs
  • Total Development Cost
  • Operating Deficit
  • Cash Flow Projection Connection

Lesson 3 – Sources of Funds Projection (66 min.)

In this lesson, you will learn how to model on a monthly basis the various sources of capital that come together to fund periodic development costs. You will learn how to simulate pari passu and staggered equity funding conventions among three equity players, and also how to model mezzanine and senior construction loan funding draws, cash and accrued financing costs, and principal repayment. Last, will learn about how the property’s own free cash flow can serve as a source of funds in the project capitalization.

Video chapter markers:

  • Overview
  • Pari Passu
  • Developer Sponsor
  • Developer Partner
  • Mezzanine Loan
  • Senior Loan
  • Property Cash Flow

Lesson 4 – Cash Flow Introduction (15 min.)

In this lesson, you will get a big picture understanding of how the cash flow projection is structured and the drivers of cash flows from the various income and expense sources.

Video chapter markers:

  • Cash Flow Set-Up
  • Cash Flow Projection Timeline

Lesson 5 – Cash Flow Projection and Returns (140 min.)

In this lesson, you will dive deep into the cash flow projection for the property. You will learn about the two major types of lease structures present in the building and their critical reimbursement math mechanics, conventions for modeling common area and tenant-specific fixed and variable operating expenses, and how to isolate the NOI streams from the office and parking use from that of the retail. You will connect together the sources and uses of funds projection with the cash flow to have a fully-dynamic model that produces investment returns to equity. Last, you will look at how to perform sensitivity analyses to answer “what if” scenario questions and solve for economic feasibility by changing key variables.

Video chapter markers:

  • Overview of Cash Flow Projection Tab
  • Control Panel
  • Cash Flow Assumptions
  • Full Service Lease Structure
  • NNN Lease Structure
  • Office Lease Year Counters
  • Office Square Footage Leased
  • Gross Potential Rent
  • Free Rent
  • Expense Reimbursement Introduction
  • Scheduled Base Rental Revenue
  • Parking Income
  • Signage/Miscellaneous Income
  • Potential Gross Revenue
  • Vacancy/Credit Loss
  • Effective Gross Revenue
  • Expenses
  • Reimbursable Expenses
  • Fixed Operating Expenses
  • Variable Operating Expenses
  • Real Estate Taxes
  • Non-Reimbursable Expenses: Fixed Utilities
  • Variable Utilities
  • Insurance
  • Management Fees
  • Total Operating Expenses and Real Estate Taxes
  • Net Operating Income
  • Leasing & Capital Costs
  • Capital Reserves/Expenditures
  • Adjusted NOI
  • Forward Adjusted NOI
  • Expense Reimbursements
  • Reinspecting NOI
  • Retail Introduction
  • Retail Lease Year Counter
  • Retail SF Leased
  • Retail Gross Potential Rent
  • Retail Free Rent
  • Retail Expense Reimbursements
  • Retail NOI
  • Retail Adjusted NOI
  • Combined Adjusted NOI
  • Cap Rate
  • Net Revenue After Capital Events Before Financing Costs
  • Unlevered Cash Flow
  • Financing Costs Paid In Cash
  • Capital Events
  • Big Picture
  • Financing Cash Flows
  • Updating Sheet References
  • Net Revenue After Capital Events After Financing Costs
  • Levered Cash Flow
  • Returns Metrics
  • Adjusting Model Inputs
  • Goal Seeking New Senior Loan Size
  • Raising Rent
  • Checking Senior Loan LTC
  • Conclusion.

REFM_Office_Industrial_Building_Development_Tutorial_v37.1

The Excel file is 100% unlocked, and you can re-purpose the tabs and comprising code as you desire.

Excel file tabs:

  1. Roadmap
  2. Profile, Lot and Building Information
  3. Stacking Plan
  4. Construction Hard Costs
  5. Capital Structure 1
  6. Capital Structure 2
  7. Capital Struture 3
  8. Conditional Statements Introduction
  9. Exercise Solution
  10. Development Financing Overview
  11. Construction Loan Sizing Methods
  12. Capital Structure 4
  13. Flow Charts
  14. Sources and Uses of Funds Overview
  15. Sources and Uses of Funds Overview 2
  16. Sources and Uses of Funds Exercise
  17. Sources and Uses of Funds Solution
  18. Construction Bell-Shaped Curve
  19. Cash Flow Set-Up
  20. Cash Flow Projection Timeline
  21. Cash Flow Exercise
  22. Cash Flow Solution
  23. Parking Income Projection
  24. Land & Acquisition Costs Schedule
  25. Hard Costs Budget Schedule
  26. Soft Costs Budget Schedule
  27. Operating Expenses Schedule
  28. Sources and Uses of Funds Exhibit