What I want is a write-up and completed model...

Caiden

memo-pro-forma-steps-25dec10.pdf

Home >Business & Finance homework help >What I want is a write-up and completed model...

Roger Staiger III Memo_Pro_Forma_Steps_25Dec10 (202) 640-8912 [email protected]

S T A G E C A P I T A L , L L C

TO: INVESTORS (YOUNG AND OLD)

FROM: ROGER STAIGER, MANAGING DIRECTOR

SUBJECT: PRO FORMA STEPS

DATE: CHRISTMAS 2010

CC: NONE

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) a. Sources/Uses b. Revenue (Rent Roll) c. Expenses (Owner and Operating) d. Financing (Structure/Assumptions) e. Operating/Fnan Considerations: (Valuation Assumptions, etc.)

2. Pro Forma Page a. Schedule Structure (above revenue) b. Revenue c. Expense

3. Debt Page a. Amortization Table(s)

4. Pro Forma Page a. Debt

5. Summary Page (Second Half) a. Summary Pro Forma b. 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 investor’s 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

- Sources Structure:

Sources/Uses

Sources Subtotal F(x)

Equity Subtotal F(x)

Investor1 Input

Investor2 Input

Debt Subtotal F(x)

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 o This is the top half of the Sources/Uses table (it is not yet complete)

- Uses – Defines all capital outflows, both magnitude and direction, i.e. location o 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) - Note: Sources less Uses @ close must be positive to close/settle a project - Uses Structure

Uses Subtotal F(x)

Purchase Price Input

Hard Costs Input

Soft Costs Input

IDC Input

Working Capital Input

Financing Input

- Finally construct the Surplus/(Deficit) calculation at the bottom of the table.

o To Close a project must have sufficient funds at the end, i.e. Sources >= Uses - 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

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’

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 o Rental Escalation o Start of Lease o End of Lease o Square Footage of unit/space (sf) o Base Rent o CAM (Common Area Maintenance) contribution (if applicable) o 1st Month 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)

o Operating 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

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 tables

o Principal (amount borrowed) o Interest (cost of funds) o Financing Fee (fees) o Closing Costs o Exit Fees o Term (years) o DSCR Minimum

- Suggested structure: Financing Alternatives:

Tranche 1

Type IO

Principal 12,000,000

Interest (current) 7.5%

Finance Fee 1.0%

Closing Cost 50,000

Exit Fee 250,000

Term (yrs) 10.0

DSCR Minimum 1.2 X

Tranche 2 (Mezzanine)

Type IO

Loan-to-Value (Max) 90.0%

Principal 1,000,000

Interest (current) 15.0%

Finance Fee 1.5%

Closing Cost 70,000

Exit Fee 100,000.0

Term (yrs) 10.0

Step 1e – Operating/Fnan Considerations - Generally beneath Financing structure and/or operating/expenses - Sections include:

o Discount rate o Terminal Capitalization Rate o Sales Year o Purchase Date o IDC (Interest During Construction) o Construction Complete (if applicable) o Vacancy (this can be located in rent roll)

- Suggested Structure: Operating/Fnan Considerations:

Financial Close 1-Jan-11

Construction Comp 1-Aug-11

Construction Cost 7,500,000

Interest During Costr 392,055

IDC Rate 9.0%

Apartment Vacancy 7.50%

Terminal Cap Rate 8.0%

Sales Costs 7.0%

Cash Flow Disc Rate 9.0%

Now – Top half of Summary page built - Consider all inputs blue - Consider naming all inputs, i.e. defining names

Step 2 – Pro Forma Page

- Start on Sheet2 and rename sheet ‘Pro Forma’ - Begin Revenue in upper left hand corner - Start Revenue second at row 6 (some amount below row 1)

o Ensures later scalability - List Revenues:

o Unit 1 o Unit 1 o Unit N o Other Revenues/Alternate Revenues

- Start Revenue not before column “G” - Timeline at top is critical

o Three parts:  Year

 Required later for summary page summation  Period

 Used for escalation  Month

 Used by analyst/reviewer to understand timing of individual cash flows o Driven from input page o Structure must be consistent for all pages using a timeline

 Same row/column start

