STAGE CAPITAL, LLC
·
This memorandum will provide you with the steps for building a beginner to intermediate pro forma model for an income producing property. While it will allow for construction/redevelopment costs, the main goal is the basic steps for modeling. This is my Christmas present to the 2010 list-serve.
Pro Forma Steps:
1 Summary Page (First Half)
Sources/Uses
Revenue (Rent Roll)
Expenses (Owner and Operating)
Financing (Structure/Assumptions)
Operating/Fnan Considerations: (Valuation Assumptions, etc.)
2 Pro Forma Page
Schedule Structure (above revenue)
Revenue
Expense
3 Debt Page
Amortization Table(s)
4 Pro Forma Page
Debt
5 Summary Page (Second Half)
Summary Pro Forma
Valuation
Step 1a Sources/Uses
– Sources/Uses defines all funds, coming and going, at time of settlement, i.e. financial close.
– Construction requires a simple table in the
upper left hand portion of sheet 1
– Rename sheet1: Summary
– Sources Defines capital used to purchase asset
o Two major sources
§ Equity (what comes from your wallet and/or an investors wallet)
§ Debt (what comes from a source with a senior lien on the property, e.g. bank)
o Construct table providing two sources for both
Roger Staiger III
– Sources Structure:
Sources/Uses
Sources
Subtotal F(x)
Equity
Subtotal F(x)
Investor1
Input
Investor2
Input
Debt
Subtotal F(x)
Tranche1
Input
Tranche1
Input
– Notes
o Inputs are placed in blue
o Totals are quantified using subtotal function not sum function
§ Alleviates possibility of double counting subtotaled lines
This is the top half of the Sources/Uses table (it is not yet complete)
2. Uses Defines all capital outflows, both magnitude and direction, i.e. location
Multiple categories (this list is NOT intended to be all inclusive)
Purchase Price
Hard Costs (Physical Uses, e.g. machinery)
Soft Costs (Services at/for settlement, e.g. accounting, attorney, engineer)
Interest During Construction (IDC) (interest spent to complete project prior to commercial operating date (COD))
Working Capital
Capital prefunded to carry project until exit or point at which project has positive monthly cash flow
Financing (Financing costs, e.g. origination points, additional fees)
3. Note: Sources less Uses @ close must be positive to close/settle a project
4. Uses Structure
Uses
Subtotal F(x)
Purchase Price
Input
Hard Costs
Input
Soft Costs
Input
IDC
Input
Working Capital
Input
Financing
Input
(1) Finally construct the Surplus/(Deficit) calculation at the bottom of the table.
To Close a project must have sufficient funds at the end, i.e. Sources >= Uses
(2) Total Structure with final portion:
Sources/Uses
Sources
Subtotal F(x)
Equity
Subtotal F(x)
Investor1
Input
Investor2
Input
Debt
Subtotal F(x)
Tranche1
Input
Tranche1
Input
Uses
Subtotal F(x)
Purchase Price
Input
Hard Costs
Input
Soft Costs
Input
IDC
Input
Working Capital
Input
Financing
Input
Surplus/(Deficit) Delta
– Sources and Uses are complete
– Consider conditional formatting for Surplus/(Deficit)
o BRIGHT RED WHEN IN DEFICIT
– Recommend Defining names for inputs
o Investor1 defined as Investor1
o Hard Costs defined as Hard_Costs
2
Step 1b Revenue (Rent Roll)
– To the right of the Sources/Uses Build a Rent Roll (summary of leasing structures) section
– Include the following:
o Unit Number
oRental Escalation oStart of Lease
o End of Lease
oSquare Footage of unit/space (sf) oBase Rent
oCAM (Common Area Maintenance) contribution (if applicable) o1stMonth Free
o Total per Year
– This section is the basic rent roll
– Note: Some modelers do not build in vacancy but rather build into rent roll (this is modeler preference)
– Note1: Escl is an abbreviation for escalation
– This section drives the revenue section of the pro forma
– Suggested structure:
TENANT
UNIT
ESC/mo
START
EXP
SF
BASE RENT
1st Mo Free
TOTAL/yr
Apartment
1
0.13%
1-Aug-11
1-Feb-12
980
3,500.00
Y
42,000.00
Apartment
51
0.13%
1-Nov-11
1-Nov-12
980
3,400.00
Y
40,800.00
TENANT
UNIT
ESC/mo
START
EXP
SF
BASE RENT
$CAM/sf/mo
TOTAL
Retail
1
0.17%
1-Aug-11
1-Jan-16
1,300
6,500.00
0.33
83,200.00
Retail
2
0.17%
1-Sep-11
1-Mar-17
1,200
5,000.00
0.33
64,800.00
TOTAL
52,480
182,750.00
2,203,000
Step 1c Operating/Owners Expenses
– Build beneath Revenue (Rent Roll) summary
– Can build in two sections (depends on size of pro forma)oOperating and Owners sections
– Line item listing of expenses
o Can be taken from existing accounting systems
o Consider separating into Fixed and Variable expenses
o Must understand variable cost/unit to operate
– Suggested Structure
Operating Expenses:
2010
ESC/mo
Association Fees
0
0.17%
Repairs & Maintenance
20,000
0.17%
Pressure Washing / Window Cleaning
2,500
0.17%
Electrical Repair
2,500
0.17%
Signs
500
0.17%
Janitorial
12,000
0.17%
Painting
6,000
0.17%
Parking Lot
0
0.17%
Plumbing Repair
12,000
0.17%
Landscape Maintenance
1,500
0.17%
Roof Repairs
5,000
0.17%
Pest Control
2,000
0.17%
Management Fees
0
0.17%
Fire Service
1,000
0.17%
Telephone
1,200
0.17%
Insurance
5,000
0.17%
Office Expenses
5,000
0.17%
Real Estate Taxes
150,000
0.17%
Professional Fees
5,000
0.17%
Electricity
20,000
0.17%
Water & Sewer
20,000
0.17%
Trash Disposal
12,000
0.17%
Recycle
6,000
0.17%
Total Operating Expenses
289,200
3
Owners Expenses:
2010
ESC/mo
Commissions
90,000
0.17%
Bank Service Charges
0
0.17%
Office Utilities
2,000
0.17%
Electric – Vacant Units
2,000
0.17%
Office Supplies
2,500
0.17%
Penalties
0
0.17%
Dues
0
0.17%
Advertising & Promotion
5,000
0.17%
Meals & Entertainment
0
0.17%
Owner Professional Fees
0
0.17%
Janitorial – Vacant Units
6,000
0.17%
Legal & Accounting
5,000
0.17%
Owner Capital Improvements
20,000
0.17%
Demolition & Construction
3,000
0.17%
Owner’s Repairs & Maintenance
0
0.17%
LL Parking Expense
0
0.17%
Taxes & Licenses
5,000
0.17%
Tenant Alterations
5,000
0.17%
Travel
0
0.17%
Miscellaneous Expenses
5,000
0.17%
Total Owners Expenses
150,500
Step 1d Financing (Structure/Assumptions)
– Built to left of Operating/Owner expenses and beneath Sources/Uses
– Construct two tranches
– Sections:
o Type (IO/CPM)
§ IO: Interest Only
§ CPM: Constant Payment Mortgage
§ Consider using a toggle if choose to build two options
§ Do NOT forget larger projects have custom amortization tablesoPrincipal (amount borrowed)
oInterest (cost of funds) oFinancing Fee (fees) oClosing Costs
o Exit Fees
o Term (years)
o DSCR Minimum
– Suggested structure: