“The New York Course” Office Building Development and Acquisition Bundle

In this 3-hour course, you will learn the basics of operating in Excel, as well as advanced techniques, specifically as they relate to real estate analysis. You will follow along in Excel in real time and perform exercises to ensure you are grasping the lesson and are mastering the technical skills being taught.

• Arithmetic and Rounding
• Statistics
• Relative vs. Absolute Cell References
• Inserting and Deleting Rows and Columns
• Dates and Timelines
• Conditional Statements
• Function Wizard
• Creating Summary Tables
• Formatting Numbers
• Conditional Cell Formatting
• Data Table Lookup Functions
• Custom Formatting
• Format Painting
• Paste Special
• Transpose
• Financial Functions for Compounding and Discounting
• Internal Rate of Return (IRR)
• Mortgage Payment Calculation
• Formula Auditing
• Data Tables for Sensitivity Analysis
• Circular References
• Keyboard Shortcuts
• Best Practices

• SUM
• ROUND, ROUNDUP, ROUNDDOWN
• MAX, MIN, AVERAGE, MEDIAN
• Cell Anchoring using Dollar Signs
• EOMONTH, EDATE, DATEVALUE
• IF, AND, OR, and combinations thereof
• SUMIF, SUMIFS
• COUNTIF, COUNTIFS
• VLOOKUP, HLOOKUP
• COLUMNS, ROWS
• IFERROR
• RATE
• PV, FV
• NPV and XNPV
• IRR and XIRR
• PMT, PPMT, IPMT
• CUMPRINC, CUMIPMT

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.

Our approach to help you learn is to isolate individual techniques and tie them to real world applications. Examples are given and then exercises are given for you to complete. Solutions are in the Excel, and are reviewed on the video.

Rest assured that you will work very hard in this course programming dozens of formulas, and you will learn a lot about the real estate business in the process.

The video running time is 3 hours. Watching all the video (replaying parts where desired) and completing the exercises will take approximately 4 to 6 hours, depending on the individual.

The sample questions and 35-question Certification exam will take approximately an additional 2 hours to complete.

This course will prepare you to pass the associated 35-question Certification exam, which is available online 24/7/365, open book, and may be completed in more than one sitting. The exam has questions similar to these sample questions.

Passing the exam requires a score of 70% or better, and passing With Distinction requires a score of 85% or better. You can retake the exam if you are unsatisfied with your score.

Upon passing, you will be listed automatically on the Certification Holders page.

Upon successful completion of this course, you will:

• Have built confidence in your ability to apply the most commonly-used set of formulas, functions and techniques for CRE Excel-based modeling
• Be able to understand how outputs are generated on any spreadsheet you come across
• Be ready to build upon the technical foundations you have put in place

• Keyboard Shortcuts
• Spreadsheet and Formula Basics
• Cell Formatting
• Arithmetic
• Statistics
• Relative vs. Absolute References
• Insert and Delete Rows and Columns
• Dates
• SUMIF and COUNTIF
• Paste Special, Transpose
• VLOOKUP and HLOOKUP
• Conditional Statements
• Conditional Statements Solution
• Conditional Formatting
• TVM and Financial Functions
• Financial Functions Exercise
• Mortgage Payment
• Mortgage Payment Exercise
• Mortgage Payment Exercise Solution
• CUMPRINC and CUMIPMT
• Formula Auditing
• Data Source
• Data Tables
• Circular References
• Best Practices

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

In this approximately 2-hour course, you will learn about the foundations of real estate finance as well as advanced techniques and topics. You will follow along in Excel in real time and perform exercises to ensure you are grasping the lesson and are mastering the technical skills being taught.

• The Time Value of Money Model
• The Discounted Cash Flow Model
• Net Present Value
• Residential Property Refinancing Feasibility Case Study
• Back of the Envelope Residual Land Valuation of Development Sites
• Acquisition and Development Transaction Capital Structuring
• Senior Construction Loan Mechanics
• Multi-Party Transaction Equity Structures
• Senior and Mezzanine Loan Funding and Repayment Dynamics
• Staggered and Pari Passu Equity Player Funding
• Maximum Refinance Loan Amount Calculation
• Equity Player Profit Sharing Introduction
• Preferred Return, Internal Rate of Return Waterfall and Promote Theory Basics

• Compounding and Discounting (FV, PV)
• Net Present Value (NPV)
• Conditional Statements (IF, AND)
• Cumulative Principal (CUMPRINC)
• Minimum (MIN)

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.

Our approach to help you learn is to walk through a series of mini case studies that illustrate key dynamics of real estate finance. In the interest of getting you to learn efficiently in your limited study time, we give you “skeletons” of the analysis set-ups and teach from that point of progress forward. You are of course welcome to re-build the spreadsheets from scratch at any time.

Rest assured that you will work very hard in this course, and you will learn a tremendous amount about real estate finance and financial modeling in the process.

The video running time is 1 hour 48 minutes. Watching all the video (replaying parts where desired) and completing the exercises will take approximately 3 to 5 hours, depending on the individual.

The sample questions and 39-question Certification exam will take approximately an additional 2 hours to complete.

This course will prepare you to pass the associated 39-question Certification exam, which is available online 24/7/365, open book, and may be completed in more than one sitting. The exam has questions similar to these sample questions.

Passing the exam requires a score of 70% or better, and passing With Distinction requires a score of 85% or better. You can retake the exam if you are unsatisfied with your score.

Upon passing, you will be listed automatically on the Certification Holders page.

Upon successful completion of this course, you will:

• Have gained a critical understanding of basic and intermediate capital structuring for real estate transactions
• Be able to project property cash flows, and discount them at both constant and variable discount rates to solve for the NPV
• Be able to back-solve for the residual land value of a site for a potential office building development
• Be able to model the funding and repayment of up to 5 equity and debt layers in a development transaction analysis context, and size a refinancing loan on an existing owned property

• TVM Timeline
• DCF Timeline
• DCF Exercise
• DCF Solution
• Residential Refi 1 – Exercise
• Residential Refi 1 – Solution
• Residential Refi 2 – Exercise
• Residential Refi 2 – Solution
• Residual Land Valuation Overview
• Office Industrial Development Back of the Envelope Exercise
• Office Industrial Development Back of the Envelope Solution
• Capital Structure 1
• Capital Structure 2
• Capital Structure 3
• Capital Structure 4
• Senior Construction Loan Overview
• Equity Exercise
• Equity Solution
• Mezzanine Loan Exercise
• Mezzanine Loan Solution
• Pari Passu Exercise
• Pari Passu Solution
• Maximum Loan Amount Exercise
• Maximum Loan Amount Solution
• Profit Sharing
• Waterfall #1 Graphs
• Double Promote
• Waterfall #2 Graphs
• Keyboard Shortcuts

In this 3-lesson course from Real Estate Financial Modeling, you will learn about the customary “dollars in” and “dollars out” dynamics of single transaction commercial real estate equity joint venture (JV) partnerships (not multi-transaction private equity funds), and the important rationales that underpin these dynamics.

Specifically, you will model:

• non-compounded and a compounded cumulative pari-passu preferred returns, with pari-passu residual cash flow splits (i.e., a 2-tier waterfall)
• a 3-tier IRR hurdle-based waterfall with residual splits including a sliding scale-based set of sponsor promotes
• 2 interlocking 5-tier waterfalls with unique sets of IRR-based hurdles and sponsor promotes (a double-promote structure).

Where applicable, deal-level levered cash flows are kept constant to allow for comparative analysis across JV structures.

This course builds upon the content in REFM’s Level 1 and  2 Bootcamps.

1. What is a joint venture and why does it exist?

2. Review of what happens on the cash flow tab

3. Dollars in considerations

4. Dollars out considerations

5. Rationale behind targeting “disproportionate financial reward” to the sponsor and how to achieve it

6. Trade-off for disproportionate financial reward / Preferred return

7. Residual cash flow splitting methods

8. Multi-tier waterfall structures

9. Which IRR serves as the trigger?

10. Sponsor promotes, and ambiguity of definition of the promote

11. How to isolate base and incremental dollars within an IRR

12. What if there are 3 equity players? / Double-promote structure

13. Common mistakes when modeling waterfalls

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 3 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 spreadsheets 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 skeletons 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 skeletons, and you will learn a tremendous amount about real estate and financial modeling in the process.

The video running time is 3.3 hours across the 3 lessons. Watching all the video (replaying parts where desired) and completing the exercises will take approximately 5 to 7 hours, depending on the individual.

The sample questions and 27-question Certification exam will take approximately an additional 2 hours to complete.

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:

1. Always set the stage by providing the big picture first, and keep it at the fore with graphs and sparklines
2. Speak in plain language and define industry terms on an ongoing basis
3. Give you the head start of having a model skeleton in which to learn
4. Provide a magnified formula bar so you don’t have to strain your eyes to see formula contents
5. Draw with “ink” on the worksheets to help you follow the lesson and grasp important points
6. Give you highly granular video chapter hyperlinks so you can find what you want quickly and easily.

This course will prepare you to pass the associated 27-question Certification exam, which is available online 24/7/365, open book, and may be completed in more than one sitting. The exam has questions similar to these sample questions.

Passing the exam requires a score of 70% or better, and passing With Distinction requires a score of 85% or better. You can retake the exam if you are unsatisfied with your score.

Upon passing, you will be listed automatically on the Certification Holders page.

Upon successful completion of this course, you will:

• Have gained a critical understanding of the business conventions that underpin the modeling of equity joint venture investment cash flow waterfalls
• Be able to distinguish between the various structural elements in waterfalls that are new to you
• Be able to model up to 5 waterfall tiers using IRR-based hurdles at a compounding frequency of daily, monthly, quarterly or annually, and be able to model a double-promote structure.

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

1. Hyperlinked Table of Contents
2. Preferred Return Overview
3. Non-compounded Pari-Passu Preferred Return Exercise
4. Non-compounded Pari-Passu Preferred Return Solution
5. Compounded Pari-Passu Preferred Return Exercise
6. Compounded Pari-Passu Preferred Return Solution
7. Compounded Pari-Passu Preferred Return Solution – Monthly
8. Compounded Pari-Passu Preferred Return Solution – Monthly vs Annual
9. IRR Explanation
10. IRR Dollar Component Calculations 1
11. IRR Dollar Component Calculations 2
12. 3-Tier Waterfall with Promote Exercise
13. 3-Tier Waterfall with Promote Solution
14. 3-Tier Waterfall with Promote With Refi Solution
15. Double Promote Diagram
16. Double-Promote Waterfall #1 Exercise
17. Double-Promote Waterfall #1 Solution
18. Double-Promote Waterfall #2
19. Double-Promote Returns Exhibit
20. Alternate Compounding Periods
21. Partnership Structure 1
22. Partnership Structure 2
23. Partnership Structure 3

In this lesson, you will learn the critical foundations to understanding the basis of waterfall modeling, and proceed to model two different Preferred Return waterfall structures, both of which are Pari-Passu.

Video chapter markers:

• Agenda
• Spreadsheet formatting notes
• Big picture: spreadsheet tab relationships
• What happens on the cash flow tab
• If it’s a unit-sales project
• Big picture: spreadsheet tab relationships revisited
• Big picture: a single investor
• Big picture: two investors
• Multiple investor cash flow 3-tier waterfall
• JV partnerships: multiple parties teaming up
• Joint venture partnership cash flow splitting
• Dollars in considerations
• Dollars out considerations
• Disproportionate targeted reward to the sponsor is typical
• To motivate the creation of a larger pie for all
• How to drive the disproportionate financial reward to sponsor
• Trade-offs for disproportionate financial reward
• Variations of priority of distributions
• Which priority of payments is best?
• Preferred return distributions in context
• Tab 2 Pref Non-Compounded Exercise overview
• Equity investment and sponsor portion
• Annual preferred return (non-compounded)
• Combined invested capital account balance
• Review of non-compounded but cumulative waterfall structure
• Calculated preferred return (accrual)
• Preferred return distributions to sponsor and investor
• Review of progress
• Cash flow available for distribution types B and C
• Tab 4 Pref Compounded Exercise overview
• Annual preferred return (compounded)
• Investor capital invested
• Preferred return interest accrual
• Invested capital balance
• Interest account balance
• Payment of accrued interest
• Repayment of invested capital
• Check on IRR
• Sponsor capital invested
• Preferred return interest accrual
• Payment of accrued interest
• Repayment of invested capital
• Invested capital balance
• Interest account balance
• Review of progress
• Residual cash flow for distribution
• Comparison of non-compounding and compounding
• Summary of cash flows and returns through Distribution Type B
• Investor summary
• Investor investment
• Gross distributions
• Net cash flow
• Gross distributions total
• Investor investment total
• Investor IRR
• Investor Multiple on equity
• Investor Share of profits
• Sponsor equity investment
• Gross distributions
• Net cash flow
• Gross distributions total
• Sponsor investment total
• Sponsor IRR
• Sponsor Multiple on equity
• Sponsor Share of profits
• If residual profits were distributed pari passu
• Investor net cash flow
• Sponsor net cash flow
• Deal summary
• Total investment
• Levered cash flow
• IRR
• Multiple on equity
• Review of graphs
• Tab 6 Pref compounded solution monthly overview
• Monthly IRR and accruals
• IRR Check
• Review of graphs
• Tab 7 Monthly vs Annual Preferred Return Calculation overview
• Analysis of differences

