Hotel Pro Forma Development
Hotel Pro Forma Development 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 III [email protected]
2 of 32
Structure 4-Part Sessions: 2-hours each
Summary Page (1st half) [Complete] Development/Draw Page [Complete] Pro Forma/Amortization Page(s) Pulling it all together/Valuation Bottom half of Summary Page
Roger Staiger III [email protected]
3 of 32
Hotel Pro Forma Model 125 Key Hotel 75,000 SF; 10,000 Banquet SF
Full Service Development/Construction/Stabilize Focused on BASIC Modeling skills Result: completed scalable and modular Hotel pro forma model
Roger Staiger III [email protected]
4 of 32
Model Summary 125 Keys with Banquet space (Pretax analysis) Occupancy Rate Average Daily Rate/Escalation Capital Sourcing Develop Costs (HC = $57.0m) Valuation Roger Staiger III [email protected]
5 of 32
Structure/Logic Flow Circular Reference
Development Page (s2)
Summary Page
Amortization Page (s3/s4)
Inputs Section (s1) Analysis (s4) Pro Forma Page (s3)
Roger Staiger III [email protected]
6 of 32
Inputs Page (1 of 2) Sources Equity Investor 1 Investor 2 Debt Tranche 1 Tranche 2
$ $/Key 70,687,627 565,501 17,671,907 141,375 17,671,907 141,375
Revenue Rooms Occupancy Average Daily Rate
53,015,720 424,126 53,015,720 424,126
Uses Land & Related General & Administrative Dev Chrgs & Mun Costs Hard Cost Construction Buidling Operations Financing Expense
70,687,627 6,500,000 1,750,000 1,080,000 56,475,000 2,175,000 2,707,627
Surplus/(Deficit)
0
PV Reversion PV Operating Total CF0 New Present Value IRR
72,226,968 12,418,101 84,645,069 (18,649,002) 65,996,066 25.05%
Roger Staiger III [email protected]
565,501 52,000 14,000 8,640 451,800 17,400 21,661
85.33% 14.67%
Year
1
2
3
Escalation
Metric
68% 72% 75% 280.00 290.00 300.00
3% POR
Food & Beverage Restaurant Lounge Banquet Other Food & Beverage
50.00 65.00 70.00 20.00 25.00 30.00 525.00 575.00 650.00
3% POR 3% POR 3% PBSF
Other Departments Telephone/Internet Garage Gift Shop Spa Other Rents & Commissions Other
1.00 5.00 1.00 1,000,000 8.00
1% 3% 3% 5% 3%
1.00 7.00 2.00 1,200,000 9.00
Characteristics Number of Rooms Total Area (sf) Banquet Area (sf) DSCR Average Minimum
1.67 0.78
1.00 8.00 2.00 1,500,000 10.00
POR POR POR Total $ POR
125 75,000 10,000 Cash‐on‐Cash Average Minimum
‐98.81% ‐193.00%
7 of 32
Input Page (2 of 2) Expense Department Rooms Restaurant Lounge Banquet Other F&B Telephone/Internet Garage Gift Shop Spa Other Rents & Commissions Other
Year
1 32.0% 85.0% 65.0% 80.0% 100.0% 50.0% 90.0% 85.0% 5.0%
Unallocated Departments General & Administrative Repairs & Maintenance Sales & Marketing Utilities Other
30.00 22.40 22.40 0.50
Other Deductions Management Fee FF&E Rep and Capital Reserves Property Taxes Insurance Permits & Licenses Equipment Rental Other
3.0% 3.0% 600,000 100,000 20,000 40,000
Roger Staiger III [email protected]
2 31.0% 80.0% 60.0% 72.0% 100.0% 50.0% 90.0% 80.0% 5.0%
31.00 22.40 22.40 0.50
3.0% 4.0% 625,000 100,000 20,000 40,000
3
Escalation Metric
30.0% 80.0% 60.0% 70.0%
% Rev % Rev % Rev % Rev % Rev % Rev % Rev % Rev % Rev % Rev % Rev
100.0% 50.0% 90.0% 75.0% 5.0%
32.00 22.40 22.40 0.50
3.0% 5.0% 650,000 100,000 20,000 40,000
3% 3% 3% 3%
3% 4% 2% 3%
PAR PAR PAR PSF
% Ttl Rev % Ttl Rev Total $ Total $ Total $ Total $
Finance Construction LtV Principal Term (Years) Interest Rate Interest Only Origination Fee Permanent LtV Principal Month Take‐Out Term (Years) Interest Rate Interest Only Origination Fee Valuation Capitalization Rate Sales Expense Discount Rate Sales Period
I/P Const_LtV
75%
6.00% Y 1.00%
Perm_LtV 75% Perm_Princ 53,015,720 30 5% Y 0.50% I/P 8% 10% 6% 10
8 of 32
Development Page (1 of 3) Development Start Construction Months Opening Date
1‐Jan‐14 24 1‐Jan‐16 1
Cost Item
Jan‐14
Land & Related Land & Related
Land_Rela
General & Administrative Legal Fees Development Management Fees Insurance & Warranty Other G&A
G_A
Dev Chrgs & Mun Costs Development Charges Building Permits Municipal Costs
Dev_Chrg_
Hard Cost Construction General Costs Site Work Concrete Work Masonry Metals Carpentry & Millwork Moisture Protection Windows & Doors Finishing Specialities Equipment Furnishings Special Construction Conveying System Mechanical Electrical Contingency
HC_Const
Buidling Operations Pre‐Opening Expenses Working Capital Contribution
Building_O
Financing Expense Construction Loan Interest Financing Fee
Fnan_Exp
Roger Staiger III [email protected]
2 Feb‐14
3 Mar‐14
4 Apr‐14
5 May‐14
6 Jun‐14
7 Jul‐14
8 Aug‐14
9 Sep‐14
10 Oct‐14
11 Nov‐14
12 Dec‐14
6,500,000 6,500,000
100.00%
1,750,000 200,000 600,000 650,000 300,000
4.17% 4.17% 4.17% 4.17%
4.17% 4.17% 4.17% 4.17%
4.17% 4.17% 4.17% 4.17%
4.17% 4.17% 4.17% 4.17%
4.17% 4.17% 4.17% 4.17%
4.17% 4.17% 4.17% 4.17%
4.17% 4.17% 4.17% 4.17%
4.17% 4.17% 4.17% 4.17%
4.17% 4.17% 4.17% 4.17%
4.17% 4.17% 4.17% 4.17%
4.17% 4.17% 4.17% 4.17%
4.17% 4.17% 4.17% 4.17%
1,080,000 850,000 200,000 30,000
4.17% 4.17% 4.17%
4.17% 4.17% 4.17%
4.17% 4.17% 4.17%
4.17% 4.17% 4.17%
4.17% 4.17% 4.17%
4.17% 4.17% 4.17%
4.17% 4.17% 4.17%
4.17% 4.17% 4.17%
4.17% 4.17% 4.17%
4.17% 4.17% 4.17%
4.17% 4.17% 4.17%
4.17% 4.17% 4.17%
56,475,000 3,500,000 1,100,000 7,200,000 400,000 950,000 5,100,000 440,000 7,100,000 8,300,000 1,250,000 2,100,000 7,500,000 135,000 1,300,000 4,100,000 2,250,000 3,750,000
4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17%
4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17%
4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17%
4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17%
4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17%
4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17%
4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17%
4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17%
4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17%
4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17%
4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17%
4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17%
2,175,000 1,850,000 325,000
4.17% 4.17%
4.17% 4.17%
4.17% 4.17%
4.17% 4.17%
4.17% 4.17%
4.17% 4.17%
4.17% 4.17%
4.17% 4.17%
4.17% 4.17%
4.17% 4.17%
4.17% 4.17%
4.17% 4.17%
2,707,627 2,442,548 265,079
100.00%
9 of 32
Development Page (2 of 3) Cost Item Land & Related Land & Related
6,500,000 ‐ 6,500,000 ‐
‐ ‐
‐ ‐
‐ ‐
‐ ‐
‐ ‐
‐ ‐
‐ ‐
‐ ‐
‐ ‐
‐ ‐
General & Administrative Legal Fees Development Management Fees Insurance & Warranty Other G&A
72,917 8,333 25,000 27,083 12,500
72,917 8,333 25,000 27,083 12,500
72,917 8,333 25,000 27,083 12,500
72,917 8,333 25,000 27,083 12,500
72,917 8,333 25,000 27,083 12,500
72,917 8,333 25,000 27,083 12,500
72,917 8,333 25,000 27,083 12,500
72,917 8,333 25,000 27,083 12,500
72,917 8,333 25,000 27,083 12,500
72,917 8,333 25,000 27,083 12,500
72,917 8,333 25,000 27,083 12,500
72,917 8,333 25,000 27,083 12,500
Dev Chrgs & Mun Costs Development Charges Building Permits Municipal Costs
45,000 35,417 8,333 1,250
45,000 35,417 8,333 1,250
45,000 35,417 8,333 1,250
45,000 35,417 8,333 1,250
45,000 35,417 8,333 1,250
45,000 35,417 8,333 1,250
45,000 35,417 8,333 1,250
45,000 35,417 8,333 1,250
45,000 35,417 8,333 1,250
45,000 35,417 8,333 1,250
45,000 35,417 8,333 1,250
45,000 35,417 8,333 1,250
Hard Cost Construction General Costs Site Work Concrete Work Masonry Metals Carpentry & Millwork Moisture Protection Windows & Doors Finishing Specialities Equipment Furnishings Special Construction Conveying System Mechanical Electrical Contingency
2,353,125 145,833 45,833 300,000 16,667 39,583 212,500 18,333 295,833 345,833 52,083 87,500 312,500 5,625 54,167 170,833 93,750 156,250
2,353,125 145,833 45,833 300,000 16,667 39,583 212,500 18,333 295,833 345,833 52,083 87,500 312,500 5,625 54,167 170,833 93,750 156,250
2,353,125 145,833 45,833 300,000 16,667 39,583 212,500 18,333 295,833 345,833 52,083 87,500 312,500 5,625 54,167 170,833 93,750 156,250
2,353,125 145,833 45,833 300,000 16,667 39,583 212,500 18,333 295,833 345,833 52,083 87,500 312,500 5,625 54,167 170,833 93,750 156,250
2,353,125 145,833 45,833 300,000 16,667 39,583 212,500 18,333 295,833 345,833 52,083 87,500 312,500 5,625 54,167 170,833 93,750 156,250
2,353,125 145,833 45,833 300,000 16,667 39,583 212,500 18,333 295,833 345,833 52,083 87,500 312,500 5,625 54,167 170,833 93,750 156,250
2,353,125 145,833 45,833 300,000 16,667 39,583 212,500 18,333 295,833 345,833 52,083 87,500 312,500 5,625 54,167 170,833 93,750 156,250
2,353,125 145,833 45,833 300,000 16,667 39,583 212,500 18,333 295,833 345,833 52,083 87,500 312,500 5,625 54,167 170,833 93,750 156,250
2,353,125 145,833 45,833 300,000 16,667 39,583 212,500 18,333 295,833 345,833 52,083 87,500 312,500 5,625 54,167 170,833 93,750 156,250
2,353,125 145,833 45,833 300,000 16,667 39,583 212,500 18,333 295,833 345,833 52,083 87,500 312,500 5,625 54,167 170,833 93,750 156,250
2,353,125 145,833 45,833 300,000 16,667 39,583 212,500 18,333 295,833 345,833 52,083 87,500 312,500 5,625 54,167 170,833 93,750 156,250
2,353,125 145,833 45,833 300,000 16,667 39,583 212,500 18,333 295,833 345,833 52,083 87,500 312,500 5,625 54,167 170,833 93,750 156,250
Buidling Operations Pre‐Opening Expenses Working Capital Contribution
90,625 90,625 90,625 90,625 90,625 90,625 90,625 90,625 90,625 90,625 90,625 90,625 77,083 77,083 77,083 77,083 77,083 77,083 77,083 77,083 77,083 77,083 77,083 77,083 13,542 13,542 13,542 13,542 13,542 13,542 13,542 13,542 13,542 13,542 13,542 13,542
Financing Expense Construction Loan Interest Financing Fee
265,079
‐
‐
‐
‐
‐
‐
‐
‐
‐
‐
‐
265,079 ‐
‐
‐
‐
‐
‐
‐
‐
‐
‐
‐
Total Project Development Budget less Loan Interest
9,326,745
2,561,667
2,561,667
2,561,667
2,561,667
2,561,667
2,561,667
2,561,667
2,561,667
2,561,667
2,561,667
Roger Staiger III [email protected]
2,561,667
10 of 32
Development Page (3 of 3) Development Cash Flow: Equity BoP Equity Draw Equity EoP Equity FV * Financial Close Construction Debt BoP Construction Debt Interest Construction Debt Draw Construction Debt EoP
Roger Staiger III [email protected]
18,649,002
2,442,548
17,671,907 9,326,745 8,345,162 10,262,745
8,345,162 2,561,667 5,783,495 2,807,051
5,783,495 2,561,667 3,221,828 2,795,403
3,221,827 2,561,667 660,161 2,783,804
660,161 660,161 ‐ 714,430
‐ ‐ ‐ ‐
‐ ‐ ‐ ‐
‐ ‐ ‐ ‐
‐ ‐ ‐ ‐
‐ ‐ ‐ ‐
‐ ‐ ‐ ‐
‐ ‐ 1,901,506 1,901,506
1,901,505 9,508 2,561,667 4,472,679
4,472,679 22,363 2,561,667 7,056,709
11 of 32
Pro Forma Page (1 of 3) (Today) Operations
Periods Days Month Year
1 31 Jan‐16 1
2 29 Feb‐16 1
3 31 Mar‐16 1
4 30 Apr‐16 1
5 31 May‐16 1
6 30 Jun‐16 1
7 31 Jul‐16 1
8 31 Aug‐16 1
9 30 Sep‐16 1
10 31 Oct‐16 1
11 30 Nov‐16 1
12 31 Dec‐16 1
Occupancy Average Daily Rate RevPar
68.00% 68.00% 68.00% 68.00% 68.00% 68.00% 68.00% 68.00% 68.00% 68.00% 68.00% 68.00% 280.00 280.00 280.00 280.00 280.00 280.00 280.00 280.00 280.00 280.00 280.00 280.00 190.40 190.40 190.40 190.40 190.40 190.40 190.40 190.40 190.40 190.40 190.40 190.40
Occupied Rooms Available Rooms
2,635 2,465 2,635 2,550 2,635 2,550 2,635 2,635 2,550 2,635 2,550 2,635 3,875 3,625 3,875 3,750 3,875 3,750 3,875 3,875 3,750 3,875 3,750 3,875
Department Revenue Rooms Restaurant Lounge Banquet Other Food & Beverage Total Food & Beverage Telephone Garage Gift Shop Spa Other Rents & Commissions Other Total Revenue
737,800 131,750 52,700 445,890
690,200 123,250 49,300 417,123
737,800 131,750 52,700 445,890
714,000 127,500 51,000 431,507
737,800 131,750 52,700 445,890
714,000 127,500 51,000 431,507
737,800 131,750 52,700 445,890
737,800 131,750 52,700 445,890
714,000 127,500 51,000 431,507
737,800 131,750 52,700 445,890
714,000 127,500 51,000 431,507
737,800 131,750 52,700 445,890
630,340 2,635 13,175 2,635 84,932 21,080
589,673 2,465 12,325 2,465 79,452 19,720
630,340 2,635 13,175 2,635 84,932 21,080
610,007 2,550 12,750 2,550 82,192 20,400
630,340 2,635 13,175 2,635 84,932 21,080
610,007 2,550 12,750 2,550 82,192 20,400
630,340 2,635 13,175 2,635 84,932 21,080
630,340 2,635 13,175 2,635 84,932 21,080
610,007 2,550 12,750 2,550 82,192 20,400
630,340 2,635 13,175 2,635 84,932 21,080
610,007 2,550 12,750 2,550 82,192 20,400
630,340 2,635 13,175 2,635 84,932 21,080
1,492,597
1,396,300
######## ########
####### ####### ####### ####### ####### ####### ####### #######
Department Expenses Rooms Restaurant Lounge Banquet Other Food & Beverage Total Food & Beverage Telephone Garage Gift Shop Spa Other Rents & Commissions Other Total Department Expenses
236,096 111,988 34,255 356,712 ‐ 502,955 2,635 6,588 2,372 72,192 1,054 ‐ 823,891
220,864 104,763 32,045 333,699 ‐ 470,506 2,465 6,163 2,219 67,534 986 ‐ 770,736
236,096 111,988 34,255 356,712 ‐ 502,955 2,635 6,588 2,372 72,192 1,054 ‐ 823,891
228,480 108,375 33,150 345,205 ‐ 486,730 2,550 6,375 2,295 69,863 1,020 ‐ 797,313
236,096 111,988 34,255 356,712 ‐ 502,955 2,635 6,588 2,372 72,192 1,054 ‐ 823,891
228,480 108,375 33,150 345,205 ‐ 486,730 2,550 6,375 2,295 69,863 1,020 ‐ 797,313
236,096 111,988 34,255 356,712 ‐ 502,955 2,635 6,588 2,372 72,192 1,054 ‐ 823,891
236,096 111,988 34,255 356,712 ‐ 502,955 2,635 6,588 2,372 72,192 1,054 ‐ 823,891
228,480 108,375 33,150 345,205 ‐ 486,730 2,550 6,375 2,295 69,863 1,020 ‐ 797,313
236,096 111,988 34,255 356,712 ‐ 502,955 2,635 6,588 2,372 72,192 1,054 ‐ 823,891
228,480 108,375 33,150 345,205 ‐ 486,730 2,550 6,375 2,295 69,863 1,020 ‐ 797,313
236,096 111,988 34,255 356,712 ‐ 502,955 2,635 6,588 2,372 72,192 1,054 ‐ 823,891
Gross Operating Profit
668,706
625,564
668,706
647,135
668,706
647,135
668,706
668,706
647,135
668,706
647,135
668,706
Roger Staiger III [email protected]
12 of 32
Pro Forma Page (2 of 3) (Today) Unallocated Departments General & Administrative Repairs & Maintenance Sale & Marketing Utilities Other Total Unallocated Departments
0.07788439 0.058153678 0.058153678 0.025123997
Other Deductions Management Fee FF&E Rep and Capital Rsvs Property Taxes Insurance Permits & Licenses Equipment Rental Other Total Other Deductions Furnitures, Fixtures and Equipment Net Operating Income Debt Tranche 1 Tranche 2 Total Debt
116,250 86,800 86,800 37,500
108,750 81,200 81,200 37,500
116,250 86,800 86,800 37,500
112,500 84,000 84,000 37,500
116,250 86,800 86,800 37,500
112,500 84,000 84,000 37,500
116,250 86,800 86,800 37,500
116,250 86,800 86,800 37,500
112,500 84,000 84,000 37,500
116,250 86,800 86,800 37,500
112,500 84,000 84,000 37,500
116,250 86,800 86,800 37,500
327,350
308,650
327,350
318,000
327,350
318,000
327,350
327,350
318,000
327,350
318,000
327,350
44,778 44,778 50,959 8,493 1,699 3,397
41,889 41,889 47,671 7,945 1,589 3,178
44,778 44,778 50,959 8,493 1,699 3,397
43,333 43,333 49,315 8,219 1,644 3,288
44,778 44,778 50,959 8,493 1,699 3,397
43,333 43,333 49,315 8,219 1,644 3,288
44,778 44,778 50,959 8,493 1,699 3,397
44,778 44,778 50,959 8,493 1,699 3,397
43,333 43,333 49,315 8,219 1,644 3,288
44,778 44,778 50,959 8,493 1,699 3,397
43,333 43,333 49,315 8,219 1,644 3,288
44,778 44,778 50,959 8,493 1,699 3,397
154,104
144,162
154,104
149,133
154,104
149,133
154,104
154,104
149,133
154,104
149,133
154,104
187,253
172,752
187,253
180,002
187,253
180,002
187,253
187,253
180,002
187,253
180,002
187,253
220,899 220,899 220,899 220,899 220,899 220,899 220,899 220,899 220,899 220,899 220,899 220,899 220,899
220,899
220,899
220,899
220,899
220,899
220,899
220,899
220,899
220,899
220,899
220,899
‐ ‐ ‐
‐ ‐ ‐
‐ ‐ ‐
‐ ‐ ‐
‐ ‐ ‐
‐ ‐ ‐
‐ ‐ ‐
‐ ‐ ‐
‐ ‐ ‐
‐ ‐ ‐
‐ ‐ ‐
‐ ‐ ‐
‐
‐
‐
‐
‐
‐
‐
‐
‐
‐
‐
‐
Sales Proceeds Sales Price Sales Expense Principal Repay ‐ Tranche 1 Principal Repay ‐ Tranche 2 Net Sales Proceeds Reversion FCF Operating FCF Total FCF
Roger Staiger III [email protected]
‐ ‐ ‐ ‐ ‐ ‐ ‐ ‐ ‐ ‐ ‐ ‐ (33,646) (48,146) (33,646) (40,896) (33,646) (40,896) (33,646) (33,646) (40,896) (33,646) (40,896) (33,646) (33,646) (48,146) (33,646) (40,896) (33,646) (40,896) (33,646) (33,646) (40,896) (33,646) (40,896) (33,646)
13 of 32
Pro Forma Page (3 of 3) (Valuation) (Today) Valuation: Equity Reversion FCF Operating FCF
Dec‐15 (18,649,002)
Feb‐16
Mar‐16
Apr‐16
May‐16
Jun‐16
Jul‐16
Aug‐16
Sep‐16
Oct‐16
Nov‐16
Dec‐16
‐ ‐ ‐ ‐ ‐ ‐ ‐ ‐ ‐ ‐ ‐ ‐ (33,646) (48,146) (33,646) (40,896) (33,646) (40,896) (33,646) (33,646) (40,896) (33,646) (40,896) (33,646) (18,649,002) (33,646) (48,146) (33,646) (40,896) (33,646) (40,896) (33,646) (33,646) (40,896) (33,646) (40,896) (33,646)
DSCR Average Minimum *RevPar Index (Star Report) Cash‐on‐Cash Average Minimum PV Reversion PV Operating Total CF0 New Present Value IRR
Jan‐16
72,226,968 12,418,101 84,645,069 (18,649,002) 65,996,066 25.05%
Roger Staiger III [email protected]
0.85 1.67 0.78 ‐0.18% 0.79% ‐0.26%
0.78
‐0.26%
0.85
‐0.18%
0.81
‐0.22%
0.85
‐0.18%
0.81
‐0.22%
0.85
‐0.18%
0.85
‐0.18%
0.81
‐0.22%
0.85
‐0.18%
0.81
‐0.22%
0.85
‐0.18%
85.33% 14.67% 100.00%
14 of 32
Amortization Page (Today/Next Session) Permanent Principal Interest Term I/O # 1 2 3 4 5 6 7 8 9 10 11 12
Roger Staiger III [email protected]
BoP 53,015,720 53,015,720 53,015,720 53,015,720 53,015,720 53,015,720 53,015,720 53,015,720 53,015,720 53,015,720 53,015,720 53,015,720
53,015,720 5.00% 30 Y Principal ‐ ‐ ‐ ‐ ‐ ‐ ‐ ‐ ‐ ‐ ‐ ‐
Interest 220,899 220,899 220,899 220,899 220,899 220,899 220,899 220,899 220,899 220,899 220,899 220,899
Payment 220,899 220,899 220,899 220,899 220,899 220,899 220,899 220,899 220,899 220,899 220,899 220,899
EoP Annual 53,015,720 53,015,720 53,015,720 53,015,720 53,015,720 53,015,720 53,015,720 53,015,720 53,015,720 53,015,720 53,015,720 53,015,720 2,650,786
15 of 32
Annual Summary (1 of 2) Annual Summary: Revenue Rooms F&B Total Telephone Garage Gift Shop Spa Other Rents & Commissions Other Total Revenue
1
2
3
4
5
6
8,710,800 7,442,084 31,110 155,550 31,110 1,002,740 248,880 ‐ 17,622,273
9,526,500 8,706,500 32,850 229,950 65,700 1,200,000 295,650 ‐ 20,057,150
10,265,625 9,921,875 34,219 273,750 68,438 1,500,000 342,188 ‐ 22,406,094
10,573,594 10,219,531 34,561 281,963 70,491 1,575,000 352,453 ‐ 23,107,592
10,920,639 10,554,956 35,002 291,217 72,804 1,658,281 364,021 ‐ 23,896,921
11,217,526 10,841,901 35,256 299,134 74,784 1,736,438 373,918 ‐ 24,578,954
Expense Department Rooms F&B Total Telephone Garage Gift Shop Spa Other Rents & Commissions Other Total Department
2,787,456 5,938,112 31,110 77,775 27,999 852,329 12,444 ‐ 9,727,225
2,953,215 6,340,950 32,850 114,975 59,130 960,000 14,783 ‐ 10,475,903
3,079,688 7,082,188 34,219 136,875 61,594 1,125,000 17,109 ‐ 11,536,672
3,172,078 7,294,653 34,561 140,981 63,442 1,181,250 17,623 ‐ 11,904,588
3,276,192 7,534,078 35,002 145,609 65,524 1,243,711 18,201 ‐ 12,318,316
3,365,258 7,738,898 35,256 149,567 67,305 1,302,328 18,696 ‐ 12,677,307
Gross Profit
7,895,049
9,581,248
10,869,422
11,203,005
11,578,605
11,901,648
Roger Staiger III [email protected]
Year
16 of 32
Annual Summary (2 of 2) Unallocated Departments General & Administrative Repairs & Maintenance Sale & Marketing Utilities Other Total Unallocated Depts
1,372,500 1,024,800 1,024,800 450,000 ‐ 3,872,100
1,414,375 1,022,000 1,022,000 450,000 ‐ 3,908,375
1,460,000 1,022,000 1,022,000 450,000 ‐ 3,954,000
1,503,800 1,052,660 1,052,660 463,500 ‐ 4,072,620
1,553,158 1,087,210 1,087,210 477,405 ‐ 4,204,983
1,595,381 1,116,767 1,116,767 491,727 ‐ 4,320,643
Other Deductions Management Fee FF&E Rep and Capital Rsvs Property Taxes Insurance Permits & Licenses Equipment Rental Other Total Other Deductions
528,668 528,668 601,644 100,274 20,055 40,110 ‐ 1,819,419
601,715 802,286 625,000 100,000 20,000 40,000 ‐ 2,189,001
672,183 1,120,305 650,000 100,000 20,000 40,000 ‐ 2,602,488
693,228 1,155,380 669,500 104,000 20,400 41,200 ‐ 2,683,707
716,908 1,194,846 691,474 108,456 20,865 42,552 ‐ 2,775,102
737,369 1,228,948 710,273 112,486 21,224 43,709 ‐ 2,854,009
Net Operating Income
2,203,530
3,483,872
4,312,934
4,446,677
4,598,520
4,726,996
Debt Tranche 1 Tranche 2 Total Debt
2,650,786 ‐ 2,650,786
2,650,786 ‐ 2,650,786
2,650,786 ‐ 2,650,786
2,650,786 ‐ 2,650,786
2,650,786 ‐ 2,650,786
2,650,786 ‐ 2,650,786
Net Sales Proceeds
‐
‐
‐
‐
‐
‐
Reversion FCF Operations FCF Total FCF
‐ ‐ (447,256) 833,086 (447,256) 833,086
‐ 1,662,148 1,662,148
‐ 1,795,891 1,795,891
‐ 1,947,734 1,947,734
‐ 2,076,210 2,076,210
Roger Staiger III [email protected]
17 of 32
Inputs - Characteristics Number of Rooms Total Area (sf) Banquet Area (sf)
Roger Staiger III [email protected]
18 of 32
Sources Sources Equity Investor 1 Investor 2
Debt Tranche 1 (source) Tranche 2 (source)
Roger Staiger III [email protected]
19 of 32
Uses Uses Land & Related General & Administrative Development Charges & Municipal Costs Hard Cost Construction Building Operations Finance Expense
Roger Staiger III [email protected]
20 of 32
Input - Revenue Rooms Occupancy Average Daily Rate
Food & Beverage Restaurant Lounge Banquet Other F&B
Roger Staiger III [email protected]
Other Departments Telephone/Internet Garage Gift Shop Spa Other Rents & Commissions Other
Page 21 of 32
Input - Expense Department Rooms Restaurant Lounge Banquet Other F&B Telephone/Internet Garage Spa Rents & Commissions Roger Staiger III [email protected]
Unallocated Dept. G&A Repairs & Maint Sales & Marketing Utilities
Page 22 of 32
Input – Expense (Cont’d) Other Deductions Management Fee FF&E Rep and Capital Reserves Property Taxes Insurance Permits & Licenses Equipment Rental
Roger Staiger III [email protected]
23 of 32
Inputs - Finance Tranche 1 Principal Term(years) Interest Rate Interest Only Origination Fee
Roger Staiger III [email protected]
Tranche 2 Principal Term(years) Interest Rate Interest Only Origination Fee
24 of 32
Inputs - Valuation Capitalization Rate Sales Expense Discount Rate Sale Period (timing)
Roger Staiger III [email protected]
25 of 32
Scalability The benefit(s) of successful pro forma modeling
Scalability (Incorporating the Waterfall) Separation of cash flows according to owners’ agreement Separation of cash flows by yield, NOT by order Order: Bank vanilla example
“Pref” Rate: Preference Rate Generally provided to Investor Acts as a coupon rate, e.g. Preferred Equity Roger Staiger III [email protected]
27 of 32
Waterfall Diagram Pari Passu 1st Tier
“Pref” Rate 15% good proxy for “Pref” Non Pari Passu split 2nd Tier
2nd Tier rate N # Tiers Nth Tier rate Nth Tier Final Tier Example – Final Distribution X % to Promote to Sponsor (1‐X%) distribution Pari Passu
Roger Staiger III [email protected]
28 of 32
Waterfall Logic Sponsor 10% 15% IRR
20% IRR
Sponsor 20%
Promote Sponsor 40%
20% + IRR Sponsor 10% Investor 90%
Investor 80% Investor 90%
Roger Staiger III [email protected]
29 of 32
Waterfall Addition (100% Scalable) Circular Reference Development Page (s2)
Summary Page Inputs Section (s1)
Amortization Page (s3/s4)
Analysis (s4)
Waterfall Page (Investor) (s5)
Roger Staiger III [email protected]
Pro Forma Page (s3)
30 of 32
ULI – Staiger (Upcoming Seminars) 1) Four part 2-hour series: How to build a Hotel pro forma model (Current Pro Forma Webinar) 2) Four part 2-hour series: Advanced Pro Forma Webinar (Multifamily Focus) 3) Two part 2-hour series: How to develop a waterfall
Roger Staiger III [email protected]
31 of 32
Questions? Let’s Begin (see memorandum for detailed steps of process at conclusion)
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 III [email protected]
2 of 32
Structure 4-Part Sessions: 2-hours each
Summary Page (1st half) [Complete] Development/Draw Page [Complete] Pro Forma/Amortization Page(s) Pulling it all together/Valuation Bottom half of Summary Page
Roger Staiger III [email protected]
3 of 32
Hotel Pro Forma Model 125 Key Hotel 75,000 SF; 10,000 Banquet SF
Full Service Development/Construction/Stabilize Focused on BASIC Modeling skills Result: completed scalable and modular Hotel pro forma model
Roger Staiger III [email protected]
4 of 32
Model Summary 125 Keys with Banquet space (Pretax analysis) Occupancy Rate Average Daily Rate/Escalation Capital Sourcing Develop Costs (HC = $57.0m) Valuation Roger Staiger III [email protected]
5 of 32
Structure/Logic Flow Circular Reference
Development Page (s2)
Summary Page
Amortization Page (s3/s4)
Inputs Section (s1) Analysis (s4) Pro Forma Page (s3)
Roger Staiger III [email protected]
6 of 32
Inputs Page (1 of 2) Sources Equity Investor 1 Investor 2 Debt Tranche 1 Tranche 2
$ $/Key 70,687,627 565,501 17,671,907 141,375 17,671,907 141,375
Revenue Rooms Occupancy Average Daily Rate
53,015,720 424,126 53,015,720 424,126
Uses Land & Related General & Administrative Dev Chrgs & Mun Costs Hard Cost Construction Buidling Operations Financing Expense
70,687,627 6,500,000 1,750,000 1,080,000 56,475,000 2,175,000 2,707,627
Surplus/(Deficit)
0
PV Reversion PV Operating Total CF0 New Present Value IRR
72,226,968 12,418,101 84,645,069 (18,649,002) 65,996,066 25.05%
Roger Staiger III [email protected]
565,501 52,000 14,000 8,640 451,800 17,400 21,661
85.33% 14.67%
Year
1
2
3
Escalation
Metric
68% 72% 75% 280.00 290.00 300.00
3% POR
Food & Beverage Restaurant Lounge Banquet Other Food & Beverage
50.00 65.00 70.00 20.00 25.00 30.00 525.00 575.00 650.00
3% POR 3% POR 3% PBSF
Other Departments Telephone/Internet Garage Gift Shop Spa Other Rents & Commissions Other
1.00 5.00 1.00 1,000,000 8.00
1% 3% 3% 5% 3%
1.00 7.00 2.00 1,200,000 9.00
Characteristics Number of Rooms Total Area (sf) Banquet Area (sf) DSCR Average Minimum
1.67 0.78
1.00 8.00 2.00 1,500,000 10.00
POR POR POR Total $ POR
125 75,000 10,000 Cash‐on‐Cash Average Minimum
‐98.81% ‐193.00%
7 of 32
Input Page (2 of 2) Expense Department Rooms Restaurant Lounge Banquet Other F&B Telephone/Internet Garage Gift Shop Spa Other Rents & Commissions Other
Year
1 32.0% 85.0% 65.0% 80.0% 100.0% 50.0% 90.0% 85.0% 5.0%
Unallocated Departments General & Administrative Repairs & Maintenance Sales & Marketing Utilities Other
30.00 22.40 22.40 0.50
Other Deductions Management Fee FF&E Rep and Capital Reserves Property Taxes Insurance Permits & Licenses Equipment Rental Other
3.0% 3.0% 600,000 100,000 20,000 40,000
Roger Staiger III [email protected]
2 31.0% 80.0% 60.0% 72.0% 100.0% 50.0% 90.0% 80.0% 5.0%
31.00 22.40 22.40 0.50
3.0% 4.0% 625,000 100,000 20,000 40,000
3
Escalation Metric
30.0% 80.0% 60.0% 70.0%
% Rev % Rev % Rev % Rev % Rev % Rev % Rev % Rev % Rev % Rev % Rev
100.0% 50.0% 90.0% 75.0% 5.0%
32.00 22.40 22.40 0.50
3.0% 5.0% 650,000 100,000 20,000 40,000
3% 3% 3% 3%
3% 4% 2% 3%
PAR PAR PAR PSF
% Ttl Rev % Ttl Rev Total $ Total $ Total $ Total $
Finance Construction LtV Principal Term (Years) Interest Rate Interest Only Origination Fee Permanent LtV Principal Month Take‐Out Term (Years) Interest Rate Interest Only Origination Fee Valuation Capitalization Rate Sales Expense Discount Rate Sales Period
I/P Const_LtV
75%
6.00% Y 1.00%
Perm_LtV 75% Perm_Princ 53,015,720 30 5% Y 0.50% I/P 8% 10% 6% 10
8 of 32
Development Page (1 of 3) Development Start Construction Months Opening Date
1‐Jan‐14 24 1‐Jan‐16 1
Cost Item
Jan‐14
Land & Related Land & Related
Land_Rela
General & Administrative Legal Fees Development Management Fees Insurance & Warranty Other G&A
G_A
Dev Chrgs & Mun Costs Development Charges Building Permits Municipal Costs
Dev_Chrg_
Hard Cost Construction General Costs Site Work Concrete Work Masonry Metals Carpentry & Millwork Moisture Protection Windows & Doors Finishing Specialities Equipment Furnishings Special Construction Conveying System Mechanical Electrical Contingency
HC_Const
Buidling Operations Pre‐Opening Expenses Working Capital Contribution
Building_O
Financing Expense Construction Loan Interest Financing Fee
Fnan_Exp
Roger Staiger III [email protected]
2 Feb‐14
3 Mar‐14
4 Apr‐14
5 May‐14
6 Jun‐14
7 Jul‐14
8 Aug‐14
9 Sep‐14
10 Oct‐14
11 Nov‐14
12 Dec‐14
6,500,000 6,500,000
100.00%
1,750,000 200,000 600,000 650,000 300,000
4.17% 4.17% 4.17% 4.17%
4.17% 4.17% 4.17% 4.17%
4.17% 4.17% 4.17% 4.17%
4.17% 4.17% 4.17% 4.17%
4.17% 4.17% 4.17% 4.17%
4.17% 4.17% 4.17% 4.17%
4.17% 4.17% 4.17% 4.17%
4.17% 4.17% 4.17% 4.17%
4.17% 4.17% 4.17% 4.17%
4.17% 4.17% 4.17% 4.17%
4.17% 4.17% 4.17% 4.17%
4.17% 4.17% 4.17% 4.17%
1,080,000 850,000 200,000 30,000
4.17% 4.17% 4.17%
4.17% 4.17% 4.17%
4.17% 4.17% 4.17%
4.17% 4.17% 4.17%
4.17% 4.17% 4.17%
4.17% 4.17% 4.17%
4.17% 4.17% 4.17%
4.17% 4.17% 4.17%
4.17% 4.17% 4.17%
4.17% 4.17% 4.17%
4.17% 4.17% 4.17%
4.17% 4.17% 4.17%
56,475,000 3,500,000 1,100,000 7,200,000 400,000 950,000 5,100,000 440,000 7,100,000 8,300,000 1,250,000 2,100,000 7,500,000 135,000 1,300,000 4,100,000 2,250,000 3,750,000
4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17%
4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17%
4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17%
4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17%
4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17%
4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17%
4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17%
4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17%
4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17%
4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17%
4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17%
4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17% 4.17%
2,175,000 1,850,000 325,000
4.17% 4.17%
4.17% 4.17%
4.17% 4.17%
4.17% 4.17%
4.17% 4.17%
4.17% 4.17%
4.17% 4.17%
4.17% 4.17%
4.17% 4.17%
4.17% 4.17%
4.17% 4.17%
4.17% 4.17%
2,707,627 2,442,548 265,079
100.00%
9 of 32
Development Page (2 of 3) Cost Item Land & Related Land & Related
6,500,000 ‐ 6,500,000 ‐
‐ ‐
‐ ‐
‐ ‐
‐ ‐
‐ ‐
‐ ‐
‐ ‐
‐ ‐
‐ ‐
‐ ‐
General & Administrative Legal Fees Development Management Fees Insurance & Warranty Other G&A
72,917 8,333 25,000 27,083 12,500
72,917 8,333 25,000 27,083 12,500
72,917 8,333 25,000 27,083 12,500
72,917 8,333 25,000 27,083 12,500
72,917 8,333 25,000 27,083 12,500
72,917 8,333 25,000 27,083 12,500
72,917 8,333 25,000 27,083 12,500
72,917 8,333 25,000 27,083 12,500
72,917 8,333 25,000 27,083 12,500
72,917 8,333 25,000 27,083 12,500
72,917 8,333 25,000 27,083 12,500
72,917 8,333 25,000 27,083 12,500
Dev Chrgs & Mun Costs Development Charges Building Permits Municipal Costs
45,000 35,417 8,333 1,250
45,000 35,417 8,333 1,250
45,000 35,417 8,333 1,250
45,000 35,417 8,333 1,250
45,000 35,417 8,333 1,250
45,000 35,417 8,333 1,250
45,000 35,417 8,333 1,250
45,000 35,417 8,333 1,250
45,000 35,417 8,333 1,250
45,000 35,417 8,333 1,250
45,000 35,417 8,333 1,250
45,000 35,417 8,333 1,250
Hard Cost Construction General Costs Site Work Concrete Work Masonry Metals Carpentry & Millwork Moisture Protection Windows & Doors Finishing Specialities Equipment Furnishings Special Construction Conveying System Mechanical Electrical Contingency
2,353,125 145,833 45,833 300,000 16,667 39,583 212,500 18,333 295,833 345,833 52,083 87,500 312,500 5,625 54,167 170,833 93,750 156,250
2,353,125 145,833 45,833 300,000 16,667 39,583 212,500 18,333 295,833 345,833 52,083 87,500 312,500 5,625 54,167 170,833 93,750 156,250
2,353,125 145,833 45,833 300,000 16,667 39,583 212,500 18,333 295,833 345,833 52,083 87,500 312,500 5,625 54,167 170,833 93,750 156,250
2,353,125 145,833 45,833 300,000 16,667 39,583 212,500 18,333 295,833 345,833 52,083 87,500 312,500 5,625 54,167 170,833 93,750 156,250
2,353,125 145,833 45,833 300,000 16,667 39,583 212,500 18,333 295,833 345,833 52,083 87,500 312,500 5,625 54,167 170,833 93,750 156,250
2,353,125 145,833 45,833 300,000 16,667 39,583 212,500 18,333 295,833 345,833 52,083 87,500 312,500 5,625 54,167 170,833 93,750 156,250
2,353,125 145,833 45,833 300,000 16,667 39,583 212,500 18,333 295,833 345,833 52,083 87,500 312,500 5,625 54,167 170,833 93,750 156,250
2,353,125 145,833 45,833 300,000 16,667 39,583 212,500 18,333 295,833 345,833 52,083 87,500 312,500 5,625 54,167 170,833 93,750 156,250
2,353,125 145,833 45,833 300,000 16,667 39,583 212,500 18,333 295,833 345,833 52,083 87,500 312,500 5,625 54,167 170,833 93,750 156,250
2,353,125 145,833 45,833 300,000 16,667 39,583 212,500 18,333 295,833 345,833 52,083 87,500 312,500 5,625 54,167 170,833 93,750 156,250
2,353,125 145,833 45,833 300,000 16,667 39,583 212,500 18,333 295,833 345,833 52,083 87,500 312,500 5,625 54,167 170,833 93,750 156,250
2,353,125 145,833 45,833 300,000 16,667 39,583 212,500 18,333 295,833 345,833 52,083 87,500 312,500 5,625 54,167 170,833 93,750 156,250
Buidling Operations Pre‐Opening Expenses Working Capital Contribution
90,625 90,625 90,625 90,625 90,625 90,625 90,625 90,625 90,625 90,625 90,625 90,625 77,083 77,083 77,083 77,083 77,083 77,083 77,083 77,083 77,083 77,083 77,083 77,083 13,542 13,542 13,542 13,542 13,542 13,542 13,542 13,542 13,542 13,542 13,542 13,542
Financing Expense Construction Loan Interest Financing Fee
265,079
‐
‐
‐
‐
‐
‐
‐
‐
‐
‐
‐
265,079 ‐
‐
‐
‐
‐
‐
‐
‐
‐
‐
‐
Total Project Development Budget less Loan Interest
9,326,745
2,561,667
2,561,667
2,561,667
2,561,667
2,561,667
2,561,667
2,561,667
2,561,667
2,561,667
2,561,667
Roger Staiger III [email protected]
2,561,667
10 of 32
Development Page (3 of 3) Development Cash Flow: Equity BoP Equity Draw Equity EoP Equity FV * Financial Close Construction Debt BoP Construction Debt Interest Construction Debt Draw Construction Debt EoP
Roger Staiger III [email protected]
18,649,002
2,442,548
17,671,907 9,326,745 8,345,162 10,262,745
8,345,162 2,561,667 5,783,495 2,807,051
5,783,495 2,561,667 3,221,828 2,795,403
3,221,827 2,561,667 660,161 2,783,804
660,161 660,161 ‐ 714,430
‐ ‐ ‐ ‐
‐ ‐ ‐ ‐
‐ ‐ ‐ ‐
‐ ‐ ‐ ‐
‐ ‐ ‐ ‐
‐ ‐ ‐ ‐
‐ ‐ 1,901,506 1,901,506
1,901,505 9,508 2,561,667 4,472,679
4,472,679 22,363 2,561,667 7,056,709
11 of 32
Pro Forma Page (1 of 3) (Today) Operations
Periods Days Month Year
1 31 Jan‐16 1
2 29 Feb‐16 1
3 31 Mar‐16 1
4 30 Apr‐16 1
5 31 May‐16 1
6 30 Jun‐16 1
7 31 Jul‐16 1
8 31 Aug‐16 1
9 30 Sep‐16 1
10 31 Oct‐16 1
11 30 Nov‐16 1
12 31 Dec‐16 1
Occupancy Average Daily Rate RevPar
68.00% 68.00% 68.00% 68.00% 68.00% 68.00% 68.00% 68.00% 68.00% 68.00% 68.00% 68.00% 280.00 280.00 280.00 280.00 280.00 280.00 280.00 280.00 280.00 280.00 280.00 280.00 190.40 190.40 190.40 190.40 190.40 190.40 190.40 190.40 190.40 190.40 190.40 190.40
Occupied Rooms Available Rooms
2,635 2,465 2,635 2,550 2,635 2,550 2,635 2,635 2,550 2,635 2,550 2,635 3,875 3,625 3,875 3,750 3,875 3,750 3,875 3,875 3,750 3,875 3,750 3,875
Department Revenue Rooms Restaurant Lounge Banquet Other Food & Beverage Total Food & Beverage Telephone Garage Gift Shop Spa Other Rents & Commissions Other Total Revenue
737,800 131,750 52,700 445,890
690,200 123,250 49,300 417,123
737,800 131,750 52,700 445,890
714,000 127,500 51,000 431,507
737,800 131,750 52,700 445,890
714,000 127,500 51,000 431,507
737,800 131,750 52,700 445,890
737,800 131,750 52,700 445,890
714,000 127,500 51,000 431,507
737,800 131,750 52,700 445,890
714,000 127,500 51,000 431,507
737,800 131,750 52,700 445,890
630,340 2,635 13,175 2,635 84,932 21,080
589,673 2,465 12,325 2,465 79,452 19,720
630,340 2,635 13,175 2,635 84,932 21,080
610,007 2,550 12,750 2,550 82,192 20,400
630,340 2,635 13,175 2,635 84,932 21,080
610,007 2,550 12,750 2,550 82,192 20,400
630,340 2,635 13,175 2,635 84,932 21,080
630,340 2,635 13,175 2,635 84,932 21,080
610,007 2,550 12,750 2,550 82,192 20,400
630,340 2,635 13,175 2,635 84,932 21,080
610,007 2,550 12,750 2,550 82,192 20,400
630,340 2,635 13,175 2,635 84,932 21,080
1,492,597
1,396,300
######## ########
####### ####### ####### ####### ####### ####### ####### #######
Department Expenses Rooms Restaurant Lounge Banquet Other Food & Beverage Total Food & Beverage Telephone Garage Gift Shop Spa Other Rents & Commissions Other Total Department Expenses
236,096 111,988 34,255 356,712 ‐ 502,955 2,635 6,588 2,372 72,192 1,054 ‐ 823,891
220,864 104,763 32,045 333,699 ‐ 470,506 2,465 6,163 2,219 67,534 986 ‐ 770,736
236,096 111,988 34,255 356,712 ‐ 502,955 2,635 6,588 2,372 72,192 1,054 ‐ 823,891
228,480 108,375 33,150 345,205 ‐ 486,730 2,550 6,375 2,295 69,863 1,020 ‐ 797,313
236,096 111,988 34,255 356,712 ‐ 502,955 2,635 6,588 2,372 72,192 1,054 ‐ 823,891
228,480 108,375 33,150 345,205 ‐ 486,730 2,550 6,375 2,295 69,863 1,020 ‐ 797,313
236,096 111,988 34,255 356,712 ‐ 502,955 2,635 6,588 2,372 72,192 1,054 ‐ 823,891
236,096 111,988 34,255 356,712 ‐ 502,955 2,635 6,588 2,372 72,192 1,054 ‐ 823,891
228,480 108,375 33,150 345,205 ‐ 486,730 2,550 6,375 2,295 69,863 1,020 ‐ 797,313
236,096 111,988 34,255 356,712 ‐ 502,955 2,635 6,588 2,372 72,192 1,054 ‐ 823,891
228,480 108,375 33,150 345,205 ‐ 486,730 2,550 6,375 2,295 69,863 1,020 ‐ 797,313
236,096 111,988 34,255 356,712 ‐ 502,955 2,635 6,588 2,372 72,192 1,054 ‐ 823,891
Gross Operating Profit
668,706
625,564
668,706
647,135
668,706
647,135
668,706
668,706
647,135
668,706
647,135
668,706
Roger Staiger III [email protected]
12 of 32
Pro Forma Page (2 of 3) (Today) Unallocated Departments General & Administrative Repairs & Maintenance Sale & Marketing Utilities Other Total Unallocated Departments
0.07788439 0.058153678 0.058153678 0.025123997
Other Deductions Management Fee FF&E Rep and Capital Rsvs Property Taxes Insurance Permits & Licenses Equipment Rental Other Total Other Deductions Furnitures, Fixtures and Equipment Net Operating Income Debt Tranche 1 Tranche 2 Total Debt
116,250 86,800 86,800 37,500
108,750 81,200 81,200 37,500
116,250 86,800 86,800 37,500
112,500 84,000 84,000 37,500
116,250 86,800 86,800 37,500
112,500 84,000 84,000 37,500
116,250 86,800 86,800 37,500
116,250 86,800 86,800 37,500
112,500 84,000 84,000 37,500
116,250 86,800 86,800 37,500
112,500 84,000 84,000 37,500
116,250 86,800 86,800 37,500
327,350
308,650
327,350
318,000
327,350
318,000
327,350
327,350
318,000
327,350
318,000
327,350
44,778 44,778 50,959 8,493 1,699 3,397
41,889 41,889 47,671 7,945 1,589 3,178
44,778 44,778 50,959 8,493 1,699 3,397
43,333 43,333 49,315 8,219 1,644 3,288
44,778 44,778 50,959 8,493 1,699 3,397
43,333 43,333 49,315 8,219 1,644 3,288
44,778 44,778 50,959 8,493 1,699 3,397
44,778 44,778 50,959 8,493 1,699 3,397
43,333 43,333 49,315 8,219 1,644 3,288
44,778 44,778 50,959 8,493 1,699 3,397
43,333 43,333 49,315 8,219 1,644 3,288
44,778 44,778 50,959 8,493 1,699 3,397
154,104
144,162
154,104
149,133
154,104
149,133
154,104
154,104
149,133
154,104
149,133
154,104
187,253
172,752
187,253
180,002
187,253
180,002
187,253
187,253
180,002
187,253
180,002
187,253
220,899 220,899 220,899 220,899 220,899 220,899 220,899 220,899 220,899 220,899 220,899 220,899 220,899
220,899
220,899
220,899
220,899
220,899
220,899
220,899
220,899
220,899
220,899
220,899
‐ ‐ ‐
‐ ‐ ‐
‐ ‐ ‐
‐ ‐ ‐
‐ ‐ ‐
‐ ‐ ‐
‐ ‐ ‐
‐ ‐ ‐
‐ ‐ ‐
‐ ‐ ‐
‐ ‐ ‐
‐ ‐ ‐
‐
‐
‐
‐
‐
‐
‐
‐
‐
‐
‐
‐
Sales Proceeds Sales Price Sales Expense Principal Repay ‐ Tranche 1 Principal Repay ‐ Tranche 2 Net Sales Proceeds Reversion FCF Operating FCF Total FCF
Roger Staiger III [email protected]
‐ ‐ ‐ ‐ ‐ ‐ ‐ ‐ ‐ ‐ ‐ ‐ (33,646) (48,146) (33,646) (40,896) (33,646) (40,896) (33,646) (33,646) (40,896) (33,646) (40,896) (33,646) (33,646) (48,146) (33,646) (40,896) (33,646) (40,896) (33,646) (33,646) (40,896) (33,646) (40,896) (33,646)
13 of 32
Pro Forma Page (3 of 3) (Valuation) (Today) Valuation: Equity Reversion FCF Operating FCF
Dec‐15 (18,649,002)
Feb‐16
Mar‐16
Apr‐16
May‐16
Jun‐16
Jul‐16
Aug‐16
Sep‐16
Oct‐16
Nov‐16
Dec‐16
‐ ‐ ‐ ‐ ‐ ‐ ‐ ‐ ‐ ‐ ‐ ‐ (33,646) (48,146) (33,646) (40,896) (33,646) (40,896) (33,646) (33,646) (40,896) (33,646) (40,896) (33,646) (18,649,002) (33,646) (48,146) (33,646) (40,896) (33,646) (40,896) (33,646) (33,646) (40,896) (33,646) (40,896) (33,646)
DSCR Average Minimum *RevPar Index (Star Report) Cash‐on‐Cash Average Minimum PV Reversion PV Operating Total CF0 New Present Value IRR
Jan‐16
72,226,968 12,418,101 84,645,069 (18,649,002) 65,996,066 25.05%
Roger Staiger III [email protected]
0.85 1.67 0.78 ‐0.18% 0.79% ‐0.26%
0.78
‐0.26%
0.85
‐0.18%
0.81
‐0.22%
0.85
‐0.18%
0.81
‐0.22%
0.85
‐0.18%
0.85
‐0.18%
0.81
‐0.22%
0.85
‐0.18%
0.81
‐0.22%
0.85
‐0.18%
85.33% 14.67% 100.00%
14 of 32
Amortization Page (Today/Next Session) Permanent Principal Interest Term I/O # 1 2 3 4 5 6 7 8 9 10 11 12
Roger Staiger III [email protected]
BoP 53,015,720 53,015,720 53,015,720 53,015,720 53,015,720 53,015,720 53,015,720 53,015,720 53,015,720 53,015,720 53,015,720 53,015,720
53,015,720 5.00% 30 Y Principal ‐ ‐ ‐ ‐ ‐ ‐ ‐ ‐ ‐ ‐ ‐ ‐
Interest 220,899 220,899 220,899 220,899 220,899 220,899 220,899 220,899 220,899 220,899 220,899 220,899
Payment 220,899 220,899 220,899 220,899 220,899 220,899 220,899 220,899 220,899 220,899 220,899 220,899
EoP Annual 53,015,720 53,015,720 53,015,720 53,015,720 53,015,720 53,015,720 53,015,720 53,015,720 53,015,720 53,015,720 53,015,720 53,015,720 2,650,786
15 of 32
Annual Summary (1 of 2) Annual Summary: Revenue Rooms F&B Total Telephone Garage Gift Shop Spa Other Rents & Commissions Other Total Revenue
1
2
3
4
5
6
8,710,800 7,442,084 31,110 155,550 31,110 1,002,740 248,880 ‐ 17,622,273
9,526,500 8,706,500 32,850 229,950 65,700 1,200,000 295,650 ‐ 20,057,150
10,265,625 9,921,875 34,219 273,750 68,438 1,500,000 342,188 ‐ 22,406,094
10,573,594 10,219,531 34,561 281,963 70,491 1,575,000 352,453 ‐ 23,107,592
10,920,639 10,554,956 35,002 291,217 72,804 1,658,281 364,021 ‐ 23,896,921
11,217,526 10,841,901 35,256 299,134 74,784 1,736,438 373,918 ‐ 24,578,954
Expense Department Rooms F&B Total Telephone Garage Gift Shop Spa Other Rents & Commissions Other Total Department
2,787,456 5,938,112 31,110 77,775 27,999 852,329 12,444 ‐ 9,727,225
2,953,215 6,340,950 32,850 114,975 59,130 960,000 14,783 ‐ 10,475,903
3,079,688 7,082,188 34,219 136,875 61,594 1,125,000 17,109 ‐ 11,536,672
3,172,078 7,294,653 34,561 140,981 63,442 1,181,250 17,623 ‐ 11,904,588
3,276,192 7,534,078 35,002 145,609 65,524 1,243,711 18,201 ‐ 12,318,316
3,365,258 7,738,898 35,256 149,567 67,305 1,302,328 18,696 ‐ 12,677,307
Gross Profit
7,895,049
9,581,248
10,869,422
11,203,005
11,578,605
11,901,648
Roger Staiger III [email protected]
Year
16 of 32
Annual Summary (2 of 2) Unallocated Departments General & Administrative Repairs & Maintenance Sale & Marketing Utilities Other Total Unallocated Depts
1,372,500 1,024,800 1,024,800 450,000 ‐ 3,872,100
1,414,375 1,022,000 1,022,000 450,000 ‐ 3,908,375
1,460,000 1,022,000 1,022,000 450,000 ‐ 3,954,000
1,503,800 1,052,660 1,052,660 463,500 ‐ 4,072,620
1,553,158 1,087,210 1,087,210 477,405 ‐ 4,204,983
1,595,381 1,116,767 1,116,767 491,727 ‐ 4,320,643
Other Deductions Management Fee FF&E Rep and Capital Rsvs Property Taxes Insurance Permits & Licenses Equipment Rental Other Total Other Deductions
528,668 528,668 601,644 100,274 20,055 40,110 ‐ 1,819,419
601,715 802,286 625,000 100,000 20,000 40,000 ‐ 2,189,001
672,183 1,120,305 650,000 100,000 20,000 40,000 ‐ 2,602,488
693,228 1,155,380 669,500 104,000 20,400 41,200 ‐ 2,683,707
716,908 1,194,846 691,474 108,456 20,865 42,552 ‐ 2,775,102
737,369 1,228,948 710,273 112,486 21,224 43,709 ‐ 2,854,009
Net Operating Income
2,203,530
3,483,872
4,312,934
4,446,677
4,598,520
4,726,996
Debt Tranche 1 Tranche 2 Total Debt
2,650,786 ‐ 2,650,786
2,650,786 ‐ 2,650,786
2,650,786 ‐ 2,650,786
2,650,786 ‐ 2,650,786
2,650,786 ‐ 2,650,786
2,650,786 ‐ 2,650,786
Net Sales Proceeds
‐
‐
‐
‐
‐
‐
Reversion FCF Operations FCF Total FCF
‐ ‐ (447,256) 833,086 (447,256) 833,086
‐ 1,662,148 1,662,148
‐ 1,795,891 1,795,891
‐ 1,947,734 1,947,734
‐ 2,076,210 2,076,210
Roger Staiger III [email protected]
17 of 32
Inputs - Characteristics Number of Rooms Total Area (sf) Banquet Area (sf)
Roger Staiger III [email protected]
18 of 32
Sources Sources Equity Investor 1 Investor 2
Debt Tranche 1 (source) Tranche 2 (source)
Roger Staiger III [email protected]
19 of 32
Uses Uses Land & Related General & Administrative Development Charges & Municipal Costs Hard Cost Construction Building Operations Finance Expense
Roger Staiger III [email protected]
20 of 32
Input - Revenue Rooms Occupancy Average Daily Rate
Food & Beverage Restaurant Lounge Banquet Other F&B
Roger Staiger III [email protected]
Other Departments Telephone/Internet Garage Gift Shop Spa Other Rents & Commissions Other
Page 21 of 32
Input - Expense Department Rooms Restaurant Lounge Banquet Other F&B Telephone/Internet Garage Spa Rents & Commissions Roger Staiger III [email protected]
Unallocated Dept. G&A Repairs & Maint Sales & Marketing Utilities
Page 22 of 32
Input – Expense (Cont’d) Other Deductions Management Fee FF&E Rep and Capital Reserves Property Taxes Insurance Permits & Licenses Equipment Rental
Roger Staiger III [email protected]
23 of 32
Inputs - Finance Tranche 1 Principal Term(years) Interest Rate Interest Only Origination Fee
Roger Staiger III [email protected]
Tranche 2 Principal Term(years) Interest Rate Interest Only Origination Fee
24 of 32
Inputs - Valuation Capitalization Rate Sales Expense Discount Rate Sale Period (timing)
Roger Staiger III [email protected]
25 of 32
Scalability The benefit(s) of successful pro forma modeling
Scalability (Incorporating the Waterfall) Separation of cash flows according to owners’ agreement Separation of cash flows by yield, NOT by order Order: Bank vanilla example
“Pref” Rate: Preference Rate Generally provided to Investor Acts as a coupon rate, e.g. Preferred Equity Roger Staiger III [email protected]
27 of 32
Waterfall Diagram Pari Passu 1st Tier
“Pref” Rate 15% good proxy for “Pref” Non Pari Passu split 2nd Tier
2nd Tier rate N # Tiers Nth Tier rate Nth Tier Final Tier Example – Final Distribution X % to Promote to Sponsor (1‐X%) distribution Pari Passu
Roger Staiger III [email protected]
28 of 32
Waterfall Logic Sponsor 10% 15% IRR
20% IRR
Sponsor 20%
Promote Sponsor 40%
20% + IRR Sponsor 10% Investor 90%
Investor 80% Investor 90%
Roger Staiger III [email protected]
29 of 32
Waterfall Addition (100% Scalable) Circular Reference Development Page (s2)
Summary Page Inputs Section (s1)
Amortization Page (s3/s4)
Analysis (s4)
Waterfall Page (Investor) (s5)
Roger Staiger III [email protected]
Pro Forma Page (s3)
30 of 32
ULI – Staiger (Upcoming Seminars) 1) Four part 2-hour series: How to build a Hotel pro forma model (Current Pro Forma Webinar) 2) Four part 2-hour series: Advanced Pro Forma Webinar (Multifamily Focus) 3) Two part 2-hour series: How to develop a waterfall
Roger Staiger III [email protected]
31 of 32
Questions? Let’s Begin (see memorandum for detailed steps of process at conclusion)
