0% Complete
0/14 Steps

Preview of Mixed-Use For Sale Condominium Building Development Modeling Tutorial

Lesson
Materials

Overview

In this lesson, you will learn how to model a for-sale residential condominium building with ground-floor retail and for-sale parking.

Instructions

Download the Excel from the Materials tab above. Then watch the videos, and follow along and perform the exercises in the Excel.

Video Sample

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)

REFM_Mixed_Use_Condominium__Development_Tutorial_v16.0.xlsm