In this lesson, you will expand on the fundamentals that were put in place in Lesson 1, integrating promoted interest dynamics (the “promote”) to make possible a disproportionate sharing of profits based on cash flow performance.

Video chapter markers:

• Review of concepts covered
• Purpose of Distribution Type C
• Residual cash flow splitting methods
• Waterfall tiers
• Which IRR serves as the trigger?
• Sponsor Promotes
• Ambiguity in definition of Promote
• Is there a promote in Tier 1?
• How do you isolate cash flows within a tier?
• Isolating base and incremental dollars within IRR
• How to think of the reported IRR
• Example of transaction interim and final IRR achieved
• Example of interim and final multiple achieved
• Isolating base and incremental dollars within IRR
• IRR unique dollar component calculations
• Tab 9 IRR Dollar Component Calcs 1
• Tab 10 IRR Dollar Component Calcs 2
• Tab 11 3-Tier Waterfall Exer overview
• Highlighting of differences in waterfall format
• Tier 1 Beginning of Period Balance
• Investor Injections,Investor Accruals
• Tier 1 Accrual Distribution
• Tier 1 End of Period Balance
• Investor Cash Flow,Sponsor Equity Cash Flow
• Tier 1 Remaining Cash to Distribute
• Investor IRR Check
• Lack of display of Sponsor investment cash flow
• Tier 2 overview
• Changing of inputs as a learning method
• Tier 2 Beginning of Period Balance
• Investor Injections
• Investor Accruals
• Tier 1 Accrual Distribution
• Tier 2 Accrual Distribution
• Tier 2 End of Period Balance
• Investor cash flow
• Sponsor Equity Cash Flow
• Sponsor Promote Cash Flow
• Tier 2 Remaining Cash to Distribute
• Tier 3 overview
• Tier 3 Investor Cash Flow
• Tier 3 Sponsor Equity Cash Flow
• Tier 3 Sponsor Promote Cash Flow
• Tier 3 Remaining Cash to Distribute
• Deal Summary
• Investor Gross Distributions
• Comparison of Deal and Investor returns
• Sponsor Summary,Sponsor Investment
• Sponsor Equity Cash Flow
• Sponsor Promote Cash Flow
• Sponsor Net Cash Flow
• Cash Flow Check
• IRR Checks
• Sensitivity Analysis
• Discussion of graphs and relative performance of players
• Share of Cash Flows by Tier
• Tab 13 3-Tier Waterfall Sol w Refi overview
• Refi Impact on returns
• Accruals and Tier 1 Remaining Cash to Distribute summaries
• Refi Repayment of All Investment

In this lesson, you will learn about how to model two unique interlocking waterfall structures among 3 equity entities, review frequency of compounding, and see some sample structure set-ups that you can re-purpose.

Video chapter markers:

