Real Estate Financial Modeling Tools

Mixed-Use Office/Industrial Building Development Financial Modeling Self-Study Video Tutorial with Excel File

$199.00

-

This product is now updated and available here.

Academic / U.S. Military Price – $99.00

To get discounted pricing, click Credentials Verification in the header and follow the instructions. Once you are approved, the discounted pricing will show.

Sample Content

Out of stock

SKU: SKU-35 Category:

An interactive Video Tutorial that teaches how to model the speculative development and sale of a commercial (office or industrial) building (with a ground-floor retail component for office building) and income-producing parking component. Also applies to acquisition of foreclosed development projects.

Product includes the following items, which never expire:

 Online access to an easily navigated 180-minute Video Tutorial, playable on any device including PC, Mac, phones and tablets

SelfStudy750pxwide

 Accompanying fully-unlocked, annotated Excel file, compatible with both PC and Mac, which you can repurpose for future analyses

100-page Audio Transcript in PDF format

What You Will Learn

A mastery of technical pro-forma (financial projection) modeling skills for the ground-up development of a commercial building, including:

Site and Building Information

– Calculation of allowable density using FAR multiple and site area
– Calculation of allowable building footprint using lot coverage ratio and site area
– Calculation of building height using slab-to-slab height and story count
– Calculation of commercial gross square footage given an assumed ground-floor retail square footage
– Calculation of commercial rentable square footage given an efficiency factor
– Calculation of parking requirements and required parking gross square footage given usable square footage loss factor

Capital Structure

– Calculation of sources of funds supplied by each of three equity sources, a mezzanine lender, and a senior lender
– Calculation of percentage of total development cost that each of the three equity investments comprise

Project Timing Elements

– Understanding of the relationship between the durations of the three phases of development (Pre-Construction, Construction and Post-Construction), and the timing and dependencies of the major milestones within each of those phases

Uses of Funds

– Quantification and timing of:
– Land and Acquisition Costs
– Hard Costs (based off of a bell-shaped distribution curve) and Tenant Improvement Costs
– Soft Costs
– Fixtures, Furnishings and Equipment Costs
– Financing Costs, including Capitalized Interest Expense, and Operating Deficit

Sources of Funds

– Quantification and timing and interrelationship of Draws, Interest and Repayment, as applicable, of:
– Developer Sponsor Equity
– Developer Partner Equity
– Third Party Investor Equity
– Mezzanine Financing
– Senior Financing

Cash Flows and Returns

– Income, Expense and Capitalization Rate Assumptions
– Calculation of Total Operating Income and Parking Total Operating Income and Vacancy
– Calculation of Total Operating Expenses and Real Estate Taxes
– Calculation of Net Operating Income (NOI)
– Calculation of Current Annual Yield
– Calculation and Timing of Non-Capitalized Interest Expense
– Timing of Financing Cash Flows
– Calculation of Project Levered Cash Flow
– Calculation of Multiple on Equity
– Calculation of Internal Rate of Return (IRR) on Equity
– Calculation of Profit Margin

Capitalized Valuation

– Calculation of “Current” Annual Net Operating Income
– Calculation of Future Stabilized Net Operating Income
– Calculation of Future Net Sale Amount

Profit Sharing/Internal Rate of Return Waterfalls

– Calculation of profit sharing based on an assumed Internal Rate of Return Waterfall structure with preferred returns and promotes

Video Tutorial Abbreviated Table of Contents

Video #1 – Module Overview, Excel Settings, Footprint and Building
Video #2 – Capital Structure, Conditional Statements
Videos 3a and 3b – Sources and Uses of Funds
Video #4 – Cash Flow and Returns Summary, NOI and Valuation
Video #5 – Profit Sharing, Waterfalls
Video #6 – Exhibits and Supporting Schedules

Video Tutorial Expanded Table of Contents

Video #1

Module Overview, Excel Settings, Footprint and Building

