copy of lbo dcf model

8/6/2019 Copy of LBO DCF Model

1/40

Leveraged Buyout (LBO) Model

Input Values*Enter values in yellow cells.

Estimated Cost of Deal: Debt Assumptions:

Curent Stock Price $6.00 $ amt Assumed DebtOffer Premium 10.00% % Maturity Year

Offer Price Per Share 6.60 $ amt Annual Repayment

No. of Shares outstanding 100,000 nos. Coupon Rate

Equity Purchase Price $660,000 $ amt Average Interest

Optional Prepayment

Existing Debt Retired $185,000 $ amt

Assumed Debt $65,000 $ amt Bank Revolver

Expensed Transaction Costs $2,500 $ amt Coupon Rate

Capitalized Financing Costs $10,888 $ amt Revolver limit

Cash Infusion $27,500 $ amt Undrawn Commitment Fee

Total Funds Requirement $950,888 $ amt Term Loan "A"

Term YearsAnnual Repayment

Sources of Funds: Coupon Rate

Excess Cash $5,000 $ amt Average Interest

Debt: Fees Optional Prepayment

Assumed Debt $65,000 $ amt

Bank Revolver $200,888 $ amt 0.75% Term Loan "B"

Term Loan "A" $80,000 $ amt 1.50% Term Years

Term Loan "B" $55,000 $ amt 1.75% Annual Repayment

Senior Notes $65,000 $ amt 2.00% Bullet Year Repayment

Subordinated Notes $40,000 $ amt 2.50% Coupon Rate

Mezzanine Debt $70,000 $ amt 2.75% Average Interest

Seller Notes $25,000 $ amt 0.00% Optional Prepayment

Preferred:

Preferred Stock $60,000 $ amt 2.00% Senior Notes

Equity: Maturity Year

Common - Sponsor Equity $220,000 $ amt Coupon Rate

Management Equity $40,000 $ amt Average Interest

New Equity $25,000 $ amt Optional Prepayment

Total $950,888 $ amt

Subordinated Notes

Maturity Year

Goodwill / Bargain Purchase: Coupon Rate

Equity Purchase Price $660,000 $ amt Average Interest

Add: Years PIK

Existing Goodwill $20,000 $ amt Optional Prepayment

Less:

Net Assets (Book Value) $464,749 $ amt Mezzanine Debt

Fixed Assets (PP&E) Write-up $2,450 $ amt Maturity Year

Identifiable Tangibles Write-up $1,200 $ amt Coupon Rate

Deferred Tax Liability - existing $1,400 $ amt Average Interest

DTL Created on Assets Write-up ($1,278) $ amt Years PIK

Goodwill/(Gain from Bargain Purchase) $211,479 $ amt Optional Prepayment

Seller Notes

Term Years


2/40

Annual Repayment

Coupon Rate

Average Interest

Years PIK

Optional Prepayment

Preferred Stock

Preferred Stock Dividend

Years PIKPref Stock Retired Year


3/40

To help us improve this model, please send your feedback to [email protected]

Income/BS Assumptions:

Income Statement:9 select no. Sales Year 1 $1,100,000 $ amt

11.11% % of FV Sales - Annual Increase 10.00% %

4.25% % annual Cost of goods sold 45.00% % of revenue

1 1=Yes 0=No Cost of goods sold - Annual Increase 0.25% %

1 1=Yes 0=No SG&A expenses 14.00% % of revenue

SG&A expenses - Annual Increase 0.50% %

Other expenses 12.00% % of revenue

4.75% % annual Other expenses - Annual Increase 0.15% %

$600,000 $ amt

1.00% % annual Balance Sheet:

Accounts receivable 18.00% % of revenue

Accounts receivable - Annual Increase 5.00% %

8 select no. Inventories 20.00% % of COGS12.50% % of FV Inventories - Annual Increase 4.00% %

5.50% % annual Prepaids 4.50% % of revenue

1 1=Yes 0=No Prepaids - Annual Increase 2.00% %

1 1=Yes 0=No Other current assets 12.00% % of revenue

Other current assets - Annual Increase 2.50% %

Other long-term assets 4.00% % of revenue

11 select no. Other long-term assets - Annual Increase 0.50% %

4.00% % of FV

60.00% % of FV Accounts payable 15.00% % of COGS plus inventory in

5.25% % annual Accounts payable - Annual Increase 2.00% %

1 1=Yes 0=No Accrued expenses 6.00% % of revenue

1 1=Yes 0=No Accrued expenses - Annual Increase 1.50% %

Other current liabilities 8.00% % of revenueOther current liabilities - Annual Increase 1.00% %

9 select no. Capex 10.00% % of revenue

4.25% % annual Capex Start Year 1 in years

1 1=Yes 0=No Capex Interval 1 in years

1 1=Yes 0=No

Other Assumptions:

12 select no. Cash Minimum $50,000 $ amt

4.50% % annual On / Off 1 1=On. 0=Off.

0 1=Yes 0=No

5 enter no. Tax Rate 35.00% %

1 1=Yes 0=No Year 1 2010 year

14 select no. DCF Assumptions:

4.50% % annual Discount Rate (WACC) 7.00% %

0 1=Yes 0=No Discount Rate Range 1.0% %

5 enter no. EBITDA Terminal Value Multiple 6.0x x

1 1=Yes 0 =No EBITDA Multiple Range 2.0x x

Perpetuity Growth Rate 3.00% %

Perpetuity Growth Range 0.5% %

7 select no. Net Debt $583,388 $ amt

http://www.globaliconnect.com/excel_models.php
http://www.globaliconnect.com/excel_models.phphttp://www.globaliconnect.com/excel_models.php


4/40

14.29%% of FV

5.25% % annual

1 1=Yes 0=No

5 enter no.

1 1=Yes 0=No

7.00% % annual

5 enter no.select no.


5/40

Financing Annual

Costs Amortization

Period (Yrs)

4,500 450 10

1,200 150

963 88

1,300 144

1,000 83

1,925 138

0 0

10,888 1,0531,200

12,088


6/40


7/40

Income Statements

Year 2010 2011 2012 2013 2014

Year No. 1 2 3 4 5

Net Sales 1,100,000 1,210,000 1,331,000 1,464,100 1,610,510

Cost of goods sold 495,000 545,861 601,948 663,799 732,004

Gross Margin 605,000 664,139 729,052 800,301 878,506

Operating Expenses 286,000 315,665 348,408 384,548 424,438SG&A expenses 154,000 170,247 188,208 208,064 230,015Other expenses 132,000 145,418 160,200 176,484 194,423

EBITDA 319,000 348,474 380,644 415,753 454,068

D&A 72,924 85,024 93,898 104,355 117,776

EBIT 246,076 263,450 286,746 311,398 336,292

Interest Expense #VALUE! #VALUE! #VALUE! #VALUE! #VALUE!Existing Debt #VALUE! #VALUE! #VALUE! #VALUE! #VALUE!

Bank Revolver 13,533 #VALUE! #VALUE! #VALUE! #VALUE!Term Loan "A" #VALUE! #VALUE! #VALUE! #VALUE! #VALUE!Term Loan "B" #VALUE! #VALUE! #VALUE! #VALUE! #VALUE!

Senior Notes #VALUE! #VALUE! #VALUE! #VALUE! #VALUE!Subordinated Notes 1,800 #VALUE! #VALUE! #VALUE! #VALUE!

Mezzanine Debt 3,150 #VALUE! #VALUE! #VALUE! #VALUE!

Seller Notes #VALUE! #DIV/0! #DIV/0! #DIV/0! #DIV/0!

Other Expenses (Income) #VALUE! #VALUE! #VALUE! #VALUE! #VALUE!Amortization of Capitalized

Financing Costs #VALUE! #VALUE! #VALUE! #VALUE! #VALUE!(Gain) Loss on Asset Sales (1,250) (1,350) (1,450) (1,550) (1,650)

Net Income before Taxes #VALUE! #VALUE! #VALUE! #VALUE! #VALUE!

Income Tax Expense (Benefit) #VALUE! #VALUE! #VALUE! #VALUE! #VALUE!

Net Income #VALUE! #VALUE! #VALUE! #VALUE! #VALUE!

Dividend 6,700 7,194 7,659 8,245 8,955Preferred Stock Dividend 4,200 4,494 4,809 5,145 5,505Common Stock Dividend 2,500 2,700 2,850 3,100 3,450

Net Income to Common Shareholders #VALUE! #VALUE! #VALUE! #VALUE! #VALUE!


8/40

2015 2016 2017 2018 2019

6 7 8 9 10

1,771,561 1,948,717 2,143,589 2,357,948 2,593,742

807,217 890,159 981,623 1,082,485 1,193,710

964,344 1,058,558 1,161,966 1,275,463 1,400,033

468,468 517,067 570,709 629,918 695,271

254,281 281,108 310,765 343,551 379,795

214,187 235,959 259,944 286,367 315,476

495,876 541,492 591,257 645,545 704,761

128,849 101,532 115,822 128,047 149,661

367,027 439,960 475,435 517,499 555,100

#VALUE! #VALUE! #VALUE! #VALUE! #VALUE!








#DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0!



(1,750) (1,850) (1,950) (2,050) (2,150)




9,391 9,591 9,891 9,991 10,291

5,891 5,891 5,891 5,891 5,891

3,500 3,700 4,000 4,100 4,400



9/40

Balance Sheets

Year Pre-Acq Adj Post-Acq 2010 2011 2012

Year No. 0 1 2 3

ASSETS

Current Assets:

Cash in bank 55,000 22,500 77,500 #VALUE! #VALUE! #VALUE!Accounts receivable 85,000 85,000 198,000 228,690 264,137

Inventories 95,999 95,999 99,000 113,539 130,213Prepaids 22,000 22,000 49,500 55,539 62,315

Other current assets 45,000 45,000 132,000 148,830 167,806

Total Current Assets 302,999 325,499 #VALUE! #VALUE! #VALUE!

Non-Current Assets:

Goodwill 20,000 191,479 211,479 211,479 211,479 211,479

Intangible Assets 950 1,200 2,150 1,971 1,792 1,613Capitalized Financing Costs 0 10,888 10,888 #VALUE! #VALUE! #VALUE!Other Long-Term Assets 15,000 15,000 44,000 48,642 53,774PP&E:

Gross PP&E 500,000 2,450 502,450 502,450 612,450 733,450Capex 0 0 110,000 121,000 133,100

Depreciation (Current) 0 0 72,745 84,845 93,718Accumulated Depreciation 25,000 25,000 97,745 182,590 276,308Net PP&E 475,000 477,450 514,705 550,860 590,242

Total Non-Current Assets 510,950 716,966 #VALUE! #VALUE! #VALUE!

Total Assets 813,949 228,516 1,042,465 #VALUE! #VALUE! #VALUE!

LIABILITIES & EQUITY

Current Liabilities:

Accounts payable 51,800 51,800 74,700 85,741 96,542Accrued expenses 24,000 24,000 66,000 73,689 82,274Other current liabilities 22,000 22,000 88,000 97,768 108,620

Total Current Liabilities 97,800 97,800 228,700 257,198 287,436

Deferred Tax Liability 1,400 (123) 1,278 #VALUE! #VALUE! #VALUE!Long-Term Debt:

Existing Debt 250,000 (185,000) 65,000 #VALUE! #VALUE! #VALUE!Bank Revolver 0 200,888 200,888 #VALUE! #VALUE! #VALUE!Term Loan "A" 0 80,000 80,000 #VALUE! #VALUE! #VALUE!

Term Loan "B" 0 55,000 55,000 #VALUE! #VALUE! #VALUE!Senior Notes 0 65,000 65,000 #VALUE! #VALUE! #VALUE!

Subordinated Notes 0 40,000 40,000 #VALUE! #VALUE! #VALUE!Mezzanine Debt 0 70,000 70,000 #VALUE! #VALUE! #VALUE!Seller Notes 0 25,000 25,000 #VALUE! #DIV/0! #DIV/0!


10/40

Total Long Term Debt 250,000 600,888 #VALUE! #VALUE! #VALUE!

Total Long-Term Liabilities 251,400 602,165 #VALUE! #VALUE! #VALUE!

Total Liabilities 349,200 699,965 #VALUE! #VALUE! #VALUE!

Shareholder's Equity:

Preferred Stock 0 60,000 60,000 64,200 68,694 73,503Common Equity 440,000 (155,000) 285,000 285,000 285,000 285,000Retained Earnings 24,749 (27,249) (2,500) #VALUE! #VALUE! #VALUE!

Total Shareholder's Equity 464,749 342,500 #VALUE! #VALUE! #VALUE!

Total Liabilities & Equity 813,949 228,516 1,042,465 #VALUE! #VALUE! #VALUE!

Balance Sheet Check 0 0 0 #VALUE! #VALUE! #VALUE!


11/40

2013 2014 2015 2016 2017 2018 2019

4 5 6 7 8 9 10

#VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE!

305,078 352,365 406,982 470,064 542,924 627,077 724,274

149,337 171,268 196,421 225,267 258,350 296,291 339,804

69,917 78,447 88,018 98,756 110,804 124,322 139,489

189,201 213,324 240,523 271,190 305,766 344,752 388,707


211,479 211,479 211,479 211,479 211,479 211,479 211,479

1,433 1,254 1,075 896 717 538 358#VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE!

59,447 65,719 72,652 80,317 88,790 98,157 108,513

866,550 1,012,960 1,174,011 1,351,167 1,546,039 1,760,398 1,996,192

146,410 161,051 177,156 194,872 214,359 235,795 259,374

104,176 117,597 128,669 101,352 115,643 127,868 149,482

380,485 498,082 626,751 728,103 843,747 971,614 1,121,096

632,475 675,929 724,416 817,935 916,651 1,024,578 1,134,470



108,708 122,413 137,851 155,242 174,837 196,914 221,789

91,859 102,560 114,508 127,849 142,743 159,373 177,940

120,677 134,072 148,954 165,488 183,857 204,266 226,939

321,244 359,045 401,313 448,579 501,437 560,552 626,668









#DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0!


12/40




78,648 84,153 84,153 84,153 84,153 84,153 84,153285,000 285,000 285,000 285,000 285,000 285,000 285,000






13/40

Cash Flows

Year 2010 2011 2012 2013

Year No. 1 2 3 4

Cash flows from operating activities

Net Income to Common Shareholders #VALUE! #VALUE! #VALUE! #VALUE!

Non-cash adjustments:

Depreciation and amortization 72,924 85,024 93,898 104,355

Amortization of Capitalized Financing Costs #VALUE! #VALUE! #VALUE! #VALUE!

Increase (Decrease) in Deferred Tax Liability #VALUE! #VALUE! #VALUE! #VALUE!

Accrued Interest Subordinated Notes 1,800 #VALUE! #VALUE! #VALUE!

Accrued Interest Mezzanine Debt 3,150 #VALUE! #VALUE! #VALUE!

Accrued Interest Seller Notes #VALUE! #DIV/0! #DIV/0! #DIV/0!

Accrued Interest Preferred Stock 4,200 4,494 4,809 5,145

Changes in operating assets and liabilities:(Increase) decrease in accounts receivable (113,000) (30,690) (35,447) (40,941)

(Increase) decrease in inventories (3,001) (14,539) (16,674) (19,123)

(Increase) decrease in prepaids & other assets (114,500) (22,869) (25,752) (28,998)

Increase (decrease) in accounts payable 22,900 11,041 10,801 12,166

Increase (decrease) in accrued exp & other liabilit ies 108,000 17,457 19,437 21,642

Net cash provided by (used in) operating activities #VALUE! #VALUE! #VALUE! #VALUE!

Cash flows from investing activities

Purchase of property & equipment (110,000) (121,000) (133,100) (146,410)

(Increase) decrease in other long-term assets (29,000) (4,642) (5,132) (5,673)

Net cash provided by (used in) investing activities (139,000) (125,642) (138,232) (152,083)

Cash flows from financing activities

Increase (decrease) in Existing Debt #VALUE! #VALUE! #VALUE! #VALUE!

Increase (decrease) in Term Loan "A" #VALUE! #VALUE! #VALUE! #VALUE!

Increase (decrease) in Term Loan "B" #VALUE! #VALUE! #VALUE! #VALUE!

Increase (decrease) in Senior Notes #VALUE! #VALUE! #VALUE! #VALUE!

Increase (decrease) in Subordinated Notes #VALUE! #VALUE! #VALUE! #VALUE!

Increase (decrease) in Mezzanine Debt #VALUE! #VALUE! #VALUE! #VALUE!

Increase (decrease) in Seller Notes #VALUE! #VALUE! #DIV/0! #DIV/0!

Increase (decrease) in Preferred Stock 0 0 0 0

Net cash provided by (used in) financing activities #VALUE! #VALUE! #VALUE! #VALUE!

Net increase (decrease) in cash #VALUE! #VALUE! #VALUE! #VALUE!

Cash at beginning of year 77,500 #VALUE! #VALUE! #VALUE!

Cash available for Bank Revolver #VALUE! #VALUE! #VALUE! #VALUE!

Added (retired) Bank Revolver #VALUE! #VALUE! #VALUE! #VALUE!


14/40

Cash at end of year #VALUE! #VALUE! #VALUE! #VALUE!

Free Cash Flows

EBIT 246,076 263,450 286,746 311,398Add: Amortization of non-deductible goodwill & intangibles 0 0 0 0

EBIT (Adj) 246,076 263,450 286,746 311,398

Less: Tax on EBIT (Adj) 86,127 92,207 100,361 108,989

Unlevered Net Income 159,949 171,242 186,385 202,409

Plus: D&A & other non-cash charges affecting EBIT 72,924 85,024 93,898 104,355

Less: Capital Expenditures 110,000 121,000 133,100 146,410

Less: Increase in Net (non-cash) Working Capital 99,601 39,600 47,635 55,254

Less: (Increase) decrease in Deferred Tax #VALUE! #VALUE! #VALUE! #VALUE!

Unlevered Free Cash Flows (UFCF) #VALUE! #VALUE! #VALUE! #VALUE!

Debt Repayment

Cash Available for Debt Repayment

Cash at beginning of year 77,500 #VALUE! #VALUE! #VALUE!

Net cash provided by (used in) operating activities #VALUE! #VALUE! #VALUE! #VALUE!

Net cash provided by (used in) investing activities (139,000) (125,642) (138,232) (152,083)

Sub-total #VALUE! #VALUE! #VALUE! #VALUE!

Minimum Cash Balance 50,000 50,000 50,000 50,000

Cash Available for Debt Repayment #VALUE! #VALUE! #VALUE! #VALUE!

Mandatory Repayment

Existing Debt 7,222 #VALUE! #VALUE! #VALUE!Term Loan "A" 10,000 #VALUE! #VALUE! #VALUE!

Term Loan "B" 2,200 #VALUE! #VALUE! #VALUE!

Senior Notes 0 0 0 0

Subordinated Notes 0 0 0 0

Mezzanine Debt 0 0 0 0

Seller Notes 3,571 #VALUE! #DIV/0! #DIV/0!

Preferred Stock 0 0 0 0

Sub-total 22,994 #VALUE! #VALUE! #VALUE!

Cash Balance #VALUE! #VALUE! #VALUE! #VALUE!

Incremental Revolver #VALUE! #VALUE! #VALUE! #VALUE!

Cash Available for Optional Repayment #VALUE! #VALUE! #VALUE! #VALUE!

Optional Repayment

Existing Debt #VALUE! #VALUE! #VALUE! #VALUE!

Term Loan "A" #VALUE! #VALUE! #VALUE! #VALUE!

Term Loan "B" #VALUE! #VALUE! #VALUE! #VALUE!

Senior Notes #VALUE! #VALUE! #VALUE! #VALUE!

Subordinated Notes #VALUE! #VALUE! #VALUE! #VALUE!

Mezzanine Debt #VALUE! #VALUE! #VALUE! #VALUE!

Seller Notes #VALUE! #VALUE! #DIV/0! #DIV/0!


15/40

Bank Revolver #VALUE! #VALUE! #VALUE! #VALUE!

Sub-total #VALUE! #VALUE! #VALUE! #VALUE!

Cash at end of year #VALUE! #VALUE! #VALUE! #VALUE!

Cash Flow Check #VALUE! #VALUE! #VALUE! #VALUE!


16/40

2014 2015 2016 2017 2018 2019

5 6 7 8 9 10

#VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE!

117,776 128,849 101,532 115,822 128,047 149,661



#VALUE! 0 0 0 0 0

#VALUE! 0 0 0 0 0

#DIV/0! 0 0 0 0 0

5,505 0 0 0 0 0

(47,287) (54,617) (63,082) (72,860) (84,153) (97,197)

(21,932) (25,152) (28,846) (33,083) (37,941) (43,513)

(32,653) (36,769) (41,405) (46,625) (52,503) (59,123)

13,704 15,438 17,392 19,594 22,077 24,876

24,097 26,830 29,874 33,264 37,038 41,240


(161,051) (177,156) (194,872) (214,359) (235,795) (259,374)

(6,272) (6,933) (7,665) (8,473) (9,367) (10,356)

(167,323) (184,089) (202,536) (222,832) (245,162) (269,730)

#VALUE! #VALUE! #VALUE! #VALUE! #VALUE! 0

#VALUE! #VALUE! #VALUE! #VALUE! 0 0





#DIV/0! #DIV/0! #DIV/0! 0 0 0

0 0 0 0 0 0







17/40


336,292 367,027 439,960 475,435 517,499 555,1000 0 0 0 0 0

336,292 367,027 439,960 475,435 517,499 555,100

117,702 128,460 153,986 166,402 181,125 194,285

218,590 238,568 285,974 309,033 336,374 360,815

117,776 128,849 101,532 115,822 128,047 149,661

161,051 177,156 194,872 214,359 235,795 259,374

64,071 74,270 86,067 99,710 115,483 133,717





(167,323) (184,089) (202,536) (222,832) (245,162) (269,730)


50,000 50,000 50,000 50,000 50,000 50,000


#VALUE! #VALUE! #VALUE! #VALUE! #VALUE! 0#VALUE! #VALUE! #VALUE! #VALUE! 0 0


0 0 0 0 #VALUE! 0

0 0 0 0 0 0

0 0 0 0 0 0

#DIV/0! #DIV/0! #DIV/0! 0 0 0

0 0 0 0 0 0






#VALUE! #VALUE! #VALUE! #VALUE! 0 0





#DIV/0! #DIV/0! #DIV/0! 0 0 0


18/40






19/40

Depreciation & Amortization

Book Depreciation & Amortization: Gross PP&E $500,000

PP&E Write-up $2,450

Fair Value PP&E $502,450

Accumulated Depreciation 25,000

Net PP&E $477,450

Year 2010 2011 2012

Year No. 1 2 3

Existing PP&E (incl Write-up)

Beginning Amt Remaining Life Salvage

Class 1 $90,000 12 $900 7,425 7,425 7,425

Class 2 85,000 10 800 8,420 8,420 8,420

Class 3 60,000 8 600 7,425 7,425 7,425

Class 4 242,450 6 600 40,308 40,308 40,308

477,450 2,900 63,578 63,578 63,578

Capex

Year Capex Useful Life Salvage

1 $110,000 12 9,167 9,167 9,167

2 121,000 10 0 12,100 12,100

3 133,100 15 0 0 8,873

4 146,410 14 0 0 0

5 161,051 12 0 0 0

6 177,156 16 0 0 0

7 194,872 15 0 0 0

8 214,359 15 0 0 0

9 235,795 12 0 0 0

10 259,374 12 0 0 0

1,753,117 0 9,167 21,267 30,140

Computed Depreciation 72,745 84,845 93,718

Manual Depreciation 40,000 40,000 40,000

Total Book Depreciation 72,745 84,845 93,718

Period (Yrs)

Amortization of Intangibles (incl Write-up) 12 179 179 179

Impairment of Goodwill 0 0 0

Total Cost ($)

Amortization of Capitalized Financing Costs 10,888 #VALUE! #VALUE! #VALUE!

Bank Revolver 450 450 450

Term Loan "A" #VALUE! #VALUE! #VALUE!

Term Loan "B" #VALUE! #VALUE! #VALUE!

Senior Notes #VALUE! #VALUE! #VALUE!

Subordinated Notes #VALUE! #VALUE! #VALUE!

Mezzanine Debt #VALUE! #VALUE! #VALUE!

Seller Notes #VALUE! #DIV/0! #DIV/0!


20/40

Tax Depreciation & Amortization: Tax PP&E $415,000

Existing PP&E (Tax)

Beginning Amt Remaining Life Salvage

Class 1 $130,000 12 $900 10,758 10,758 10,758

Class 2 105,000 10 800 10,420 10,420 10,420Class 3 95,000 8 600 11,800 11,800 11,800

Class 4 85,000 6 600 14,067 14,067 14,067

415,000 2,900 47,045 47,045 47,045

Capex

Year Capex Useful Life Salvage

1 $110,000 12 9,167 9,167 9,167

2 121,000 8 0 15,125 15,125

3 133,100 15 0 0 8,873

4 146,410 9 0 0 0

5 161,051 4 0 0 0

6 177,156 16 0 0 0

7 194,872 15 0 0 0

8 214,359 2 0 0 0

9 235,795 12 0 0 0

10 259,374 12 0 0 0

1,753,117 0 9,167 24,292 33,165

Total Tax Depreciation 56,212 71,337 80,210

Period (Yrs)

Amortization of Intangibles (incl Write-up) 15 143 143 143

Amortization of Goodwill 15 14,099 14,099 14,099


21/40

Depreciation Method Goodwill & Intangibles Amortization

0 0=SLN 1=Sum of Years 0=Not Deductible 1=Tax Deductible

1 Tax Deductible

Manual or Computed Depreciation

1 0=Manual 1=Computed

2013 2014 2015 2016 2017 2018 2019

4 5 6 7 8 9 10