• What if there are 3 equity players?
• Top-level sponsor example
• Teaming up with third party capital
• How $20MM in equity might break out
• Double-promote structure for 3 equity players
• Tab 15 D-P Waterfall #1 Exer overview
• Tier 1 review
• Tier 2 review
• Tier 3 overview
• Beginning of Period Balance
• Investor Injections
• Investor Accruals
• Tier 1 and 2 Accrual Distributions
• Tier 3 Accrual Distribution
• End of Period Balance
• Tier 3 Investor Cash Flow
• Sponsor Equity Cash Flow
• Sponsor Promote Cash Flow
• Tier 3 Remaining Cash to Distribute
• Tier 4 Beginning of Period Balance
• Investor Injections
• Investor Accruals
• Tier 1 and 2 and 3 Accrual Distributions
• Tier 4 Accrual Distribution
• Tier 4 End of Period Balance
• Investor Cash Flow
• Sponsor Equity Cash Flow
• Sponsor Promote Cash Flow
• Tier 4 Remaining Cash to Distribute
• Tier 5 Cash Flows
• Tier 5 Remaining Cash to Distribute
• Deal Cash Flow Check
• IRR Checks
• Review of returns graphs
• Share of Cash Flows by Tier
• Tab 14 Double Promote Diagram tab revisited
• Why 2 or 3 or 5 Tiers?
• Tab 17 DP Waterfall #2 Exercise overview
• Differences vs Waterfall #1
• Equity Investment Connection to Waterfall #1
• Top-Level Sponsor Investment Connection to Waterfall #1
• Cash Flow to Top-Level Sponsor Entity from Waterfall #1
• Promote to Top-Level Sponsor Entity from Waterfall #1
• Partner Tier 1 Accrual Distribution
• Review of returns across all entities
• Review of returns graphs for Waterfall #2 players
• Tab 19 DP Returns Exhibit
• Tab 20 Alternate Compounding Frequencies
• Monthly Compounding
• Daily Compounding
• Quarterly Compounding
• Sample Partnership Structure Set-Ups

In this 5-lesson course, 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.

1. What is a joint venture and why does it exist?

2. Review of what happens on the cash flow tab

3. Dollars in considerations

4. Dollars out considerations

5. Rationale behind targeting “disproportionate financial reward” to the sponsor and how to achieve it

6. Trade-off for disproportionate financial reward / Preferred return

7. Residual cash flow splitting methods

8. Multi-tier waterfall structures

• 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.

10. Sponsor promotes, and ambiguity of definition of the promote

11. How to isolate base and incremental dollars within an IRR

12. What if there are 3 equity players? / Double-promote structure

13. Common mistakes when modeling waterfalls

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.

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.

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

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

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

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

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

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.

In this course, you will learn the industry-standard operating projection line item set-up structure for commercial office properties, how to project annual operating cash flows, and how to screen the acquisition of a property on a levered basis given an assumed purchase price and targeted returns levels.

In the first part of the tutorial you will build out the operating projection. Projection formula mathematical descriptions are provided to guide your Excel formula construction. In the second part of the tutorial you will weave the NOI line into an acquisition analysis screening tab that integrates purchase, sale, debt and equity elements.

The two senior debt financing options modeled are an amortizing loan without any future funding amounts available for the Year 1-2 tenant improvements and leasing commissions related to Suite 300, and an interest-only loan with delayed draw capacity for the near-term capital costs.

1. The nature of all income and expense line items from Gross Potential Rent (GPR) through Before-Tax Levered Cash Flow

2. Lease types and reimbursement dynamics

3. Projection formula modeling

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 extend beyond the analysis end date, and that Suite 300, which comprises 36% of the rentable SF, is currently vacant 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, and its lease will extend beyond the projection analysis end date. Suite 100 is a triple-net (NNN) lease, and Suites 200 and 300 are Gross leases (Base Year expense stop).

The sections of the acquisition analysis are:

• uses of funds
• sources of funds
  • sponsor equity
  • third party investor equity
  • senior acquisition loan
• unlevered cash flow projection
• debt schedule
• levered cash flow projection
• sensitivity tables.

The resulting unlevered and levered cash flow returns metrics are net cash flow, IRR, multiple on equity and NPV, allowing you to assess the valuation of the property. In addition, a levered summary table is constructed to evaluate returns for multiple hold durations side-by-side.

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 throughout the course.

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 1.5 hours. Watching all the video (replaying parts where desired) and completing the exercises will take approximately 4 to 6 hours, depending on the individual.

Upon successful completion of this course, you will:

• Have gained a holistic understanding of the industry-standard office building operating projection statement
• Be able to model reimbursements for both full service and NNN leases
• Be able to model an acquisition loan with and without future funding capacity, and back-solve to targeted investment returns to the equity position.

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

1. Office Cash Flow Set-Up
2. Projection Formulas
3. Office Exercise
4. Office Solution
5. Screening Analysis Exercise
6. Screening Analysis Solution