Description
This Product Is For You If You Want To
This is a good fit if you want to:
- Learn about the intricacies of the value add business model such that you can simulate it in an Excel-based projection model whose programming 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 analysis template from scratch
- Get lifetime access to two unlocked Excel-based tools that will help you analyze real transactions rapidly and in detail
Tutorial Sample Video
Details
In this 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 acquisition and capital improvement of an existing operating apartment building. To facilitate learning, a case study is used of a 120-unit multi-family asset.
To set the proper knowledge foundation, you will first learn in depth about the value add business model and the analytical approach to be taken in the Excel-based analysis.
Next, you will transition to the Excel and learn how a unit-by-unit rent roll report can be rolled up into a unit mix summary table.
With transaction base case assumptions provided for you, you will then be guided through the programming of individual line item projection formulas on a pre-formatted monthly cash flow tab.
The advanced capital structure you will model includes:
1. sponsor and third party investor equity
2. senior acquisition loan
3. refinancing of senior loan.
- integration of historical property data and existing rent roll into your pro-forma
- modeling of future lease expirations and renewals and the individual unit renovation program
- modeling of operating expense savings gained from the renovation/greening of apartment units
- modeling of acquisition loan financing, residual equity requirement, and permanent take-out loan/refinancing
- investment returns sensitivity analysis around key deal variables.
This is a hands-on-the-keyboard course. You will watch the video and pause it to complete exercises in the associated Excel.
Our approach to help you learn most efficiently 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 quickly 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 4.5 hours. Watching the video and replaying parts where desired and completing the exercises will take approximately 5 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 value add business model and what needs to be reflected in a detailed financial model
- Be able to approach any multi-family renovation investment analysis with a solid analytical framework
- Be confident in your ability to screen prospective acquisition opportunities rapidly to decide which ones warrant deeper study.
What You Get
Lifetime online access to:
- An easily-navigated 4.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 tutorial Excel file, compatible with Excel on both PC and Mac.
- Two downloadable Excel templates for use on the job: a one-tab multi-period annual Back of the Envelope tool, and a presentation version of the 15-tab monthly-based template you learned from in the tutorial.
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
Monthly Template Tabs
The Excel file is 100% unlocked, and you can re-purpose the tabs and comprising code as you desire.
- Cover
- Rent Roll Inputs
- Assumptions Inputs
- Detailed Renovation Budget
- Capital Structure Exhibit
- Monthly Cash Flow
- Annual Cash Flow
- Stabilized Pro-Forma 1-Pager
- Renovation Timing Exhibit
- Partnership Structure Exhibit
- IRR Hurdle-based Waterfall
- JV Returns Summary Exhibit
- Floating Interest Rate Index
- Acquisition Amortization Schedule
- Permanent Loan Amortization Schedule
Video 1 Chapters
1. Introduction
2. Goals of the training
3. Asset potential
4. Value source
5. Value creation opportunities
6. Three potential renovation zones
7. Conceptual renovation budget
8. Trade offs made
9. Unit downtime
10. Analysis approach
11. Making money on the buy
12. Excel workbook tabs overview
13. Model characteristics
14. The hypothetical average unit paradigm
15. NOI comparison
16. Pacing renovations
17. Feasibility and breakeven/ROI
18. Investment returns comparison
19. How to finance the capital investments
20. How to model rent growth
21. How to model operating expense growth
Video 2 Chapters
1. Excel workbook tab set
2. Formatting conventions
3. Rent Roll Inputs tab
4. Studio units inputs
5. Lease expiration schedule
6. Unit mix on Assumptions tab
7. Rent growth and renovation characteristics
8. Construction period
9. Marketing period
10. Average monthly rent premium
11. Annual Rent PSF
12. Post-renovation OpEx savings
13. Renovation cost per unit
14. Detailed Renovation Budget tab
Video 3 Chapters
1. Monthly Cash Flow tab overview
2. Apartment forward year trigger
3. Gross Potential Rent
4. Studio unit type modeling
5. Rent CAGR
6. Studio GPR
7. Units Taken Offline To Be Newly Renovated
8. Renovation-related vacancy
9. Down Month #1
10. Down Month #2
11. Down Month #3
12. Down Month #4
13. Total Unit-Months Offline
14. Vacancy Loss – Renovation/Down Units
15. Total Unit-Months in Construction
16. Renovated Unit Rent Premium
17. Property GPR
18. GPR PSF
19. Units Taken Offline To Be Newly Renovated
20. Vacancy Loss – Renovation/Down Units
21. Renovated Unit Rent Premium
Video 4 Chapters
1. Other Income on Cash Flow tab
2. Other Income on Assumptions tab
3. Parking income
4. Other Income on cash flow tab
5. Loss to Lease
6. Total Minimum Rent
7. Vacancy Loss (systemic)
8. Gross Rent Deductions on Assumptions tab
9. Vacancy Loss (systemic)
10. Non-Revenue Units
11. Rent Concessions
12. Effective Rental Income
13. Write Off
14. Passthroughs
15. Effective Gross Income
Video 5 Chapters
1. Operating Expenses on the Cash Flow tab
2. Operating Expenses on the Assumptions tab
3. Real Estate Taxes
4. Management Fees
5. OpEx projection on Cash Flow tab
6. Total Expenses and RE Taxes
7. New Renovation Savings offsets
8. Replacement Reserves Balance
9. Net Operating Income
Video 6 Chapters
1. Forward NOI
2. Trailing NOI
3. 12th Month Forward NOI Annualized
4. Capital Expenditures
5. CapEx Reserve Draw
6. Common Area Renovation
7. Tenant Buyout
8. Unit Renovation Cost
9. Asset Management Fee
10. Unit Renovation Escrow Budget Balance and Draws
11. Working Capital Escrow Budget and Draws
12. Sweep of Remaining Reserves and Working Capital
13. Operating Cash Flow
Video 7 Chapters
1. Review of renovation pacing
2. Retail Component
3. Retail assumptions on Assumptions tab
4. Lease Calendar Month #
5. NNN Gross Potential Rent
6. Unleased Space OpEx
7. Vacancy, Credit Loss, NOI
8. Reserves
9. Combined Apartment and Retail NOI Cap Rate
10. Unlevered Cash Flow
11. Acquisition
12. Uses of Funds on Assumptions tab
13. Common Area & Unit Renovation Budget
14. Eligible Loan Costs
15. Fees and Taxes
16. Unleveraged Uses
17. Loan Fee
18. Total Leveraged Uses
19. Sources of Funds
20. JV Equity
21. Deficits borne by equity
22. Senior Loan Amount
23. Total Acquisition Sources of Funds
24. Amortization Period
25. Disposition
26. Valuation Basis
27. Adjusted Sale Cap Rate
28. Chapter Title
29. Gross Valuation
30. Tax Abatement introduction
31. Selling Costs
32. Disposition Net Proceeds
33. Net Proceeds on Cash Flow tab
34. Unlevered Returns
Video 8 Chapters
1. Integrating Debt
2. Senior Debt Settings
3. Chatham Financial Forward Rate Data
4. Pasting into Floating Rate tab
5. Senior Amortization Schedule tab
6. Base Variable Rate
7. Fixed Spread
8. 30/360 Floating Rate
9. Actual/360 Floating Rate
10. 365/365 Floating Rate
11. 30/360 Fixed Rate
12. Actual/360 Fixed Rate
13. 365/365 Fixed Rate
14. Amortization Period
15. Beginning Balance
16. Interest Payment
17. Principal Payment
18. Repayment from Permanent Loan
19. Ending Balance
20. Repayment from Residential Sale
21. Repayment from Retail Sale
22. Repayment from Shortfall Draws
Video 9 Chapters
1. Refinancing section on Assumptions tab
2. Permanent loan sizing tests
3. Amount to refinance
4. Permanent Loan Amortization Schedule tab
5. Financing Costs on Cash Flow tab
6. Acquisition Loan Debt Service
7. Permanent Loan Fees/Closing Costs
8. Permanent Loan Debt Service
9. Financing Cash Flows
10. Sponsor Draw
11. Sponsor Deficit Draw
12. Sponsor Refinancing Shortfall Draw
13. Third Party Investor Draw
14. Third Party Investor Deficit Draw
15. Third Party Investor Refinancing Shortfall Draw
16. Acquisition Loan Repayment from Refi or Sale
17. Permanent Loan Proceeds for Loan Repayment
18. Permanent Loan Repayment
19. Levered Cash Flow
20. Annual Cash Flow tab
21. Real Estate Tax Re-assessment calculation at Sale
22. Real Estate Tax Abatement calculation
23. Levered Returns
24. Sensitivity Analysis
