Pro Forma Modeling with Excel Part II: ULI Online Live Workshop

Building on the earlier introductory pro forma modeling course, this four-part online live program will help further build the participants’ financial modeling and Excel skills. Attendees will learn more advanced Excel functions for valuation, amortization, rent roll, construction and permanent financing, construction draws, and cash flows.

Students will receive concise direction in each session. At the end of the last session, attendees will have a complete pro forma model that can be scaled further to include waterfalls, stochastic processes, portfolio analysis, and visual displays of results.

Each session of the workshop is designed to build on the previous one. Participants are expected to use learned skills to build individual sections and maintain the pace of the workshop. However, to avoid the possibility of anyone falling behind, the completed stages will be distributed the evening before each workshop.

Session 1: April 23, 2013 1:30–3:30 p.m. ET

The first session begins with a review of the Introduction to Pro Forma Modeling program and the component structure of a pro forma. This review will include scalability and modularity, how the pro forma is used in analysis, and how the pro forma will be scaled through the four sessions. The goal is to ensure a complete understanding of the 4-unit multifamily pro forma and Excel basics. 

The amortization table will be expanded to include custom amortization as well as a pricing curve for variable interest rates. The model will then be able to amortize using the three main methods: Constant Payment Mortgage, Interest Only, and Custom Amortization.

Session Highlights:
  • Pro Forma overview
  • Advanced MS Excel Functions for Valuation
  • Advanced MS Excel Functions for Amortization
  • Review consistent naming convention
  • Understanding of what and how the model will be scaled and built upon over the next three sessions

Session 2: April 25, 2013 1:30–3:30 p.m. ET

The second session will focused on the development of a rent roll and unit count for an "n" unit building. This will demonstrate the scalability of the base model adding features such as additional units and several retail rentals. The additional sheet, 'Rent_Roll', will be developed and added to the existing model.

Session Highlights:
  • Understand the rent roll characteristics for a multifamily unit
  • Understand that each unit can be summarized to include vacancy and collection loss
  • Understand that the summary page will be a roll-up of the individual unit characteristics
  • Develop ability to add additional types of units and revenue sources to include retail space and parking

Session 3: April 30, 2013 1:30–3:30 p.m. ET

This session will focus on further expansion of the existing pro forma model to include a construction/draw page as well as construction financing.  As in the previous section, an additional page, "Draw", will be added to scale the current model to allow a construction portion. In addition, one of the tranches will be modified for the construction portion providing two loan products: Construction and Permanent.

Session Highlights:
  • Understand basics components for a construction/renovation (this will be high level)
  • Understand structure for a “Draw” page and methods to develop
  • Understand construction financing and method to include within capital stack
  • Major headers for construction and financing
  • Clear demonstration of how to scale existing model to include construction and development (this course will only focus on construction)

Session 4: May 2, 2013 1:30–3:30 p.m. ET

The final session will focused on the complete cash flow for the project from "cradle to grave". A final sheet will be created, "inflow_outflow", demonstrating how cash enters and exits the project, i.e. both debt and equity. The model will be reviewed in total and each segment addressed and demonstrating how capital is utilized. 

While a waterfall will not be constructed as part of this course, the location and method to complete one will be discussed and demonstrated. Participants will leave with a working model which is scalable and modular for any asset class.

Session Highlights:
  • Complete scalable and modular pro forma model which includes construction draw, construction and permanent financing facilities
  • Understanding of the complete pro forma structure for multi-phase projects
  • Understanding of how to analyze and add scalability to existing models
  • Understanding of how to scale the existing model further to include waterfalls, stochastic processes, portfolio analysis, and visual displays of results

Roger P. Staiger
Managing Director
Stage Capital, LLC

Roger Staiger is managing director for Stage Capital LLC, an international real estate advisory firm providing expertise to clients in areas of global portfolio management and asset repositioning. Staiger, through Stage Capital, has successfully repositioned over $500 million in distressed and inefficient real estate projects globally.

During his 20-year career, Staiger has worked in a number of industries at senior levels. He was managing director for Constellation Energy’s retail commodity division, CFO for Caruso Homes, and a portfolio manager for a large commingled pension fund in New York.

Staiger holds multiple degrees, including a BS in electrical engineering, an MBA in investments, an MA in international transactions, and an MS in finance. Currently, he holds faculty positions in the real estate departments at Georgetown University and Johns Hopkins University.

Staiger makes numerous capital market and economic presentations to organizations internationally. He also appears regularly on television and is often quoted in national periodicals and local newspapers.
Subscription Information
ULI Member Price:
Non-Member Price: