Description
This Course Is For You If You Want To
This is a good fit if you want to:
- Learn the commercial office building 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
Details
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.
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.
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 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.
What You Get
Lifetime online access to:
- An easily-navigated 1.5-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
Excel File Tabs
The Excel file is 100% unlocked, and you can re-purpose the tabs and comprising code as you desire.
- Office Cash Flow Set-Up
- Projection Formulas
- Office Exercise
- Office Solution
- Screening Analysis Exercise
- Screening Analysis Solution
Video Chapter Markers
- Office Cashflow Set-up tab
- Overview
- Base Rental Revenue
- Absorption & Turnover Vacancy
- Base Rent Abatements
- Expense Reimbursement Revenue
- Scheduled Base Rental Revenue
- Parking and Miscellaneous Revenue
- Potential Gross Revenue
- General Vacancy and Credit Loss
- Effective Gross Revenue
- Operating Expenses and RE Taxes
- Net Operating Income
- Leasing & Capital Costs
- Tenant Improvements
- Leasing Commissions
- Capital Reserves/Replacement Reserves and Capital Expenditures
- Total Leasing & Capital Costs
- Operating Cash Flow
- Unlevered Cash Flow
- Loan Points
- Debt Service Expense
- Before-Tax Levered Cash Flow
- Projection Formulas Syntax
- Exercise,Solution
- Office Exercise Tab Overview
- Screen set-up
- Input entries and Assumptions
- Lease types
- Assumption inputting completion
- Physical Occupancy
- Formula math guidance
- Suite 100 Base Rental Revenue
- Suite 200/300 Base Rental Revenue
- Absorption and Turnover Vacancy
- Suggested work approach
- Solution
- Absorption and Turnover Vacancy
- Base Rent Abatements
- Expense Reimbursement Suite 100
- Suite 200
- Suite 300
- Real Estate Taxes Suite 100/200/300
- Scheduled Base Rental Revenue
- Parking and Miscellaneous Revenue
- Potential Gross Revenue
- General Vacancy & Credit Loss and Effective Gross Revenue
- Reimbursable Expenses
- Fixed Operating Expenses
- Variable Operating Expenses
- Real Estate Taxes
- Non-Reimbursable Expenses
- Fixed Utilities
- Variable Utilities
- Insurance
- Management Fees
- Total Operating Expenses & RE Taxes
- Net Operating Income
- Leasing & Capital Costs
- Tenant Improvements
- Leasing Commissions
- Capital Reserves/Expenditures
- Operating Cash Flow,
- Screening Analysis Exercise Overview
- Screen set-up
- Effective Gross Income and Net Operating Income
- Asset Management Fee
- Audits/Accounting/Other Partnership Expenses
- Total Leasing & Capital Costs
- Cash Flow Before Debt Service
- Uses of Funds Overview/Year 1 NOI
- Purchase Price
- Going-in Cap Rate
- Price per RSF
- Transaction Costs Inputs
- Closing Costs
- Acquisition Costs
- Total Unlevered Uses for Acquisition
- Loan Fee
- Levered Uses for Acquisition
- Sources of Funds
- Sponsor/Owner Equity %
- Investor Equity %
- Senior Loan %
- Equity %
- Senior Loan $ Amount
- Equity $ Amounts
- Total Sources of Funds
- NOI in Unlevered Projection Analysis table
- Cap Rate
- Cash Flow Before Debt Service and Taxes
- CFBDS YOY Growth
- Unlevered Exercise
- Solution Review
- Purchase Price
- Transaction Costs
- Disposition Net Proceeds
- Unlevered Cash Flow
- Debt Schedule Exercise
- Solution Review
- Interest Rate & Amortization Term
- Beginning Balance
- Draw
- Principal Amortization
- Balloon Repayment
- Ending Balance
- Interest Expense Payment
- Total Payment
- Levered Cash Flow Exercise
- Solution Review
- Net Cash Flow Before Financing Adjustments
- Sponsor Equity Draw
- Investor Equity Draw
- Senior Loan Proceeds
- Senior Loan Balloon Repayment
- Net Levered Cash Flow & Equity Multiple
- Cash-on-Cash
- DSCR
- Levered IRR
- Net Present Value
- Sensitivity Tables
- Levered Summary table
- Sale in Year 3 Net Proceeds After Debt Repayment
- Review of Returns.
