Financial Model Example: Hotel Evaluation + M&A

Results Loan Balance

Assumptions Depreciation

ProForma Personnel
 Control tabs
Scenario Include M&A WACC calculation
✘ M&A

Results, '000
Income Statement Cash-Flow Valuation

Revenue 71,903 FCFF 12,025 EV 42,793

EBITDA 22,909 Terminal value 30,769 Debt 7,307
EBITDA margin 32% Debt (Body+Intere (9,393) EqV 35,486
NOPLAT 12,681 FCFE 1,680 IRR 21%

Growth rate
Year 1 Year 2Year 3 Year 4 Year 5
Basic 10% 5% 3% 3% 3%
Pessimistic 10% -5% 0% 1% 2%
 1 Basic
100% Pessimistic
1

Industry Average
Firm's data
2
 Assumptions, '000

Current faclity Source

Revenue
# of Rooms
Luxe 10 Imagined
Premium 20 Imagined
Standard 70 Imagined

Occupancy rate
Luxe 40% Imagined
Premium 50% Imagined
Standard 60% Imagined

APR
Lux $200 Imagined
Premium $150 Imagined
Standard $90 Imagined

Restaurant
Guests visiting Restaurant 60% Imagined
Average check per guest 120 Imagined
Average nights per guest 2 Imagined
Total guests 10,220 Calculation

Other Services 5% Imagined

Costs as % of Revenue
COGS
Rooms 25% Imagined
Restaurant 35% Imagined
Other Business 25% Imagined

SG&A
Utilities 5% Imagined
Advertising and Promotion 2% Imagined
Credit Cards fees 3% Imagined
Other costs 10% Imagined

CAPEX
Software 1,200 Imagined
Rooms 400 Imagined

Depreciation
Current Depreciation 500 Imagined
Depreciation rate: Rooms 10% Imagined
Depreciation rate: Software 4% Imagined
'000

Potential Target data Source

# of Rooms 80 Imagined
APR 100 Imagined
Occupancy rate 60% Imagined
EBITDA margin 30% Imagined
Owner's salary 150 Imagined
Annual CAPEX 300 Imagined
Annual Depreciation 200 Imagined

Deal Structure
EV/EBITDA 15 Imagined
Target EV 7,884 Calculated
Debt Share 80% Imagined
Interest rate 9% Imagined
Debt 6,307 Calculated
Equity 1,577 Calculated

Balance data
Current Equity 1000 Imagined
Current Debt 1000 Imagined

Loan Terms
Current Interest rate 8% Imagined
Loan period, years 5 Imagined

WACC
Corporate tax 30% Imagined
D/E 55.41% [Link]
Unlevered beta 0.97 [Link]
Risk Free Rate 2.70% [Link]
Risk premium 8.00% Imagined
Total Debt 7307.2 Calculated
Total Equity 2576.8 Calculated
Total Capital 9884 Calculated
Share of Equity 0.2607042 Calculated
Share of Debt 0.7392958 Calculated
Interest rate 9% Calculated
WACC (firm's data) 7.4% Calculated
WACC (industry average 9.0% Calculated
Chosen WACC 7.4% Calculated
Terminal growth 1% Imagined
# of a period 0 1 2 3
Quantative Forecast
FY 0 FY 1 FY 2 FY 3
Rooms
# of Rooms
Luxe 10 10 10
Premium 20 20 20
Standard 70 70 70
Target 80 80 80

Occupancy
Luxe 40% 44% 46% 48%
Premium 50% 55% 58% 59%
Standard 60% 66% 69% 71%
Target 60% 66% 69% 71%

Restaurant
# of guests 11,242 11,804 12,158
Visiting Restaurant 60% 66% 69% 71%
Guests at Restaurant 7,420 8,180 8,678

Income Statement Pro-Forma, '000

FY 0 FY 1 FY 2 FY 3
Revenue Current Business 7,977 8,425 8,710
Rooms 6,688 7,022 7,233
Luxe 880 924 952
Premium 1,650 1,733 1,784
Standard 4,158 4,366 4,497
Restaurant 890 982 1,041
Other Services 399 421 435
Revenue Target 5,280 5,544 5,710

Total Revenue 13,257 13,969 14,420

COGS 2,083 2,204 2,282

Rooms 1,672 1,756 1,808
Restaurant 312 344 364
Other Services 100 105 109

Gross Profit (Current Business) 5,894 6,221 6,428

SG&A
Personnel 1,950 1,950 1,950
Advertising & Promotion 160 169 174
Utilities 399 421 435
Credit card fees 239 253 261
 Other Opex 798 843 871

EBITDA (current business) 2,348 2,586 2,736

EBITDA margin 29% 31% 31%

Target EBITDA
Nominal EBITDA 1,584 1,663 1,713
Normalized EBITDA 1,734 1,813 1,863

Total EBITDA 4,082 4,399 4,599

Total Depreciation 700 740 828

Starting 500 500 500
Rooms - 40 80
Software - - 48
Target 200 200 200

EBIT 3,232 3,509 3,621

NOPLAT 2,263 2,456 2,535

Free Cash-Flow to Firm (FCFF) Calculation, '000

NOPLAT 2,263 2,456 2,535
Deprecition 700 740 828
CAPEX (1,180) (1,420) (700)
Software (480) (720) -
Rooms (400) (400) (400)
Target (300) (300) (300)
FCFF 1,783 1,776 2,663

Enterprise Value (EV) Calculation, '000

Discounted FCFF (DCFF) 1,660 1,540 2,151
Terminal Value
Discounted Terminal Value
EV

Free Cash-Flow to Equity (FCFE) Calculation, '000

FCFF 1,783 1,776 2,663
Interest (658) (548) (428)
Tax Shield 197 164 128
Debt Repayment (1,221) (1,331) (1,451)
Equity Investment (1,577)
Total FCFE (1,577) 101 62 913
 4 5

FY 4 FY 5

10 10
20 20
70 70
80 80

49% 50%
61% 63%
74% 76%
74% 76%

12,523 12,899
74% 76%
9,207 9,768

FY 4 FY 5
9,005 9,311
7,450 7,674
980 1,010
1,838 1,893
4,632 4,771
1,105 1,172
450 466
5,882 6,058

14,887 15,369

2,362 2,445
1,863 1,918
387 410
113 116

6,643 6,866

1,950 1,950
180 186
450 466
270 279
 901 931

2,892 3,054
32% 33%

1,764 1,817
1,914 1,967

4,807 5,021

868 908
500 500
120 160
48 48
200 200

3,789 3,963

2,652 2,774

00
2,652 2,774
868 908
(700) (700)
- -
(400) (400)
(300) (300)
2,820 2,982

2,121 2,089
47,180
30,769
42,793

00
2,820 2,982
(297) (155)
89 47
(1,581) (1,724)

1,031 1,150
 Loan Balance, '000
FY 1 FY 2 FY 3 FY 4 FY 5
Beginning 7,307 6,086 4,755 3,305 1,724
Payment (1,879) (1,879) (1,879) (1,879) (1,879)
Interest (658) (548) (428) (297) (155)
Body (1,221) (1,331) (1,451) (1,581) (1,724)
Ending 6,086 4,755 3,305 1,724 -
 Tangible & Intangible Assets Balance, '000

Rooms
Year1 Year2 Year3 Year4 Year5
Year1 Beginning balance 0 400 360 320 280
CAPEX 400 0 0 0 0
Depreciation 0 40 40 40 40
Ending balance 400 360 320 280 240

Year2 Beginning balance 0 0 400 360 320

CAPEX 0 400 0 0 0
Depreciation 0 0 40 40 40
Ending balance 0 400 360 320 280

Year3 Beginning balance 0 0 0 400 360

CAPEX 0 0 400 0 0
Depreciation 0 0 0 40 40
Ending balance 0 0 400 360 320

Year4 Beginning balance 0 0 0 0 400

CAPEX 0 0 0 400 0
Depreciation 0 0 0 0 40
Ending balance 0 0 0 400 360

Year5 Beginning balance 0 0 0 0 0

CAPEX 0 0 0 0 400
Depreciation 0 0 0 0 0
Ending balance 0 0 0 0 400

Total Depreciation 0 40 80 120 160

Software
Beginning balance 0 480 1200 1152 1104
CAPEX 480 720 0 0 0
Depreciation 0 0 48 48 48
Ending balance 480 1200 1152 1104 1056

Software CAPEX distribution 40% 60% 0% 0% 0%

 Personnel # $
A&G 10 70
Rooms 20 35
Restaurant 10 30
Other Personnel 5 50