7,425 7,425 7,425 7,425 7,425 7,425 7,425

8,420 8,420 8,420 8,420 8,420 8,420 8,420

7,425 7,425 7,425 7,425 7,425 0 0

40,308 40,308 40,308 0 0 0 0

63,578 63,578 63,578 23,270 23,270 15,845 15,845

9,167 9,167 9,167 9,167 9,167 9,167 9,167

12,100 12,100 12,100 12,100 12,100 12,100 12,100

8,873 8,873 8,873 8,873 8,873 8,873 8,873

10,458 10,458 10,458 10,458 10,458 10,458 10,458

0 13,421 13,421 13,421 13,421 13,421 13,421

0 0 11,072 11,072 11,072 11,072 11,072

0 0 0 12,991 12,991 12,991 12,991

0 0 0 0 14,291 14,291 14,291

0 0 0 0 0 19,650 19,650

0 0 0 0 0 0 21,615

40,598 54,019 65,091 78,082 92,373 112,023 133,637

104,176 117,597 128,669 101,352 115,643 127,868 149,482

40,000 40,000 40,000 40,000 40,000 40,000 40,000

104,176 117,597 128,669 101,352 115,643 127,868 149,482

179 179 179 179 179 179 179

0 0 0 0 0 0 0


450 450 450 450 450 450 450






#DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0!


22/40

Depreciation Method

0 0=SLN 1=Sum of Years

10,758 10,758 10,758 10,758 10,758 10,758 10,758

10,420 10,420 10,420 10,420 10,420 10,420 10,42011,800 11,800 11,800 11,800 11,800 0 0

14,067 14,067 14,067 0 0 0 0

47,045 47,045 47,045 32,978 32,978 21,178 21,178

9,167 9,167 9,167 9,167 9,167 9,167 9,167

15,125 15,125 15,125 15,125 15,125 15,125 0

8,873 8,873 8,873 8,873 8,873 8,873 8,873

16,268 16,268 16,268 16,268 16,268 16,268 16,268

0 40,263 40,263 40,263 40,263 0 0

0 0 11,072 11,072 11,072 11,072 11,072

0 0 0 12,991 12,991 12,991 12,991

0 0 0 0 107,179 107,179 0

0 0 0 0 0 19,650 19,650

0 0 0 0 0 0 21,615

49,433 89,696 100,768 113,759 220,939 200,325 99,636

96,478 136,741 147,813 146,738 253,917 221,504 120,814

143 143 143 143 143 143 143

14,099 14,099 14,099 14,099 14,099 14,099 14,099


23/40


24/40


25/40

Income Tax Calculation

Year 2010 2011 2012 2013 2014

Year No. 1 2 3 4 5

Net Income before Tax #VALUE! #VALUE! #VALUE! #VALUE! #VALUE!

Add: Book Depreciation 72,745 84,845 93,718 104,176 117,597Add: Book (Gain) Loss on Assets Sale 1,250 1,350 1,450 1,550 1,650

Add: Book Impairment of Goodwill 0 0 0 0 0Add: Book Amortization of Intangibles 179 179 179 179 179

Add: Non-Deductible Expenses 0 0 0 0 0

Less: Tax Depreciation 56,212 71,337 80,210 96,478 136,741

Less: Tax (Gain) Loss on Assets Sale 1,350 1,500 1,600 1,700 1,750Less: Tax Amortization of Goodwill 14,099 14,099 14,099 14,099 14,099

Less: Tax Amortization of Intangibles 143 143 143 143 143

Pre-NOL Taxable Income #VALUE! #VALUE! #VALUE! #VALUE! #VALUE!

Less: NOLs Used #VALUE! #VALUE! #VALUE! #VALUE! #VALUE!

Post-NOL Taxable Income #VALUE! #VALUE! #VALUE! #VALUE! #VALUE!

Cash Taxes Payable #VALUE! #VALUE! #VALUE! #VALUE! #VALUE!

Total Book Tax #VALUE! #VALUE! #VALUE! #VALUE! #VALUE!

Increase (Decrease) in Deferred

Tax Liability #VALUE! #VALUE! #VALUE! #VALUE! #VALUE!

Beginning NOL Balance 15,500 #VALUE! #VALUE! #VALUE! #VALUE!

Current Year NOL #VALUE! #VALUE! #VALUE! #VALUE! #VALUE!NOLs Used #VALUE! #VALUE! #VALUE! #VALUE! #VALUE!

Ending NOL Balance #VALUE! #VALUE! #VALUE! #VALUE! #VALUE!

Projected Asset Sales:

Estimated Sale Proceeds 4,000 4,500 5,000 5,500 6,000

Book Basis of Assets 2,750 3,150 3,550 3,950 4,350Tax Basis of Assets 2,650 3,000 3,400 3,800 4,250

Book Gain (Loss) on Sale 1,250 1,350 1,450 1,550 1,650

Tax Gain (Loss) on Sale 1,350 1,500 1,600 1,700 1,750

Allocation to Balance Sheet:

PP&E: 1,500 1,700 1,900 2,100 2,300


26/40

Other Long-Term Assets 1,250 1,450 1,650 1,850 2,050


27/40

2015 2016 2017 2018 2019

6 7 8 9 10


128,669 101,352 115,643 127,868 149,482

1,750 1,850 1,950 2,050 2,150

0 0 0 0 0

179 179 179 179 179

0 0 0 0 0

147,813 146,738 253,917 221,504 120,814

1,900 2,000 2,200 2,300 2,500

14,099 14,099 14,099 14,099 14,099

143 143 143 143 143











6,500 7,000 7,500 8,000 8,500

4,750 5,150 5,550 5,950 6,350

4,600 5,000 5,300 5,700 6,000

1,750 1,850 1,950 2,050 2,150

1,900 2,000 2,200 2,300 2,500

2,500 2,700 2,900 3,100 3,300


28/40

2,250 2,450 2,650 2,850 3,050


29/40

Weighted Average Cost of Capital

Risk Free Rate (Treasury Bill Rate) 5.00% WACC

Market Risk Premium 4.50% 0 = Manual 1 = Co

Levered Beta 1.20 1 Com

Estimated Cost of Debt Amount Int. Rate Wtd. Rate C

Existing Debt 65,000 4.25% 0.46%

Bank Revolver 200,888 4.75% 1.59% Cost of Debt (Post-Tax)

Term Loan "A" 80,000 5.50% 0.73% Cost of Preferred Stock

Term Loan "B" 55,000 5.25% 0.48% Cost of Equity

Senior Notes 65,000 4.25% 0.46%

Subordinated Notes 40,000 4.50% 0.30% WACC

Mezzanine Debt 70,000 4.50% 0.52%

Seller Notes 25,000 5.25% 0.22%

Total Debt 600,888 4.76%

Post-Tax 3.10%

Estimated Cost of Preferred Stock

Preferred Stock 60,000 7.00%

Estimated Cost of Equity (CAPM)

Total Book Equity 282,500 16.03%

Beta Calculation

Levered Beta 1.20

Pre-Acquisition:

Debt $250,000

Equity $600,000

Preferred Stock $0

Debt/Equity Ratio 0.42

Preferred Stock/Equity Ratio 0.00

Post-Acquisition:

Debt $600,888

Equity $282,500

Preferred Stock $60,000

Debt/Equity Ratio 2.13Preferred Stock/Equity Ratio 0.21

Tax Rate 35.00%

Unlevered Beta 0.94

Relevered Beta 2.45


30/40

Manual

mputed WACC

uted 8.50%

mputed WACC

Proportion Rate Wtd. Cost

63.69% 3.10% 1.97%

6.36% 7.00% 0.45%

29.95% 16.03% 4.80%

7.22%


31/40

Discounted Cash Flow Analysis

NPV of Free Cash Flow

NPV of FCFF @ 6.00% #VALUE!



FV of Terminal Value (EBITDA) PV of T

Terminal Value @ 4.0x 2,819,044 Terminal Value @ 6.00%



FV of Terminal Value (Perpetuity) PV of Te

2.5% 3.0% 3.5%

Terminal Value @ 6.00% #VALUE! #VALUE! #VALUE! Terminal Value @ 6.00%Terminal Value @ 7.00% #VALUE! #VALUE! #VALUE! Terminal Value @ 7.00%

Terminal Value @ 8.00% #VALUE! #VALUE! #VALUE! Terminal Value @ 8.00%

Total Enterprise Value

EBITDA Multiple Method Perpetuity Growth

4.0x 6.0x 8.0x 2.5% 3.0%

6.0% #VALUE! #VALUE! #VALUE! 6.0% #VALUE! #VALUE!



Total Equity Value


4.0x 6.0x 8.0x 2.5% 3.0%




Total Equity Value Per Share


4.0x 6.0x 8.0x 2.5% 3.0%


7.0% #VALUE! #VALUE! #VALUE! 7.0% #VALUE! #VALUE!8.0% #VALUE! #VALUE! #VALUE! 8.0% #VALUE! #VALUE!


32/40

rminal Value (EBITDA)

4.0x 6.0x 8.0x

1,574,140 2,361,210 3,148,279

1,433,059 2,149,589 2,866,119

1,305,763 1,958,645 2,611,526

minal Value (Perpetuity)

2.5% 3.0% 3.5%

#VALUE! #VALUE! #VALUE!#VALUE! #VALUE! #VALUE!

#VALUE! #VALUE! #VALUE!

Percentage Split of DCF Analysis

ethod

3.5% EBITDA Multiple Method

#VALUE! Cash Flow % of TEV: 4.0x 6.0x 8.0x

#VALUE! @ 6.00% #VALUE! #VALUE! #VALUE!


@ 8.00% #VALUE! #VALUE! #VALUE!

Terminal Value % of TEV:@ 6.00% #VALUE! #VALUE! #VALUE!

ethod @ 7.00% #VALUE! #VALUE! #VALUE!

3.5% @ 8.00% #VALUE! #VALUE! #VALUE!

#VALUE!

#VALUE! Perpetuity Growth Method

#VALUE! Cash Flow % of TEV: 2.5% 3.0% 3.5%




ethod Terminal Value % of TEV:

3.5% @ 6.00% #VALUE! #VALUE! #VALUE!


#VALUE! @ 8.00% #VALUE! #VALUE! #VALUE!#VALUE!


33/40


34/40


35/40

Allocation of Exit Proceeds

EBITDA Multiple (Exit Year) 6.0x

EBITDA (Exit Year) 645,545

Exit Year Enterprise Value 3,873,273

