pro forma development (part ii) - amazon s3...pro forma development (part ii) roger staiger, frics...

Pro Forma Development

(Part II) Roger Staiger, FRICS

Stage Capital, LLC (202) 640-8912

[email protected]

Disclaimer All slides in this presentation are the intellectual property of Stage Capital, LLC and Roger Staiger, FRICS. Use of these slides is only permitted when presented by Roger Staiger, FRICS or a designee of Stage Capital, LLC. Further distribution is strictly prohibited.

Roger Staiger, FRICS [email protected] 2 of 53

3 of 53

Structure 4-Part Sessions: 2-hours each Pro Forma Review Amortization: Custom Amortization

Rent Roll Draw Page Inflow/Outflow Risk Quantification

Roger Staiger, FRICS [email protected]

4 of 53

Pro Forma Model 4-unit apartment building Adjusted to 39-unit (n-unit)

Based on Harvard Business Case Study: Revere Street (on Steroids)

MS Excel 2007/2010 Focused on Intermediate Modeling skills Result: completed scalable/modular pro forma

model


5 of 53

Structure/Logic Flow (Part I)


Summary Page Pro Forma Page (s2)

Amortization Page (s3) Inputs Section

(s1)

Analysis (s4)

Structure/Logic Flow (Part II)


Summary Page

Pro Forma Page

Amortization Page (s1) Inputs Section

Analysis (Project)

Rent Roll Page (s2)

Construction Draw Page (s3)

Risk Quantification (Best/ML/Worst) (s4)

7 of 53

Inputs Page (Pro Forma)


Equity

Debt

Uses

Revenue

Expenses

Finance

Valuation/ Sale

8 of 53

Pro Forma


Uses

Debt

Equity = CF0

Rev

enue

Exp

Fnan

Rev

enue

Exp

S Exp

Princ

T+1

NI

CFT

NI NI

Summary Page


Source 4,924,686 Operations # Units Avg SF Unit $/mo Finance I/PEquity 984,937 Efficiency 12 837.50 1,256 Tranche 1

Investor 1 984,937 One Bed 12 975.00 1,550 Principal Principal_T1 3,939,749 Investor 2 One Bed + De 6 1,250.00 1,800 Loan‐to‐Value LtV_T1 80%

Debt 3,939,749 Two Bed 9 1,450.00 2,300 Term (Years) Term_T1 20Tranche 1 3,939,749 39 Interest Rate Interest_T1 8.00%Tranche 2 ‐ Vacancy 1 5.00% Structure (1:CPM, 2:I/O, 3:Custom) 2

Vacancy 2 5.00% Origination Fee Orig_Fee_T1 1.00%Uses 4,924,686 Vacancy 3 5.00% Tranche 2

Land & Related 1,000,000 Vacancy 4 5.00% Principal Principal_T2 ‐ General & Administ 170,000 Term (Years) Term_T2 20Dev Chrgs & Mun C 110,000 Rental Escl 2.00% Interest Rate Interest_T2 15.00%Hard Cost Construc 3,401,500 Structure (1:CPM, 2:I/O, 3:Custom) 2Buidling Operation 60,000 Management per Net Rev 5.00% Origination Fee Orig_Fee_T2 1.00%Financing Expense 183,186 Repairs per Net Rev 5.00%

Janitor per Year 20,600 Valuation I/PHeat per Year 21,000 Capitalization Rate Cap_Rate 8.00%Electricity per Year 10,000 Sales Expense Sales_Exp 6.00%

Surplus/(Deficit) ‐ Water per Year 12,000 Discount Rate Discount_Rate 10.00%Property Tax per Year 78,000

PV Ops 1,213,645 46.37% Insurance per Year 32,000 Purchase/Dev Start Dev_Start 1‐Jan‐13PV Rev 1,403,507 53.63% Comm Operating Date COD 1‐Jan‐14

2,617,152 Expense Escl 2.00% Sales Period Sale_Term_Yr 10 CF0 (1,059,622) NPV 1,557,530 DSCR Minimum 0.56 Case (Best ‐ 1; ML ‐ 2; Worst ‐ 3) 2 IRR 27.03% Cash‐on‐Cash (monthly) Minimum 1.50%

Summary Page (Bottom)


Valuation:Year 1 2 3 4 5 6 7 8 9 10

RevenueRental 782,100 797,742 813,697 829,971 846,570 863,502 880,772 898,387 916,355 934,682 Vacancy 39,105 39,887 40,685 41,499 42,329 43,175 44,039 44,919 45,818 46,734

Total Revenue 742,995 757,855 773,012 788,472 804,242 820,327 836,733 853,468 870,537 887,948

ExpenseManagement 37,150 37,893 38,651 39,424 40,212 41,016 41,837 42,673 43,527 44,397 Repairs 37,150 37,893 38,651 39,424 40,212 41,016 41,837 42,673 43,527 44,397 Janitor 20,790 21,210 21,638 22,074 22,520 22,974 23,438 23,911 24,394 24,886 Heat 21,194 21,621 22,058 22,503 22,957 23,421 23,893 24,376 24,868 25,370 Electricity 10,092 10,296 10,504 10,716 10,932 11,153 11,378 11,607 11,842 12,081 Water 12,111 12,355 12,604 12,859 13,118 13,383 13,653 13,929 14,210 14,497 Property Tax 78,719 80,308 81,929 83,583 85,270 86,991 88,747 90,538 92,365 94,230 Insurance 32,295 32,947 33,612 34,290 34,982 35,688 36,409 37,144 37,893 38,658

Total Expenses 249,500 254,522 259,645 264,872 270,204 275,643 281,191 286,851 292,626 298,516

Net Operating Income 493,495 503,333 513,367 523,600 534,038 544,684 555,542 566,616 577,911 589,432

DebtTranche 1 315,180 315,180 315,180 315,180 315,180 315,180 315,180 315,180 315,180 315,180 Tranche 2 ‐ ‐ ‐ ‐ ‐ ‐ ‐ ‐ ‐ ‐

Total Debt 315,180 315,180 315,180 315,180 315,180 315,180 315,180 315,180 315,180 315,180

Net Sales Proceeds ‐ ‐ ‐ ‐ ‐ ‐ ‐ ‐ ‐ 3,124,136

Reversion FCF ‐ ‐ ‐ ‐ ‐ ‐ ‐ ‐ ‐ 3,124,136 Operating FCF 178,315 188,153 198,187 208,420 218,858 229,504 240,362 251,436 262,731 274,252

Total FCF (984,937) 178,315 188,153 198,187 208,420 218,858 229,504 240,362 251,436 262,731 3,398,388

11 of 53

Amortization Page


Tranche 1 Tranche 2Principal 3,939,749 Principal ‐ Interest 8.00% Interest 15.00%Term 20 Term 20I/O 2 I/O 2

100.00% 100.00%# BoP Principal Interest Payment EoP Custom # BoP Principal Interest Payment EoP Custom1 3,939,749 ‐ 26,265 26,265 3,939,749 0.42% 1 ‐ ‐ ‐ ‐ ‐ 0.42%2 3,939,749 ‐ 26,265 26,265 3,939,749 0.42% 2 ‐ ‐ ‐ ‐ ‐ 0.42%3 3,939,749 ‐ 26,265 26,265 3,939,749 0.42% 3 ‐ ‐ ‐ ‐ ‐ 0.42%4 3,939,749 ‐ 26,265 26,265 3,939,749 0.42% 4 ‐ ‐ ‐ ‐ ‐ 0.42%5 3,939,749 ‐ 26,265 26,265 3,939,749 0.42% 5 ‐ ‐ ‐ ‐ ‐ 0.42%6 3,939,749 ‐ 26,265 26,265 3,939,749 0.42% 6 ‐ ‐ ‐ ‐ ‐ 0.42%7 3,939,749 ‐ 26,265 26,265 3,939,749 0.42% 7 ‐ ‐ ‐ ‐ ‐ 0.42%8 3,939,749 ‐ 26,265 26,265 3,939,749 0.42% 8 ‐ ‐ ‐ ‐ ‐ 0.42%9 3,939,749 ‐ 26,265 26,265 3,939,749 0.42% 9 ‐ ‐ ‐ ‐ ‐ 0.42%10 3,939,749 ‐ 26,265 26,265 3,939,749 0.42% 10 ‐ ‐ ‐ ‐ ‐ 0.42%11 3,939,749 ‐ 26,265 26,265 3,939,749 0.42% 11 ‐ ‐ ‐ ‐ ‐ 0.42%12 3,939,749 ‐ 26,265 26,265 3,939,749 0.42% 12 ‐ ‐ ‐ ‐ ‐ 0.42%

Pro Forma Page Completed (Top Section)


Periods 1 2 3 4 5 6 7 8 9 10 11 12Month Jan‐14 Feb‐14 Mar‐14 Apr‐14 May‐14 Jun‐14 Jul‐14 Aug‐14 Sep‐14 Oct‐14 Nov‐14 Dec‐14

Revenue Year 1 1 1 1 1 1 1 1 1 1 1 1Efficiency 15,075 15,075 15,075 15,075 15,075 15,075 15,075 15,075 15,075 15,075 15,075 15,075 One Bed 18,600 18,600 18,600 18,600 18,600 18,600 18,600 18,600 18,600 18,600 18,600 18,600 One Bed + Den 10,800 10,800 10,800 10,800 10,800 10,800 10,800 10,800 10,800 10,800 10,800 10,800 Two Bed 20,700 20,700 20,700 20,700 20,700 20,700 20,700 20,700 20,700 20,700 20,700 20,700

Total Rent 65,175 65,175 65,175 65,175 65,175 65,175 65,175 65,175 65,175 65,175 65,175 65,175

Vacancy 1 754 754 754 754 754 754 754 754 754 754 754 754 Vacancy 2 930 930 930 930 930 930 930 930 930 930 930 930 Vacancy 3 540 540 540 540 540 540 540 540 540 540 540 540 Vacancy 4 1,035 1,035 1,035 1,035 1,035 1,035 1,035 1,035 1,035 1,035 1,035 1,035

Total Vacancy 3,259 3,259 3,259 3,259 3,259 3,259 3,259 3,259 3,259 3,259 3,259 3,259

Total Revenue 61,916 61,916 61,916 61,916 61,916 61,916 61,916 61,916 61,916 61,916 61,916 61,916

13 of 53

Analysis/Valuation (Bottom of Pro Forma)


Valuation: Dec‐13 Jan‐14 Feb‐14 Mar‐14 Apr‐14 May‐14 Jun‐14 Jul‐14 Aug‐14 Sep‐14 Oct‐14Total FCF (1,059,622) 14,993 14,969 14,945 14,921 14,896 14,872 14,848 14,823 14,799 14,775

DSCR 0.57 0.57 0.57 0.57 0.57 0.57 0.57 0.56 0.56 0.56 Minimum 0.56

Cash‐on‐Cash 1.52% 1.52% 1.52% 1.51% 1.51% 1.51% 1.51% 1.51% 1.50% 1.50%Minimum 1.50%

PV Reversion 1,213,645 46.37%PV Operating 1,403,507 53.63%

2,617,152 CF0 (1,059,622) Net Present Value 1,557,530 IRR 27.03%

Unit Characteristics


Periods 1 2 3 4 5 6Months Jan‐14 Feb‐14 Mar‐14 Apr‐14 May‐14 Jun‐14

Unit # Unit Type Area SF Rental Rate ($/mo) Year 1 1 1 1 1 11 Efficiency 800 1200 1 1,200 1,200 1,200 1,200 1,200 1,200 2 Efficiency 825 1250 2 1,250 1,250 1,250 1,250 1,250 1,250 3 Efficiency 850 1275 3 1,275 1,275 1,275 1,275 1,275 1,275 4 Efficiency 875 1300 4 1,300 1,300 1,300 1,300 1,300 1,300 5 Efficiency 800 1200 5 1,200 1,200 1,200 1,200 1,200 1,200 6 Efficiency 825 1250 6 1,250 1,250 1,250 1,250 1,250 1,250 7 Efficiency 850 1275 7 1,275 1,275 1,275 1,275 1,275 1,275 8 Efficiency 875 1300 8 1,300 1,300 1,300 1,300 1,300 1,300 9 Efficiency 800 1200 9 1,200 1,200 1,200 1,200 1,200 1,200 10 Efficiency 825 1250 10 1,250 1,250 1,250 1,250 1,250 1,250 11 Efficiency 850 1275 11 1,275 1,275 1,275 1,275 1,275 1,275 12 Efficiency 875 1300 12 1,300 1,300 1,300 1,300 1,300 1,300 13 One Bed 950 1500 13 1,500 1,500 1,500 1,500 1,500 1,500 14 One Bed 975 1550 14 1,550 1,550 1,550 1,550 1,550 1,550

Summary Table12 Efficiency 837.50 1,256.25 Efficiency 15,075 15,075 15,075 15,075 15,075 15,075 12 One Bed 975.00 1,550.00 One Bed 18,600 18,600 18,600 18,600 18,600 18,600 6 One Bed + Den 1,250.00 1,800.00 One Bed + Den 10,800 10,800 10,800 10,800 10,800 10,800 9 Two Bed 1,450.00 2,300.00 Two Bed 20,700 20,700 20,700 20,700 20,700 20,700 39

Development (Today)


Development Start 1‐Jan‐13Construction Months 12Opening Date 1‐Jan‐14

1 2 3 4 5 6 7Cost Item Jan‐13 Feb‐13 Mar‐13 Apr‐13 May‐13 Jun‐13 Jul‐13

Land & Related Land_Rela 1,000,000 Land & Related 1,000,000 100.00%

General & Administrative G_A 170,000 Legal Fees 20,000 8.33% 8.33% 8.33% 8.33% 8.33% 8.33% 8.33%Development Management Fees 60,000 8.33% 8.33% 8.33% 8.33% 8.33% 8.33% 8.33%Insurance & Warranty 60,000 8.33% 8.33% 8.33% 8.33% 8.33% 8.33% 8.33%Other G&A 30,000 8.33% 8.33% 8.33% 8.33% 8.33% 8.33% 8.33%

Dev Chrgs & Mun Costs Dev_Chrg_ 110,000 Development Charges 85,000 8.33% 8.33% 8.33% 8.33% 8.33% 8.33% 8.33%Building Permits 20,000 8.33% 8.33% 8.33% 8.33% 8.33% 8.33% 8.33%Municipal Costs 5,000 8.33% 8.33% 8.33% 8.33% 8.33% 8.33% 8.33%

Hard Cost Construction HC_Const 3,401,500 General Costs 1,500,000 0.62% 1.65% 4.41% 9.18% 14.99% 19.15% 19.15%Site Work 450,000 0.62% 1.65% 4.41% 9.18% 14.99% 19.15% 19.15%

Development (cont’d) (Today)


Cost Item

Land & Related 1,000,000 ‐ ‐ ‐ ‐ ‐ ‐ Land & Related 1,000,000 ‐ ‐ ‐ ‐ ‐ ‐

General & Administrative 14,167 14,167 14,167 14,167 14,167 14,167 14,167 Legal Fees 1,667 1,667 1,667 1,667 1,667 1,667 1,667 Development Management Fees 5,000 5,000 5,000 5,000 5,000 5,000 5,000 Insurance & Warranty 5,000 5,000 5,000 5,000 5,000 5,000 5,000 Other G&A 2,500 2,500 2,500 2,500 2,500 2,500 2,500

Dev Chrgs & Mun Costs 9,167 9,167 9,167 9,167 9,167 9,167 9,167 Development Charges 7,083 7,083 7,083 7,083 7,083 7,083 7,083 Building Permits 1,667 1,667 1,667 1,667 1,667 1,667 1,667 Municipal Costs 417 417 417 417 417 417 417

Hard Cost Construction 21,122 56,262 149,860 312,421 509,825 651,260 651,260 General Costs 9,314 24,811 66,086 137,772 224,823 287,194 287,194 Site Work 2,794 7,443 19,826 41,332 67,447 86,158 86,158

Development (cont’d) (Today)


Development Cash Flow:Equity BoP 984,937 ‐ ‐ ‐ ‐ ‐ ‐ Equity Draw 984,937 ‐ ‐ ‐ ‐ ‐ ‐ Equity EoP ‐ ‐ ‐ ‐ ‐ ‐ ‐ Equity FV * Financial Close Equity_COD 1,059,622 1,059,622 ‐ ‐ ‐ ‐ ‐ ‐

