Finance

profileJohnzheng
Keys_to_Final_Practice_for_Finance_Fall_2020.xls
Home>Business & Finance homework help>Finance

WACC

WACC Calculation for The BUSHA Hospitality Corp. (BUSHAHC) Restaurant Project
MRP 8%
Bond Bank Loan Preferred Stock Common Stock Net Income $ 400,000
Par $ 1,000 7.50% Price $ 50.00 Price $ 20.00 rrf 2% D.P.R 30%
Coupon Rate 7% Annual Dividend $ 4.00 D0 $ 1.20 rm 10% Div.Paid $ 120,000
Maturity 10 Floation Costs 8% Growth Rate 6% Beta 1.2 Shares O.S 100000
Shares 800 F Rps 8.70% Flotation Costs 5% rs 11.60% D0 $ 1.20
Current Value 960 6% Tax Rate 40% Rs 12.36% AVG 11.98%
YTM 8.46% QTRly Re 12.69%
Capital Component CSR Cost of Capital After Tax WACC For CSR for Common Stock 50%
Bank Loan 20% 4.500% 0.90% Internal (given) 13%
Bond 20% 5.07% 1.01% External 38%
Preferred Stock: r ps 10% 8.70% 0.87% R.Earnings 500,000
CS (internal): rs 13% 11.98% 1.50% Project cost 4000000
CS (external): re 38% 12.69% 4.76%
100% WACC 9.04%

Initial Outlay

Initial Outlay (I0)
Expenditure Cost
Building Construction $ 2,600,000
FF&E Purchase $ 220,000
Transportation & Installation $ 10,000
Inventory $ 200,000
Land (Opportunity Cost) $ 700,000
Site Development (Land) $ 150,000
Working Capital $ 120,000
Initial Investment (I0) $ 4,000,000

InvesmtParameters

BUSHAHC SHAPR Case Analysis
Investment Analysis
Total Project Costs $4,000,000
Overall Assumptions
Desired Holding Period 10 years
Cash Flow Multiple for Hotel Real Estate times
Selling Expenses 4% of Gross Sales Price
Desired Total Property Discount Rate (WACC) 9.04%
UnLVGD
Equity Investment Parameters
Desired Equity After-Tax Discount Rate (Hurdle Rate) 12.34%
LVGD
Lending Parameters
Loan to Cost Ratio (LTC) 20% bank Origination fee 1.50%
Mortgage Amount Closing fee 3%
Interest Rate 7.50% fixed Total Fees 4.50%
Amortization Term 15 years
Annual Debt Service
Tax Environment
Ordinary Income Tax Rate 40% of annual taxable income
Depreciation Recapture Tax Rate 25% of depreciation taken over holding period, paid at sale
Capital Gains Tax Rate 15% of capital gains, paid at sale
Depreciation Parameters - Initial Investment
Depreciable Basis (BLDG)--STL $2,750,000 Life Years 20
Depreciable Basis (FFE)--STL $230,000 Life Years 8
Annual Depreciation Expense (Bldg) $137,500
Annual Depreciation Expense (FFE) $28,750

P&L 4 Unlvgd

BUSHAHC SHAPR Case Analysis (Unleveraged)
Year 1 2 3 4 5 6 7 8 9 10
Covers per day 200.00 204.00 208.08 212.24 212.24 212.24 212.24 212.24 212.24 212.24
Days per year 360 360 360 360 360 360 360 360 360 360
Average Guest Check $30.00 30.90 31.83 32.78 33.77 34.78 35.82 36.90 38.00 39.14
Food Revenue $2,160,000 $2,269,296 $2,384,122 $2,504,759 $2,579,902 $2,657,299 $2,737,018 $2,819,128 $2,903,702 $2,990,813
Beverage Revenue $648,000 $680,789 $715,237 $751,428 $773,971 $797,190 $821,105 $845,738 $871,111 $897,244
Total Revenue $2,808,000 $2,950,085 $3,099,359 $3,256,187 $3,353,872 $3,454,488 $3,558,123 $3,664,867 $3,774,813 $3,888,057
Direct Operating Exp.
Cost of Food sold $864,000 $907,718 $953,649 $1,001,904 $1,031,961 $1,062,920 $1,094,807 $1,127,651 $1,161,481 $1,196,325
Cost of Beverage sold $162,000 $170,197 $178,809 $187,857 $193,493 $199,297 $205,276 $211,435 $217,778 $224,311
Total D.O.E (CGS) $1,026,000 $1,077,916 $1,132,458 $1,189,761 $1,225,453 $1,262,217 $1,300,083 $1,339,086 $1,379,259 $1,420,636
Gross Proft $1,782,000 $1,872,169 $1,966,901 $2,066,426 $2,128,419 $2,192,272 $2,258,040 $2,325,781 $2,395,554 $2,467,421
Controllable Expenses
Payroll & Benefit Expenses $814,320 $830,606 $847,219 $864,163 $881,446 $899,075 $917,057 $935,398 $954,106 $973,188
MKT & Sales $84,240 $87,610 $91,114 $94,759 $98,549 $102,491 $106,590 $110,854 $115,288 $119,900
Utili & Maint $224,640 $235,872 $247,666 $260,049 $273,051 $286,704 $301,039 $316,091 $331,896 $348,490
Total Controllable Expenses 1,123,200 1,154,088 1,185,998 1,218,970 1,253,046 1,288,270 1,324,686 1,362,343 1,401,290 1,441,578
NIBT & Depreciation $658,800 $718,081 $780,903 $847,456 $875,373 $904,002 $933,354 $963,438 $994,265 $1,025,843
Depreciation (Bldg) $137,500 $137,500 $137,500 $137,500 $137,500 $137,500 $137,500 $137,500 $137,500 $137,500
Depreciation (FFE) $28,750 $28,750 $28,750 $28,750 $28,750 $28,750 $28,750 $28,750 $0 $0
NIBT $492,550 $551,831 $614,653 $681,206 $709,123 $737,752 $767,104 $797,188 $856,765 $888,343
Tax (40%) $197,020 $220,732 $245,861 $272,482 $283,649 $295,101 $306,841 $318,875 $342,706 $355,337
Net Income $295,530 $331,099 $368,792 $408,723 $425,474 $442,651 $460,262 $478,313 $514,059 $533,006
OCF $461,780 $497,349 $535,042 $574,973 $591,724 $608,901 $626,512 $644,563 $651,559 $670,506 UnLvgd
Working Capital 120000 $7,909,119 TCF
Net Sales Proceeds $7,118,613
WACC 9.04%
Period 0 1 2 3 4 5 6 7 8 9 10
CF ($4,000,000) $461,780 $497,349 $535,042 $574,973 $591,724 $608,901 $626,512 $644,563 $651,559 $7,909,119
Discounted Cash Flow ($4,000,000) $423,487 $418,284 $412,670 $406,694 $383,835 $362,224 $341,794 $322,482 $298,950 $3,327,963
Cumulative CF (PBP) ($4,000,000) $461,780 $959,129 $1,494,170 $2,069,144 $2,660,867 $3,269,768 $3,896,281 $4,540,843
Discount Cumulative CF (DPBP) ($4,000,000) $423,487 $841,771 $1,254,441 $1,661,135 $2,044,970 $2,407,193 $2,748,988 $3,071,470 $3,370,420 $6,698,383
NPV $ 2,698,383
IRR 17.45% MIRR
MIRR 14.81% FV(10) $15,919,139 14.81%
EAA $421,245 14.81%
PI $ 1.67
PBP 7.16
DPBP 9.19

P&L LVGD

BUSHAHC SHAPR Case Analysis (Leveraged)
Year 1 2 3 4 5 6 7 8 9 10
Covers per day 200.00 204.00 208.08 212.24 212.24 212.24 212.24 212.24 212.24 212.24
Days per year 360 360 360 360 360 360 360 360 360 360
Average Guest Check $30.00 30.90 31.83 32.78 33.77 34.78 35.82 36.90 38.00 39.14
Food Revenue $2,160,000 $2,269,296 $2,384,122 $2,504,759 $2,579,902 $2,657,299 $2,737,018 $2,819,128 $2,903,702 $2,990,813
Beverage Revenue $648,000 $680,789 $715,237 $751,428 $773,971 $797,190 $821,105 $845,738 $871,111 $897,244
Total Revenue $2,808,000 $2,950,085 $3,099,359 $3,256,187 $3,353,872 $3,454,488 $3,558,123 $3,664,867 $3,774,813 $3,888,057
Direct Operating Exp.
Cost of Food sold $864,000 $907,718 $953,649 $1,001,904 $1,031,961 $1,062,920 $1,094,807 $1,127,651 $1,161,481 $1,196,325
Cost of Beverage sold $162,000 $170,197 $178,809 $187,857 $193,493 $199,297 $205,276 $211,435 $217,778 $224,311
Total D.O.E (CGS) $1,026,000 $1,077,916 $1,132,458 $1,189,761 $1,225,453 $1,262,217 $1,300,083 $1,339,086 $1,379,259 $1,420,636
Gross Profit $1,782,000 $1,872,169 $1,966,901 $2,066,426 $2,128,419 $2,192,272 $2,258,040 $2,325,781 $2,395,554 $2,467,421
Controllable Expenses
Payroll Expenses $814,320 $830,606 $847,219 $864,163 $881,446 $899,075 $917,057 $935,398 $954,106 $973,188
MKT & Sales $84,240 $87,610 $91,114 $94,759 $98,549 $102,491 $106,590 $110,854 $115,288 $119,900
Utili & Maint $224,640 $235,872 $247,666 $260,049 $273,051 $286,704 $301,039 $316,091 $331,896 $348,490
Total Controllable Expenses 1,123,200 1,154,088 1,185,998 1,218,970 1,253,046 1,288,270 1,324,686 1,362,343 1,401,290 1,441,578
EBITDA $658,800 $718,081 $780,903 $847,456 $875,373 $904,002 $933,354 $963,438 $994,265 $1,025,843
Depreciation (Bldg) $137,500 $137,500 $137,500 $137,500 $137,500 $137,500 $137,500 $137,500 $137,500 $137,500
Depreciation (FFE) $28,750 $28,750 $28,750 $28,750 $28,750 $28,750 $28,750 $28,750 $0 $0
NIBT & Int.Exp. $492,550 $551,831 $614,653 $681,206 $709,123 $737,752 $767,104 $797,188 $856,765 $888,343
Coupon PMT $56,000 $56,000 $56,000 $56,000 $56,000 $56,000 $56,000 $56,000 $56,000 $56,000
Interest Expense (Loan) $60,000 $57,703 $55,233 $52,578 $49,725 $46,657 $43,359 $39,813 $36,002 $31,905
NIBT $376,550 $438,128 $503,420 $572,627 $603,398 $635,095 $667,745 $701,375 $764,763 $800,438
Tax (40%) $150,620 $175,251 $201,368 $229,051 $241,359 $254,038 $267,098 $280,550 $305,905 $320,175
Net Income $225,930 $262,877 $302,052 $343,576 $362,039 $381,057 $400,647 $420,825 $458,858 $480,263
Annual Debt Service $90,630 $90,630 $90,630 $90,630 $90,630 $90,630 $90,630 $90,630 $90,630 $90,630
OCF $417,550 $452,200 $488,905 $527,775 $543,384 $559,334 $575,626 $592,258 $597,730 $615,038 LVGD
Working Capital 120000 $5,817,931 TCF
Net Sales Proceeds $5,082,893
WACC 12.34%
Period 0 1 2 3 4 5 6 7 8 9 10
CF ($2,000,000) $417,550 $452,200 $488,905 $527,775 $543,384 $559,334 $575,626 $592,258 $597,730 $5,817,931
($2,000,000) $371,693 $358,329 $344,867 $331,400 $303,728 $278,308 $254,959 $233,516 209790.977709137 1817716.42006874
($2,000,000) $417,550 $869,750 $1,358,655 $1,886,431 $2,429,814 $2,989,148 $3,564,774 $4,157,032
($2,000,000) $371,693 $730,022 $1,074,889 $1,406,289 $1,710,017 $1,988,325 $2,243,284 $2,476,801 $2,686,592 $4,504,308
NPV $ 2,504,308
IRR 28.72% MIRR
MIRR 21.84% FV(10) $14,416,856 21.84%
EAA $449,361 21.84%
PI $ 2.25
PBP 4.21
DPBP 6.05

CF from Sales

BUSHAHC SHAPR Investment Analysis
D. Taxes Due on Sale Calculations:
A. Sale Price Calculation -At End of Holding Period Net Sale Proceeds (after brokerage fees) 7,118,613
Less: Project Cost (Initial Outlay) $4,000,000
Estimated OCF at the End of Holding Period $670,506 Equals: Total Gain (capital) 3,118,613
WACC (cap rate) for Total Property Yield 9.04%
Expected Sale Price $7,415,222 Accumulated Depreciation (total) $1,605,000
Less: Selling Expenses $296,609
Equals: Net Sale Proceeds $7,118,613
Depreciation Recapture Tax 25% $401,250
B. Calculation of Before-Tax Cash Flow From Sale Capital Gain Tax 15% $467,791.94
Net Sale Proceeds (after brokerage fees) $7,118,613 Equals: Total Taxes on Sale $869,042
Less: Remaining Mortgage Balance (RMB) $366,678
Less: Bond Par PMT $800,000
Equals: Before-Tax Equity Reversion 5,951,935 E. Calculation of After-Tax Cash Flow From Sale
Hurdle Rate for Equity Position 12.34% Before-Tax Equity Reversion (after RMB and Bond PMT) 5,951,935
C. Initial Outlay: Less: Total Taxes on Sale 869,042
Initial Investment $4,000,000 Equals: After-tax cash flow from sale (for equity position) 5,082,893

InvestmentAnalysis

Investment Return Analysis
Overall Property Investment Decision (UnLVGD) Year 0 Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7 Year 8 Year 9 Year 10
Initial Investment & WCR ($4,000,000) 120000
Operating Cash Flow $461,780 $497,349 $535,042 $574,973 $591,724 $608,901 $626,512 $644,563 $651,559 $670,506
Net Sales Proceeds $7,118,613
Cash Flows to the Total Property ($4,000,000) $461,780 $497,349 $535,042 $574,973 $591,724 $608,901 $626,512 $644,563 $651,559 $7,909,119 PBP
WACC (cap rate) 9.04% 0 1 2 3 4 5 6 7 8 9 10
Discounted Cash Flow (PV) ($4,000,000) $423,487 $418,284 $412,670 $406,694 $383,835 $362,224 $341,794 $322,482 $298,950 $3,327,963 DPBP
NPV $2,698,383
IRR 17.45%
MIRR 14.81%
Equity Investment Decision (LVGD) Year 0 Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7 Year 8 Year 9 Year 10
Initial Investment & WCR ($2,000,000) 120000
Operating Cash Flows to Equity $417,550 $452,200 $488,905 $527,775 $543,384 $559,334 $575,626 $592,258 $597,730 $615,038
Net Sales Proceeds $5,082,893
After-Tax Cash Flows to Equity Position ($2,000,000) $417,550 $452,200 $488,905 $527,775 $543,384 $559,334 $575,626 $592,258 $597,730 $5,817,931 PBP
Hurdle Rate (rs) 12.34% 0 1 2 3 4 5 6 7 8 9 10
Discounted Cash Flow (PV) ($2,000,000) $371,693 $358,329 $344,867 $331,400 $303,728 $278,308 $254,959 $233,516 $209,791 $1,817,716 DPBP
NPV $2,504,308
IRR 28.72%
MIRR 21.84%

Mezz & Partners

Equity Investment Decision (LVGD) Year 0 Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7 Year 8 Year 9 Year 10
Initial Investment & WCR ($2,000,000) 120000
Operating Cash Flows to Equity $417,550 $452,200 $488,905 $527,775 $543,384 $559,334 $575,626 $592,258 $597,730 $615,038
Net Sales Proceeds $5,082,893
After-Tax Cash Flows to Equity Position ($2,000,000) $417,550 $452,200 $488,905 $527,775 $543,384 $559,334 $575,626 $592,258 $597,730 $5,817,931 PBP
Hurdle Rate (rs) 12.34% 0 1 2 3 4 5 6 7 8 9 10
Discounted Cash Flow (PV) ($2,000,000) $371,693 $358,329 $344,867 $331,400 $303,728 $278,308 $254,959 $233,516 $209,791 $1,817,716 DPBP
PI
NPV $2,504,308 EAA
IRR 28.72%
MIRR 21.84%
0 1 2 3 4 5 6 7 8 9 10
Mezzanine Financing 40.00% ($800,000) $160,000 $160,000 $160,000 $160,000 $160,000 $160,000 $160,000 $160,000 $160,000 $160,000 coupon
Coupon Rate 20.00% $800,000 principal
Profit Sharing Ratio 50.00% $ 2,141,447 profit 2141447
IRR for Mezz. Financier 27.21% ($800,000) $160,000 $160,000 $160,000 $160,000 $160,000 $160,000 $160,000 $160,000 $160,000 $3,101,447 TCF for MEZZ
IRR for Deal Sponsor 29.84%
NPV for Deal Sponsor $ 1,493,615
CF to Deal Sponsor ($1,200,000) $257,550 $292,200 $328,905 $367,775 $383,384 $399,334 $415,626 $432,258 $437,730 $2,716,485
$2,716,485
Waterfall Analysis (Partners & Sponsors) 0 1 2 3 4 5 6 7 8 9 10
CF after Mezz to Partners & Sponsors 720000 $257,550 $292,200 $328,905 $367,775 $383,384 $399,334 $415,626 $432,258 $437,730 $455,038
Partner's preferred return on Balance 8% $57,600 $50,306 $40,708 $28,503 $13,358 $0 $0 $0 $0 $0
Sponsor's return on Sweat equity on Bal. 10% $480,000 $48,000 $41,922 $33,923 $23,752 $11,131 $0 $0 $0 $0 $0
Balance for Principal PMT $151,950 $199,972 $254,274 $315,520 $358,895 $399,334 $415,626 $432,258 $437,730 $455,038
Partner Capital Repayment 60% $91,170 $119,983 $152,565 $189,312 $166,970 $0 $0 $0 $0 $0
Sponsor Capital Repayment 40% $60,780 $79,989 $101,710 $126,208 $111,313 $0 $0 $0 $0 $0
CF available for Waterfall $0 $0 $0 $0 $80,611 $399,334 $415,626 $432,258 $437,730 $455,038
Waterfall for Partner Promote 45% $0 $0 $0 $0 $36,275 $179,700 $187,032 $194,516 $196,978 $204,767
Waterfall for Sponsor Promote 55% $0 $0 $0 $0 $44,336 $219,634 $228,594 $237,742 $240,751 $250,271
Supporting Schedule - Equity Capital Accounts
Partner Equity Account
Annual Begin. Bal. (principal) $720,000 $628,830 $508,847 $356,282 $166,970 $0 Total
Partner Distributions 720000 $91,170 $119,983 $152,565 $189,312 $166,970 $0 $720,000
Sponsor Equity Account
Annual Beginning Balance (principal) $480,000 $419,220 $339,231 $237,522 $111,313 $0
Sponsor Distributions 480000 $60,780 $79,989 $101,710 $126,208 $111,313 $0 $480,000
Capital Partners Return on Investment
Annual CF to Partners Inflows (OCF) $148,770 $170,289 $193,272 $217,815 $216,603 $179,700 $187,032 $194,516 $196,978 $204,767
Residual Partners Inflows (45%) $1,017,651
Total CF to Capital Partners ($720,000) $148,770 $170,289 $193,272 $217,815 $216,603 $179,700 $187,032 $194,516 $196,978 $1,222,418 TCF for Partners
IRR for Capital Partners 26.55%
Deal Sponsors Return on Investment
Annual CF to Sponsors Inflows (OCF) $ 108,780 $ 121,911 $ 135,633 $ 149,960 $ 166,781 $ 219,634 $ 228,594 $ 237,742 $ 240,751 $ 250,271
Residual Sponsors Inflows (55%) $1,243,796
Total CF to Deal Sponsors ($480,000) $ 108,780 $ 121,911 $ 135,633 $ 149,960 $ 166,781 $ 219,634 $ 228,594 $ 237,742 $ 240,751 $ 1,494,067 TCF for Sponsors
IRR for Deal Sponsors 33.89%
Lender Yield Year 0 Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7 Year 8 Year 9 Year 10
Initial Loan Amount (LTC-20%) ($800,000)
Fees Revenue 4.50% $36,000
Annual Debt Service $90,630 $90,630 $90,630 $90,630 $90,630 $90,630 $90,630 $90,630 $90,630 $90,630 OCF (PMT)
Remaining Mortgage Balance $366,678 RMB
Total Cash Flows to the Lender ($764,000) $90,630 $90,630 $90,630 $90,630 $90,630 $90,630 $90,630 $90,630 $90,630 $457,307
IRR 8.33%
DCR (OCF/PMT) for Debtee 4.61 4.99 5.39 5.82 6.00 6.17 6.35 6.53 6.60 6.79
NPV

Amortization

Loan Amount $800,000 PMT $90,630
Loan Periods 15
Interest Rate 7.50%
Amortization Table
Year Beg.Balance Payment Interest for Year Principal Paid Ending Balance
0 800000 $0 $0 $0 $800,000
1 800000 $90,630 $60,000 $30,630 $769,370
2 769370 $90,630 $57,703 $32,927 $736,443
3 736443 $90,630 $55,233 $35,397 $701,047
4 701047 $90,630 $52,578 $38,051 $662,995
5 662995 $90,630 $49,725 $40,905 $622,090
6 622090 $90,630 $46,657 $43,973 $578,117
7 578117 $90,630 $43,359 $47,271 $530,846
8 530846 $90,630 $39,813 $50,816 $480,030
9 480030 $90,630 $36,002 $54,628 $425,402
10 425402 $90,630 $31,905 $58,725 $366,678 RMB $366,678