An interactive Video Tutorial that teaches you how to efficiently model the ground-up development of a for-sale residential condominium building with ground-floor retail and for-sale parking. Includes fully-assembled, fully-unlocked Excel file with 12 hands-on Tutorial exercises and accompanying solutions.
Product includes the following items, which never expire:
Online access to an easily navigated 65-minute Video Tutorial, playable on any device including PC, Mac, phones and tablets
Accompanying fully-unlocked Excel file, which you can repurpose and customize for future analyses.
Excel file includes the following tabs:
- Profile, Lot and Building Information
- Stacking Plan
- Sources and Uses Overview
- Cost Inputs & Capital Structure
- Sources and Uses – Exercise
- Sources and Uses – Solution
- Construction Bell-Shaped Curve
- Unit Mix and Pricing – Exercise
- Unit Mix and Pricing – Solution
- Cash Flow Overview
- Cash Flows – Exercise
- Cash Flows – Solution
What You Will Learn
A mastery of Excel-based technical financial projection modeling skills for the projection modeling of a mixed-use residential condominium building with ground-floor retail and income-producing parking. A strong emphasis is placed on leveraging the power of conditional (“If, Then”) statement formulas.
Topics covered include the modeling of:
- Site and building profile for above- and below-grade project elements
- Uses and Sources of Funds without creating potentially dangerous circular references
- Dynamic land purchase closing
- Dynamic base building hard costs allocation from a bell-shaped curve lookup table
- Modeling multiple retail tenants
- Three equity sources, mezzanine loan, and senior construction loan
- Weighted average unit pricing for Market, Pre-Sales and Affordable units
- Optimizing unit sizes to consume all available FAR
- Dynamic unit absorption for pre-sales and regular sales
- Sale of retail condominium unit
Video Tutorial Contents
- Introduction
- Profile, Lot & Building Information (7 mins)
- Stacking Plan (1 min)
- Sources & Uses Overview (2 mins)
- Cost Inputs & Capital Structure (Section)
- Circular References (2.5 min)
- Stating Equity and Mezzanine Dollar Amounts (1 min)
- Sources & Uses Exercise Tab (Section)
- Control Panel & Analysis Timeline (1 min)
- Certificate of Occupancy (C of O) (1.5 min)
- Retail Component Timing (40 sec)
- Dynamic Timeline (40 sec)
- Elapsed Month # (5 sec)
- Construction Month # (10 sec)
- Construction Status (15 sec)
- Uses of Funds (Section)
- Basic Setup & Customization (30 sec)
- Land & Acquisition Costs (20 sec)
- Exercise #1 (30 sec)
- Exercise #1 Solution (1 min)
- Hard Costs & Contingency and VLOOKUP Function (1.5 min)
- Construction Bell-Shape Curve Tab (3 min)
- Retail Tenant Improvements (TIs) (1 min)
- Exercise #2 (30 sec)
- Exercise #2 Solution (1.5 min)
- Soft Costs (1 min)
- Furnishings, Fixtures & Equipment (FF&E) (1 min)
- Subtotal Eligible Senior Loan Costs (15 sec)
- Financing Costs Overview (20 sec)
- Total Development Costs (20 sec)
- Sources of Funds (Section)
- Developer Sponsor Equity Direct Project Costs Draw (45 sec)
- Operating & Financing Deficits (30 sec)
- Developer Partner Equity Priority (10 sec)
- Developer Partner Equity Direct Project Costs Draw (40 sec)
- Third Party Investor Equity Priority (10 sec)
- Third Party Investor Direct Project Costs Draw (30 sec)
- Total Equity Investment (5 sec)
- Mezzanine Loan Overview (10 sec)
- Mezzanine Loan Direct Project Costs Draw (20 sec)
- Mezzanine Loan Origination Costs (10 sec)
- Mezzanine Loan Cash Interest Payments (10 sec
- Mezzanine Loan Repayment (1 min)
- Mezzanine Loan End of Period Balance (10 sec)
- Senior Loan Overview (5 sec)
- Senior Loan Direct Project Costs Draw (1.5 min)
- Senior Loan Fees (10 sec)
- Senior Loan Funded Interest Reserve and Cash Interest Payments (1.5 min)
- Senior Loan Repayment (20 sec)
- Senior Loan Ending Balance (10 sec)
- Property Cash Flow As Source Of Funds (1.5 min)
- Operating & Financing Deficits Combined (1 min)
- Interest Rate Sensitivity Analysis (Section)
- Resizing The Senior Loan With Goal Seek (2 min)
- Creating A Macro To Run Goal Seek (3 min)
- Exercise #3
- Exercise #3 Solution
- Unit Mix and Pricing Exercise Tab (Section)
- Exercises #4, 5, 6, 7 (30 sec)
- Exercises #4, 5, 6, 7 Solutions (1.5 min)
- Cash Flow Overview Tab (40 sec)
- Cash Flows Exercise Tab (Section)
- Cash Flow Assumptions (40 sec)
- Pre-Sales (20 sec)
- Regular Market Rate Units (20 sec)
- Affordable Units (20 sec)
- Exercise #8 (45 sec)
- Exercise #8 Solution (2.5 min)
- Parking and Storage Sales (15 sec)
- Revenues Overview (20 sec)
- Exercise #9 (20 sec)
- Exercise #9 Solution (20 sec)
- Retail Net Operating Income (NOI) (20 sec)
- Gross Revenue & Selling Costs (10 sec)
- Net Revenues (10 sec)
- Operating Expenses & Real Estate Taxes (30 sec)
- Exercise #10 (5 sec)
- Exercise #10 Solution (1.5 min)
- Exercise #11 (10 sec)
- Exercise #11 Solution (1 min)
- Net Revenue After Expenses and Retail Condominium Sale Before Financing Costs (15 sec)
- Unlevered Cash Flow (45 sec)
- Net Revenue Before Retail Sale After Financing Costs (15 sec)
- Exercise #12 (15 sec)
- Exercise #12 Solution (20 sec)
- Net Revenue After Retail Sale After Financing Costs (10 sec)
- Levered Cash Flow (45 sec)
- The Transaction Story Revisited (40 sec)
- Net Levered Cash Flow (Pre-Income Tax Profit) (10 sec)
- Investment Performance Metrics (10 sec)