Construction Debt BoP ‐ 99,288 180,538 357,579 701,228 1,248,054 1,942,455 Construction Debt Interest 143,789 ‐ 662 1,204 2,384 4,675 8,320 12,950 Construction Debt Draw 99,288 80,588 175,837 341,265 542,151 686,081 686,081 Construction Debt EoP 99,288 180,538 357,579 701,228 1,248,054 1,942,455 2,641,485

Z‐Score (2.50) (2.00) (1.50) (1.00) (0.50) ‐ 0.50

S‐Curve 0.62% 2.28% 6.68% 15.87% 30.85% 50.00% 69.15%Draw 100.00% 0.62% 1.65% 4.41% 9.18% 14.99% 19.15% 19.15%

Risk

Scenario Analysis

19 of 53

Risk Variability of returns from an investment Quantified as variance and standard

deviation about an expected outcome (sample mean)

Types 1. Systematic (Market – not diversifiable) 2. Unsystematic (Unique/Security Specific –

diversifiable)


20 of 53

Risk - Variability

X-bar

Spread/Dispersion (σ)


21 of 53

Return Equity investor’s share of company’s

earnings Forms

1. Earnings Distributed 2. Earnings Reinvested

Expected value to be earned on initial investment


22 of 53

Equity Return – Methods to Calculate

