Real Estate Financial Modeling Tools

Mixed-Use Condominium Building Development Financial Modeling Self-Study Video Tutorial with Excel File – UCLA Anderson Real Estate Assocation




SKU: SKU-804 Category:

Product Overview
An interactive Video Tutorial (click  here to download and play with a 3-minute sample) that teaches you how to efficiently model the ground-up development of a residential condominium building with ground-floor retail and income-producing parking. Includes fully-assembled, fully-unlocked Excel file with 12 hands-on Tutorial exercises and accompanying solutions.
What’s Included
With this product, you will receive the following items:

Easily navigated chaptered 65-minute Video Tutorial instant download, which is played within your web browser (compatible with Apple Safari in addition to Internet Explorer and Mozilla Firefox)

Chapter Navigation Controller
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
And more!
Video Tutorial Marker Titles and Durations

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)


Registration is open for April Excel Bootcamp Webinars Register Now
left to take 50% off with coupon code: yearend50