– REFM Product Module Orientation
– Important Excel Settings

Tab: Office Industrial Module
– Office Industrial Module Introduction

Tab: FAR, Footprint and Massing
– FAR, Footprint & Massing Introduction
– FAR
– Lot Coverage
– Massing

Tab: Profile, Lot and Building Info.
– Profile, Lot & Building Info Tab Intro
– Project Profile
– Lot & Above-Grade Building Description
– Below-Grade Parking Garage Description

 

Video #2

Capital Structure, Conditional Statements

Tab: Capital Structure 1
– Capital Structure 1 Tab

Tab: Capital Structure 2
– Capital Structure 2 Tab

Tab: Capital Structure 3
– Capital Structure 3 Tab

Tab: Capital Structure 4
– Capital Structure 4 Tab

Tab: Conditional Statements Introduction
– Conditional Statements Introduction Overview
– Control Panel
– Timeline Compression Discussion
– Pre-Construction Period
– Construction Start Month and Duration
– Construction End Month/C of O Received
– Post-Construction Leasing Duration
– Office Tenant Rent Commencement
– Tenant Improvement Payments
– Disposition (Sale) of Office, Parking and Retail
– Retail Tenant Rent Commencement
– Time Coding Overview
– Calendar Month
– Counting Numbers
– Construction Month #
– Cell Reference Locking
– Construction Status
– Calendar Year
– Development Fee Example

 

Video #3a

Sources and Uses of Funds, Part 1

Tab: Sources and Uses of Funds
– Sources and Uses Overview
– Land and Acquisition Costs
– Hard Costs Bell-Shaped Curve Description

Tab: Construction Bell-Shaped Curve
– Bell-Shaped Curve Tab

Tab: Sources and Uses of Funds
– Hard Costs Allocation
– Tenant Improvements
– Soft Costs/Customizing Soft Costs
– FF&E Costs
– Financing Costs Overview
– Sources of Funds Introduction
– Equity Capital
– Equity Capital Relationships
– Developer Sponsor Equity
– Developer Sponsor Draw
– Developer Sponsor Cumulative Draw
– Developer Partner Equity Draw

 

Video #3b

Sources and Uses of Funds, Part 2

Tab: Sources and Uses of Funds
– Third Party Investor Draw
– Mezzanine Loan
– Senior Loan
– Financing Costs

Tab: Mezz Int Graph
– Capitalizing Interest

Tab: Mezz Int Graph – C v. Non-C
– Capitalizing Interest, continued

Tab: Sources and Uses of Funds
– Capitalizing Interest, continued
– Operating Deficit
– Total Financing Costs
– Total Development Costs
– Checks on Sources & Uses

 

Video #4

Cash Flow and Returns Summary, NOI and Valuation

Tab: Cash Flow and Returns Summary
– Cash Flow & Returns Summary Introduction
– Cash Flow Assumptions
– Ave. Rent PSF
– Discussion on Rent PSF Assumption
– Operating Expenses PSF
– Discussion on Operating Expense PSF Assumption
– Real Estate Taxes PSF
– Vacancy
– Capitalization Rate
– Selling Costs
– Retail Component Assumptions Introduction
– Rent PSF
– Operating Expenses and Real Estate Taxes PSF
– Vacancy
– Capitalization Rate
– Revenues Section
– Office Total Operating Income
– Office Vacancy
– Parking Total Operating Income

Tab: Parking Income Projection
– Parking Income Projection Tab

Tab: Cash Flow and Returns Summary
– Parking Income, continued
– Office Building and Parking Sale, Net Overview

Tab: Net Operating Income; Valuation
– Net Operating Income; Valuation Tab Overview
– “Current” Office and Parking Asset Annual NOI
– Stabilized NOI Rationale
– Gross Potential Annual Operating Income
– Vacancy Allowance
– Operating Expenses & RE Taxes
– Parking NOI
– “Current” Annual NOI and Yield on Cost
– Capital Expenditure Discussion
– Inflation Discussion
– Average Rent at Capitalization
– Future Stabilized NOI
– Future Stabilized Yield On Cost
– Capitalized Value
– Net Sale Amount

