Introduction to Pro Forma Modeling with Excel: ULI Live Online Workshop (July 2012)

Held over four sessions, this online workshop will guide students through the construction and manipulation of a basic multifamily investment pro forma. Starting with a blank spreadsheet, the program is geared toward individuals who have never completed a pro forma from scratch. Students will receive step-by-step direction in the construction of this investment pro forma. At the end of the last session, participants will have completed their own investment pro forma and will have gained a new appreciation for how valuation and investment decisions are made.

Each session of the workshop is designed to build on the previous one with each participant having a completed pro forma model at the conclusion. Participants are expected to use learned skills to independently 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. At the conclusion of the workshop, the instructor’s copy will be distributed, which will provide, at a minimum, a working pro forma investment model for each participant.  

The program is designed to be interactive. The instructor will be available by e-mail between sessions to assist participants.

Course Outline

Session 1: July 10, 2012      1:30–3:30 p.m. ET

The class will begin with a blank spreadsheet, and students will assign cell colors, design borders to separate areas, and create sections for the inputs for the basic apartment model. Students will construct the input sections and headers for remaining sheets.

Session Highlights
  • Understanding of a basic apartment model to be developed
  • Understanding of the pro forma overview (structural overview)
  • Understanding for developing input page
  • Basic structure, to include borders, colors, delineating inputs, naming cells
  • Major sections for inputs—sources/uses, revenue (rent roll), expenses, financing structure, operating/finance concerns
  • Structure of Pro Forma and Amortization pages

Session 2: July 12, 2011      1:30-3:30 p.m. ET

Picking up with the structure from the first class, participants will develop the cash flow for the project down to net operating income (NOI). Students will learn basic functions such as linking, adding and subtracting, and the subtotal function. Students will be taught how to link assumptions and escalate costs over time.

Session Highlights
  • Linking cells
  • Structuring cash flow for a project—e.g., revenue, expense
  • Basic functions, “sum,” “subtotal”
  • Basic escalation of revenue/expense over time
  • Structuring costs and escalating over time
  • Determining cash flow before debt service

Session 3: July 17, 2012    1:30–3:30 p.m. ET

This session will focus on the amortization table, how principal is amortized, and the basic structure of an amortization table. Students will learn how to link the debt payments from the debt page to the pro forma page and calculate free cash flow for the project.

Session Highlights
  • Amortization table structure
  • Basic functions for debt payments (PMT, PPMT, IPMT)
  • Amortization versus interest only
  • Linking to pro forma page

Session 4: July 19, 2012    1:30–3:30 p.m. ET

The final session will pull it all together and value the cash flows for investment decisions. Students will link the pro forma page to the bottom of the input/summary page and value cash flows. Students will understand basic valuation and how investment decisions are made.

Session Highlights
  • Valuation (debt-service coverage ratio, cash-on-cash)
  • Net present value (NPV function)
  • Internal rate of return (IRR function)
  • Bifurcation of cash flows (operations versus reversion)
  • How to present the data to a lender and/or equity partner


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: