REFM University Curriculum Supplement Program

REFM University Curriculum Supplement Program

University curriculum supplement program

 

Why we created the UCSP

University real estate faculty are faced with a monumental task in educating undergraduate and graduate students in the principles and practices of real estate, finance, and financial modeling hard skills.

REFM’s University Curriculum Supplement Program assists by teaching financial modeling through self-study and online testing, which enrich faculty instruction and further promote student development.

The use of the REFM’s self-study courses has been instrumental in getting new students up and running. This course series accelerates their understanding of CRE finance and their preparation for the real world after graduation.
Alex Morcate, Adjunct ProfessorUniversity of Miami School of Architecture

Program Participants

ucsp_collage_2016

Program Details

Video Overview

In many cases, students have little or no real estate or finance knowledge base, nor any of the Excel-based financial modeling skills that are valued so highly by employers. As time during the semester is severely limited, the thorough hands-on teaching of hard financial modeling skills cannot always be accommodated in class. This is where the UCSP adds value.

View the video below to learn more.

Training Purpose and Teaching Format

The purpose of the program is to:

  • Improve student Excel-based problem-solving and analysis skills, resulting in quicker work turnaround and fewer mistakes
  • Enable stronger student performance on projects, assignments, and in interviews
  • Increase student confidence in advanced sensitivity analysis abilities
  • Improve student financial modeling knowledge, understanding and customization capabilities
  • Sharpen student presentation of transaction financials
  • Heighten student credibility with and marketability to potential employers and advanced degree-granting programs

Teaching and Learning Format

The teaching and learning are intended to be a supplement to, not a replacement for, faculty-taught content. Engaging in the work is to occur on the student’s own time, at their own pace, outside of class.

REFM recognizes that there can never be a substitute for constructing financial models from scratch, and recommends that all students replicate as many of the program models as they can starting from a blank spreadsheet.

The minimum suggested engagement level for students is as follows:

  • Student accesses program content via a university-branded portal webpage
  • Student plays video-based tutorials (samples below) on their computer, following along in the accompanying unlocked Excel file
  • Student performs hands-on exercises in the Excel file, and reviews solutions
  • Student takes Completion Confirmation exams online on their own schedule.

Program Materials

The materials contained in the full program include:

  • Over 16 hours of detailed online video instruction across 13 content modules
  • 13 accompanying unlocked Excel files with hands-on exercises and documented solutions
  • 3 online Completion Certification exams, which bestow REFM Certification In Excel For Real Estate

000_ucspSpreadSample

Authorship

The University Curriculum Supplement Program’s training and testing content is created by REFM founder Bruce Kirsch, who is the Editor of the Third and Fourth Editions of Real Estate Finance and Investments: Risks and Opportunities by The Wharton School’s Emeritus Professor of Real Estate, Dr. Peter Linneman. Mr. Kirsch has been creating self-study financial modeling training modules for executives and students since 2009, and he personally trains executives at some of the world’s largest institutional real estate investors.

Mr. Kirsch was previously a three-time Adjunct Faculty member in Real Estate at Georgetown University. He holds an MBA in Real Estate from The Wharton School, where he received a scholarship for academic excellence in real estate, and he holds a BA in Communication from Stanford University. Prior to founding REFM, Mr. Kirsch worked in analytical roles for two top real estate platforms and a global fund manager.

BruceTeaching400pxLinnemanTextCoverThirdEd(2)

Testing and Certification

All self-study modules contain rigorous Excel-based hands-on exercises and solutions, so there is in effect continuous testing integrated into the program.

In addition, there are three formal online Completion Certification exams that the students may take at any time. These exams bestow REFM Certification In Excel For Real Estate. Scoring is automatically, instantaneously reported back to the students, and it can be reported back to the university faculty if desired. A sample test question appears below.

Level3TestImage_2

Body of Knowledge

The program body of knowledge comprises how to apply Excel functions, construct formulas, and build and operate advanced transaction and joint venture partnership financial projection models. The program also includes three online Completion Confirmation tests that students may take at any time.

Detailed module descriptions are found below. The three main categories of content are:

  • Excel and real estate finance modeling foundations
  • Modeling development and acquisition transactions
  • Modeling joint ventures and private equity funds

000_UCSPCollage

Excel basics

This module is for those with little or no prior exposure to Microsoft Excel. Topics covered include:

  • What Excel is, and what versions are in use
  • Launching Excel and saving a new file
  • The spreadsheet grid and worksheets
  • Basic formatting
  • Basic interface navigation using the ribbon tabs
  • Saving alternate file versions
  • Printing

Excel for real estate bootcamp

This module teaches the basics of operating in Excel, as well as advanced techniques, specifically as they relate to real estate analysis. All topics include exercises. Topics covered include:

  • Arithmetic and Rounding
  • Statistics
  • Relative vs. Absolute Cell References
  • Inserting and Deleting Rows and Columns
  • Dates and Timelines
  • Conditional Statements
  • Function Wizard
  • Creating Summary Tables
  • Formatting Numbers
  • Conditional Cell Formatting
  • Data Table Lookup Functions
  • Custom Formatting
  • Format Painting
  • Paste Special
  • Transpose
  • Financial Functions for Compounding and Discounting
  • Internal Rate of Return (IRR)
  • Mortgage Payment Calculation
  • Formula Auditing
  • Data Tables for Sensitivity Analysis
  • Circular References
  • Keyboard Shortcuts
  • Best Practices

 

Truly understanding IRR

In this module, students will come to truly understand the nature of the IRR calculation and see why it reacts the way it does in the various acquisition and development cases that are presented in spreadsheet form. Topics covered include:

  • Time value of money crash course: Present Value, Future Value and Net Present Value
  • The nature of the IRR calculation, and how the IRR value changes during a transaction
  • Equivalent Annual IRR rates for quarterly-, monthly-, and daily-based IRR calculations
  • Why a monthly-based IRR is more accurate than an annual-based IRR
  • Relationship between the IRR and the Equity Multiple
  • What IRR tells us, and what it doesn’t tell us
  • IRR for acquisitions and development examples

 

Excel functions taught include:

  • PV – Present Value
  • FV – Future Value
  • NPV – Net Present Value
  • IRR – Internal Rate of Return
  • XNPV – NPV for non-periodic cash flows with known dates
  • XIRR – IRR for non-periodic cash flows with known dates

 

Truly understanding cap rates

In this module, students learn the answers to the following questions:

    • What is a capitalization (cap) rate?
    • What composes a cap rate?
    • Why do cap rates change?
    • How do you calculate a cap rate?
    • How do you select one when performing a property valuation?
    • What should you think when people quote cap rates verbally?
    • Where can I find cap rates for a certain property in a certain geography?

 

Residual land valuation

In this module, student learn the answers the question: how does a real estate developer know what to pay for a piece of developable land?

Students will learn the basics of real estate development residual land valuation for both income-producing assets and unit sales assets, as well as the principles of valuation through comparable sales (comps).

Topics covered include

Residual Land Valuation Basics:

  • Allowable Building Density/FAR
  • Ground-Floor Retail Square Footage
  • Primary Building Use Gross and Net Square Footages
  • Calculation of Building Parking Requirements
  • Development Cost Excluding Land
  • Sources of Funds For Development

Specific To Income-Producing Assets (Office/Industrial/Retail, or Apartments):

  • Stabilized Net Operating Income From Primary Use and Parking
  • Future Stabilized Yield On Cost
  • Solving for Residual Land Value


Specific To Unit Sales Assets (Condominiums or Housing Subdivisions):

  • Net Sales Proceeds From Housing Units, Parking & Storage
  • Gross Valuation and Net Sales Proceeds of Retail Component
  • Solving for Residual Land Value

 

Real estate finance bootcamp

In this module, students learn the foundations of real estate finance as well as advanced techniques and topics. Topics covered include:

  • The Time Value of Money Model (includes Exercise #1)
  • The Discounted Cash Flow Model (includes Exercise #2)
  • Internal Rate of Return (IRR)
  • Mortgage Payment Calculation
  • Refinancing/Interest-Only Scenario Payment Calculation (includes Exercise #3)
  • Refinancing Case Study
  • Maximum Loan Amount Calculation (for income-producing property acquisition)
  • Residual Land Valuation (includes Exercise #4)
  • Future Net Operating Income Calculation
  • Transaction Capital Structures
  • Financing Development Transactions
  • Multiple Equity Investors Discussion (includes Exercise #5)
  • Profit Sharing Discussion
  • Preferred Return, Internal Rate of Return Waterfall and Promote theory

 

Mixed-use development transaction financial modeling

The developer’s analysis is conducted assuming a mixed-use prototype with ground floor retail and income-producing parking for multi-family, office/industrial and condominiums/housing subdivisions. All topics contain exercises. Topics covered include:

  • Site and building information
  • Rentable/salable square footage allocations across uses
  • Project timing elements
  • Capital structure
  • Uses and sources of funds
  • Cash flows and returns

 

Single transaction equity joint venture partnership waterfall modeling

In this module, students learn advanced techniques and topics related to joint venture partnerships and investment waterfall modeling for single property transactions. Topics covered include:

  • Rationale behind targeting disproportionate returns to the Sponsor
  • How to achieve disproportionate returns through fees and partitioning of cash flows
  • Preferred Return overview
  • Preferred Return variations with respect to priority of payment
  • Preferred Return in context (Payment Types A, B and C)
  • Nature of Preferred Return (Non-compounded/compounded, non-cumulative/cumulative)
  • Annual Preferred Return Exercise
  • Monthly Preferred Return Exercise
  • Waterfall Distribution overview, with Animation
  • Promote Mechanism overview and modeling
  • Look-Back Internal Rate of Return (IRR) Method
  • 3-Tier Waterfall modeling
  • Double-Promote, 5-Tier Waterfall modeling
  • Double-Promote Exercise
  • Alternate Compounding Periods: Monthly, Daily, Quarterly
  • Sample Partnership Structures

 

Private equity fund modeling

In this module, students will learn big picture real estate private equity fund basics, the general legal structure of funds based in the U.S., how to set up a fund-level model, and considerations for running sensitivity analyses. A property-level partnership analysis is also addressed. Topics covered include:

  • Capital sources
  • Major types of funds
  • General legal structure
  • Rationale behind disproportionate reward to the General Partner/Fund Manager through a promote structure
  • Modeling and then replicating a prototypical transaction starting at Net Operating Income and ending at Levered Cash Flow
  • Weaving in and rolling up and modeling multiple transactions on the fund-level
  • Considerations for running sensitivities
  • Individual transaction typical partner joint venture structure and modeling

 

Ready to get more information?

Register now to get FREE e-Books, Excel tools and access to Valuate software REGISTER
left to take 50% off with coupon code: yearend50