Day 1
Section 1: Introduction
Real estate model structure compared to corporate model, LBO model, project finance model
Difficulties in real estate modeling
o Modeling timing of construction, phases and exit proceeds
o Modeling portfolios of projects
o Modeling milestone payments
o Modeling of cash flow waterfalls and structured finance
o Lease portfolios and risk analysis
Excel techniques for real-estate modeling and annual single project
o Short-cut keys for setting-up sheets
o Use of switches for project phases and exit period
o Presentation of cash flows and sensitivity analysis
Section 2: Model of Single Project
Periodic modeling and flexible analysis of alternative periods
o Modeling delays in construction and alternative terminal periods
o Theory of capitalization rates
o Conversion of periodic model to annual model
Developing flexible inputs for utilization rates, lease rates and operating costs
o Variables that change as a function of calendar years
o Variables that change depending on the age of a project
o Development of annual period counters
Debt sizing and debt re-structuring
o Debt inputs including repayment pattern, interest rates, covenants, debt service reserves and debt sizing
o Modeling of debt drawdowns during construction period
o Computation of repayment during operation and at exit
o Adjustments for periodic interest expense
Model verification
o Creative establishment of multiple tests
o Aggregation of verification checks
o Identification of places in which model is not working
Scenario analysis with single project model
o Computation of Equity IRR, Unleveraged IRR and other ratios
o Creation of flexible master scenario pages
o Presentation of sensitivity analysis demonstrating the relative effect of different variables
Day 2
Section 3: Model of Mixed Development Project
Set-up of inputs for overall project and for individual sub-projects
o Land costs and development of infrastructure costs
o Timing assumptions for individual sub=projects
o Operating assumptions for commercial projects
o Operating assumptions for residential projects including s-curves and progress payment profiles
o Set-up of financial assumptions
Development of model for single project
o Use of common date structure for all projects
o Computation of time period counters for different projects
o Construction of models that allow flexible construction, revenue and operating costs that evaluated different types of projects
o Pre-tax cash flow and IRR’s on sub-project basis
Consolidation of operating inputs for multiple sub-projects
o Efficiently summing sub-project items without creating separate models
o Alternative presentations of project portfolio
o Items required for financial model
Financial model of consolidated model
o Debt commitment and debt draws with multiple completion dates
o Allocation of interest during construction
o Repayment of mortgage debt
Scenario analysis in mixed development model
o Problems with traditional excel tools for sensitivity and scenario analysis
o Creation of master scenario page
o Use of macros in creating scenarios
Section 4: Model of Residential Portfolio with Milestone Dates
Model inputs with milestone dates
o Set-up of flexible milestones
o Use of dates or periods
o Construction expenditures for different milestones
Individual projects with milestone dates
o Model timing and switches
o Calculation of construction time periods for different milestones
o Computation of construction expenditures and revenues
o Cash flows and IRR’s for individual projects
Consolidation and financial model
o Effect of milestone dates on IRR
o Debt sizing and debt capacity with different residential margin and timing assumptions
Day 3
Section 5: Structured Finance in Real Estate Models
Alternative Financing Structures
o Mortgage debt
o Senior and subordinated debt
o Preferred stock and trigger returns
Inclusion of alternative finance structure in mixed development model
o Inputs for alternative financing instruments
o Set-up of schedules for alternative financing instruments
o Modeling of cash flow waterfall
o Auditing of cash flow waterfall
Evaluation of risk and return of different financing instruments
o Computation of IRR and NPV for each financial instrument
o Break-even points for different instruments
o Inclusion of NPV and IRR in scenario analysis
Section 6: Lease Roll Analysis and Risk Simulation
Risk and return of projects with different lease expirations
o Volatility of lease rates
o Effect of lease rate on debt capacity and required return
o Valuation of projects with different lease rate structures
Inputs for lease roll
o Lease rate, expiration dates, idle time and renewal rates
o Volatility of lease rates
o Downside and upside scenarios
Modeling of future lease rates and idle time
o Vintage of lease rates
o Use of range names with formulas
o Monte Carlo simulation of the distribution of returns with different lease rolls