- Suggested Revenue Structure: Revenue Year 1 1 1 1

Rental Income Period 1 2 3 4 Unit Tenant SF $/SF/YR 1-Aug-11 1-Sep-11 1-Oct-11 1-Nov-11

1 Apartment 980 42.86 - 3,500 3,504 3,509 2 Apartment 980 44.08 - - - 3,600 3 Apartment 980 39.80 - 3,250 3,254 3,258

- Other Revenue Line Items Structure: 51 Apartment 980 41.63 - - - -

Apartments 30,100 76,688 120,633 157,884 1 Retail 1,300 64.00 6,500 6,511 6,522 6,533 2 Retail 1,200 54.00 - 5,000 5,008 5,017

Retail 6,500 11,511 11,530 11,549 1 Retail CAM 433 434 435 436 2 Retail CAM - 400 401 401

CAM 433 834 835 837

- After Revenue build vacancy allowance o Depending on analyst/user philosophy

 May not have vacancy allowance  May account for vacancy in rent roll  Could develop collection loss allowance in same section

o Suggested Vacancy Structure: Vacancy Allowance (Apartment Only) - - - -

Total Revenue 37,033 89,033 132,999 170,270 - After Revenue section build Expenses

o Possibly separate into Owners and Operating - List Expenses:

o Operating  Repairs and Maintenance  Painting

o Owners Expenses  Commissions  Taxes & Licenses  Tenant Alternations

o Suggested Operating Expense Structure:

Expenses Operating Expenses

Association Fees - - - - Repairs & Maintenance 1,667 1,669 1,672 1,675 Pressure Washing / Window Cleaning 208 209 209 209 Electrical Repair 208 209 209 209

o Suggested Owners Expense Structure: Owners Expenses

Commissions 7,500 7,513 7,525 7,538 Bank Service Charges - - - - Office Utilities 167 167 167 168

- Take Delta and form Total Expenses - Suggested Total Expense Structure w/ NOI (Revenue less Expense):

Owners Expenses 12,542 12,563 12,584 12,604

Total Expenses 36,642 36,703 36,764 36,825

Net Operating Income (NOI) 392 52,330 96,235 133,445 - Remember that pro forma must extend one year, 12-periods, beyond sale

o Need NOI + 1 period to determine final sales price - Finished with Pro Forma page (for now)

Step 3 – Debt Page - Go to sheet 3 and rename ‘Debt’ - Summarize characteristics of Tranche1 in upper left - Suggested Structure for summary (takes all values from ‘Summary’ page)

Amortization Schedule 1

Principal 12,000,000 Interest 7.50% Term 10 Exit Fee 250,000 I/O or CPM IO

- Note: All values linked to ‘Summary’ page (this is important) - Construct Amortization Table

o Be careful regarding static versus dynamic references for cells o Construct to enable Tranche1 to be copied almost identically for Tranche2

- Start, not before, row 9 - Amortization sections:

o Period o Date o Beginning of Period (BoP) o Principal o Interest o End of Period (EoP) o Payment (Summation of Principal and Interest)

- Build in logic to allow for Interest Only structure (see accompanying excel file for example of logic) - Build to accommodate 30-year period, i.e. 360 periods - Suggested Amortization Structure:

Period Date BoP Principal Interest EoP Payment 0 12,000,000 - 1 Aug-11 12,000,000 - 75,000 12,000,000 75,000 2 Sep-11 12,000,000 - 75,000 12,000,000 75,000 3 Oct-11 12,000,000 - 75,000 12,000,000 75,000

- After complete 1st amortization table… o Copy/Paste to right of Tranche1 o Relink finance assumptions o Test and verify both Amortization tables

- Amortization suggestions: o Begin at period 0

 EoP period 0 is original principal for Tranche  Principal: use PPMT function  Interest: use IPMT function  Payment: Summation of interest and pricinpal

- Prior to leaving this page name the tranche arrays o Start at period 0 and highlight all the way down to payment at period 360 o Name this range for tranche1, “tranche1” o Repeat for Tranche1

Step 4 – Pro Forma Page - Return to ‘Pro Forma’ page to complete debt below Net Operating Income line - Below Net Operating Income line create debt structure - Create Debt Structure - Suggested Debt Structure:

Tranche 1 75,000 75,000 75,000 75,000 Tranche 2 12,500 12,500 12,500 12,500

Total Debt 87,500 87,500 87,500 87,500

Operating Cash Flow (87,108) (35,170) 8,735 45,945 - Notice that the Operating Cash Flow is beneath debt

o Revenue less expense less debt = Operating Cash Flow for ‘pro forma’ page - Debt suggestions:

o Bring values over using VLOOKUP function  Remember that amortization table is vertical structure  Pro forma is horizontal  Need to link converting vertical to horizontal

- After debt and operating cash flow built consider additional section, ‘Inflow/Outflow’ o Create a time zero (0) settlement surplus o Show all inflows and outflows o Show cumulative cash balance

 Must always be positive or capital call will result

Step 5 – Summary Page (Second Half) - Build structure similar to pro forma beneath summary inputs - Structure must be annual as it is summary - Start summary at T=0

o Represents initial equity invested - Suggested structure (to NOI):

Summary Pro Forma 0 1 2 3 Revenue

Apartments 93.6% 1,765,184 2,100,439 2,132,163 Retail 5.9% 134,180 138,336 138,567 CAM Contribution 0.4% 9,685 10,024 10,041

1,909,048 2,248,799 2,280,771 Vacancy Allowance - (157,533) (159,912)

Total Revenues 1,909,048 2,091,266 2,120,859

Expenses Operating 65.8% 291,866 297,757 303,767 Owner's 34.2% 151,887 154,953 158,081

Total Expenses 443,753 452,710 461,848

Net Operating Income (NOI) 1,465,295 1,638,557 1,659,011

Financing Tranche 1 900,000 900,000 900,000 Tranche 2 150,000 150,000 150,000

Total Financing 1,050,000 1,050,000 1,050,000

NOI less Financing Expense 415,295 588,557 609,011 - Bring over values in columns using ‘Sumif’ function

o Reference the period o Sumif from the pro forma using the Year schedule o Sum each year on summary page

- After summary need to build sales section o Section includes all costs for sale and produces net proceeds from sales o Section includes:

 Sales Price  Calculated using NOI (period sale + 1)  Sales Cost (% of sales price)  Debt Repay – Tranche1  Debt Repay – Tranche2  Exit Fee – Tranche1  Exit Fee – Tranche2  Net Proceeds from Sale

o Suggested structure: Sales Price Sales Cost Debt Repay - Tranche1 Debt Repay - Tranche2 Exit Fee - Tranche1 Exit Fee - Tranche2 Net Proceeds from Sale

- Calculate Free Cash Flow o Bifurcate operations and sales proceeds

 Need to calculate percent value from operations and sale o Create cumulative cash flow line

 Remember this only shows positive on annual basis  Could still have negative monthly and must check pro forma page

o Suggested structure:

Free Cash Flow (5,300,000) Operations 415,295 588,557 609,011 Sale - - -

(5,300,000) 415,295 588,557 609,011 Cumm Free Cash Flow (5,300,000) 515,295 1,103,852 1,712,863

- Create Analysis section o Section includes DSCR summary o Cash-on-Cash Return (generally ignore principal pay down for simplicity in case of CPM) o Suggested structure:

Analysis: DSCR (Senior Debt) 1.63 1.82 1.84 Cash-on-Cash Return 5,300,000 7.8% 11.1% 11.5%

- Create Valuation Section o Present value of operating cash flow o Present value of reversion cash flow o Total present value o Less equity investment (CF0) o Net Present Value o Calculate IRR

- Suggested valuation structure: Valuation:

PV CF 4,014,961 54.45% PV Reversion 3,358,035 45.55% Present Value 7,372,996 100.00% CF0 5,300,000 NPV 2,072,996

IRR 14.15%

Note: All Pro Formas (generally) are individual; therefore, this is a guide. Merry 2010 Christmas!!!