factorsmicmacroeconoreturn to Relates -

)(...)()()()((APT)Theory Pricing Arbitrage 3.

)]()([)()((CAPM) Model PricingAsset Capital 2.

)(...)()()(Return of Rate Expected 1.

2211 nnfi

ifMfi

ZBA

rbrbrbRERE

RERERERE

KZPKBPKAPRE


23 of 53

Risk and Return Markowitz is father of modern portfolio theory Relates to fully diversified ‘efficient’ portfolio Goal: Maximize Return for each Risk level

E(R)

(Risk)

Efficient Frontier (Efficient Portfolios)


24 of 53

Measures of Central Locations (Return)

1. Mean Measure of central location Affected by outliers

2. Median Central value in data set when numbers are arranged in

ascending or descending order

3. Mode Data value which occurs with greatest frequency

4. Interquartile Range (IQR) Data separation into quarters (LIBOR calculation)


25 of 53

Measures of Dispersion (Risk)

1. Range • Distance from largest to smallest value

2. Variance (sample) • Measure of dispersion utilizing all data values • Deviations about mean are squared

LowHi ValValRange

1

2

2

n

xxS i


26 of 53

Standard Deviation (scenario) Symbol () Positive Square Root of Variance Measured in base units Same units as mean

ii PrErES2

)()()(


27 of 53

Standard Deviation (sample) Symbol () Positive Square Root of Variance Measured in base units Same units as mean

1

1)(

2

n

xxS i


Population versus Sample

28 of 53

1

1 vs

11

2

1

2

n

xx

N

xt

ii

s

t

ii

p


11:;1:

n

PN

P isip

29 of 53

Skewness Measure of symmetry for a distribution Exists when Mean Median Mode Third moment about mean

3

1

3/

S

nxxSkewness

n

ii


30 of 53

Skewness (cont’d) Standard Normal Curve Skewness = 0

Positive (Rightward Skew) Mean > Median > Mode Rightward Tail

Negative (Leftward Skew) Mean < Median < Mode


31 of 53

Skewness (cont’d) Positive

Negative (Tail Risk)

Mode Median

Mean

Mode Median

Mean


32 of 53

Kurtosis Measure of Peakedness Fourth moment about the mean

4

1

4/

S

nxxKurtosis

n

ii


33 of 53

Kurtosis (cont’d) Standard Normal Curve Kurtosis = 3.0 (some correct and make 0.0)

Platykurtic (Flat) Kurtosis < 3.0

Leptokurtic (Peaked) Kurtosis > 3.0


34 of 53

Kurtosis (cont’d)

X-bar

Standard Normal (Kurtosis = 3.0)

Leptokurtic (Kurtosis > 3.0)

Platykurtic (Kurtosis < 3.0)


Measures of Efficiency Efficiency – The ratio of Risk and Reward Goal: Maximize reward for any risk level - Markowitz Portfolio Theorty Measures 1. Coefficient of Variation (CV) (Goal: Minimize)

2. Sharpe Ratio (Goal: Maximize)

3. Treynor Ratio (Goal: Maximize)

35 of 53 Roger Staiger, FRICS [email protected]

36 of 53

Coefficient of Variation (CV) Descriptive statistic relating standard deviation to

mean; measure of efficiency (unitless) Required to quantify Return OF Capital Useful when comparing data sets with different

means and standard deviations

Measures relative size of standard deviation to mean

Goal: Minimize Roger Staiger, FRICS [email protected]

xCV

Sharpe Ratio Similar to Coefficient of Variation Reciprocal adjusted for purchasing power Industry standard measure of efficiency Reward to Risk Ratio (Goal: Maximize)

37 of 53

fRX

SR


Treynor Ratio Similar to Sharpe Ratio Normalized to portfolio beta Reward to Risk Ratio (Goal: Maximize) Requires calculation of portfolio beta

38 of 53

p

fRXTR


39 of 53

Measures of Linear Association between Two Variables (1)

Covariance Descriptive measure of the linear

association between two variables

iprelationshlinear better Sxy larger statenot Can es.in variabl expressed unitson depends Value *

iprelationshlinear no indicatesSxy Zero(3)iprelationshlinear negative indicatesSxy Negative (2)

iprelationshlinear positive indicatesSxy Positive (1)*:Note

1

n

YYXXS ii

xy


40 of 53

Measures of Linear Association (2)

iprelationshlinear no indicates 0 -iprelationshlinear negativeperfect indicates 1- -iprelationshlinear positiveperfect indicates 1 -

1- to1 Range -

tCoefficienn Correlatio

yx

xyxy SS

Sr

Covariance Sample Means


Measures of Linear Association (3) Coefficient of Determination Measure of Goodness of Fit Range 0 – 1 (zero to one) Used in regression analysis as determinant

of strength NOT a Test Statistics Must be used after T- and F- Statistics determine

statistically significant relationship

41 of 53

SSTSSRr 2


42 of 53

Probability Distribution A listing of all possible outcomes, or

events, with a probability (chance of occurrence) assigned to each outcome

Types: 1. Discrete

2. Continuous


Probability Distribution (Internal Rate of Return (IRR))

Continuous Distribution Statistical Certainty 100% not accurate

Decisions based on IRR value?! Valid???


44 of 53

Expected Rate of Return E(r) Rate of return expected to be realized from

an investment Weighted average of the probability

distribution of possible results

ievent ofy Probabilit ievent h Return wit Expected )(

:where

)()(1

i

i

n

iii

PRE

PRERE


45 of 53

Scenario Risk Weighted average outcome standard

deviations proportioned with probability of occurrence Provides idea of how far above or below

expected value actual value may be

n

iii PrErE

1

2


Probability

46 of 53

Coefficient of Variation Normalize risk to Expected return

P

n

iii

P RE

PrErE

REcv

1

2

* Used to Compare return risk of different portfolios


Coefficient of Variation (CV) Addresses first (and most important)

investment goal: 1) Return OF Capital Primary

2) Return ON Capital Secondary


Z-Score

48 of 53

xxZ i

Negative reciprocal of CV

At xi = 0; 100% of capital returns with 0.0% return


Z-Score (cont’d)

49 of 53

0 1 -1

68% of Data

P(Gain)

P(Loss)


50 of 53

Normal Distribution Securities modeled using normal

distribution Mean = Median (no skewing) Most likely value (X-bar) Equally probably value above X-bar as

below Expected value more likely closer to X-bar

than farther


51 of 53

Normal Distribution (cont’d)

X-bar


Warning!


Will entertain all questions

May be slow for more advanced Excel users

At end will produce working, scalable pro forma

Questions?

Let’s Begin (see memorandum for detailed steps of

process at conclusion)

pro forma development (part ii) - amazon s3...pro forma development (part ii) roger staiger, frics...

Documents