Financial LBO Model over Excel

LBO FIN36085 Final Exam - LBO Model Golden Flash Manufacturing (000s) SOURCES & USES Purchase Price / Enterprise Value $ 165,269 LBO Sources of Cash LBO Uses of Cash Historical EBITDA @ Close Equity Investment from Buyer $ 167,269 Seller Proceeds (i.e. pay the OLDCO equity holders) $ 160,269 OLDCO Equity Value EV as Multiple of Min EBITDA ERROR:#DIV/0! Subordinated Debt Borrowed $ - OLDCO Debt $ 5,000 Subordinated Debt EBITDA Multiple 2.5x Senior Debt Borrowed $ - Transaction Expenses $ 1,000 Senior Debt EBITDA Multiple 1.5x Financing Fees $ 1,000 Transaction Expenses Transaction Expenses (come out of post-close retained earnings) $ 1,000 Financing Fees (capitalized on balance sheet) $ 1,000 Total $ 167,269 Total $ 167,269 Financial Statements Golden Flash Manufacturing (000s) Historical Transaction Projected Projected Projected Projected Projected INCOME STATEMENT 20X1 20X2 20X3 20X4 20X5 20X6 20X7 Revenue 121,216 128,945 Growth (%) NA 0.0% 0.0% 0.0% 0.0% 0.0% Cost of Goods Sold (Net of D&A) 86,220 91,951 % of Sales ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! Gross Profit % of Sales Selling General &Administrative (aka "fixed costs") (doesn't include Dep&Amort) 10,812 11,893 % of Sales ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! EBITDA % of Sales Depreciation 1,474 1,622 Amortization - - EBIT (1,474) (1,622) Interest Expense 400 400 Pretax Income (1,874) (2,022) - - - - - Income Tax Expense 4,676 5,158 Tax Rate NM NM 35.0% 35.0% 35.0% 35.0% 35.0% Net Income BALANCE SHEET ADJUSTMENTS Source Source Use Use Use Use Post-Closing Historical Post-Closing tc={2FA82F91-8F41-4431-BA04-B44BB76145B7}: [Threaded comment] Your version of Excel allows you to read this threaded comment; however, any edits to it will get removed if the file is opened in a newer version of Excel. Learn more: https://go.microsoft.com/fwlink/?linkid=870924 Comment: Calculated on the right in column U Projected Projected Projected Projected Projected Pre-Closing Equity Debt Oldco Equity Oldco Debt Trnsctn Fees Fnce Fees BALANCE SHEET 20X1 20X2 20X3 20X4 20X5 20X6 20X7 20X2 20X2 Current Assets Cash 4,653 - 12,918 (1,000) (1,000) Accounts Receivable 10,326 - 11,358 Inventory 5,642 - 6,206 Prepaid Expenses 1,849 - - - - - - 1,849 Total Current Assets 22,470 - - - - - - 32,332 - PP&E, Net of Accum. Depreciation 11,853 - 12,231 Other Assets Capitalized Financing Fee - 1,000 800 600 400 200 - - 1,000 1,000 Goodwill - 127,991 - 127,991 127,991 Other Long Term Asset - - - - - - - - Total Other Assets - 128,991 800 600 400 200 - - 128,991 TOTAL ASSETS 34,323 128,991 800 600 400 200 - 44,563 128,991 Current Liabilities Accounts Payable 6,623 - - - - - - 7,285 Total Current Liabilities 6,623 - - - - - - 7,285 - Long Term Liabilities OLDCO Notes Payable - - - - - - - - - OLDCO Current Maturities of Long Term Debt - - - - - - - - - OLDCO Long Term Debt, Net of Current Maturities 5,000 - - - - - - 5,000 NEWCO Revolving Line of Credit - 2,000 4,000 6,000 8,000 10,000 - NEWCO Subordinated Debt - NEWCO Senior Debt - TOTAL LIABILITIES 11,623 - 2,000 4,000 6,000 8,000 10,000 12,285 - Common Stock 5,015 - - - - - - 5,015 Retained Earnings 17,685 - 27,263 (1,000) TOTAL EQUITY 22,700 - - - - - - 32,278 - TOTAL LIABILITIES & EQUITY 34,323 - 2,000 4,000 6,000 8,000 10,000 44,563 - Check??? (assets = liab & equity) 128,990.8