Description
This Course Is For You If You Want To
This is a good fit if you want to:
- Learn the commercial office building development business such that you can simulate it in an Excel-based projection model whose calculation bases would pass muster with even the most sophisticated institutional investment groups and lenders
- Have a step-by-step guide through the modeling process, but don’t want to be forced to build the model from scratch
- Gain confidence in your ability and skills to solve new modeling problems that arise over time
- Understand the basis of the REFM Professional Mixed-Use Office Building Development template, which has been used by professionals to successfully raise capital for actual 9-figure office building developments, such as at The Wharf in Washington, D.C.
Details
In this 5-lesson course from Real Estate Financial Modeling, you will learn the collection of technical skills and best practices for the monthly-based pro-forma (projection) modeling for the ground-up development, operation and sale of an office property with retail and income-producing parking. To facilitate learning, a case study is used of an urban office building with a total development cost of approximately $100MM.
To set the proper skills and knowledge foundation, you will first learn about formula writing involving conditional statements, as well as REFM’s generic projection formula framework.
You will begin with pre-formatted tabs for lot and building information for a subject development site, and for capital structure.
With transaction base case assumptions provided for you, you will then derive and fill in line item projection formulas on a standard, pre-formatted monthly sources and uses of funds tab. Next, you will derive and fill in line item projection formulas on a standard, pre-formatted monthly cash flow tab. You will wire the tabs together as applicable. When you have completed all of the hands-on modeling, you will have a fully-working multi-tab model that ends at before-tax levered cash flow.
The advanced capital structure you will model includes:
1. sponsor, partner, and third party investor equity
2. mezzanine loan
3. senior construction loan.
- 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.
This is a hands-on-the-keyboard course. You will watch the video and pause it to complete exercises in the associated Excel; you will repeat this process through all 5 lessons.
Our approach to help you learn is to not force you to build the model from scratch. Rather, we provide you with pre-formatted, pre-labeled model “skeletons” so that you can focus on learning the business underpinnings of the formulas that you will be writing, and the formula mechanics themselves. Real estate is a business, and the business models, analytical conventions and operating realities must be understood intimately so they can manifest correctly in the Excel-based logic statements and calculations.
Our teaching and learning format not only allows you to learn more deeply, but also allows you to do so 20% faster than if you were to start from a blank worksheet.
You are of course welcome to re-build the spreadsheet from scratch at any time, but in the interest of getting you to learn efficiently in your limited study time, we choose to give you the skeleton and teach from that point of progress forward.
Rest assured that you will work very hard in this course programming dozens and dozens of lines to fully populate the model skeleton, and you will learn a tremendous amount about real estate and financial modeling in the process.
The video running time is 5.7 hours across the 5 lessons. Watching all the video (replaying parts where desired) and completing the exercises will take approximately 8 to 12 hours, depending on the individual.
Through the direct experience of REFM’s principals in commercial real estate transactions, and REFM’s ongoing consulting to industry practitioners across the U.S. for the last decade, we have developed and have continually refined “The REFM Way” of teaching financial modeling.
To enhance your learning and help you to efficiently build a practical skill set, we:
- Always set the stage by providing the big picture first, and keep it at the fore with graphs and sparklines
- Speak in plain language and define industry terms on an ongoing basis
- Give you the head start of having a model skeleton in which to learn
- Provide a magnified formula bar so you don’t have to strain your eyes to see formula contents
- Draw with “ink” on the worksheets to help you follow the lesson and grasp important points
- Give you highly granular video chapter hyperlinks so you can find what you want quickly and easily.
Upon successful completion of this course, you will:
- Have gained a holistic understanding of the development process and learn what needs to be reflected in a financial model
- Be able to approach any ground-up office building development analysis with a solid analytical framework
- Be confident in your ability to solve for investment returns to the equity position in an office building transaction.
What You Get
Lifetime online access to:
- An easily-navigated 344-minute (5.7-hour) video tutorial with captions available and playback controls up to 2x speed. Videos are playable on any device including PC, Mac, phones and tablets.
- Accompanying fully-unlocked, annotated Excel file, compatible with Excel on both PC and Mac, which you can re-purpose for future analyses.
Meet Your Instructor
Since 2009, top companies and educational institutions have trusted REFM to train thousands of their employees and students.
New to REFM training?
Clients Include
Clients Include
Amherst
ASB Capital Management
Barings
BentallGreenOak
BrightSpire
Brixmor
Brookfield
CapitalOne
CBRE Global Investors
CCRE
Centennial Bank
Citibank
ColonyNorthstar
Conrex
CPPIB
CRC Companies
CreditSuisse
Crescent Communities
Crow Holdings
DDG
DLA Piper
Greystar
GTIS Partners
Hodes Weill & Associates
Houlihan Lokey
Howard Hughes
Jamestown
JBG Smith
JPMorganChase
Kennedy Wilson
Link Logistics
LivCor
Lubert-Adler
Macys
Oxford Properties
Park Hill
Peterson Companies
PGIM
PN Hoffman
Prometheus
QuadReal
Related
Revantage
SEO
Skanska USA
Square Mile Capital
USAA Real Estate
Asset Living
CBRE Global Investors
Clark Enterprises
Comstock Homes
Conor Commercial
Crescent Communities
Federal City Property
Flournoy
Four Seasons
Grosvenor
GTIS Partners
Hines
Hoffman
Howard Hughes
HPET
Hunt Companies
Insight Property Group
Kettler
LaSalle Investment
MadisonMarquette
Markwood
Mesa Capital
Midway
Miller Valentine
Panoramic Interests
PEG Development
Renaissance Downtowns
Rockefeller Group
Skanska USA
Snavely Group
Trammell Crow Residential
Trion Properties
Vornado
Sample Video
- Lesson 1: Overview
- Lesson 1: Floor Area Ratio (FAR)
- Lesson 1: Capital Structure Overview
- Lesson 1: Detailed Capital Stack
- Lesson 2: Linear Logic
- Lesson 2: Sources and Uses Overview
- Lesson 2: Land Acquisition Projection
- Lesson 2: Construction Bell-Shaped Curve Table
- Lesson 3: Property Cash Flow As A Source Of Funds
- Lesson 3: Loan Sizing
- Lesson 5: Expense Reimbursement Mechanics
- Lesson 5: Tenant Lease Year Counter
- Lesson 5: Expense Reimbursement Projection
Excel File Tabs
The Excel file is 100% unlocked, and you can re-purpose the tabs and comprising code as you desire.
- Roadmap
- Profile, Lot and Building Information
- Stacking Plan
- Construction Hard Costs
- Capital Structure 1
- Capital Structure 2
- Capital Struture 3
- Conditional Statements Introduction
- Exercise Solution
- 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
- Sources and Uses of Funds Exercise
- Sources and Uses of Funds Solution
- Construction Bell-Shaped Curve
- Cash Flow Set-Up
- Cash Flow Projection Timeline
- Cash Flow Exercise
- Cash Flow Solution
- Parking Income Projection
- Land & Acquisition Costs Schedule
- Hard Costs Budget Schedule
- Soft Costs Budget Schedule
- Operating Expenses Schedule
- 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 is 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.
