simple lbo model
TRANSCRIPT
LBO Model Disclaimer and Instructions:
Disclaimer:Please note this is a sample LBO model created for illustrative purposes only. This model simplifies many of the key inputsin a traditional LBO model. This model should not be used for investment analysis or decision making purposes. This modelshould only be used for educational purposes. Do not rely solely on this model to understand how LBO models function. Thereare other key variables in LBO models that are not included in this model. Muppies is not liable for any inaccuracies or mistakesin this model. Please contact [email protected] if you have any questions or concerns. This model is solely for Muppies membersnon members shall not use this model without explicit authorization from Muppies officers.
Instructions:This model has been designed by a Muppies member to help you with your interview process. Following are somesteps you may need to take when evaluating an interview case LBO:
1. Build Functioning Historical and Forecasted Income Statement, Balance Sheet and Cash Flow Statements (Not shown in model)
2. Determine Impact on Balance Sheet on date of acquisition (e.g Impact on Goodwill etc.) - (Not shown in model)
3. Value company using traditional valuation metrics (DCF, Comparable company comps) - (Not shown in model)
4. Calculate IRR on investment given a leveraged buy-out using various assumptions (shown in model)
5. Incorporate both quantitative and qualitative measures to determine investment decision
Please note this is a sample LBO model created for illustrative purposes only. This model simplifies many of the key inputsin a traditional LBO model. This model should not be used for investment analysis or decision making purposes. This modelshould only be used for educational purposes. Do not rely solely on this model to understand how LBO models function. Thereare other key variables in LBO models that are not included in this model. Muppies is not liable for any inaccuracies or mistakesin this model. Please contact [email protected] if you have any questions or concerns. This model is solely for Muppies members
This model has been designed by a Muppies member to help you with your interview process. Following are some
1. Build Functioning Historical and Forecasted Income Statement, Balance Sheet and Cash Flow Statements (Not shown in model)
2. Determine Impact on Balance Sheet on date of acquisition (e.g Impact on Goodwill etc.) - (Not shown in model)
3. Value company using traditional valuation metrics (DCF, Comparable company comps) - (Not shown in model)
Transaction OverviewCompany Overview Halliburton Company Exit Sources of funds Amount EBITDA Mult. Libor Spread Interest RateTicker hal Exit Year 2010 Excess Cash on Balance Sheet 500 Stock Price (02/03/08) $ 32.40 Exit Multiple 9.5x Existing DebtOffer Premium 20% Senior Bank Term "A" 9,123 2.0x 2.0% 7.2%Purchase Share Price $ 38.88 Other Senior Bank Term "B" 4,561 1.0x 3.0% 8.2%Fully Diluted Shares O/S 978.25 Libor Rate 5.2% Senior Subordinated Debt 4,561 1.0x 3.5% 8.7%Equity Value 38,034 Op. Cash (% rev 3.0% Junior Subordinated Debt 4,561 1.0x 4.0% 9.2%Add: Short-term Debt - Interest on cash 5.0% Preferred StockAdd: Long-term Debt 2,796 Common Equity 18,664 Add: Pension Obligations Returns Total Sources of funds 41,971 Less: Operating Cash (675) IRR 37%Less: Excess Cash (3,704) MOI 2.6x Uses of fundsTotal Enterprise Value 36,451 Equity Purchase Price 38,034
Debt Retired 2,796 Implied Multiples Pre-Acquistion Acquisition Cash InfusionEV/Rev 06 1.3x 1.6x Advisory fees 1.5% 571 EV/Rev 07 2.0x 2.4x Financing fees 1.5% 571 EV/EBITDA 06 7.8x 9.4x Other usesEV/EBITDA 07 6.6x 8.0x Other usesP/E 06 15.0x 18.0x Total Uses Of funds 41,971 P/E 07 13.1x 15.8x
Overall Check okLeverage Multiples Pre-Acquistion Acquisition MaxDebt/EBITDA 0.7x 4.1x 4.1xEBITDA/Interest 22.1x 2.5x 2.5x
IRR CalculationsExit Year
Exit Year 2010 3 Years 5 Years 8 Years Exit Multiple in Year: 2010Exit Multiple 9.5x 37% 2010 2012 2015 37% 8.4x 8.9x 9.4x 9.9x 10.4xExit EBITDA 6,097 11.4x 47% 32% 22% 21% Err:504 Err:504 Err:504 Err:504 Err:504
10.9x 45% 31% 21% 20% Err:504 Err:504 Err:504 Err:504 Err:504Exit Enterprise Value 57,922 10.4x 42% 30% 21% 19% Err:504 Err:504 Err:504 Err:504 Err:504Debt on books 13,102 9.9x 39% 28% 20% 18% Err:504 Err:504 Err:504 Err:504 Err:504Cash on Books 3,204 9.4x 36% 27% 19% 17% Err:504 Err:504 Err:504 Err:504 Err:504Exit Equity Value 48,024 8.4x 30% 24% 18% 16% Err:504 Err:504 Err:504 Err:504 Err:504Entry Equity Value 18,664 7.4x 24% 21% 16% 15% Err:504 Err:504 Err:504 Err:504 Err:504
6.4x 16% 17% 14% 14% Err:504 Err:504 Err:504 Err:504 Err:504IRR 37% 5.4x 7% 13% 12% 13% Err:504 Err:504 Err:504 Err:504 Err:504MOI 2.6x 4.4x -3% 8% 9%
EBITDA Margins
Exit Multiples
Forecast Assumptions
Choose 2Historical (Years 1-3) 1Analyst (Year 1-3) 2User Defined 3
1 2002 2003 2004 2005 2006 2007 2008 2009 2010 2011 2012 2013 20142 Income Statement3 Revenue Growth -3.4% 30.0% 22.4% 1.9% 11.0% -33.0% 14.8% 14.2% 11.0% 7.7% 4.5% 4.5% 4.5%4 Historical growth rate 11.0% -33.0% 14.8% 11.4% 7.9% 4.5% 4.5% 4.5%5 Analyst Estimates -33.0% 14.8% 14.2% 11.0% 7.7% 4.5% 4.5% 4.5%6 User Defined78
1213 EBITDA margins 2.3% 7.2% 6.6% 14.6% 17.2% 30.3% 27.3% 27.8% 27.8% 27.8% 27.8% 27.8% 27.8%14 Historical Margins 17.2% 30.3% 27.3% 27.8% 27.8% 27.8% 27.8% 27.8%15 Analyst Estimates 30.3% 27.3% 27.8% 27.8% 27.8% 27.8% 27.8% 27.8%16 User Defined17 Tax Rate -26.9% 43.4% 37.5% 2.8% 34.3% 34.3% 34.3% 34.3% 34.3% 34.3% 34.3% 34.3% 34.3%18 Historical Margins 34.3% 34.3% 34.3% 34.3% 34.3% 34.3% 34.3% 34.3%19 Analyst Estimates 34.3% 34.3% 34.3% 34.3% 34.3% 34.3% 34.3% 34.3%20 User Defined 34.3% 34.3% 34.3% 34.3% 34.3% 34.3% 34.3% 34.3%21 D&A (% of Revenue) 4.0% 3.2% 2.6% 2.5% 2.3% 2.3% 2.3% 2.3% 2.3% 2.3% 2.3% 2.3% 2.3%2223 Balance Sheet24 WC (% of revenue) 23.4% 23.2% 21.8% 18.5% 17.4% 17.9% 17.8% 17.8% 17.8% 17.8% 17.8% 17.8% 17.8%25 Operating Cash (% rev) 3.0% 3.0% 3.0% 3.0% 3.0% 3.0% 3.0% 3.0% 3.0% 3.0% 3.0% 3.0% 3.0%26 Days Inventory 22 17 15 20 25 25 25 25 25 25 25 25 25 27 Days Receivables O/S 95 107 106 86 76 76 76 76 76 76 76 76 7628 Prepaid-expense (% rev) 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%29 Days Payable O/S 32 42 46 41 38 38 38 38 38 38 38 38 3830 Accrued expenses (% rev 3.0% 2.5% 2.4% 3.2% 3.4% 3.4% 3.4% 3.4% 3.4% 3.4% 3.4% 3.4% 3.4%3132 Choose capex method 133 PP&E (% of revenue) 21.0% 15.5% 12.8% 13.1% 13.5% 13.5% 13.5% 13.5% 13.5% 13.5% 13.5% 13.5% 13.5%34 Capex (% of revenue) 6.1% 3.2% 2.9% 3.2% 4.0% 4.0% 4.0% 4.0% 4.0% 4.0% 4.0% 4.0% 4.0%3536 Cash Flows37 2002 2003 2004 2005 2006 2007 2008 2009 2010 2011 2012 2013 201438 Sales 12,498 16,246 19,884 20,266 22,504 15,074 17,312 19,770 21,938 23,635 24,698 25,810 26,971 39 growth 30.0% 22.4% 1.9% 11.0% -33.0% 14.8% 14.2% 11.0% 7.7% 4.5% 4.5% 4.5%43 EBITDA 289 1,166 1,320 2,953 3,875 4,561 4,725 5,495 6,097 6,569 6,864 7,173 7,496 44 margins 7.2% 6.6% 14.6% 17.2% 30.3% 27.3% 27.8% 27.8% 27.8% 27.8% 27.8% 27.8%45 Less D&A (505) (518) (509) (504) (527) (353) (405) (463) (514) (553) (578) (604) (632)4647 Senior Bank Term "A" (660) (369) (268) (133) - - - - 48 Senior Bank Term "B" (375) (375) (375) (375) (327) (81) - - 49 Senior Subordinated Debt (398) (398) (398) (398) (398) (398) (175) - 50 Junior Subordinated Debt (421) (421) (421) (421) (421) (421) (421) (244)51 Less: Interest Expense (113) (139) (229) (207) (175) (1,854) (1,563) (1,463) (1,328) (1,147) (900) (596) (244)52 EBITDA / Interest 2.6x 8.4x 5.8x 14.3x 22.1x 2.5x 3.0x 3.8x 4.6x 5.7x 7.6x 12.0x 30.7x5354 Add: Interest Income 32 30 44 64 162 160 160 160 160 160 160 160 160
55 4.4% 2.3% 2.0% 3.6% 4.4% 5.0% 5.0% 5.0% 5.0% 5.0% 5.0% 5.0% 5.0%5657 EBT (297) 539 626 2,306 3,335 2,514 2,916 3,729 4,415 5,029 5,546 6,133 6,780 58 Less: Taxes (80) (234) (235) (64) (1,144) (863) (1,000) (1,279) (1,515) (1,725) (1,902) (2,104) (2,326)59 ADD: D&A 505 518 509 504 527 353 405 463 514 553 578 604 632 60 Less Changes in Working Capital (852) (555) 580 (168) 1,222 (381) (440) (385) (302) (189) (198) (206)61 Less: Gross Capex (114) (161) (259) (716) 795 (546) (611) (602) (562) (491) (513) (536)62 Free Cash Flow Before Debt Paydown (143) 184 3,067 1,834 4,022 1,393 1,862 2,428 2,994 3,542 3,923 4,344 6364 Debt Paydown Schedule65 Senior Bank Term "A" 5,101 3,708 1,845 - - - - - 66 BOY Debt 9,123 5,101 3,708 1,845 - - - - 67 Debt Paydown (4,022) (1,393) (1,862) (1,845) - - - - 68 EOY Debt 5,101 3,708 1,845 - - - - - 6970 Senior Bank Term "B" 4,561 4,561 4,561 3,979 985 - - - 71 BOY Debt 4,561 4,561 4,561 4,561 3,979 985 - - 72 Debt Paydown - - - (582) (2,994) (985) - - 73 EOY Debt 4,561 4,561 4,561 3,979 985 - - - 7475 Senior Subordinated Debt 4,561 4,561 4,561 4,561 4,561 2,005 - - 76 BOY Debt 4,561 4,561 4,561 4,561 4,561 4,561 2,005 - 77 Debt Paydown - - - - - (2,556) (2,005) - 78 EOY Debt 4,561 4,561 4,561 4,561 4,561 2,005 - - 7980 Junior Subordianted Debt 4,561 4,561 4,561 4,561 4,561 4,561 2,644 - 81 BOY Debt 4,561 4,561 4,561 4,561 4,561 4,561 4,561 2,644 82 Debt Paydown - - - - - - (1,918) (2,644)83 EOY Debt 4,561 4,561 4,561 4,561 4,561 4,561 2,644 - 8485 Total EOY Debt 18,785 17,392 15,530 13,102 10,108 6,566 2,644 - 86 Debt/EBITDA 4.1x 3.7x 2.8x 2.1x 1.5x 1.0x 0.4x 0.0x
Reinvstment Summary1 Working Capital 2002 2003 2004 2005 2006 2007 2008 2009 2010 2011 2012 2013 20142 Excess Cash (excluded) 732 1,328 2,211 1,783 3,704 3,204 3,204 3,204 3,204 3,204 3,204 3,204 3,204 3 Operating Cash 375 487 597 608 675 452 519 593 658 709 741 774 809 4 Inventory 734 695 791 953 1,261 712 853 967 1,073 1,156 1,208 1,262 1,319 5 Accounts Receivable 3,257 4,765 5,751 4,801 4,674 3,131 3,596 4,106 4,557 4,909 5,130 5,361 5,602 6 Pre-paid Expenses - - - - - - - - - - - - - 7 Accounts Payable (1,077) (1,776) (2,339) (1,967) (1,931) (1,091) (1,306) (1,481) (1,643) (1,770) (1,850) (1,933) (2,020)8 Accrued Expenses (370) (400) (473) (648) (764) (512) (588) (671) (745) (802) (839) (876) (916)9 Working Capital 2,919 3,771 4,327 3,747 3,915 2,693 3,074 3,514 3,900 4,201 4,390 4,588 4,794
10 -13% 4% -31% 14%11 Capex 21% 16% 13% 13% 14%12 Gross PP&E 5,952 6,066 6,227 6,486 7,202 6,407 6,954 7,564 8,166 8,727 9,218 9,731 10,267 13 Accumulated Depreciation (3,323) (3,540) (3,674) (3,838) (4,154) (4,366) (4,609) (4,886) (5,194) (5,526) (5,873) (6,236) (6,614)14 Net PP&E 2,629 2,526 2,553 2,648 3,048 2,042 2,345 2,678 2,971 3,201 3,345 3,496 3,653
Depreciation 0 (217) (134) (164) (316) (212) (243) (278) (308) (332) (347) (362) (379)Depreciation (% of NPPE) 0% -9% -5% -6% -10% -10% -10% -10% -10% -10% -10% -10% -10%
Capex based on PP&E (114) (161) (259) (716) 795 (546) (611) (602) (562) (491) (513) (536)Capex based on Capex/rev (764) (515) (575) (651) (891) (597) (685) (783) (869) (936) (978) (1,022) (1,068)Selected Capex - (114) (161) (259) (716) 795 (546) (611) (602) (562) (491) (513) (536)
2/3/2008Checks OKSources & Uses OK Balance Sheet OKCash Usage OKRow for Hlookup OK
2015
4.5%4.5%4.5%
27.8%27.8%27.8%
34.3%34.3%34.3%34.3%
2.3%
17.8%3.0%
25 76
0.0%38
3.4%
13.5%4.0%
2015 28,185
4.5% 7,833
27.8% (660)
- - - - -
160
5.0%
7,333 (2,516) 660 (216) (560) 4,702
- - - -
- - - -
- - - -
- - - -
- 0.0x
2015 3,204 846 1,378 5,854 - (2,111) (957) 5,010
10,828 (7,010) 3,817 (396)
-10%
(560) (1,116) (560)
hal
2002 2003 2004 2005 12/06 03/07995 996 997 998 999 497 498
Income Statement
Sales 12,939 12,498 16,246 19,884 20,266 6,016 3,422 COGS 12,379 15,268 18,720 17,437 - 2,566 SG&A 335 330 353 380 36 69 Operating income (216) 648 811 2,449 1,043 787
EBITDA (216) 289 1,166 1,320 2,953 1,174 918 D&A 505 518 509 504 EBIT (216) 648 811 2,449 1,043 787 Interest & Inv Income 32 30 44 64 52 38 Interest Expense (113) (139) (229) (207) (43) (38)EBT (329) 509 582 2,242 Tax provision (80) (234) (235) (64) 343 259 Net income (346) 339 374 2,327 667 529 EPS $ (0.40) $ 0.39 $ 0.42 $ 2.24 $ 0.65 $ 0.52
Shares - primary 864 868 874 1,010 996 992 Shares - diluted 864 874 882 1,038 1,030 1,025
Balance Sheet
Cash 1,107 1,815 2,808 2,391 4,379 3,043 Inventory 734 695 791 953 1,261 1,430 A/R 3,257 4,765 5,751 4,801 4,674 2,700 Pre-paid Expenses - - - - - - Other Current Assets 462 644 680 1,241 869 4,542 Total Current Assets 5,560 7,919 10,030 9,386 11,183 11,715
Gross PP&E 5,952 6,066 6,227 6,486 7,202 6,646 D&A (3,323) (3,540) (3,674) (3,838) (4,154) (3,888)Net PP&E 2,629 2,526 2,553 2,648 3,048 2,758
Goodwill 723 670 795 765 775 593 Other Intangible Assets - - - - - - Other Long-term assets 3,932 4,384 2,486 2,249 1,814 2,572 Total Assets 12,844 15,499 15,864 15,048 16,820 17,638
Accounts Payable 1,077 1,776 2,339 1,967 1,931 739 Accrued Expenses 370 400 473 648 764 383 Short-term Debt 49 18 15 - - - Total Current Liabilities 1,496 2,194 2,827 2,615 2,695 1,122
Long-term Debt 1,181 3,415 3,593 2,813 2,786 2,785 Minority Interest 71 100 108 145 447 65 Other Debt 6,538 7,243 5,404 3,103 3,516 5,774 Total Liabilities 9,286 12,952 11,932 8,676 9,444 9,746
Preferred Equity - - - - - -
Common Equity 3,558 2,547 3,932 6,372 7,376 7,892 Other Equity - - - - - - Total Liabilities & Equity 12,844 15,499 15,864 15,048 16,820 17,638
Checks
Balance Sheet balance - - - - - - Assets Check - - - - - -
Supplemental
Depreciation - - - - - - Capex (764) (515) (575) (651)
06/07 09/07 2006499 500 1000
3,735 3,928 22,504 2,809 2,956 18,706 82 63 450 844 909 3,348
984 1,055 3,875 527
844 909 3,348 36 26 162 (41) (39) (175)
3,173 284 152 (1,144) 595 726 2,272 $ 0.63 $ 0.79 $ 2.16
905 880 1,014 942 917 1,054
2,223 1,891 4,379 1,500 1,560 1,261 2,948 3,109 4,674 - - - 601 661 869 7,272 7,221 11,183
6,898 7,328 7,202 (3,910) (3,991) (4,154) 2,988 3,337 3,048
594 731 775 - - - 1,135 1,180 1,814 11,989 12,469 16,820
856 798 1,931 447 525 764 - - - 1,303 1,323 2,695
2,784 2,796 2,786 71 90 447 1,973 2,063 3,516 6,131 6,272 9,444
- - -
5,858 6,197 7,376 - - - 11,989 12,469 16,820
- - - - - -
- - - (891)