Tab: Cash Flow and Returns Summary
– Office and Parking Sale, Net
– Retail Net Operating Income
– Retail Vacancy
– Retail Condominium Sale, Net
– Total Potential Revenue
– Operating Expenses
– Real Estate Taxes
– Total Operating Expenses and RE Taxes
– Net Operating Income (NOI)
– Interest Expense
– Senior Loan Interest Expense
– Mezzanine Loan Interest Expense
– Total Interest Expense
– NOI After Interest
– Financing Cash Flows Overview
– Equity and Loan Draws
– Senior and Mezzanine Loan Repayment
– Operating Deficit
– Project Levered Cash Flow
– Net Levered Cash Flow
– Returns Summary Box
– Multiple on Equity
– Internal Rate of Return (IRR) on Equity
– Profit Margin

 

Video #5

Profit Sharing, Waterfalls

Tab: Profit Sharing
– Profit Sharing Overview
– IRR Waterfall Structure Introduction

Tab: Waterfall #1 Graphs
– Waterfall #1 Overview

Tab: Waterfall #1
– Waterfall #1 Detail Overview
– Waterfall #1 Structure Box
– Monthly Timeline
– Total Project Invested Equity
– Total Project Distributions
– Third Party Investor Equity Investment
– Inter-Tier Relationship
– Waterfall Animation
– Tier 1 IRR Range
– Beginning of Period Balance
– End of Period Balance
– Third Party Investor Injection
– Accrual of Returns Assumption
– Investor Accruals
– Tier 1 Accrual Distribution
– End of Period Balance
– Accrual Distribution Discussion
– Partitioning Tier 1 Cash Flows
– Investor Cash Flow
– Top-Level Developer Cash Flow
– Developer Promote Cash Flow
– Tier 1 Remaining Cash to Distribute
– Tier 2 Overview
– Investor Injections
– Investor Accruals
– Tier 1 Accrual Distribution
– Tier 2 Accrual Distribution
– End of Period Balance
– Investor Cash Flow
– Developer Equity Cash Flow
– Developer Promote Cash Flow
– Remaining Cash to Distribute
– Tiers 3 and 4
– Tier 5 Overview
– Cash Flow Calculation
– Discussion of Tier 5 Cash Flows
– Waterfall Logic Illustration
– Waterfall #1 Review
– Returns Summary Overview
– Project Level Returns
– Investor Returns
– Top-Level Developer Returns
– Cash Flow Check
– IRR Check

Tab: Profit Sharing
– Waterfall #2 Overview

Tab: Waterfall #2 Graphs
– Overview
– Tab: Waterfall #2
– Waterfall #2 Calculation Overview
– Differences Between Waterfalls #1 and #2
– Equity Split
– Project Level Equity Flows
– Developer Gross Cash Flow
– Developer Partner Equity Investment
– Tier 1 Remaining Cash to Distribute
– Checking Waterfall #2
– Returns Summary
– Cash Flow Check
– IRR Check

 

Video #6

Exhibits and Supporting Schedules

Tab: Sources and Uses Exhibit
– Sources and Uses Exhibit

Tab: Supporting Schedules
– Supporting Schedules Overview

Tab: Land & Acquisition Costs Schedule
– Land & Acquisition Costs Schedule

Tab: Hard Costs Budget Schedule
– Hard Costs Budget Schedule

Tab: Soft Costs Budget Schedule
– Soft Costs Budget Schedule

Tab: Operating Expenses Schedule
– Operating Expenses Schedule

Tab: Returns Exhibit
– Returns Exhibit

Tab: Transaction Summary
– Transaction Summary Exhibit

Registration is open for April Excel Bootcamp Webinars Register Now
left to take 50% off with coupon code: yearend50