Leveraged Buyout (LBO) Model
Input Values
*Enter values in yellow cells.
Estimated Cost of Deal: Debt Assumptions:
Curent Stock Price $6.00 $ amt Assumed Debt
Offer 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 Years
Annual 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
Annual Repayment
Coupon Rate
Average Interest
Years PIK
Optional Prepayment
Preferred Stock
Preferred Stock Dividend
Years PIK
Pref Stock Retired Year
http://www.globaliconnect.com/excel_models.php
To help us improve this model, please send your feedback to info@globaliconnect.com
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 COGS
12.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 increase
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 revenue
Other 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
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.
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,053
1,200
12,088
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,438
SG&A expenses 154,000 170,247 188,208 208,064 230,015
Other 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 32,061 30,500 26,817 22,763 20,734
Existing Debt 2,609 1,228 0 0 0
Bank Revolver 13,533 14,005 14,005 14,005 14,005
Term Loan "A" 4,125 3,491 1,566 0 0
Term Loan "B" 2,830 2,714 2,083 755 0
Senior Notes 2,763 2,763 2,763 1,498 117
Subordinated Notes 1,800 1,881 1,966 2,054 2,147
Mezzanine Debt 3,150 3,292 3,440 3,595 3,756
Seller Notes 1,252 1,127 995 856 709
Other Expenses (Income) (197) (297) 353 (35) (257)
Amortization of Capitalized
Financing Costs 1,053 1,053 1,803 1,515 1,393
(Gain) Loss on Asset Sales (1,250) (1,350) (1,450) (1,550) (1,650)
Net Income before Taxes 214,212 233,247 259,576 288,670 315,814
Income Tax Expense (Benefit) 74,974 81,636 90,852 101,035 110,537
Net Income 139,238 151,610 168,725 187,636 205,284
Dividend 6,700 7,194 7,659 8,245 8,955
Preferred Stock Dividend 4,200 4,494 4,809 5,145 5,505
Common Stock Dividend 2,500 2,700 2,850 3,100 3,450
Net Income to Common Shareholders 132,538 144,416 161,066 179,390 196,329
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
16,044 9,827 9,125 6,000 6,000
0 0 0 0 0
14,005 9,827 9,125 6,000 6,000
0 0 0 0 0
0 0 0 0 0
0 0 0 0 0
97 0 0 0 0
1,625 0 (0) 0 0
317 0 0 0 0
(1,079) (1,179) (121) (1,600) (1,617)
671 671 1,829 450 533
(1,750) (1,850) (1,950) (2,050) (2,150)
352,062 431,313 466,431 513,099 550,716
123,222 150,959 163,251 179,585 192,751
228,840 280,353 303,180 333,514 357,966
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
219,450 270,762 293,290 323,523 347,675
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 50,000 50,000 50,000
Accounts receivable 85,000 85,000 198,000 228,690 264,137
Inventories 95,999 95,999 99,000 113,539 130,213
Prepaids 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 528,500 596,598 674,471
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,613
Capitalized Financing Costs 0 10,888 10,888 9,835 8,782 6,979
Other Long-Term Assets 15,000 15,000 44,000 48,642 53,774
PP&E:
Gross PP&E 500,000 2,450 502,450 502,450 612,450 733,450
Capex 0 0 110,000 121,000 133,100
Depreciation (Current) 0 0 72,745 84,845 93,718
Accumulated Depreciation 25,000 25,000 97,745 182,590 276,308
Net PP&E 475,000 477,450 514,705 550,860 590,242
Total Non-Current Assets 510,950 716,966 781,989 821,554 864,086
Total Assets 813,949 228,516 1,042,465 1,310,489 1,418,152 1,538,557
LIABILITIES & EQUITY
Current Liabilities:
Accounts payable 51,800 51,800 74,700 85,741 96,542
Accrued expenses 24,000 24,000 66,000 73,689 82,274
Other 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 5,873 6,119 6,366
Long-Term Debt:
Existing Debt 250,000 (185,000) 65,000 57,778 0 0
Bank Revolver 0 200,888 200,888 213,470 213,470 213,470
Term Loan "A" 0 80,000 80,000 70,000 56,951 0
Term Loan "B" 0 55,000 55,000 52,800 50,600 28,768
Senior Notes 0 65,000 65,000 65,000 65,000 65,000
Subordinated Notes 0 40,000 40,000 41,800 43,681 45,647
Mezzanine Debt 0 70,000 70,000 73,150 76,442 79,882
Seller Notes 0 25,000 25,000 22,680 20,235 17,659
Total Long Term Debt 250,000 600,888 596,678 526,380 450,425
Total Long-Term Liabilities 251,400 602,165 602,551 532,499 456,791
Total Liabilities 349,200 699,965 831,251 789,697 744,227
Shareholder's Equity:
Preferred Stock 0 60,000 60,000 64,200 68,694 73,503
Common Equity 440,000 (155,000) 285,000 285,000 285,000 285,000
Retained Earnings 24,749 (27,249) (2,500) 130,038 274,454 435,520
Total Shareholder's Equity 464,749 342,500 479,238 628,148 794,023
Total Liabilities & Equity 813,949 228,516 1,042,465 1,310,489 1,417,846 1,538,250
Balance Sheet Check 0 0 0 0 307 307
2013 2014 2015 2016 2017 2018 2019
4 5 6 7 8 9 10
49,990 49,990 50,000 50,000 135,480 264,637 354,070
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
763,523 865,395 981,943 1,115,277 1,353,324 1,657,079 1,946,346
211,479 211,479 211,479 211,479 211,479 211,479 211,479
1,433 1,254 1,075 896 717 538 358
5,464 4,071 3,400 2,729 900 450 (83)
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
910,298 958,451 1,013,021 1,113,355 1,218,536 1,335,202 1,454,737
1,673,821 1,823,846 1,994,965 2,228,632 2,571,860 2,992,281 3,401,082
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
8,646 20,303 31,978 52,837 106,242 144,024 139,035
0 0 0 0 0 0 0
213,470 213,470 161,545 56,324 0 0 0
0 0 0 0 0 0 0
0 0 0 0 0 0 0
5,486 0 0 0 0 0 0
47,701 2,147 0 0 0 0 0
83,476 36,121 0 0 0 0 0
14,943 12,081 0 0 0 0 0
365,076 263,819 161,545 56,324 0 0 0
373,722 284,122 193,522 109,161 106,242 144,024 139,035
694,966 643,167 594,836 557,741 607,679 704,576 765,703
78,648 84,153 84,153 84,153 84,153 84,153 84,153
285,000 285,000 285,000 285,000 285,000 285,000 285,000
614,911 811,239 1,030,689 1,301,451 1,594,741 1,918,264 2,265,939
978,558 1,180,392 1,399,842 1,670,604 1,963,894 2,287,417 2,635,092
1,673,524 1,823,559 1,994,677 2,228,345 2,571,573 2,991,993 3,400,795
297 287 287 287 287 287 287
Cash Flows
Year 2010 2011 2012 2013
Year No. 1 2 3 4
Cash flows from operating activities
Net Income to Common Shareholders 132,538 144,416 161,066 179,390
Non-cash adjustments:
Depreciation and amortization 72,924 85,024 93,898 104,355
Amortization of Capitalized Financing Costs 1,053 1,053 1,803 1,515
Increase (Decrease) in Deferred Tax Liability 4,595 247 247 2,280
Accrued Interest Subordinated Notes 1,800 1,881 1,966 2,054
Accrued Interest Mezzanine Debt 3,150 3,292 3,440 3,595
Accrued Interest Seller Notes 1,252 1,127 995 856
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 liabilities 108,000 17,457 19,437 21,642
Net cash provided by (used in) operating activities 121,911 201,933 220,587 243,937
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 (7,222) (57,778) 0 0
Increase (decrease) in Term Loan "A" (10,000) (13,049) (56,951) 0
Increase (decrease) in Term Loan "B" (2,200) (2,200) (21,832) (28,768)
Increase (decrease) in Senior Notes 0 0 0 (59,514)
Increase (decrease) in Subordinated Notes 0 0 0 0
Increase (decrease) in Mezzanine Debt 0 0 0 0
Increase (decrease) in Seller Notes (3,571) (3,571) (3,571) (3,571)
Increase (decrease) in Preferred Stock 0 0 0 0
Net cash provided by (used in) financing activities (22,994) (76,598) (82,355) (91,853)
Net increase (decrease) in cash (40,083) (307) 0 0
Cash at beginning of year 77,500 50,000 50,000 50,000
Cash available for Bank Revolver 37,417 49,693 50,000 50,000
Added (retired) Bank Revolver 12,583 307 0 0
Cash at end of year 50,000 50,000 50,000 50,000
Free Cash Flows
EBIT 246,076 263,450 286,746 311,398
Add: 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 (4,595) (247) (247) (2,280)
Unlevered Free Cash Flows (UFCF) 27,868 95,913 99,794 107,380
Debt Repayment
Cash Available for Debt Repayment
Cash at beginning of year 77,500 50,000 50,000 50,000
Net cash provided by (used in) operating activities 121,911 201,933 220,587 243,937
Net cash provided by (used in) investing activities (139,000) (125,642) (138,232) (152,083)
Sub-total 60,411 126,291 132,355 141,853
Minimum Cash Balance 50,000 50,000 50,000 50,000
Cash Available for Debt Repayment 10,411 76,291 82,355 91,853
Mandatory Repayment
Existing Debt 7,222 7,222 0 0
Term Loan "A" 10,000 10,000 10,000 0
Term Loan "B" 2,200 2,200 2,200 2,200
Senior Notes 0 0 0 0
Subordinated Notes 0 0 0 0
Mezzanine Debt 0 0 0 0
Seller Notes 3,571 3,571 3,571 3,571
Preferred Stock 0 0 0 0
Sub-total 22,994 22,994 15,771 5,771
Cash Balance (12,583) 53,298 66,584 86,082
Incremental Revolver 12,583 0 0 0
Cash Available for Optional Repayment 0 53,298 66,584 86,082
Optional Repayment
Existing Debt 0 50,556 0 0
Term Loan "A" 0 3,049 46,951 0
Term Loan "B" 0 0 19,632 26,568
Senior Notes 0 0 0 59,514
Subordinated Notes 0 0 0 0
Mezzanine Debt 0 0 0 0
Seller Notes 0 0 0 0
Bank Revolver 0 0 0 0
Sub-total 0 53,604 66,584 86,092
Cash at end of year 50,000 50,000 50,000 49,990
Cash Flow Check 0 0 0 10
2014 2015 2016 2017 2018 2019
5 6 7 8 9 10
196,329 219,450 270,762 293,290 323,523 347,675
117,776 128,849 101,532 115,822 128,047 149,661
1,393 671 671 1,829 450 533
11,657 11,675 20,859 53,405 37,782 (4,989)
2,147 0 0 0 0 0
3,756 0 0 0 0 0
709 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
275,202 286,373 307,757 364,637 374,319 359,163
(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)
0 0 0 0 0 0
0 0 0 0 0 0
0 0 0 0 0 0
(5,486) 0 0 0 0 0
(47,701) (2,147) 0 0 0 0
(51,112) (36,121) 0 0 0 0
(3,571) (12,081) 0 0 0 0
0 0 0 0 0 0
(107,870) (50,348) 0 0 0 0
10 51,936 105,220 141,804 129,157 89,433
50,000 50,000 50,000 50,000 135,480 264,637
50,010 101,936 155,220 191,804 264,637 354,070
(10) (51,936) (105,220) (56,324) 0 0
50,000 50,000 50,000 135,480 264,637 354,070
336,292 367,027 439,960 475,435 517,499 555,100
0 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
(11,657) (11,675) (20,859) (53,405) (37,782) 4,989
122,901 127,665 127,426 164,192 150,925 112,395
49,990 49,990 50,000 50,000 135,480 264,637
275,202 286,373 307,757 364,637 374,319 359,163
(167,323) (184,089) (202,536) (222,832) (245,162) (269,730)
157,870 152,274 155,220 191,804 264,637 354,070
50,000 50,000 50,000 50,000 50,000 50,000
107,870 102,274 105,220 141,804 214,637 304,070
0 0 0 0 0 0
0 0 0 0 0 0
0 0 0 0 0 0
0 0 0 0 0 0
0 0 0 0 0 0
0 0 0 0 0 0
3,571 3,571 0 0 0 0
0 0 0 0 0 0
3,571 3,571 0 0 0 0
104,298 98,703 105,220 141,804 214,637 304,070
0 0 0 0 0 0
104,298 98,703 105,220 141,804 214,637 304,070
0 0 0 0 0 0
0 0 0 0 0 0
0 0 0 0 0 0
5,486 0 0 0 0 0
47,701 2,147 0 0 0 0
51,112 36,121 0 0 0 0
0 8,509 0 0 0 0
0 51,926 105,220 56,324 0 0
104,308 98,703 105,220 56,324 0 0
49,990 50,000 50,000 135,480 264,637 354,070
10 0 0 0 0 0
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 1,053 1,053 1,803
Bank Revolver 450 450 450
Term Loan "A" 150 150 900
Term Loan "B" 88 88 88
Senior Notes 144 144 144
Subordinated Notes 83 83 83
Mezzanine Debt 138 138 138
Seller Notes 0 0 0
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,420
Class 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
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
1,515 1,393 671 671 1,829 450 533
450 450 450 450 450 450 450
0 0 0 0 0 0 0
700 0 0 0 0 0 0
144 722 0 0 0 0 0
83 83 83 83 417 0 83
138 138 138 138 963 0 0
0 0 0 0 0 0 0
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,420
11,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
Income Tax Calculation
Year 2010 2011 2012 2013 2014
Year No. 1 2 3 4 5
Net Income before Tax 214,212 233,247 259,576 288,670 315,814
Add: Book Depreciation 72,745 84,845 93,718 104,176 117,597
Add: 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 0
Add: 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,750
Less: 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 216,582 233,014 258,872 282,156 282,508
Less: NOLs Used 15,500 0 0 0 0
Post-NOL Taxable Income 201,082 233,014 258,872 282,156 282,508
Cash Taxes Payable 70,379 81,555 90,605 98,760 98,880
Total Book Tax 74,974 81,636 90,852 101,035 110,537
Increase (Decrease) in Deferred
Tax Liability 4,595 247 247 2,280 11,657
Beginning NOL Balance 15,500 0 0 0 0
Current Year NOL 0 0 0 0 0
NOLs Used 15,500 0 0 0 0
Ending NOL Balance 0 0 0 0 0
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,350
Tax 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
Other Long-Term Assets 1,250 1,450 1,650 1,850 2,050
2015 2016 2017 2018 2019
6 7 8 9 10
352,062 431,313 466,431 513,099 550,716
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
318,706 371,715 313,845 405,150 564,972
0 0 0 0 0
318,706 371,715 313,845 405,150 564,972
111,547 130,100 109,846 141,802 197,740
123,222 150,959 163,251 179,585 192,751
11,675 20,859 53,405 37,782 (4,989)
0 0 0 0 0
0 0 0 0 0
0 0 0 0 0
0 0 0 0 0
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
2,250 2,450 2,650 2,850 3,050
Weighted Average Cost of Capital
Risk Free Rate (Treasury Bill Rate) 5.00% WACC
Market Risk Premium 4.50% 0 = Manual 1 = Computed
Levered Beta 1.20 1 Computed
Estimated Cost of Debt Amount Int. Rate Wtd. Rate Computed WACC
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.13
Preferred Stock/Equity Ratio 0.21
Tax Rate 35.00%
Unlevered Beta 0.94
Relevered Beta 2.45
WACC Manual
nual 1 = Computed WACC
Computed 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% 802,189
NPV of FCFF @ 7.00% 760,040
NPV of FCFF @ 8.00% 720,896
FV of Terminal Value (EBITDA) PV of Terminal Value (E
Terminal Value @ 4.0x 2,819,044 Terminal Value @ 6.00%
Terminal Value @ 6.0x 4,228,567 Terminal Value @ 7.00%
Terminal Value @ 8.0x 5,638,089 Terminal Value @ 8.00%
FV of Terminal Value (Perpetuity) PV of Terminal Value (Pe
2.5% 3.0% 3.5%
Terminal Value @ 6.00% 3,291,577 3,858,906 4,653,167 Terminal Value @ 6.00%
Terminal Value @ 7.00% 2,560,116 2,894,180 3,323,690 Terminal Value @ 7.00%
Terminal Value @ 8.00% 2,094,640 2,315,344 2,585,093 Terminal Value @ 8.00%
Total Enterprise Value
EBITDA Multiple Method Perpetuity Growth Method
4.0x 6.0x 8.0x 2.5% 3.0%
6.0% $2,376,329 $3,163,399 $3,950,468 6.0% $2,640,189 $2,956,982
7.0% $2,193,100 $2,909,629 $3,626,159 7.0% $2,061,474 $2,231,295
8.0% $2,026,659 $2,679,541 $3,332,422 8.0% $1,691,120 $1,793,348
Total Equity Value
EBITDA Multiple Method Perpetuity Growth Method
4.0x 6.0x 8.0x 2.5% 3.0%
6.0% $1,792,941 $2,580,011 $3,367,081 6.0% $2,056,801 $2,373,595
7.0% $1,609,712 $2,326,242 $3,042,771 7.0% $1,478,086 $1,647,907
8.0% $1,443,272 $2,096,153 $2,749,035 8.0% $1,107,732 $1,209,961
Total Equity Value Per Share
EBITDA Multiple Method Perpetuity Growth Method
4.0x 6.0x 8.0x 2.5% 3.0%
6.0% $17.93 $25.80 $33.67 6.0% $20.57 $23.74
7.0% $16.10 $23.26 $30.43 7.0% $14.78 $16.48
8.0% $14.43 $20.96 $27.49 8.0% $11.08 $12.10
PV of Terminal 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
PV of Terminal Value (Perpetuity)
2.5% 3.0% 3.5%
1,838,000 2,154,793 2,598,304
1,301,433 1,471,254 1,689,596
970,224 1,072,452 1,197,398
Percentage Split of DCF Analysis
ity Growth Method
3.5% EBITDA Multiple Method
$3,400,493 Cash Flow % of TEV: 4.0x 6.0x 8.0x
$2,449,636 @ 6.00% 33.8% 25.4% 20.3%
$1,918,294 @ 7.00% 34.7% 26.1% 21.0%
@ 8.00% 35.6% 26.9% 21.6%
Terminal Value % of TEV:
@ 6.00% 66.2% 74.6% 79.7%
ity Growth Method @ 7.00% 65.3% 73.9% 79.0%
3.5% @ 8.00% 64.4% 73.1% 78.4%
$2,817,105
$1,866,249 Perpetuity Growth Method
$1,334,907 Cash Flow % of TEV: 2.5% 3.0% 3.5%
@ 6.00% 30.4% 27.1% 23.6%
@ 7.00% 36.9% 34.1% 31.0%
@ 8.00% 42.6% 40.2% 37.6%
ity Growth Method Terminal Value % of TEV:
3.5% @ 6.00% 69.6% 72.9% 76.4%
$28.17 @ 7.00% 63.1% 65.9% 69.0%
$18.66 @ 8.00% 57.4% 59.8% 62.4%
$13.35
IRR / Returns Calculation
Exit Year 2018 9
Equity Allocation on Exit Investment Undiluted % Allocation Diluted Allocation
Common - Sponsor Equity $220,000 77.19% 0.00% 63.30% 2,565,957
Management Equity 40,000 14.04% 0.00% 11.51% 466,538
New Equity 25,000 8.77% 2.00% 9.19% 372,661
Subordinated Notes 40,000 N/A 3.00% 3.00% 121,613
Mezzanine Debt 70,000 N/A 5.00% 5.00% 202,688
Preferred Stock 60,000 N/A 4.00% 4.00% 162,150
Management Performance Equity N/A N/A 4.00% 4.00% 162,150
Total 455,000 100.00% 18.00% 100.00% 4,053,757
Cash Flows & IRR Post-Acq 2010 2011 2012 2013
0 1 2 3 4
Subordinated Notes
Initial Investment (40,000)
Cash Interest 0 0 0 0
Repayment 0 0 0 0
Principal 0 0 0 0
Participation on Exit 0 0 0 0
IRR 21.41% (40,000) 0 0 0 0
Mezzanine Debt
Initial Investment (70,000)
Cash Interest 0 0 0 0
Repayment 0 0 0 0
Principal 0 0 0 0
Participation on Exit 0 0 0 0
IRR 20.57% (70,000) 0 0 0 0
Preferred Stock
Initial Investment (60,000)
Cash Dividend 0 0 0 0
Repayment 0 0 0 0
Principal 0 0 0 0
Participation on Exit 0 0 0 0
IRR 18.54% (60,000) 0 0 0 0
Common - Sponsor Equity
Initial Investment (220,000)
Cash Dividend 1,930 2,084 2,200 2,393
Participation on Exit 0 0 0 0
IRR 31.83% (220,000) 1,930 2,084 2,200 2,393
Management Equity
Initial Investment (40,000)
Cash Dividend 351 379 400 435
Participation on Exit 0 0 0 0
IRR 36.22% (40,000) 351 379 400 435
New Equity
Initial Investment (25,000)
Cash Dividend 219 237 250 272
Participation on Exit 0 0 0 0
IRR 35.43% (25,000) 219 237 250 272
Allocation of Exit Proceeds
EBITDA Multiple (Exit Year) 6.0x
EBITDA (Exit Year) 645,545
Exit Year Enterprise Value 3,873,273
Less: Debt 0
Less: Preferred Stock 84,153
Add: Excess Cash 264,637
Equity Value - Allocable Proceeds 4,053,757
2014 2015 2016 2017 2018 2019
5 6 7 8 9 10
0 97 0 0 0 0
47,701 2,147 0 0 0 0
0 0 0 0 0 0
0 0 0 0 121,613 0
47,701 2,243 0 0 121,613 0
0 1,625 0 (0) 0 0
51,112 36,121 0 0 0 0
0 0 0 0 0 0
0 0 0 0 202,688 0
51,112 37,747 0 (0) 202,688 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 162,150 0
0 5,891 5,891 5,891 252,194 0
2,663 2,702 2,856 3,088 3,165 0
0 0 0 0 2,565,957 0
2,663 2,702 2,856 3,088 2,569,122 0
484 491 519 561 575 0
0 0 0 0 628,688 0
484 491 519 561 629,263 0
303 307 325 351 360 0
0 0 0 0 372,661 0
303 307 325 351 373,021 0
Ratio Analysis
Year 2010 2011 2012 2013 2014
Year No. 1 2 3 4 5
Capital Ratios
Debt Ratio 0.7x 0.6x 0.5x 0.5x 0.4x
Total Debt / Book Capitalization 0.6x 0.5x 0.4x 0.3x 0.2x
Total Debt / Equity 1.6x 1.1x 0.7x 0.5x 0.3x
Leverage Ratios
Total Debt / EBITDA 2.1x 1.7x 1.4x 1.1x 0.8x
Total Debt / (EBITDA - Capex) 3.2x 2.6x 2.1x 1.6x 1.2x
Net Debt / EBITDA 1.9x 1.6x 1.2x 0.9x 0.7x
Senior Debt / EBITDA 1.4x 1.1x 0.8x 0.5x 0.5x
Interest Coverage
EBITDA / Total Interest 8.8x 10.0x 12.0x 14.9x 17.3x
EBITDA / Senior Interest 12.3x 14.4x 18.6x 25.6x 32.2x
EBITDA / Cash Interest 12.3x 14.4x 18.6x 25.6x 32.2x
(EBITDA - Capex) / Total Interest 5.8x 6.5x 7.8x 9.7x 11.2x
(EBITDA - Capex) / Senior Interest 8.1x 9.4x 12.1x 16.6x 20.7x
(EBITDA - Capex) / Cash Interest 8.1x 9.4x 12.1x 16.6x 20.7x
EBIT / Total Interest 6.8x 7.5x 9.1x 11.2x 12.8x
Asset Utilization
Total Asset Turnover (Sales) 0.9x 0.9x 0.9x 0.9x 0.9x
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 Ratios
Current Ratio 2.3x 2.3x 2.3x 2.4x 2.4x
Quick (Acid Test) Ratio 1.1x 1.1x 1.1x 1.1x 1.1x
Returns
Return on Equity (ROE) 38.72% 30.19% 25.61% 22.52% 20.02%
Return on Assets (ROA) 13.25% 12.24% 12.27% 12.28% 12.19%
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 19.47% 19.28% 19.50% 19.72% 19.61%
2015 2016 2017 2018 2019
6 7 8 9 10
0.3x 0.3x 0.3x 0.3x 0.2x
0.2x 0.1x 0.0x 0.0x 0.0x
0.2x 0.1x 0.0x 0.0x 0.0x
0.5x 0.3x 0.1x 0.1x 0.1x
0.8x 0.4x 0.2x 0.2x 0.2x
0.4x 0.2x -0.1x -0.3x -0.4x
0.3x 0.1x 0.0x 0.0x 0.0x
22.6x 34.5x 39.4x 54.3x 59.3x
35.4x 55.1x 64.8x 107.6x 117.5x
22.6x 34.5x 39.4x 54.3x 59.3x
14.5x 22.1x 25.1x 34.5x 37.5x
22.8x 35.3x 41.3x 68.3x 74.2x
14.5x 22.1x 25.1x 34.5x 37.5x
16.7x 28.0x 31.7x 43.5x 46.7x
0.9x 0.9x 0.9x 0.8x 0.8x
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
2.4x 2.5x 2.7x 3.0x 3.1x
1.1x 1.2x 1.4x 1.6x 1.7x
18.49% 18.91% 17.15% 16.05% 14.81%
12.22% 13.30% 12.63% 11.92% 11.14%
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%
19.87% 22.13% 21.76% 21.76% 21.23%