Description
A spreadsheet showing the workings of Leveraged buyout using a DCF model.
Leveraged Buyout (LBO) Model Input Values
*Enter values in yellow cells. Estimated Cost of Deal: Curent Stock Price Offer Premium Offer Price Per Share No. of Shares outstanding Equity Purchase Price Existing Debt Retired Assumed Debt Expensed Transaction Costs Capitalized Financing Costs Cash Infusion Total Funds Requirement Debt Assumptions: Assumed Debt Maturity Year Annual Repayment Coupon Rate Average Interest Optional Prepayment Bank Revolver Coupon Rate Revolver limit Undrawn Commitment Fee Term Loan "A" Term Years Annual Repayment Coupon Rate Average Interest Optional Prepayment Term Loan "B" Term Years Annual Repayment Bullet Year Repayment Coupon Rate Average Interest Optional Prepayment Senior Notes Maturity Year Coupon Rate Average Interest Optional Prepayment Subordinated Notes Maturity Year Coupon Rate Average Interest Years PIK Optional Prepayment Mezzanine Debt Maturity Year Coupon Rate Average Interest Years PIK Optional Prepayment Seller Notes Term Years Annual Repayment Coupon Rate
$6.00 10.00% 6.60 100,000 $660,000 $185,000 $65,000 $2,500 $10,888 $27,500
$ amt % $ amt nos. $ amt $ amt $ amt $ amt $ amt $ amt
$950,888 $ amt
Sources of Funds: Excess Cash Debt: Assumed Debt Bank Revolver Term Loan "A" Term Loan "B" Senior Notes Subordinated Notes Mezzanine Debt Seller Notes Preferred: Preferred Stock Equity: Common - Sponsor Equity Management Equity New Equity Total
$5,000 $ amt Fees $65,000 $200,888 $80,000 $55,000 $65,000 $40,000 $70,000 $25,000 $ amt $ amt $ amt $ amt $ amt $ amt $ amt $ amt 0.75% 1.50% 1.75% 2.00% 2.50% 2.75% 0.00% 2.00%
$60,000 $ amt $220,000 $40,000 $25,000 $950,888 $ amt $ amt $ amt $ amt
Goodwill / Bargain Purchase: Equity Purchase Price Add: Existing Goodwill Less: Net Assets (Book Value) Fixed Assets (PP&E) Write-up Identifiable Tangibles Write-up Deferred Tax Liability - existing DTL Created on Assets Write-up Goodwill/(Gain from Bargain Purchase)
$660,000 $ amt $20,000 $ amt $464,749 $2,450 $1,200 $1,400 ($1,278) $211,479 $ amt $ amt $ amt $ amt $ amt $ amt
Average Interest Years PIK Optional Prepayment Preferred Stock Preferred Stock Dividend Years PIK Pref Stock Retired Year
9 11.11% 4.25% 1 1
select no. % of FV % annual 1=Yes 0=No 1=Yes 0=No
4.75% % annual $600,000 $ amt 1.00% % annual
Income/BS Assumptions: Income Statement: Sales Year 1 Sales - Annual Increase Cost of goods sold Cost of goods sold - Annual Increase SG&A expenses SG&A expenses - Annual Increase Other expenses Other expenses - Annual Increase Balance Sheet: Accounts receivable Accounts receivable - Annual Increase Inventories Inventories - Annual Increase Prepaids Prepaids - Annual Increase Other current assets Other current assets - Annual Increase Other long-term assets Other long-term assets - Annual Increase Accounts payable Accounts payable - Annual Increase Accrued expenses Accrued expenses - Annual Increase Other current liabilities Other current liabilities - Annual Increase Capex Capex Start Year Capex Interval
$1,100,000 10.00% 45.00% 0.25% 14.00% 0.50% 12.00% 0.15%
$ amt % % of revenue % % of revenue % % of revenue %
8 12.50% 5.50% 1 1
select no. % of FV % annual 1=Yes 0=No 1=Yes 0=No
11 4.00% 60.00% 5.25% 1 1
select no. % of FV % of FV % annual 1=Yes 0=No 1=Yes 0=No
18.00% 5.00% 20.00% 4.00% 4.50% 2.00% 12.00% 2.50% 4.00% 0.50% 15.00% 2.00% 6.00% 1.50% 8.00% 1.00% 10.00% 1 1
% of revenue % % of COGS % % of revenue % % of revenue % % of revenue % % of COGS plus inventory increase % % of revenue % % of revenue % % of revenue in years in years
9 4.25% 1 1
select no. % annual 1=Yes 0=No 1=Yes 0=No
12 4.50% 0 5 1
select no. % annual 1=Yes 0=No enter no. 1=Yes 0=No
Other Assumptions: Cash Minimum On / Off Tax Rate Year 1
$50,000 $ amt 1 1=On. 0=Off. 35.00% % 2010 year
14 4.50% 0 5 1
select no. % annual 1=Yes 0=No enter no. 1=Yes 0=No
7 select no. 14.29% % of FV 5.25% % annual
DCF Assumptions: Discount Rate (WACC) Discount Rate Range EBITDA Terminal Value Multiple EBITDA Multiple Range Perpetuity Growth Rate Perpetuity Growth Range Net Debt
7.00% 1.0% 6.0x 2.0x 3.00% 0.5% $583,388
% % x x % % $ amt
1 1=Yes 0=No 5 enter no. 1 1=Yes 0=No
7.00% % annual 5 enter no. select no.
Financing Costs
Annual Amortization
4,500 1,200 963 1,300 1,000 1,925 0 10,888 1,200 12,088
450 150 88 144 83 138 0 1,053
Period (Yrs) 10
Income Statements
Year Year No. 2010 1 2011 2 2012 3 2013 4 2014 5
Net Sales Cost of goods sold Gross Margin Operating Expenses SG&A expenses Other expenses EBITDA D&A EBIT Interest Expense Existing Debt Bank Revolver Term Loan "A" Term Loan "B" Senior Notes Subordinated Notes Mezzanine Debt Seller Notes Other Expenses (Income) Amortization of Capitalized Financing Costs (Gain) Loss on Asset Sales Net Income before Taxes Income Tax Expense (Benefit) Net Income Dividend Preferred Stock Dividend Common Stock Dividend Net Income to Common Shareholders
1,100,000 495,000 605,000 286,000 154,000 132,000 319,000 72,924 246,076 32,061 2,609 13,533 4,125 2,830 2,763 1,800 3,150 1,252 (197) 1,053 (1,250) 214,212 74,974 139,238 6,700 4,200 2,500 132,538
1,210,000 545,861 664,139 315,665 170,247 145,418 348,474 85,024 263,450 30,509 1,228 14,005 3,500 2,714 2,763 1,881 3,292 1,127 (297) 1,053 (1,350) 233,238 81,633 151,605 7,194 4,494 2,700 144,411
1,331,000 601,948 729,052 348,408 188,208 160,200 380,644 93,898 286,746 26,835 0 14,005 1,575 2,092 2,763 1,966 3,440 995 353 1,803 (1,450) 259,559 90,846 168,713 7,659 4,809 2,850 161,055
1,464,100 663,799 800,301 384,548 208,064 176,484 415,753 104,355 311,398 22,778 0 14,005 0 764 1,505 2,054 3,595 856 (35) 1,515 (1,550) 288,655 101,029 187,625 8,245 5,145 3,100 179,380
1,610,510 732,004 878,506 424,438 230,015 194,423 454,068 117,776 336,292 20,741 0 14,005 0 (0) 124 2,147 3,756 709 (257) 1,393 (1,650) 315,807 110,533 205,275 8,955 5,505 3,450 196,319
2015 6
2016 7
2017 8
2018 9
2019 10
1,771,561 807,217 964,344 468,468 254,281 214,187 495,876 128,849 367,027 16,059 0 14,005 0 (0) 0 97 1,640 317 521 2,271 (1,750) 350,447 122,656 227,791 9,391 5,891 3,500 218,400
1,948,717 890,159 1,058,558 517,067 281,108 235,959 541,492 101,532 439,960 12,049 0 12,049 0 (0) 0 0 0 0 (1,400) 450 (1,850) 429,310 150,259 279,052 9,591 5,891 3,700 269,461
2,143,589 981,623 1,161,966 570,709 310,765 259,944 591,257 115,822 475,435 8,161 0 8,161 0 (0) (0) 0 0 0 (1,500) 450 (1,950) 468,774 164,071 304,703 9,891 5,891 4,000 294,813
2,357,948 1,082,485 1,275,463 629,918 343,551 286,367 645,545 128,047 517,499 6,000 0 6,000 0 (0) (0) 0 0 0 (1,600) 450 (2,050) 513,099 179,585 333,514 9,991 5,891 4,100 323,523
2,593,742 1,193,710 1,400,033 695,271 379,795 315,476 704,761 149,661 555,100 6,000 0 6,000 0 (0) (0) 0 0 0 (1,700) 450 (2,150) 550,800 192,780 358,020 10,291 5,891 4,400 347,729
Balance Sheets
Year Year No. ASSETS Current Assets: Cash in bank Accounts receivable Inventories Prepaids Other current assets Total Current Assets Non-Current Assets: Goodwill Intangible Assets Capitalized Financing Costs Other Long-Term Assets PP&E: Gross PP&E Capex Depreciation (Current) Accumulated Depreciation Net PP&E Total Non-Current Assets Total Assets Pre-Acq Adj Post-Acq 0 2010 1 2011 2 2012 3
55,000 85,000 95,999 22,000 45,000 302,999
22,500
77,500 85,000 95,999 22,000 45,000 325,499
50,000 198,000 99,000 49,500 132,000 528,500
50,000 228,690 113,539 55,539 148,830 596,598
50,000 264,137 130,213 62,315 167,806 674,471
20,000 950 0 15,000 500,000 0 0 25,000 475,000 510,950 813,949
191,479 1,200 10,888
211,479 2,150 10,888 15,000 502,450 0 0 25,000 477,450 716,966
211,479 1,971 9,835 44,000 502,450 110,000 72,745 97,745 514,705 781,989 1,310,489
211,479 1,792 8,782 48,642 612,450 121,000 84,845 182,590 550,860 821,554 1,418,152
211,479 1,613 6,979 53,774 733,450 133,100 93,718 276,308 590,242 864,086 1,538,557
2,450
228,516
1,042,465
LIABILITIES & EQUITY Current Liabilities: Accounts payable Accrued expenses Other current liabilities Total Current Liabilities Deferred Tax Liability Long-Term Debt: Existing Debt Bank Revolver Term Loan "A" Term Loan "B" Senior Notes Subordinated Notes Mezzanine Debt Seller Notes Total Long Term Debt
51,800 24,000 22,000 97,800 1,400 250,000 0 0 0 0 0 0 0 250,000 (123) (185,000) 200,888 80,000 55,000 65,000 40,000 70,000 25,000
51,800 24,000 22,000 97,800 1,278 65,000 200,888 80,000 55,000 65,000 40,000 70,000 25,000 600,888
74,700 66,000 88,000 228,700 5,873 57,778 213,470 70,000 52,800 65,000 41,800 73,150 22,680 596,678
85,741 73,689 97,768 257,198 6,119 0 213,470 57,264 50,600 65,000 43,681 76,442 20,235 526,692
96,542 82,274 108,620 287,436 6,366 0 213,470 0 29,091 65,000 45,647 79,882 17,659 450,748
Total Long-Term Liabilities Total Liabilities Shareholder's Equity: Preferred Stock Common Equity Retained Earnings Total Shareholder's Equity Total Liabilities & Equity
251,400 349,200
602,165 699,965
602,551 831,251
532,811 790,010
457,114 744,551
0 440,000 24,749 464,749 813,949
60,000 (155,000) (27,249)
60,000 285,000 (2,500) 342,500
64,200 285,000 130,038 479,238 1,310,489
68,694 285,000 274,449 628,143 1,418,152
73,503 285,000 435,503 794,006 1,538,557
228,516
1,042,465
Balance Sheet Check
0
0
0
0
0
0
2013 4
2014 5
2015 6
2016 7
2017 8
2018 9
2019 10
50,000 305,078 149,337 69,917 189,201 763,533
50,000 352,365 171,268 78,447 213,324 865,405
50,000 406,982 196,421 88,018 240,523 981,943
50,000 470,064 225,267 98,756 271,190 1,115,277
134,329 542,924 258,350 110,804 305,766 1,352,173
263,486 627,077 296,291 124,322 344,752 1,655,928
352,890 724,274 339,804 139,489 388,707 1,945,165
211,479 1,433 5,464 59,447 866,550 146,410 104,176 380,485 632,475 910,298 1,673,831
211,479 1,254 4,071 65,719 1,012,960 161,051 117,597 498,082 675,929 958,451 1,823,856
211,479 1,075 1,800 72,652 1,174,011 177,156 128,669 626,751 724,416 1,011,421 1,993,365
211,479 896 1,350 80,317 1,351,167 194,872 101,352 728,103 817,935 1,111,976 2,227,253
211,479 717 900 88,790 1,546,039 214,359 115,643 843,747 916,651 1,218,536 2,570,709
211,479 538 450 98,157 1,760,398 235,795 127,868 971,614 1,024,578 1,335,202 2,991,130
211,479 358 0 108,513 1,996,192 259,374 149,482 1,121,096 1,134,470 1,454,820 3,399,985
108,708 91,859 120,677 321,244 8,646 0 213,470 0 0 5,819 47,701 83,476 14,943 365,410
122,413 102,560 134,072 359,045 20,303 0 213,470 0 (0) 0 2,147 36,454 12,081 264,152
137,851 114,508 148,954 401,313 31,978 0 161,317 0 (0) 0 0 0 0 161,317
155,242 127,849 165,488 448,579 52,837 0 57,619 0 (0) 0 0 0 0 57,619
174,837 142,743 183,857 501,437 106,242 0 0 0 (0) (0) 0 0 0 0
196,914 159,373 204,266 560,552 144,024 0 0 0 (0) (0) 0 0 0 0
221,789 177,940 226,939 626,668 139,035 0 0 0 0 (0) 0 0 0 0
374,055 695,300
284,455 643,500
193,295 594,608
110,456 559,036
106,242 607,679
144,024 704,576
139,035 765,703
78,648 285,000 614,884 978,532 1,673,831
84,153 285,000 811,203 1,180,356 1,823,856
84,153 285,000 1,029,603 1,398,756 1,993,365
84,153 285,000 1,299,064 1,668,217 2,227,253
84,153 285,000 1,593,877 1,963,030 2,570,709
84,153 285,000 1,917,400 2,286,553 2,991,130
84,153 285,000 2,265,129 2,634,282 3,399,985
0
0
0
0
0
0
0
Cash Flows
Year Year No. Cash flows from operating activities Net Income to Common Shareholders Non-cash adjustments: Depreciation and amortization Amortization of Capitalized Financing Costs Increase (Decrease) in Deferred Tax Liability Accrued Interest Subordinated Notes Accrued Interest Mezzanine Debt Accrued Interest Seller Notes Accrued Interest Preferred Stock Changes in operating assets and liabilities: (Increase) decrease in accounts receivable (Increase) decrease in inventories (Increase) decrease in prepaids & other assets Increase (decrease) in accounts payable Increase (decrease) in accrued exp & other liabilities Net cash provided by (used in) operating activities Cash flows from investing activities Purchase of property & equipment (Increase) decrease in other long-term assets Net cash provided by (used in) investing activities Cash flows from financing activities Increase (decrease) in Existing Debt Increase (decrease) in Term Loan "A" Increase (decrease) in Term Loan "B" Increase (decrease) in Senior Notes Increase (decrease) in Subordinated Notes Increase (decrease) in Mezzanine Debt Increase (decrease) in Seller Notes Increase (decrease) in Preferred Stock Net cash provided by (used in) financing activities Net increase (decrease) in cash Cash at beginning of year Cash available for Bank Revolver Added (retired) Bank Revolver Cash at end of year 132,538 144,411 161,055 179,380
2010 1
2011 2
2012 3
2013 4
72,924 1,053 4,595 1,800 3,150 1,252 4,200
85,024 1,053 247 1,881 3,292 1,127 4,494
93,898 1,803 247 1,966 3,440 995 4,809
104,355 1,515 2,280 2,054 3,595 856 5,145
(113,000) (3,001) (114,500) 22,900 108,000 121,911
(30,690) (14,539) (22,869) 11,041 17,457 201,928
(35,447) (16,674) (25,752) 10,801 19,437 220,576
(40,941) (19,123) (28,998) 12,166 21,642 243,926
(110,000) (29,000) (139,000)
(121,000) (4,642) (125,642)
(133,100) (5,132) (138,232)
(146,410) (5,673) (152,083)
(7,222) (10,000) (2,200) 0 0 0 (3,571) 0 (22,994) (40,083) 77,500 37,417 12,583 50,000
(57,778) (12,736) (2,200) 0 0 0 (3,571) 0 (76,286) 0 50,000 50,000 0 50,000
0 (57,264) (21,509) 0 0 0 (3,571) 0 (82,344) 0 50,000 50,000 0 50,000
0 0 (29,091) (59,181) 0 0 (3,571) 0 (91,843) 0 50,000 50,000 (0) 50,000
Free Cash Flows
EBIT Add: Amortization of non-deductible goodwill & intangibles EBIT (Adj) Less: Tax on EBIT (Adj) Unlevered Net Income Plus: D&A & other non-cash charges affecting EBIT Less: Capital Expenditures Less: Increase in Net (non-cash) Working Capital Less: (Increase) decrease in Deferred Tax 246,076 0 246,076 86,127 159,949 72,924 110,000 99,601 (4,595) 27,868 263,450 0 263,450 92,207 171,242 85,024 121,000 39,600 (247) 95,913 286,746 0 286,746 100,361 186,385 93,898 133,100 47,635 (247) 99,794 311,398 0 311,398 108,989 202,409 104,355 146,410 55,254 (2,280) 107,380
Unlevered Free Cash Flows (UFCF)
Debt Repayment
Cash Available for Debt Repayment Cash at beginning of year Net cash provided by (used in) operating activities Net cash provided by (used in) investing activities Sub-total Minimum Cash Balance Cash Available for Debt Repayment Mandatory Repayment Existing Debt Term Loan "A" Term Loan "B" Senior Notes Subordinated Notes Mezzanine Debt Seller Notes Preferred Stock Sub-total Cash Balance Incremental Revolver Cash Available for Optional Repayment Optional Repayment Existing Debt Term Loan "A" Term Loan "B" Senior Notes Subordinated Notes Mezzanine Debt Seller Notes Bank Revolver Sub-total Cash at end of year
77,500 121,911 (139,000) 60,411 50,000 10,411
50,000 201,928 (125,642) 126,286 50,000 76,286
50,000 220,576 (138,232) 132,344 50,000 82,344
50,000 243,926 (152,083) 141,843 50,000 91,843
7,222 10,000 2,200 0 0 0 3,571 0 22,994 (12,583) 12,583 0
7,222 10,000 2,200 0 0 0 3,571 0 22,994 53,292 0 53,292
0 10,000 2,200 0 0 0 3,571 0 15,771 66,572 0 66,572
0 0 2,200 0 0 0 3,571 0 5,771 86,072 0 86,072
0 0 0 0 0 0 0 0 0 50,000
50,556 2,736 0 0 0 0 0 0 53,292 50,000
0 47,264 19,309 0 0 0 0 0 66,572 50,000
0 0 26,891 59,181 0 0 0 0 86,072 50,000
Cash Flow Check
0
0
0
0
2014 5
2015 6
2016 7
2017 8
2018 9
2019 10
196,319
218,400
269,461
294,813
323,523
347,729
117,776 1,393 11,657 2,147 3,756 709 5,505
128,849 2,271 11,675 0 0 0 0
101,532 450 20,859 0 0 0 0
115,822 450 53,405 0 0 0 0
128,047 450 37,782 0 0 0 0
149,661 450 (4,989) 0 0 0 0
(47,287) (21,932) (32,653) 13,704 24,097 275,193
(54,617) (25,152) (36,769) 15,438 26,830 286,924
(63,082) (28,846) (41,405) 17,392 29,874 306,235
(72,860) (33,083) (46,625) 19,594 33,264 364,780
(84,153) (37,941) (52,503) 22,077 37,038 374,319
(97,197) (43,513) (59,123) 24,876 41,240 359,134
(161,051) (6,272) (167,323)
(177,156) (6,933) (184,089)
(194,872) (7,665) (202,536)
(214,359) (8,473) (222,832)
(235,795) (9,367) (245,162)
(259,374) (10,356) (269,730)
0 0 (0) (5,819) (47,701) (50,779) (3,571) 0 (107,870) 0 50,000 50,000 0 50,000
0 0 (0) 0 (2,147) (36,454) (12,081) 0 (50,681) 52,153 50,000 102,153 (52,153) 50,000
0 0 (0) (0) 0 0 0 0 (0) 103,698 50,000 153,698 (103,698) 50,000
0 0 (0) (0) 0 0 0 0 (0) 141,948 50,000 191,948 (57,619) 134,329
0 0 0 (0) 0 0 0 0 (0) 129,157 134,329 263,486 (0) 263,486
0 0 0 0 0 (0) 0 0 0 89,404 263,486 352,890 (0) 352,890
336,292 0 336,292 117,702 218,590 117,776 161,051 64,071 (11,657) 122,901
367,027 0 367,027 128,460 238,568 128,849 177,156 74,270 (11,675) 127,665
439,960 0 439,960 153,986 285,974 101,532 194,872 86,067 (20,859) 127,426
475,435 0 475,435 166,402 309,033 115,822 214,359 99,710 (53,405) 164,192
517,499 0 517,499 181,125 336,374 128,047 235,795 115,483 (37,782) 150,925
555,100 0 555,100 194,285 360,815 149,661 259,374 133,717 4,989 112,395
50,000 275,193 (167,323) 157,870 50,000 107,870
50,000 286,924 (184,089) 152,834 50,000 102,834
50,000 306,235 (202,536) 153,698 50,000 103,698
50,000 364,780 (222,832) 191,948 50,000 141,948
134,329 374,319 (245,162) 263,486 50,000 213,486
263,486 359,134 (269,730) 352,890 50,000 302,890
0 0 0 0 0 0 3,571 0 3,571 104,299 0 104,299
0 0 (0) 0 0 0 3,571 0 3,571 99,263 0 99,263
0 0 (0) 0 0 0 0 0 (0) 103,698 0 103,698
0 0 (0) 0 0 0 0 0 (0) 141,948 0 141,948
0 0 (0) (0) 0 0 0 0 (0) 213,486 0 213,486
0 0 (0) 0 0 0 0 0 (0) 302,890 0 302,890
0 0 0 5,819 47,701 50,779 0 0 104,299 50,000
0 0 0 0 2,147 36,454 8,509 52,153 99,263 50,000
0 0 0 0 0 0 0 103,698 103,698 50,000
0 0 0 0 0 0 0 57,619 57,619 134,329
0 0 0 0 0 0 0 0 0 263,486
0 0 0 0 0 0 0 0 0 352,890
0
0
0
(0)
0
0
Depreciation & Amortization
Book Depreciation & Amortization:
Gross PP&E PP&E Write-up Fair Value PP&E Accumulated Depreciation Net PP&E $500,000 $2,450 $502,450 25,000 $477,450
Year Year No. Existing PP&E (incl Write-up) Beginning Amt Remaining Life Class 1 $90,000 12 Class 2 85,000 10 Class 3 60,000 8 Class 4 242,450 6 477,450 Capex Year 1 2 3 4 5 6 7 8 9 10 Capex $110,000 121,000 133,100 146,410 161,051 177,156 194,872 214,359 235,795 259,374 1,753,117 Useful Life 12 10 15 14 12 16 15 15 12 12 Salvage
2010 1
2011 2
2012 3
Salvage $900 800 600 600 2,900
7,425 8,420 7,425 40,308 63,578
7,425 8,420 7,425 40,308 63,578
7,425 8,420 7,425 40,308 63,578
0
9,167 0 0 0 0 0 0 0 0 0 9,167 72,745 40,000 72,745
9,167 12,100 0 0 0 0 0 0 0 0 21,267 84,845 40,000 84,845
9,167 12,100 8,873 0 0 0 0 0 0 0 30,140 93,718 40,000 93,718
Computed Depreciation Manual Depreciation Total Book Depreciation Period (Yrs) 12 Total Cost ($) 10,888
Amortization of Intangibles (incl Write-up) Impairment of Goodwill Amortization of Capitalized Financing Costs Bank Revolver Term Loan "A" Term Loan "B" Senior Notes Subordinated Notes Mezzanine Debt Seller Notes
179 0 1,053 450 150 88 144 83 138 0
179 0 1,053 450 150 88 144 83 138 0
179 0 1,803 450 900 88 144 83 138 0
Tax Depreciation & Amortization:
Tax PP&E
$415,000
Existing PP&E (Tax) Beginning Amt Remaining Life Class 1 $130,000 12 Class 2 105,000 10 Class 3 95,000 8 Class 4 85,000 6 415,000 Capex Year 1 2 3 4 5 6 7 8 9 10 Capex $110,000 121,000 133,100 146,410 161,051 177,156 194,872 214,359 235,795 259,374 1,753,117 Useful Life 12 8 15 9 4 16 15 2 12 12
Salvage $900 800 600 600 2,900
10,758 10,420 11,800 14,067 47,045
10,758 10,420 11,800 14,067 47,045
10,758 10,420 11,800 14,067 47,045
Salvage 9,167 0 0 0 0 0 0 0 0 0 9,167 56,212 Period (Yrs) 15 15 9,167 15,125 0 0 0 0 0 0 0 0 24,292 71,337 9,167 15,125 8,873 0 0 0 0 0 0 0 33,165 80,210
0
Total Tax Depreciation
Amortization of Intangibles (incl Write-up) Amortization of Goodwill
143 14,099
143 14,099
143 14,099
0
Depreciation Method 0=SLN 1=Sum of Years
Goodwill & Intangibles Amortization 0=Not Deductible 1=Tax Deductible 1 Tax Deductible
Manual or Computed Depreciation 1 0=Manual 1=Computed
2013 4
2014 5
2015 6
2016 7
2017 8
2018 9
2019 10
7,425 8,420 7,425 40,308 63,578
7,425 8,420 7,425 40,308 63,578
7,425 8,420 7,425 40,308 63,578
7,425 8,420 7,425 0 23,270
7,425 8,420 7,425 0 23,270
7,425 8,420 0 0 15,845
7,425 8,420 0 0 15,845
9,167 12,100 8,873 10,458 0 0 0 0 0 0 40,598 104,176 40,000 104,176
9,167 12,100 8,873 10,458 13,421 0 0 0 0 0 54,019 117,597 40,000 117,597
9,167 12,100 8,873 10,458 13,421 11,072 0 0 0 0 65,091 128,669 40,000 128,669
9,167 12,100 8,873 10,458 13,421 11,072 12,991 0 0 0 78,082 101,352 40,000 101,352
9,167 12,100 8,873 10,458 13,421 11,072 12,991 14,291 0 0 92,373 115,643 40,000 115,643
9,167 12,100 8,873 10,458 13,421 11,072 12,991 14,291 19,650 0 112,023 127,868 40,000 127,868
9,167 12,100 8,873 10,458 13,421 11,072 12,991 14,291 19,650 21,615 133,637 149,482 40,000 149,482
179 0 1,515 450 0 700 144 83 138 0
179 0 1,393 450 0 0 722 83 138 0
179 0 2,271 450 0 0 0 583 1,238 0
179 0 450 450 0 0 0 0 0 0
179 0 450 450 0 0 0 0 0 0
179 0 450 450 0 0 0 0 0 0
179 0 450 450 0 0 0 0 0 0
0
Depreciation Method 0=SLN 1=Sum of Years
10,758 10,420 11,800 14,067 47,045
10,758 10,420 11,800 14,067 47,045
10,758 10,420 11,800 14,067 47,045
10,758 10,420 11,800 0 32,978
10,758 10,420 11,800 0 32,978
10,758 10,420 0 0 21,178
10,758 10,420 0 0 21,178
9,167 15,125 8,873 16,268 0 0 0 0 0 0 49,433 96,478
9,167 15,125 8,873 16,268 40,263 0 0 0 0 0 89,696 136,741
9,167 15,125 8,873 16,268 40,263 11,072 0 0 0 0 100,768 147,813
9,167 15,125 8,873 16,268 40,263 11,072 12,991 0 0 0 113,759 146,738
9,167 15,125 8,873 16,268 40,263 11,072 12,991 107,179 0 0 220,939 253,917
9,167 15,125 8,873 16,268 0 11,072 12,991 107,179 19,650 0 200,325 221,504
9,167 0 8,873 16,268 0 11,072 12,991 0 19,650 21,615 99,636 120,814
143 14,099
143 14,099
143 14,099
143 14,099
143 14,099
143 14,099
143 14,099
Income Tax Calculation
Year Year No. 2010 1 2011 2 2012 3 2013 4 2014 5
Net Income before Tax Add: Book Depreciation Add: Book (Gain) Loss on Assets Sale Add: Book Impairment of Goodwill Add: Book Amortization of Intangibles Add: Non-Deductible Expenses Less: Tax Depreciation Less: Tax (Gain) Loss on Assets Sale Less: Tax Amortization of Goodwill Less: Tax Amortization of Intangibles Pre-NOL Taxable Income Less: NOLs Used Post-NOL Taxable Income Cash Taxes Payable Total Book Tax Increase (Decrease) in Deferred Tax Liability
214,212 72,745 1,250 0 179 0 56,212 1,350 14,099 143 216,582 15,500 201,082 70,379 74,974 4,595
233,238 84,845 1,350 0 179 0 71,337 1,500 14,099 143 232,534 0 232,534 81,387 81,633 247
259,559 93,718 1,450 0 179 0 80,210 1,600 14,099 143 258,855 0 258,855 90,599 90,846 247
288,655 104,176 1,550 0 179 0 96,478 1,700 14,099 143 282,140 0 282,140 98,749 101,029 2,280
315,807 117,597 1,650 0 179 0 136,741 1,750 14,099 143 282,501 0 282,501 98,875 110,533 11,657
Beginning NOL Balance Current Year NOL NOLs Used Ending NOL Balance
15,500 0 15,500 0
0 0 0 0
0 0 0 0
0 0 0 0
0 0 0 0
Projected Asset Sales: Estimated Sale Proceeds Book Basis of Assets Tax Basis of Assets Book Gain (Loss) on Sale Tax Gain (Loss) on Sale Allocation to Balance Sheet: PP&E: Other Long-Term Assets
4,000 2,750 2,650 1,250 1,350 4,500 3,150 3,000 1,350 1,500 5,000 3,550 3,400 1,450 1,600 5,500 3,950 3,800 1,550 1,700 6,000 4,350 4,250 1,650 1,750
1,500 1,250
1,700 1,450
1,900 1,650
2,100 1,850
2,300 2,050
2015 6
2016 7
2017 8
2018 9
2019 10
350,447 128,669 1,750 0 179 0 147,813 1,900 14,099 143 317,091 0 317,091 110,982 122,656 11,675
429,310 101,352 1,850 0 179 0 146,738 2,000 14,099 143 369,713 0 369,713 129,399 150,259 20,859
468,774 115,643 1,950 0 179 0 253,917 2,200 14,099 143 316,188 0 316,188 110,666 164,071 53,405
513,099 127,868 2,050 0 179 0 221,504 2,300 14,099 143 405,150 0 405,150 141,802 179,585 37,782
550,800 149,482 2,150 0 179 0 120,814 2,500 14,099 143 565,055 0 565,055 197,769 192,780 (4,989)
0 0 0 0
0 0 0 0
0 0 0 0
0 0 0 0
0 0 0 0
6,500 4,750 4,600 1,750 1,900
7,000 5,150 5,000 1,850 2,000
7,500 5,550 5,300 1,950 2,200
8,000 5,950 5,700 2,050 2,300
8,500 6,350 6,000 2,150 2,500
2,500 2,250
2,700 2,450
2,900 2,650
3,100 2,850
3,300 3,050
Weighted Average Cost of Capital
Risk Free Rate (Treasury Bill Rate) Market Risk Premium Levered Beta 5.00% 4.50% 1.20 WACC 0 = Manual 1 = Computed 1 Computed
Estimated Cost of Debt Existing Debt Bank Revolver Term Loan "A" Term Loan "B" Senior Notes Subordinated Notes Mezzanine Debt Seller Notes Total Debt
Amount 65,000 200,888 80,000 55,000 65,000 40,000 70,000 25,000 600,888
Int. Rate Wtd. Rate 4.25% 0.46% 4.75% 1.59% 5.50% 0.73% 5.25% 0.48% 4.25% 0.46% 4.50% 0.30% 4.50% 0.52% 5.25% 0.22% 4.76% 3.10%
Computed WACC Cost of Debt (Post-Tax) Cost of Preferred Stock Cost of Equity WACC
Post-Tax
Estimated Cost of Preferred Stock Preferred Stock Estimated Cost of Equity (CAPM) Total Book Equity
60,000
7.00%
282,500
16.03%
Beta Calculation Levered Beta Pre-Acquisition: Debt Equity Preferred Stock Debt/Equity Ratio Preferred Stock/Equity Ratio Post-Acquisition: Debt Equity Preferred Stock Debt/Equity Ratio Preferred Stock/Equity Ratio Tax Rate Unlevered Beta Relevered Beta 1.20
$250,000 $600,000 $0 0.42 0.00
$600,888 $282,500 $60,000 2.13 0.21 35.00% 0.94 2.45
WACC nual 1 = Computed Computed
Manual WACC 8.50%
Computed 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%
Discounted Cash Flow Analysis
NPV of Free Cash Flow NPV of FCFF @ 6.00% NPV of FCFF @ 7.00% NPV of FCFF @ 8.00% 802,189 760,040 720,896
FV of Terminal Value (EBITDA) Terminal Value @ 4.0x Terminal Value @ 6.0x Terminal Value @ 8.0x 2,819,044 4,228,567 5,638,089
PV of Terminal Value (E Terminal Value @ 6.00% Terminal Value @ 7.00% Terminal Value @ 8.00%
FV of Terminal Value (Perpetuity) 2.5% 3.0% Terminal Value @ 6.00% 3,291,577 3,858,906 Terminal Value @ 7.00% 2,560,116 2,894,180 Terminal Value @ 8.00% 2,094,640 2,315,344
PV of Terminal Value (Pe 3.5% 4,653,167 3,323,690 2,585,093 Terminal Value @ 6.00% Terminal Value @ 7.00% Terminal Value @ 8.00%
6.0% 7.0% 8.0%
Total Enterprise Value EBITDA Multiple Method 4.0x 6.0x 8.0x $2,376,329 $3,163,399 $3,950,468 6.0% $2,193,100 $2,909,629 $3,626,159 7.0% $2,026,659 $2,679,541 $3,332,422 8.0%
Perpetuity Growth Method 2.5% 3.0% $2,640,189 $2,956,982 $2,061,474 $2,231,295 $1,691,120 $1,793,348
6.0% 7.0% 8.0%
Total Equity Value EBITDA Multiple Method 4.0x 6.0x 8.0x $1,792,941 $2,580,011 $3,367,081 $1,609,712 $2,326,242 $3,042,771 $1,443,272 $2,096,153 $2,749,035
6.0% 7.0% 8.0%
Perpetuity Growth Method 2.5% 3.0% $2,056,801 $2,373,595 $1,478,086 $1,647,907 $1,107,732 $1,209,961
6.0% 7.0% 8.0%
Total Equity Value Per Share EBITDA Multiple Method 4.0x 6.0x 8.0x $17.93 $25.80 $33.67 6.0% $16.10 $23.26 $30.43 7.0% $14.43 $20.96 $27.49 8.0%
Perpetuity Growth Method 2.5% 3.0% $20.57 $23.74 $14.78 $16.48 $11.08 $12.10
PV of Terminal Value (EBITDA) 4.0x 6.0x 1,574,140 2,361,210 1,433,059 2,149,589 1,305,763 1,958,645
8.0x 3,148,279 2,866,119 2,611,526
PV of Terminal Value (Perpetuity) 2.5% 3.0% 1,838,000 2,154,793 1,301,433 1,471,254 970,224 1,072,452
3.5% 2,598,304 1,689,596 1,197,398
Percentage Split of DCF Analysis EBITDA Multiple Method 4.0x 6.0x 8.0x 33.8% 25.4% 20.3% 34.7% 26.1% 21.0% 35.6% 26.9% 21.6% 66.2% 65.3% 64.4% 74.6% 73.9% 73.1% 79.7% 79.0% 78.4%
uity Growth Method 3.5% $3,400,493 $2,449,636 $1,918,294
uity Growth Method 3.5% $2,817,105 $1,866,249 $1,334,907
Cash Flow % of TEV: @ 6.00% @ 7.00% @ 8.00% Terminal Value % of TEV: @ 6.00% @ 7.00% @ 8.00%
uity Growth Method 3.5% $28.17 $18.66 $13.35
Cash Flow % of TEV: @ 6.00% @ 7.00% @ 8.00% Terminal Value % of TEV: @ 6.00% @ 7.00% @ 8.00%
Perpetuity Growth Method 2.5% 3.0% 3.5% 30.4% 27.1% 23.6% 36.9% 34.1% 31.0% 42.6% 40.2% 37.6% 69.6% 63.1% 57.4% 72.9% 65.9% 59.8% 76.4% 69.0% 62.4%
IRR / Returns Calculation
Exit Year Equity Allocation on Exit Common - Sponsor Equity Management Equity New Equity Subordinated Notes Mezzanine Debt Preferred Stock Management Performance Equity Total 2018 9 Investment $220,000 40,000 25,000 40,000 70,000 60,000 N/A 455,000 Undiluted 77.19% 14.04% 8.77% N/A N/A N/A N/A 100.00% % Allocation 0.00% 0.00% 2.00% 3.00% 5.00% 4.00% 4.00% 18.00% Diluted 63.30% 11.51% 9.19% 3.00% 5.00% 4.00% 4.00% 100.00% Allocation 2,565,228 466,405 372,555 121,578 202,630 162,104 162,104 4,052,606
Cash Flows & IRR
Post-Acq 0
2010 1
2011 2
2012 3
2013 4
Subordinated Notes Initial Investment Cash Interest Repayment Principal Participation on Exit IRR
(40,000) 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
21.41%
(40,000)
Mezzanine Debt Initial Investment Cash Interest Repayment Principal Participation on Exit IRR
(70,000) 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
20.56%
(70,000)
Preferred Stock Initial Investment Cash Dividend Repayment Principal Participation on Exit IRR
(60,000) 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
18.54%
(60,000)
Common - Sponsor Equity Initial Investment Cash Dividend Participation on Exit IRR
(220,000) 1,930 0 1,930 2,084 0 2,084 2,200 0 2,200 2,393 0 2,393
31.82%
(220,000)
Management Equity
Initial Investment Cash Dividend Participation on Exit IRR
(40,000) 351 0 351 379 0 379 400 0 400 435 0 435
36.21%
(40,000)
New Equity Initial Investment Cash Dividend Participation on Exit IRR
(25,000) 219 0 219 237 0 237 250 0 250 272 0 272
35.42%
(25,000)
Allocation of Exit Proceeds EBITDA Multiple (Exit Year) EBITDA (Exit Year) Exit Year Enterprise Value Less: Debt Less: Preferred Stock Add: Excess Cash Equity Value - Allocable Proceeds 6.0x 645,545 3,873,273 0 84,153 263,486 4,052,606
2014 5
2015 6
2016 7
2017 8
2018 9
2019 10
0 47,701 0 0 47,701
97 2,147 0 0 2,243
0 0 0 0 0
0 0 0 0 0
0 0 0 121,578 121,578
0 0 0 0 0
0 50,779 0 0 50,779
1,640 36,454 0 0 38,094
0 0 0 0 0
0 0 0 0 0
0 0 0 202,630 202,630
0 0 0 0 0
0 0 0 0 0
5,891 0 0 0 5,891
5,891 0 0 0 5,891
5,891 0 0 0 5,891
5,891 0 84,153 162,104 252,148
0 0 0 0 0
2,663 0 2,663
2,702 0 2,702
2,856 0 2,856
3,088 0 3,088
3,165 2,565,228 2,568,393
0 0 0
484 0 484
491 0 491
519 0 519
561 0 561
575 628,509 629,085
0 0 0
303 0 303
307 0 307
325 0 325
351 0 351
360 372,555 372,915
0 0 0
Ratio Analysis
Year Year No. 2010 1 2011 2 2012 3 2013 4 2014 5
Capital Ratios Debt Ratio Total Debt / Book Capitalization Total Debt / Equity
0.7x 0.6x 1.6x
0.6x 0.5x 1.1x
0.5x 0.4x 0.7x
0.5x 0.3x 0.5x
0.4x 0.2x 0.3x
Leverage Ratios Total Debt / EBITDA Total Debt / (EBITDA - Capex) Net Debt / EBITDA Senior Debt / EBITDA
2.1x 3.2x 1.9x 1.4x
1.7x 2.6x 1.6x 1.1x
1.4x 2.1x 1.2x 0.8x
1.1x 1.6x 0.9x 0.5x
0.8x 1.2x 0.7x 0.5x
Interest Coverage EBITDA / Total Interest EBITDA / Senior Interest EBITDA / Cash Interest (EBITDA - Capex) / Total Interest (EBITDA - Capex) / Senior Interest (EBITDA - Capex) / Cash Interest EBIT / Total Interest
8.8x 12.3x 12.3x 5.8x 8.1x 8.1x 6.8x
10.0x 14.4x 14.4x 6.5x 9.4x 9.4x 7.5x
12.0x 18.6x 18.6x 7.8x 12.1x 12.1x 9.1x
14.9x 25.5x 25.5x 9.6x 16.6x 16.6x 11.2x
17.3x 32.1x 32.1x 11.2x 20.7x 20.7x 12.8x
Asset Utilization Total Asset Turnover (Sales) Fixed Asset Turnover (Sales)
0.9x 2.2x
0.9x 2.3x
0.9x 2.3x
0.9x 2.4x
0.9x 2.5x
Activity Ratios Inventory Turnover (COGS) Accounts Receivable Turnover (Sales) Accounts Payable Turnover (COGS & Inv) Inventory Holding Period (Days) Days Receivable (Sales) Days Payable (COGS & Inv)
5.1x 7.8x 7.9x 71.9 47.0 46.4
5.1x 5.7x 7.0x 71.1 64.4 52.2
4.9x 5.4x 6.8x 73.9 67.6 53.8
4.7x 5.1x 6.7x 76.9 71.0 54.8
4.6x 4.9x 6.5x 79.9 74.5 55.9
Liquidity Ratios Current Ratio Quick (Acid Test) Ratio
2.3x 1.1x
2.3x 1.1x
2.3x 1.1x
2.4x 1.1x
2.4x 1.1x
Returns Return on Equity (ROE) Return on Assets (ROA)
38.72% 13.25%
30.19% 12.24%
25.61% 12.27%
22.52% 12.28%
20.02% 12.19%
Operating Results Sales Growth
N/A
10.00%
10.00%
10.00%
10.00%
Gross Margin EBITDA Margin EBIT Margin Pre-Tax Profit Ratio
55.00% 29.00% 22.37% 19.47%
54.89% 28.80% 21.77% 19.28%
54.77% 28.60% 21.54% 19.50%
54.66% 28.40% 21.27% 19.72%
54.55% 28.19% 20.88% 19.61%
2015 6
2016 7
2017 8
2018 9
2019 10
0.3x 0.2x 0.2x
0.3x 0.1x 0.1x
0.3x 0.0x 0.0x
0.3x 0.0x 0.0x
0.2x 0.0x 0.0x
0.5x 0.8x 0.4x 0.3x
0.3x 0.4x 0.2x 0.1x
0.1x 0.2x -0.1x 0.0x
0.1x 0.2x -0.3x 0.0x
0.1x 0.2x -0.4x 0.0x
22.6x 35.4x 22.6x 14.5x 22.8x 14.5x 16.7x
30.2x 44.9x 30.2x 19.3x 28.8x 19.3x 24.5x
42.1x 72.5x 42.1x 26.8x 46.2x 26.8x 33.8x
54.3x 107.6x 54.3x 34.5x 68.3x 34.5x 43.5x
59.3x 117.5x 59.3x 37.5x 74.2x 37.5x 46.7x
0.9x 2.5x
0.9x 2.5x
0.9x 2.5x
0.8x 2.4x
0.8x 2.4x
4.4x 4.7x 6.4x 83.1 78.2 57.1
4.2x 4.4x 6.3x 86.5 82.1 58.2
4.1x 4.2x 6.1x 89.9 86.2 59.4
3.9x 4.0x 6.0x 93.5 90.6 60.6
3.8x 3.8x 5.9x 97.2 95.1 61.8
2.4x 1.1x
2.5x 1.2x
2.7x 1.4x
3.0x 1.6x
3.1x 1.7x
18.41% 12.17%
18.85% 13.32%
17.26% 12.68%
16.05% 11.92%
14.82% 11.14%
10.00%
10.00%
10.00%
10.00%
10.00%
54.43% 27.99% 20.72% 19.78%
54.32% 27.79% 22.58% 22.03%
54.21% 27.58% 22.18% 21.87%
54.09% 27.38% 21.95% 21.76%
53.98% 27.17% 21.40% 21.24%
doc_921373667.xls
A spreadsheet showing the workings of Leveraged buyout using a DCF model.
Leveraged Buyout (LBO) Model Input Values
*Enter values in yellow cells. Estimated Cost of Deal: Curent Stock Price Offer Premium Offer Price Per Share No. of Shares outstanding Equity Purchase Price Existing Debt Retired Assumed Debt Expensed Transaction Costs Capitalized Financing Costs Cash Infusion Total Funds Requirement Debt Assumptions: Assumed Debt Maturity Year Annual Repayment Coupon Rate Average Interest Optional Prepayment Bank Revolver Coupon Rate Revolver limit Undrawn Commitment Fee Term Loan "A" Term Years Annual Repayment Coupon Rate Average Interest Optional Prepayment Term Loan "B" Term Years Annual Repayment Bullet Year Repayment Coupon Rate Average Interest Optional Prepayment Senior Notes Maturity Year Coupon Rate Average Interest Optional Prepayment Subordinated Notes Maturity Year Coupon Rate Average Interest Years PIK Optional Prepayment Mezzanine Debt Maturity Year Coupon Rate Average Interest Years PIK Optional Prepayment Seller Notes Term Years Annual Repayment Coupon Rate
$6.00 10.00% 6.60 100,000 $660,000 $185,000 $65,000 $2,500 $10,888 $27,500
$ amt % $ amt nos. $ amt $ amt $ amt $ amt $ amt $ amt
$950,888 $ amt
Sources of Funds: Excess Cash Debt: Assumed Debt Bank Revolver Term Loan "A" Term Loan "B" Senior Notes Subordinated Notes Mezzanine Debt Seller Notes Preferred: Preferred Stock Equity: Common - Sponsor Equity Management Equity New Equity Total
$5,000 $ amt Fees $65,000 $200,888 $80,000 $55,000 $65,000 $40,000 $70,000 $25,000 $ amt $ amt $ amt $ amt $ amt $ amt $ amt $ amt 0.75% 1.50% 1.75% 2.00% 2.50% 2.75% 0.00% 2.00%
$60,000 $ amt $220,000 $40,000 $25,000 $950,888 $ amt $ amt $ amt $ amt
Goodwill / Bargain Purchase: Equity Purchase Price Add: Existing Goodwill Less: Net Assets (Book Value) Fixed Assets (PP&E) Write-up Identifiable Tangibles Write-up Deferred Tax Liability - existing DTL Created on Assets Write-up Goodwill/(Gain from Bargain Purchase)
$660,000 $ amt $20,000 $ amt $464,749 $2,450 $1,200 $1,400 ($1,278) $211,479 $ amt $ amt $ amt $ amt $ amt $ amt
Average Interest Years PIK Optional Prepayment Preferred Stock Preferred Stock Dividend Years PIK Pref Stock Retired Year
9 11.11% 4.25% 1 1
select no. % of FV % annual 1=Yes 0=No 1=Yes 0=No
4.75% % annual $600,000 $ amt 1.00% % annual
Income/BS Assumptions: Income Statement: Sales Year 1 Sales - Annual Increase Cost of goods sold Cost of goods sold - Annual Increase SG&A expenses SG&A expenses - Annual Increase Other expenses Other expenses - Annual Increase Balance Sheet: Accounts receivable Accounts receivable - Annual Increase Inventories Inventories - Annual Increase Prepaids Prepaids - Annual Increase Other current assets Other current assets - Annual Increase Other long-term assets Other long-term assets - Annual Increase Accounts payable Accounts payable - Annual Increase Accrued expenses Accrued expenses - Annual Increase Other current liabilities Other current liabilities - Annual Increase Capex Capex Start Year Capex Interval
$1,100,000 10.00% 45.00% 0.25% 14.00% 0.50% 12.00% 0.15%
$ amt % % of revenue % % of revenue % % of revenue %
8 12.50% 5.50% 1 1
select no. % of FV % annual 1=Yes 0=No 1=Yes 0=No
11 4.00% 60.00% 5.25% 1 1
select no. % of FV % of FV % annual 1=Yes 0=No 1=Yes 0=No
18.00% 5.00% 20.00% 4.00% 4.50% 2.00% 12.00% 2.50% 4.00% 0.50% 15.00% 2.00% 6.00% 1.50% 8.00% 1.00% 10.00% 1 1
% of revenue % % of COGS % % of revenue % % of revenue % % of revenue % % of COGS plus inventory increase % % of revenue % % of revenue % % of revenue in years in years
9 4.25% 1 1
select no. % annual 1=Yes 0=No 1=Yes 0=No
12 4.50% 0 5 1
select no. % annual 1=Yes 0=No enter no. 1=Yes 0=No
Other Assumptions: Cash Minimum On / Off Tax Rate Year 1
$50,000 $ amt 1 1=On. 0=Off. 35.00% % 2010 year
14 4.50% 0 5 1
select no. % annual 1=Yes 0=No enter no. 1=Yes 0=No
7 select no. 14.29% % of FV 5.25% % annual
DCF Assumptions: Discount Rate (WACC) Discount Rate Range EBITDA Terminal Value Multiple EBITDA Multiple Range Perpetuity Growth Rate Perpetuity Growth Range Net Debt
7.00% 1.0% 6.0x 2.0x 3.00% 0.5% $583,388
% % x x % % $ amt
1 1=Yes 0=No 5 enter no. 1 1=Yes 0=No
7.00% % annual 5 enter no. select no.
Financing Costs
Annual Amortization
4,500 1,200 963 1,300 1,000 1,925 0 10,888 1,200 12,088
450 150 88 144 83 138 0 1,053
Period (Yrs) 10
Income Statements
Year Year No. 2010 1 2011 2 2012 3 2013 4 2014 5
Net Sales Cost of goods sold Gross Margin Operating Expenses SG&A expenses Other expenses EBITDA D&A EBIT Interest Expense Existing Debt Bank Revolver Term Loan "A" Term Loan "B" Senior Notes Subordinated Notes Mezzanine Debt Seller Notes Other Expenses (Income) Amortization of Capitalized Financing Costs (Gain) Loss on Asset Sales Net Income before Taxes Income Tax Expense (Benefit) Net Income Dividend Preferred Stock Dividend Common Stock Dividend Net Income to Common Shareholders
1,100,000 495,000 605,000 286,000 154,000 132,000 319,000 72,924 246,076 32,061 2,609 13,533 4,125 2,830 2,763 1,800 3,150 1,252 (197) 1,053 (1,250) 214,212 74,974 139,238 6,700 4,200 2,500 132,538
1,210,000 545,861 664,139 315,665 170,247 145,418 348,474 85,024 263,450 30,509 1,228 14,005 3,500 2,714 2,763 1,881 3,292 1,127 (297) 1,053 (1,350) 233,238 81,633 151,605 7,194 4,494 2,700 144,411
1,331,000 601,948 729,052 348,408 188,208 160,200 380,644 93,898 286,746 26,835 0 14,005 1,575 2,092 2,763 1,966 3,440 995 353 1,803 (1,450) 259,559 90,846 168,713 7,659 4,809 2,850 161,055
1,464,100 663,799 800,301 384,548 208,064 176,484 415,753 104,355 311,398 22,778 0 14,005 0 764 1,505 2,054 3,595 856 (35) 1,515 (1,550) 288,655 101,029 187,625 8,245 5,145 3,100 179,380
1,610,510 732,004 878,506 424,438 230,015 194,423 454,068 117,776 336,292 20,741 0 14,005 0 (0) 124 2,147 3,756 709 (257) 1,393 (1,650) 315,807 110,533 205,275 8,955 5,505 3,450 196,319
2015 6
2016 7
2017 8
2018 9
2019 10
1,771,561 807,217 964,344 468,468 254,281 214,187 495,876 128,849 367,027 16,059 0 14,005 0 (0) 0 97 1,640 317 521 2,271 (1,750) 350,447 122,656 227,791 9,391 5,891 3,500 218,400
1,948,717 890,159 1,058,558 517,067 281,108 235,959 541,492 101,532 439,960 12,049 0 12,049 0 (0) 0 0 0 0 (1,400) 450 (1,850) 429,310 150,259 279,052 9,591 5,891 3,700 269,461
2,143,589 981,623 1,161,966 570,709 310,765 259,944 591,257 115,822 475,435 8,161 0 8,161 0 (0) (0) 0 0 0 (1,500) 450 (1,950) 468,774 164,071 304,703 9,891 5,891 4,000 294,813
2,357,948 1,082,485 1,275,463 629,918 343,551 286,367 645,545 128,047 517,499 6,000 0 6,000 0 (0) (0) 0 0 0 (1,600) 450 (2,050) 513,099 179,585 333,514 9,991 5,891 4,100 323,523
2,593,742 1,193,710 1,400,033 695,271 379,795 315,476 704,761 149,661 555,100 6,000 0 6,000 0 (0) (0) 0 0 0 (1,700) 450 (2,150) 550,800 192,780 358,020 10,291 5,891 4,400 347,729
Balance Sheets
Year Year No. ASSETS Current Assets: Cash in bank Accounts receivable Inventories Prepaids Other current assets Total Current Assets Non-Current Assets: Goodwill Intangible Assets Capitalized Financing Costs Other Long-Term Assets PP&E: Gross PP&E Capex Depreciation (Current) Accumulated Depreciation Net PP&E Total Non-Current Assets Total Assets Pre-Acq Adj Post-Acq 0 2010 1 2011 2 2012 3
55,000 85,000 95,999 22,000 45,000 302,999
22,500
77,500 85,000 95,999 22,000 45,000 325,499
50,000 198,000 99,000 49,500 132,000 528,500
50,000 228,690 113,539 55,539 148,830 596,598
50,000 264,137 130,213 62,315 167,806 674,471
20,000 950 0 15,000 500,000 0 0 25,000 475,000 510,950 813,949
191,479 1,200 10,888
211,479 2,150 10,888 15,000 502,450 0 0 25,000 477,450 716,966
211,479 1,971 9,835 44,000 502,450 110,000 72,745 97,745 514,705 781,989 1,310,489
211,479 1,792 8,782 48,642 612,450 121,000 84,845 182,590 550,860 821,554 1,418,152
211,479 1,613 6,979 53,774 733,450 133,100 93,718 276,308 590,242 864,086 1,538,557
2,450
228,516
1,042,465
LIABILITIES & EQUITY Current Liabilities: Accounts payable Accrued expenses Other current liabilities Total Current Liabilities Deferred Tax Liability Long-Term Debt: Existing Debt Bank Revolver Term Loan "A" Term Loan "B" Senior Notes Subordinated Notes Mezzanine Debt Seller Notes Total Long Term Debt
51,800 24,000 22,000 97,800 1,400 250,000 0 0 0 0 0 0 0 250,000 (123) (185,000) 200,888 80,000 55,000 65,000 40,000 70,000 25,000
51,800 24,000 22,000 97,800 1,278 65,000 200,888 80,000 55,000 65,000 40,000 70,000 25,000 600,888
74,700 66,000 88,000 228,700 5,873 57,778 213,470 70,000 52,800 65,000 41,800 73,150 22,680 596,678
85,741 73,689 97,768 257,198 6,119 0 213,470 57,264 50,600 65,000 43,681 76,442 20,235 526,692
96,542 82,274 108,620 287,436 6,366 0 213,470 0 29,091 65,000 45,647 79,882 17,659 450,748
Total Long-Term Liabilities Total Liabilities Shareholder's Equity: Preferred Stock Common Equity Retained Earnings Total Shareholder's Equity Total Liabilities & Equity
251,400 349,200
602,165 699,965
602,551 831,251
532,811 790,010
457,114 744,551
0 440,000 24,749 464,749 813,949
60,000 (155,000) (27,249)
60,000 285,000 (2,500) 342,500
64,200 285,000 130,038 479,238 1,310,489
68,694 285,000 274,449 628,143 1,418,152
73,503 285,000 435,503 794,006 1,538,557
228,516
1,042,465
Balance Sheet Check
0
0
0
0
0
0
2013 4
2014 5
2015 6
2016 7
2017 8
2018 9
2019 10
50,000 305,078 149,337 69,917 189,201 763,533
50,000 352,365 171,268 78,447 213,324 865,405
50,000 406,982 196,421 88,018 240,523 981,943
50,000 470,064 225,267 98,756 271,190 1,115,277
134,329 542,924 258,350 110,804 305,766 1,352,173
263,486 627,077 296,291 124,322 344,752 1,655,928
352,890 724,274 339,804 139,489 388,707 1,945,165
211,479 1,433 5,464 59,447 866,550 146,410 104,176 380,485 632,475 910,298 1,673,831
211,479 1,254 4,071 65,719 1,012,960 161,051 117,597 498,082 675,929 958,451 1,823,856
211,479 1,075 1,800 72,652 1,174,011 177,156 128,669 626,751 724,416 1,011,421 1,993,365
211,479 896 1,350 80,317 1,351,167 194,872 101,352 728,103 817,935 1,111,976 2,227,253
211,479 717 900 88,790 1,546,039 214,359 115,643 843,747 916,651 1,218,536 2,570,709
211,479 538 450 98,157 1,760,398 235,795 127,868 971,614 1,024,578 1,335,202 2,991,130
211,479 358 0 108,513 1,996,192 259,374 149,482 1,121,096 1,134,470 1,454,820 3,399,985
108,708 91,859 120,677 321,244 8,646 0 213,470 0 0 5,819 47,701 83,476 14,943 365,410
122,413 102,560 134,072 359,045 20,303 0 213,470 0 (0) 0 2,147 36,454 12,081 264,152
137,851 114,508 148,954 401,313 31,978 0 161,317 0 (0) 0 0 0 0 161,317
155,242 127,849 165,488 448,579 52,837 0 57,619 0 (0) 0 0 0 0 57,619
174,837 142,743 183,857 501,437 106,242 0 0 0 (0) (0) 0 0 0 0
196,914 159,373 204,266 560,552 144,024 0 0 0 (0) (0) 0 0 0 0
221,789 177,940 226,939 626,668 139,035 0 0 0 0 (0) 0 0 0 0
374,055 695,300
284,455 643,500
193,295 594,608
110,456 559,036
106,242 607,679
144,024 704,576
139,035 765,703
78,648 285,000 614,884 978,532 1,673,831
84,153 285,000 811,203 1,180,356 1,823,856
84,153 285,000 1,029,603 1,398,756 1,993,365
84,153 285,000 1,299,064 1,668,217 2,227,253
84,153 285,000 1,593,877 1,963,030 2,570,709
84,153 285,000 1,917,400 2,286,553 2,991,130
84,153 285,000 2,265,129 2,634,282 3,399,985
0
0
0
0
0
0
0
Cash Flows
Year Year No. Cash flows from operating activities Net Income to Common Shareholders Non-cash adjustments: Depreciation and amortization Amortization of Capitalized Financing Costs Increase (Decrease) in Deferred Tax Liability Accrued Interest Subordinated Notes Accrued Interest Mezzanine Debt Accrued Interest Seller Notes Accrued Interest Preferred Stock Changes in operating assets and liabilities: (Increase) decrease in accounts receivable (Increase) decrease in inventories (Increase) decrease in prepaids & other assets Increase (decrease) in accounts payable Increase (decrease) in accrued exp & other liabilities Net cash provided by (used in) operating activities Cash flows from investing activities Purchase of property & equipment (Increase) decrease in other long-term assets Net cash provided by (used in) investing activities Cash flows from financing activities Increase (decrease) in Existing Debt Increase (decrease) in Term Loan "A" Increase (decrease) in Term Loan "B" Increase (decrease) in Senior Notes Increase (decrease) in Subordinated Notes Increase (decrease) in Mezzanine Debt Increase (decrease) in Seller Notes Increase (decrease) in Preferred Stock Net cash provided by (used in) financing activities Net increase (decrease) in cash Cash at beginning of year Cash available for Bank Revolver Added (retired) Bank Revolver Cash at end of year 132,538 144,411 161,055 179,380
2010 1
2011 2
2012 3
2013 4
72,924 1,053 4,595 1,800 3,150 1,252 4,200
85,024 1,053 247 1,881 3,292 1,127 4,494
93,898 1,803 247 1,966 3,440 995 4,809
104,355 1,515 2,280 2,054 3,595 856 5,145
(113,000) (3,001) (114,500) 22,900 108,000 121,911
(30,690) (14,539) (22,869) 11,041 17,457 201,928
(35,447) (16,674) (25,752) 10,801 19,437 220,576
(40,941) (19,123) (28,998) 12,166 21,642 243,926
(110,000) (29,000) (139,000)
(121,000) (4,642) (125,642)
(133,100) (5,132) (138,232)
(146,410) (5,673) (152,083)
(7,222) (10,000) (2,200) 0 0 0 (3,571) 0 (22,994) (40,083) 77,500 37,417 12,583 50,000
(57,778) (12,736) (2,200) 0 0 0 (3,571) 0 (76,286) 0 50,000 50,000 0 50,000
0 (57,264) (21,509) 0 0 0 (3,571) 0 (82,344) 0 50,000 50,000 0 50,000
0 0 (29,091) (59,181) 0 0 (3,571) 0 (91,843) 0 50,000 50,000 (0) 50,000
Free Cash Flows
EBIT Add: Amortization of non-deductible goodwill & intangibles EBIT (Adj) Less: Tax on EBIT (Adj) Unlevered Net Income Plus: D&A & other non-cash charges affecting EBIT Less: Capital Expenditures Less: Increase in Net (non-cash) Working Capital Less: (Increase) decrease in Deferred Tax 246,076 0 246,076 86,127 159,949 72,924 110,000 99,601 (4,595) 27,868 263,450 0 263,450 92,207 171,242 85,024 121,000 39,600 (247) 95,913 286,746 0 286,746 100,361 186,385 93,898 133,100 47,635 (247) 99,794 311,398 0 311,398 108,989 202,409 104,355 146,410 55,254 (2,280) 107,380
Unlevered Free Cash Flows (UFCF)
Debt Repayment
Cash Available for Debt Repayment Cash at beginning of year Net cash provided by (used in) operating activities Net cash provided by (used in) investing activities Sub-total Minimum Cash Balance Cash Available for Debt Repayment Mandatory Repayment Existing Debt Term Loan "A" Term Loan "B" Senior Notes Subordinated Notes Mezzanine Debt Seller Notes Preferred Stock Sub-total Cash Balance Incremental Revolver Cash Available for Optional Repayment Optional Repayment Existing Debt Term Loan "A" Term Loan "B" Senior Notes Subordinated Notes Mezzanine Debt Seller Notes Bank Revolver Sub-total Cash at end of year
77,500 121,911 (139,000) 60,411 50,000 10,411
50,000 201,928 (125,642) 126,286 50,000 76,286
50,000 220,576 (138,232) 132,344 50,000 82,344
50,000 243,926 (152,083) 141,843 50,000 91,843
7,222 10,000 2,200 0 0 0 3,571 0 22,994 (12,583) 12,583 0
7,222 10,000 2,200 0 0 0 3,571 0 22,994 53,292 0 53,292
0 10,000 2,200 0 0 0 3,571 0 15,771 66,572 0 66,572
0 0 2,200 0 0 0 3,571 0 5,771 86,072 0 86,072
0 0 0 0 0 0 0 0 0 50,000
50,556 2,736 0 0 0 0 0 0 53,292 50,000
0 47,264 19,309 0 0 0 0 0 66,572 50,000
0 0 26,891 59,181 0 0 0 0 86,072 50,000
Cash Flow Check
0
0
0
0
2014 5
2015 6
2016 7
2017 8
2018 9
2019 10
196,319
218,400
269,461
294,813
323,523
347,729
117,776 1,393 11,657 2,147 3,756 709 5,505
128,849 2,271 11,675 0 0 0 0
101,532 450 20,859 0 0 0 0
115,822 450 53,405 0 0 0 0
128,047 450 37,782 0 0 0 0
149,661 450 (4,989) 0 0 0 0
(47,287) (21,932) (32,653) 13,704 24,097 275,193
(54,617) (25,152) (36,769) 15,438 26,830 286,924
(63,082) (28,846) (41,405) 17,392 29,874 306,235
(72,860) (33,083) (46,625) 19,594 33,264 364,780
(84,153) (37,941) (52,503) 22,077 37,038 374,319
(97,197) (43,513) (59,123) 24,876 41,240 359,134
(161,051) (6,272) (167,323)
(177,156) (6,933) (184,089)
(194,872) (7,665) (202,536)
(214,359) (8,473) (222,832)
(235,795) (9,367) (245,162)
(259,374) (10,356) (269,730)
0 0 (0) (5,819) (47,701) (50,779) (3,571) 0 (107,870) 0 50,000 50,000 0 50,000
0 0 (0) 0 (2,147) (36,454) (12,081) 0 (50,681) 52,153 50,000 102,153 (52,153) 50,000
0 0 (0) (0) 0 0 0 0 (0) 103,698 50,000 153,698 (103,698) 50,000
0 0 (0) (0) 0 0 0 0 (0) 141,948 50,000 191,948 (57,619) 134,329
0 0 0 (0) 0 0 0 0 (0) 129,157 134,329 263,486 (0) 263,486
0 0 0 0 0 (0) 0 0 0 89,404 263,486 352,890 (0) 352,890
336,292 0 336,292 117,702 218,590 117,776 161,051 64,071 (11,657) 122,901
367,027 0 367,027 128,460 238,568 128,849 177,156 74,270 (11,675) 127,665
439,960 0 439,960 153,986 285,974 101,532 194,872 86,067 (20,859) 127,426
475,435 0 475,435 166,402 309,033 115,822 214,359 99,710 (53,405) 164,192
517,499 0 517,499 181,125 336,374 128,047 235,795 115,483 (37,782) 150,925
555,100 0 555,100 194,285 360,815 149,661 259,374 133,717 4,989 112,395
50,000 275,193 (167,323) 157,870 50,000 107,870
50,000 286,924 (184,089) 152,834 50,000 102,834
50,000 306,235 (202,536) 153,698 50,000 103,698
50,000 364,780 (222,832) 191,948 50,000 141,948
134,329 374,319 (245,162) 263,486 50,000 213,486
263,486 359,134 (269,730) 352,890 50,000 302,890
0 0 0 0 0 0 3,571 0 3,571 104,299 0 104,299
0 0 (0) 0 0 0 3,571 0 3,571 99,263 0 99,263
0 0 (0) 0 0 0 0 0 (0) 103,698 0 103,698
0 0 (0) 0 0 0 0 0 (0) 141,948 0 141,948
0 0 (0) (0) 0 0 0 0 (0) 213,486 0 213,486
0 0 (0) 0 0 0 0 0 (0) 302,890 0 302,890
0 0 0 5,819 47,701 50,779 0 0 104,299 50,000
0 0 0 0 2,147 36,454 8,509 52,153 99,263 50,000
0 0 0 0 0 0 0 103,698 103,698 50,000
0 0 0 0 0 0 0 57,619 57,619 134,329
0 0 0 0 0 0 0 0 0 263,486
0 0 0 0 0 0 0 0 0 352,890
0
0
0
(0)
0
0
Depreciation & Amortization
Book Depreciation & Amortization:
Gross PP&E PP&E Write-up Fair Value PP&E Accumulated Depreciation Net PP&E $500,000 $2,450 $502,450 25,000 $477,450
Year Year No. Existing PP&E (incl Write-up) Beginning Amt Remaining Life Class 1 $90,000 12 Class 2 85,000 10 Class 3 60,000 8 Class 4 242,450 6 477,450 Capex Year 1 2 3 4 5 6 7 8 9 10 Capex $110,000 121,000 133,100 146,410 161,051 177,156 194,872 214,359 235,795 259,374 1,753,117 Useful Life 12 10 15 14 12 16 15 15 12 12 Salvage
2010 1
2011 2
2012 3
Salvage $900 800 600 600 2,900
7,425 8,420 7,425 40,308 63,578
7,425 8,420 7,425 40,308 63,578
7,425 8,420 7,425 40,308 63,578
0
9,167 0 0 0 0 0 0 0 0 0 9,167 72,745 40,000 72,745
9,167 12,100 0 0 0 0 0 0 0 0 21,267 84,845 40,000 84,845
9,167 12,100 8,873 0 0 0 0 0 0 0 30,140 93,718 40,000 93,718
Computed Depreciation Manual Depreciation Total Book Depreciation Period (Yrs) 12 Total Cost ($) 10,888
Amortization of Intangibles (incl Write-up) Impairment of Goodwill Amortization of Capitalized Financing Costs Bank Revolver Term Loan "A" Term Loan "B" Senior Notes Subordinated Notes Mezzanine Debt Seller Notes
179 0 1,053 450 150 88 144 83 138 0
179 0 1,053 450 150 88 144 83 138 0
179 0 1,803 450 900 88 144 83 138 0
Tax Depreciation & Amortization:
Tax PP&E
$415,000
Existing PP&E (Tax) Beginning Amt Remaining Life Class 1 $130,000 12 Class 2 105,000 10 Class 3 95,000 8 Class 4 85,000 6 415,000 Capex Year 1 2 3 4 5 6 7 8 9 10 Capex $110,000 121,000 133,100 146,410 161,051 177,156 194,872 214,359 235,795 259,374 1,753,117 Useful Life 12 8 15 9 4 16 15 2 12 12
Salvage $900 800 600 600 2,900
10,758 10,420 11,800 14,067 47,045
10,758 10,420 11,800 14,067 47,045
10,758 10,420 11,800 14,067 47,045
Salvage 9,167 0 0 0 0 0 0 0 0 0 9,167 56,212 Period (Yrs) 15 15 9,167 15,125 0 0 0 0 0 0 0 0 24,292 71,337 9,167 15,125 8,873 0 0 0 0 0 0 0 33,165 80,210
0
Total Tax Depreciation
Amortization of Intangibles (incl Write-up) Amortization of Goodwill
143 14,099
143 14,099
143 14,099
0
Depreciation Method 0=SLN 1=Sum of Years
Goodwill & Intangibles Amortization 0=Not Deductible 1=Tax Deductible 1 Tax Deductible
Manual or Computed Depreciation 1 0=Manual 1=Computed
2013 4
2014 5
2015 6
2016 7
2017 8
2018 9
2019 10
7,425 8,420 7,425 40,308 63,578
7,425 8,420 7,425 40,308 63,578
7,425 8,420 7,425 40,308 63,578
7,425 8,420 7,425 0 23,270
7,425 8,420 7,425 0 23,270
7,425 8,420 0 0 15,845
7,425 8,420 0 0 15,845
9,167 12,100 8,873 10,458 0 0 0 0 0 0 40,598 104,176 40,000 104,176
9,167 12,100 8,873 10,458 13,421 0 0 0 0 0 54,019 117,597 40,000 117,597
9,167 12,100 8,873 10,458 13,421 11,072 0 0 0 0 65,091 128,669 40,000 128,669
9,167 12,100 8,873 10,458 13,421 11,072 12,991 0 0 0 78,082 101,352 40,000 101,352
9,167 12,100 8,873 10,458 13,421 11,072 12,991 14,291 0 0 92,373 115,643 40,000 115,643
9,167 12,100 8,873 10,458 13,421 11,072 12,991 14,291 19,650 0 112,023 127,868 40,000 127,868
9,167 12,100 8,873 10,458 13,421 11,072 12,991 14,291 19,650 21,615 133,637 149,482 40,000 149,482
179 0 1,515 450 0 700 144 83 138 0
179 0 1,393 450 0 0 722 83 138 0
179 0 2,271 450 0 0 0 583 1,238 0
179 0 450 450 0 0 0 0 0 0
179 0 450 450 0 0 0 0 0 0
179 0 450 450 0 0 0 0 0 0
179 0 450 450 0 0 0 0 0 0
0
Depreciation Method 0=SLN 1=Sum of Years
10,758 10,420 11,800 14,067 47,045
10,758 10,420 11,800 14,067 47,045
10,758 10,420 11,800 14,067 47,045
10,758 10,420 11,800 0 32,978
10,758 10,420 11,800 0 32,978
10,758 10,420 0 0 21,178
10,758 10,420 0 0 21,178
9,167 15,125 8,873 16,268 0 0 0 0 0 0 49,433 96,478
9,167 15,125 8,873 16,268 40,263 0 0 0 0 0 89,696 136,741
9,167 15,125 8,873 16,268 40,263 11,072 0 0 0 0 100,768 147,813
9,167 15,125 8,873 16,268 40,263 11,072 12,991 0 0 0 113,759 146,738
9,167 15,125 8,873 16,268 40,263 11,072 12,991 107,179 0 0 220,939 253,917
9,167 15,125 8,873 16,268 0 11,072 12,991 107,179 19,650 0 200,325 221,504
9,167 0 8,873 16,268 0 11,072 12,991 0 19,650 21,615 99,636 120,814
143 14,099
143 14,099
143 14,099
143 14,099
143 14,099
143 14,099
143 14,099
Income Tax Calculation
Year Year No. 2010 1 2011 2 2012 3 2013 4 2014 5
Net Income before Tax Add: Book Depreciation Add: Book (Gain) Loss on Assets Sale Add: Book Impairment of Goodwill Add: Book Amortization of Intangibles Add: Non-Deductible Expenses Less: Tax Depreciation Less: Tax (Gain) Loss on Assets Sale Less: Tax Amortization of Goodwill Less: Tax Amortization of Intangibles Pre-NOL Taxable Income Less: NOLs Used Post-NOL Taxable Income Cash Taxes Payable Total Book Tax Increase (Decrease) in Deferred Tax Liability
214,212 72,745 1,250 0 179 0 56,212 1,350 14,099 143 216,582 15,500 201,082 70,379 74,974 4,595
233,238 84,845 1,350 0 179 0 71,337 1,500 14,099 143 232,534 0 232,534 81,387 81,633 247
259,559 93,718 1,450 0 179 0 80,210 1,600 14,099 143 258,855 0 258,855 90,599 90,846 247
288,655 104,176 1,550 0 179 0 96,478 1,700 14,099 143 282,140 0 282,140 98,749 101,029 2,280
315,807 117,597 1,650 0 179 0 136,741 1,750 14,099 143 282,501 0 282,501 98,875 110,533 11,657
Beginning NOL Balance Current Year NOL NOLs Used Ending NOL Balance
15,500 0 15,500 0
0 0 0 0
0 0 0 0
0 0 0 0
0 0 0 0
Projected Asset Sales: Estimated Sale Proceeds Book Basis of Assets Tax Basis of Assets Book Gain (Loss) on Sale Tax Gain (Loss) on Sale Allocation to Balance Sheet: PP&E: Other Long-Term Assets
4,000 2,750 2,650 1,250 1,350 4,500 3,150 3,000 1,350 1,500 5,000 3,550 3,400 1,450 1,600 5,500 3,950 3,800 1,550 1,700 6,000 4,350 4,250 1,650 1,750
1,500 1,250
1,700 1,450
1,900 1,650
2,100 1,850
2,300 2,050
2015 6
2016 7
2017 8
2018 9
2019 10
350,447 128,669 1,750 0 179 0 147,813 1,900 14,099 143 317,091 0 317,091 110,982 122,656 11,675
429,310 101,352 1,850 0 179 0 146,738 2,000 14,099 143 369,713 0 369,713 129,399 150,259 20,859
468,774 115,643 1,950 0 179 0 253,917 2,200 14,099 143 316,188 0 316,188 110,666 164,071 53,405
513,099 127,868 2,050 0 179 0 221,504 2,300 14,099 143 405,150 0 405,150 141,802 179,585 37,782
550,800 149,482 2,150 0 179 0 120,814 2,500 14,099 143 565,055 0 565,055 197,769 192,780 (4,989)
0 0 0 0
0 0 0 0
0 0 0 0
0 0 0 0
0 0 0 0
6,500 4,750 4,600 1,750 1,900
7,000 5,150 5,000 1,850 2,000
7,500 5,550 5,300 1,950 2,200
8,000 5,950 5,700 2,050 2,300
8,500 6,350 6,000 2,150 2,500
2,500 2,250
2,700 2,450
2,900 2,650
3,100 2,850
3,300 3,050
Weighted Average Cost of Capital
Risk Free Rate (Treasury Bill Rate) Market Risk Premium Levered Beta 5.00% 4.50% 1.20 WACC 0 = Manual 1 = Computed 1 Computed
Estimated Cost of Debt Existing Debt Bank Revolver Term Loan "A" Term Loan "B" Senior Notes Subordinated Notes Mezzanine Debt Seller Notes Total Debt
Amount 65,000 200,888 80,000 55,000 65,000 40,000 70,000 25,000 600,888
Int. Rate Wtd. Rate 4.25% 0.46% 4.75% 1.59% 5.50% 0.73% 5.25% 0.48% 4.25% 0.46% 4.50% 0.30% 4.50% 0.52% 5.25% 0.22% 4.76% 3.10%
Computed WACC Cost of Debt (Post-Tax) Cost of Preferred Stock Cost of Equity WACC
Post-Tax
Estimated Cost of Preferred Stock Preferred Stock Estimated Cost of Equity (CAPM) Total Book Equity
60,000
7.00%
282,500
16.03%
Beta Calculation Levered Beta Pre-Acquisition: Debt Equity Preferred Stock Debt/Equity Ratio Preferred Stock/Equity Ratio Post-Acquisition: Debt Equity Preferred Stock Debt/Equity Ratio Preferred Stock/Equity Ratio Tax Rate Unlevered Beta Relevered Beta 1.20
$250,000 $600,000 $0 0.42 0.00
$600,888 $282,500 $60,000 2.13 0.21 35.00% 0.94 2.45
WACC nual 1 = Computed Computed
Manual WACC 8.50%
Computed 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%
Discounted Cash Flow Analysis
NPV of Free Cash Flow NPV of FCFF @ 6.00% NPV of FCFF @ 7.00% NPV of FCFF @ 8.00% 802,189 760,040 720,896
FV of Terminal Value (EBITDA) Terminal Value @ 4.0x Terminal Value @ 6.0x Terminal Value @ 8.0x 2,819,044 4,228,567 5,638,089
PV of Terminal Value (E Terminal Value @ 6.00% Terminal Value @ 7.00% Terminal Value @ 8.00%
FV of Terminal Value (Perpetuity) 2.5% 3.0% Terminal Value @ 6.00% 3,291,577 3,858,906 Terminal Value @ 7.00% 2,560,116 2,894,180 Terminal Value @ 8.00% 2,094,640 2,315,344
PV of Terminal Value (Pe 3.5% 4,653,167 3,323,690 2,585,093 Terminal Value @ 6.00% Terminal Value @ 7.00% Terminal Value @ 8.00%
6.0% 7.0% 8.0%
Total Enterprise Value EBITDA Multiple Method 4.0x 6.0x 8.0x $2,376,329 $3,163,399 $3,950,468 6.0% $2,193,100 $2,909,629 $3,626,159 7.0% $2,026,659 $2,679,541 $3,332,422 8.0%
Perpetuity Growth Method 2.5% 3.0% $2,640,189 $2,956,982 $2,061,474 $2,231,295 $1,691,120 $1,793,348
6.0% 7.0% 8.0%
Total Equity Value EBITDA Multiple Method 4.0x 6.0x 8.0x $1,792,941 $2,580,011 $3,367,081 $1,609,712 $2,326,242 $3,042,771 $1,443,272 $2,096,153 $2,749,035
6.0% 7.0% 8.0%
Perpetuity Growth Method 2.5% 3.0% $2,056,801 $2,373,595 $1,478,086 $1,647,907 $1,107,732 $1,209,961
6.0% 7.0% 8.0%
Total Equity Value Per Share EBITDA Multiple Method 4.0x 6.0x 8.0x $17.93 $25.80 $33.67 6.0% $16.10 $23.26 $30.43 7.0% $14.43 $20.96 $27.49 8.0%
Perpetuity Growth Method 2.5% 3.0% $20.57 $23.74 $14.78 $16.48 $11.08 $12.10
PV of Terminal Value (EBITDA) 4.0x 6.0x 1,574,140 2,361,210 1,433,059 2,149,589 1,305,763 1,958,645
8.0x 3,148,279 2,866,119 2,611,526
PV of Terminal Value (Perpetuity) 2.5% 3.0% 1,838,000 2,154,793 1,301,433 1,471,254 970,224 1,072,452
3.5% 2,598,304 1,689,596 1,197,398
Percentage Split of DCF Analysis EBITDA Multiple Method 4.0x 6.0x 8.0x 33.8% 25.4% 20.3% 34.7% 26.1% 21.0% 35.6% 26.9% 21.6% 66.2% 65.3% 64.4% 74.6% 73.9% 73.1% 79.7% 79.0% 78.4%
uity Growth Method 3.5% $3,400,493 $2,449,636 $1,918,294
uity Growth Method 3.5% $2,817,105 $1,866,249 $1,334,907
Cash Flow % of TEV: @ 6.00% @ 7.00% @ 8.00% Terminal Value % of TEV: @ 6.00% @ 7.00% @ 8.00%
uity Growth Method 3.5% $28.17 $18.66 $13.35
Cash Flow % of TEV: @ 6.00% @ 7.00% @ 8.00% Terminal Value % of TEV: @ 6.00% @ 7.00% @ 8.00%
Perpetuity Growth Method 2.5% 3.0% 3.5% 30.4% 27.1% 23.6% 36.9% 34.1% 31.0% 42.6% 40.2% 37.6% 69.6% 63.1% 57.4% 72.9% 65.9% 59.8% 76.4% 69.0% 62.4%
IRR / Returns Calculation
Exit Year Equity Allocation on Exit Common - Sponsor Equity Management Equity New Equity Subordinated Notes Mezzanine Debt Preferred Stock Management Performance Equity Total 2018 9 Investment $220,000 40,000 25,000 40,000 70,000 60,000 N/A 455,000 Undiluted 77.19% 14.04% 8.77% N/A N/A N/A N/A 100.00% % Allocation 0.00% 0.00% 2.00% 3.00% 5.00% 4.00% 4.00% 18.00% Diluted 63.30% 11.51% 9.19% 3.00% 5.00% 4.00% 4.00% 100.00% Allocation 2,565,228 466,405 372,555 121,578 202,630 162,104 162,104 4,052,606
Cash Flows & IRR
Post-Acq 0
2010 1
2011 2
2012 3
2013 4
Subordinated Notes Initial Investment Cash Interest Repayment Principal Participation on Exit IRR
(40,000) 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
21.41%
(40,000)
Mezzanine Debt Initial Investment Cash Interest Repayment Principal Participation on Exit IRR
(70,000) 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
20.56%
(70,000)
Preferred Stock Initial Investment Cash Dividend Repayment Principal Participation on Exit IRR
(60,000) 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
18.54%
(60,000)
Common - Sponsor Equity Initial Investment Cash Dividend Participation on Exit IRR
(220,000) 1,930 0 1,930 2,084 0 2,084 2,200 0 2,200 2,393 0 2,393
31.82%
(220,000)
Management Equity
Initial Investment Cash Dividend Participation on Exit IRR
(40,000) 351 0 351 379 0 379 400 0 400 435 0 435
36.21%
(40,000)
New Equity Initial Investment Cash Dividend Participation on Exit IRR
(25,000) 219 0 219 237 0 237 250 0 250 272 0 272
35.42%
(25,000)
Allocation of Exit Proceeds EBITDA Multiple (Exit Year) EBITDA (Exit Year) Exit Year Enterprise Value Less: Debt Less: Preferred Stock Add: Excess Cash Equity Value - Allocable Proceeds 6.0x 645,545 3,873,273 0 84,153 263,486 4,052,606
2014 5
2015 6
2016 7
2017 8
2018 9
2019 10
0 47,701 0 0 47,701
97 2,147 0 0 2,243
0 0 0 0 0
0 0 0 0 0
0 0 0 121,578 121,578
0 0 0 0 0
0 50,779 0 0 50,779
1,640 36,454 0 0 38,094
0 0 0 0 0
0 0 0 0 0
0 0 0 202,630 202,630
0 0 0 0 0
0 0 0 0 0
5,891 0 0 0 5,891
5,891 0 0 0 5,891
5,891 0 0 0 5,891
5,891 0 84,153 162,104 252,148
0 0 0 0 0
2,663 0 2,663
2,702 0 2,702
2,856 0 2,856
3,088 0 3,088
3,165 2,565,228 2,568,393
0 0 0
484 0 484
491 0 491
519 0 519
561 0 561
575 628,509 629,085
0 0 0
303 0 303
307 0 307
325 0 325
351 0 351
360 372,555 372,915
0 0 0
Ratio Analysis
Year Year No. 2010 1 2011 2 2012 3 2013 4 2014 5
Capital Ratios Debt Ratio Total Debt / Book Capitalization Total Debt / Equity
0.7x 0.6x 1.6x
0.6x 0.5x 1.1x
0.5x 0.4x 0.7x
0.5x 0.3x 0.5x
0.4x 0.2x 0.3x
Leverage Ratios Total Debt / EBITDA Total Debt / (EBITDA - Capex) Net Debt / EBITDA Senior Debt / EBITDA
2.1x 3.2x 1.9x 1.4x
1.7x 2.6x 1.6x 1.1x
1.4x 2.1x 1.2x 0.8x
1.1x 1.6x 0.9x 0.5x
0.8x 1.2x 0.7x 0.5x
Interest Coverage EBITDA / Total Interest EBITDA / Senior Interest EBITDA / Cash Interest (EBITDA - Capex) / Total Interest (EBITDA - Capex) / Senior Interest (EBITDA - Capex) / Cash Interest EBIT / Total Interest
8.8x 12.3x 12.3x 5.8x 8.1x 8.1x 6.8x
10.0x 14.4x 14.4x 6.5x 9.4x 9.4x 7.5x
12.0x 18.6x 18.6x 7.8x 12.1x 12.1x 9.1x
14.9x 25.5x 25.5x 9.6x 16.6x 16.6x 11.2x
17.3x 32.1x 32.1x 11.2x 20.7x 20.7x 12.8x
Asset Utilization Total Asset Turnover (Sales) Fixed Asset Turnover (Sales)
0.9x 2.2x
0.9x 2.3x
0.9x 2.3x
0.9x 2.4x
0.9x 2.5x
Activity Ratios Inventory Turnover (COGS) Accounts Receivable Turnover (Sales) Accounts Payable Turnover (COGS & Inv) Inventory Holding Period (Days) Days Receivable (Sales) Days Payable (COGS & Inv)
5.1x 7.8x 7.9x 71.9 47.0 46.4
5.1x 5.7x 7.0x 71.1 64.4 52.2
4.9x 5.4x 6.8x 73.9 67.6 53.8
4.7x 5.1x 6.7x 76.9 71.0 54.8
4.6x 4.9x 6.5x 79.9 74.5 55.9
Liquidity Ratios Current Ratio Quick (Acid Test) Ratio
2.3x 1.1x
2.3x 1.1x
2.3x 1.1x
2.4x 1.1x
2.4x 1.1x
Returns Return on Equity (ROE) Return on Assets (ROA)
38.72% 13.25%
30.19% 12.24%
25.61% 12.27%
22.52% 12.28%
20.02% 12.19%
Operating Results Sales Growth
N/A
10.00%
10.00%
10.00%
10.00%
Gross Margin EBITDA Margin EBIT Margin Pre-Tax Profit Ratio
55.00% 29.00% 22.37% 19.47%
54.89% 28.80% 21.77% 19.28%
54.77% 28.60% 21.54% 19.50%
54.66% 28.40% 21.27% 19.72%
54.55% 28.19% 20.88% 19.61%
2015 6
2016 7
2017 8
2018 9
2019 10
0.3x 0.2x 0.2x
0.3x 0.1x 0.1x
0.3x 0.0x 0.0x
0.3x 0.0x 0.0x
0.2x 0.0x 0.0x
0.5x 0.8x 0.4x 0.3x
0.3x 0.4x 0.2x 0.1x
0.1x 0.2x -0.1x 0.0x
0.1x 0.2x -0.3x 0.0x
0.1x 0.2x -0.4x 0.0x
22.6x 35.4x 22.6x 14.5x 22.8x 14.5x 16.7x
30.2x 44.9x 30.2x 19.3x 28.8x 19.3x 24.5x
42.1x 72.5x 42.1x 26.8x 46.2x 26.8x 33.8x
54.3x 107.6x 54.3x 34.5x 68.3x 34.5x 43.5x
59.3x 117.5x 59.3x 37.5x 74.2x 37.5x 46.7x
0.9x 2.5x
0.9x 2.5x
0.9x 2.5x
0.8x 2.4x
0.8x 2.4x
4.4x 4.7x 6.4x 83.1 78.2 57.1
4.2x 4.4x 6.3x 86.5 82.1 58.2
4.1x 4.2x 6.1x 89.9 86.2 59.4
3.9x 4.0x 6.0x 93.5 90.6 60.6
3.8x 3.8x 5.9x 97.2 95.1 61.8
2.4x 1.1x
2.5x 1.2x
2.7x 1.4x
3.0x 1.6x
3.1x 1.7x
18.41% 12.17%
18.85% 13.32%
17.26% 12.68%
16.05% 11.92%
14.82% 11.14%
10.00%
10.00%
10.00%
10.00%
10.00%
54.43% 27.99% 20.72% 19.78%
54.32% 27.79% 22.58% 22.03%
54.21% 27.58% 22.18% 21.87%
54.09% 27.38% 21.95% 21.76%
53.98% 27.17% 21.40% 21.24%
doc_921373667.xls