Less: Debt #VALUE!

Less: Preferred Stock 84,153

Add: Excess Cash #VALUE!

Equity Value - Allocable Proceeds #VALUE!

2014 2015 2016 2017 2018 20195 6 7 8 9 10



0 0 0 0 #VALUE! 0

0 0 0 0 #VALUE! 0




0 0 0 0 #VALUE! 0

0 0 0 0 #VALUE! 0


0 5,891 5,891 5,891 5,891 0

0 0 0 0 0 0

0 0 0 0 84,153 0

0 0 0 0 #VALUE! 00 5,891 5,891 5,891 #VALUE! 0

2,663 2,702 2,856 3,088 3,165 0

0 0 0 0 #VALUE! 0

2,663 2,702 2,856 3,088 #VALUE! 0


36/40

484 491 519 561 575 0

0 0 0 0 #VALUE! 0

484 491 519 561 #VALUE! 0

303 307 325 351 360 0

0 0 0 0 #VALUE! 0

303 307 325 351 #VALUE! 0


37/40

Ratio Analysis

Year 2010 2011 2012 2013 2014

Year No. 1 2 3 4 5

Capital Ratios

Debt Ratio #VALUE! #VALUE! #VALUE! #VALUE! #VALUE!

Total Debt / Book Capitalization #VALUE! #VALUE! #VALUE! #VALUE! #VALUE!

Total Debt / Equity #VALUE! #VALUE! #VALUE! #VALUE! #VALUE!

Leverage Ratios

Total Debt / EBITDA #VALUE! #VALUE! #VALUE! #VALUE! #VALUE!

Total Debt / (EBITDA - Capex) #VALUE! #VALUE! #VALUE! #VALUE! #VALUE!

Net Debt / EBITDA #VALUE! #VALUE! #VALUE! #VALUE! #VALUE!

Senior Debt / EBITDA #VALUE! #VALUE! #VALUE! #VALUE! #VALUE!

Interest CoverageEBITDA / Total Interest #VALUE! #VALUE! #VALUE! #VALUE! #VALUE!

EBITDA / Senior Interest #VALUE! #VALUE! #VALUE! #VALUE! #VALUE!

EBITDA / Cash Interest #VALUE! #VALUE! #VALUE! #VALUE! #VALUE!

(EBITDA - Capex) / Total Interest #VALUE! #VALUE! #VALUE! #VALUE! #VALUE!

(EBITDA - Capex) / Senior Interest #VALUE! #VALUE! #VALUE! #VALUE! #VALUE!

(EBITDA - Capex) / Cash Interest #VALUE! #VALUE! #VALUE! #VALUE! #VALUE!

EBIT / Total Interest #VALUE! #VALUE! #VALUE! #VALUE! #VALUE!

Asset Utilization

Total Asset Turnover (Sales) #VALUE! #VALUE! #VALUE! #VALUE! #VALUE!Fixed Asset Turnover (Sales) 2.2x 2.3x 2.3x 2.4x 2.5x

Activity Ratios

Inventory Turnover (COGS) 5.1x 5.1x 4.9x 4.7x 4.6x

Accounts Receivable Turnover (Sales) 7.8x 5.7x 5.4x 5.1x 4.9x

Accounts Payable Turnover (COGS & Inv) 7.9x 7.0x 6.8x 6.7x 6.5x

Inventory Holding Period (Days) 71.9 71.1 73.9 76.9 79.9

Days Receivable (Sales) 47.0 64.4 67.6 71.0 74.5

Days Payable (COGS & Inv) 46.4 52.2 53.8 54.8 55.9

Liquidity RatiosCurrent Ratio #VALUE! #VALUE! #VALUE! #VALUE! #VALUE!

Quick (Acid Test) Ratio #VALUE! #VALUE! #VALUE! #VALUE! #VALUE!

Returns

Return on Equity (ROE) #VALUE! #VALUE! #VALUE! #VALUE! #VALUE!

Return on Assets (ROA) #VALUE! #VALUE! #VALUE! #VALUE! #VALUE!


38/40

Operating Results

Sales Growth N/A 10.00% 10.00% 10.00% 10.00%

Gross Margin 55.00% 54.89% 54.77% 54.66% 54.55%

EBITDA Margin 29.00% 28.80% 28.60% 28.40% 28.19%

EBIT Margin 22.37% 21.77% 21.54% 21.27% 20.88%

Pre-Tax Profit Ratio #VALUE! #VALUE! #VALUE! #VALUE! #VALUE!


39/40

2015 2016 2017 2018 2019

6 7 8 9 10















#VALUE! #VALUE! #VALUE! #VALUE! #VALUE!2.5x 2.5x 2.5x 2.4x 2.4x

4.4x 4.2x 4.1x 3.9x 3.8x

4.7x 4.4x 4.2x 4.0x 3.8x

6.4x 6.3x 6.1x 6.0x 5.9x

83.1 86.5 89.9 93.5 97.2

78.2 82.1 86.2 90.6 95.1

57.1 58.2 59.4 60.6 61.8






40/40

10.00% 10.00% 10.00% 10.00% 10.00%

54.43% 54.32% 54.21% 54.09% 53.98%

27.99% 27.79% 27.58% 27.38% 27.17%

20.72% 22.58% 22.18% 21.95% 21.40%


copy of lbo dcf model

Documents