0% found this document useful (0 votes)
2K views20 pages

NYSF Leveraged Buyout Model Template

The document appears to be an outline for a leveraged buyout model, listing various financial terms and metrics involved in a leveraged buyout transaction. It includes sections on the purchase price and multiples, sources and uses of funds, pro forma income statements, cash flow statements, and balance sheets, along with supplemental schedules on depreciation, operating working capital, and assumptions. The model will be used to evaluate the financial effects of a potential leveraged buyout transaction.

Uploaded by

Ben
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as XLSX, PDF, TXT or read online on Scribd
0% found this document useful (0 votes)
2K views20 pages

NYSF Leveraged Buyout Model Template

The document appears to be an outline for a leveraged buyout model, listing various financial terms and metrics involved in a leveraged buyout transaction. It includes sections on the purchase price and multiples, sources and uses of funds, pro forma income statements, cash flow statements, and balance sheets, along with supplemental schedules on depreciation, operating working capital, and assumptions. The model will be used to evaluate the financial effects of a potential leveraged buyout transaction.

Uploaded by

Ben
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as XLSX, PDF, TXT or read online on Scribd

Leveraged Buyout Model

(in US$ millions except per share amounts)

Purchase Price Sources


Target share price Amount
Purchase premium Debt
Purchase price per share Revolving line of credit
Shares outstanding Term facilities
Purchase price The notes
Purchase Multiples Equity
Enterprise value Berkshire Hathaway Preferred
Implied EV / LTM EBITDA Berkshire Hathaway Common
Implied EV / LTM EBIT 3G Capital Common
Total

The Analyst Exchange


On - Line Training 473535625.xlsx, Assumptions
www.theanalystexchange.com 1 of 20
Uses
% of Total Capital % Equity Ownership Purchase price (equity value)
Net debt
Transaction fees
% fee
Total
Goodwill and Intangible Assets
Purchase price (equity value)
Target book value
Price over book
% book to amortization
New Intangible Assets
Amortization (years)
Amortization ($/year)
Adjustments:
Step-up of PP&E
Deferred tax adjustments
Goodwill

The Analyst Exchange


On - Line Training 473535625.xlsx, Assumptions
www.theanalystexchange.com 2 of 20
Consolidated Income Statements
(in US$ millions except per share amounts) Actuals

Period Ending 2010A 2011A 2012A

Revenue
Y/Y revenue growth (%)

Cost of goods sold


COGS as a % of revenue

Gross profit
Gross profit margin (%)

Operating expenses
Selling, general, and administrative
SG&A as a % of revenue
Fund management fee
Cost savings
Cost savings as a % of SG&A
Total operating expenses

Other income
Equity in earnings of unconsolidated affiliates

EBITDA
EBITDA margin (%)

Depreciation
Amortization
Amortization of identifiable intangible assets
Total depreciation and amortization

EBIT
EBIT margin (%)

Interest
Interest expense
Interest income
Net interest expense

EBT
EBT margin (%)

Income tax expense


All-in effective tax rate (%)

Net income (adjusted)

The Analyst Exchange


On - Line Training 473535625.xlsx, Financials
www.theanalystexchange.com 3 of 20
Nonrecurring events
Other expense, net
Loss from discontinued operations, net of tax
Effect of accounting changes
Extraordinary items, net of tax
Total nonrecurring events

Net income (after nonrecurring events)


Net income attirbutable to noncontrolling interests

Net income (as reported)

Earnings per share (EPS)


Basic
Diluted

Average common shares outstanding


Basic
Diluted
Consolidated Statements of Cash Flows
(in US$ millions except per share amounts) Actuals

Period Ending 2010A 2011A 2012A

Cash flows from operating activities


Net income
Depreciation
Amortization
Amortization of identifiable intangible assets
Deferred tax benefit / (provision)
Net losses on divestitures
Pension contributions
Asset write-downs from Fiscal 2012 productivity initiatives
Other items, net
Changes in operating working capital
Changes in receivables
Changes in inventories
Changes in prepaid expenses and other current assets
Changes in accounts payable
Changes in accrued liabilities
Changes in accrued income taxes
Net changes in operating working capital
Total cash provided by (used for) operating activities

Cash flows from investing activities


Capital expenditures (capex)
Proceeds from disposal of property, plant, and equipment
Acquisitions net of cash acquired
Proceeds from divestitures
Sale of short-term investments
Change in restricted cash
The Analyst Exchange
On - Line Training 473535625.xlsx, Financials
www.theanalystexchange.com 4 of 20
Other items, net
Total cash provided by (used for) investing activities

Cash flows from financing activities


Revolver borrowings (repayments)
Term facilities borrowings (repayments)
The notes borrowings (repayments)
Common equity
Preferred equity
Proceeds (payments) of long-term debt
Net payments on commercial paper and short-term debt
Dividends
Purchase of treasury stock
Exercise of stock options
Acquisitions of subsidiary shares from noncontrolling interests
Other items, net
Total cash provided by (used for) financing activities
Effect of exchange rate on cash and cash equivalents

Total change in cash and cash equivalents

SUPPLEMENTAL DATA:
Cash flow before debt paydown
Consolidated Balance Sheet Adjustments
(in US$ millions except per share amounts) Actuals Pro Forma

On January 27, 2013 LTM Additions (+)

Assets
Current assets:
Cash and cash equivalents
Trade receivables, net
Other receivables, net
Total inventories
Prepaid expenses
Other current assets
Total current assets
Property, plant, and equipment, net
Goodwill
Trademarks, net
Other intangibles, net
Other noncurrent assets
Total assets

Liabilities
Current liabilities:
Short-term debt
Portion of long-term debt due within one year
Revolving line of credit
Trade payables
The Analyst Exchange
On - Line Training 473535625.xlsx, Financials
www.theanalystexchange.com 5 of 20
Other payables
Accrued marketing
Other accrued liabilities
Income taxes
Total current liabilities
Term facilities
The notes
Long-term debt
Deferred income taxes
Nonpension postretirement benefits
Other noncurrent liabilities
Redeemable noncontrolling interest
Total liabilities

Total Equity
Shareholders' equity
Capital stock
Common equity investment
Preferred equity investment
Retained earnings
Treasury stock
Accumulated other comprehensive loss
Total shareholders' equity
Noncontrolling interest
Total equity

Total liabilities and equity

SUPPLEMENTAL DATA:
Balance? (Y/N) Y
Consolidated Balance Sheets
(in US$ millions except per share amounts) Actuals

On January 27,

Assets
Current assets:
Cash and cash equivalents
Receivables
Inventories
Prepaid expenses and other current assets
Total current assets
Property, plant, and equipment, net
Goodwill
Trademarks, net
Other intangibles, net
Other noncurrent assets
Total assets

Liabilities

The Analyst Exchange


On - Line Training 473535625.xlsx, Financials
www.theanalystexchange.com 6 of 20
Current liabilities:
Short-term debt
Portion of long-term debt due within one year
Revolving line of credit
Accounts payable
Accrued marketing and other accrued liabilities
Income taxes
Total current liabilities
Term facilities
The notes
Long-term debt
Deferred income taxes
Nonpension postretirement benefits
Other noncurrent liabilities
Redeemable noncontrolling interest
Total liabilities

Total Equity
Shareholders' equity
Capital stock
Common equity investment
Preferred equity investment
Retained earnings
Treasury stock
Accumulated other comprehensive loss
Total shareholders' equity
Noncontrolling interest
Total equity

Total liabilities and equity

SUPPLEMENTAL DATA:
Balance? (Y/N)
Depreciation
(in US$ millions except per share amounts)

Period Ending
Property, plant, and equipment beginning of year
Capital expenditures beginning of year

Straight-line depreciation
Years (PP&E)
Years (CAPEX)
Existing PP&E
2014 CAPEX
2015 CAPEX
2016 CAPEX
2017 CAPEX
2018 capex
Total straight-line depreciation

The Analyst Exchange


On - Line Training 473535625.xlsx, Financials
www.theanalystexchange.com 7 of 20
Amortization
Amortization of identifiable intangible assets
Total depreciation and amortization
Operating Working Capital Schedule (OWC)
(in US$ millions except per share amounts)

On January 27,

Current assets
Receivables
Days receivable
Inventories
Inventory turnover days
Prepaid expenses and other current assets
Days prepaid
Total current assets

Current liabilities
Accounts payable
Days payable
Accrued liabilities
Days payable
Income taxes
Days payable
Total current liabilities

Total operating working capital


Change in total operating working capital
Match? (Y/N)
Debt Schedule
(in US$ millions except per share amounts) Actuals

On January 27,

Cash available to pay down debt


Cash at beginning of year
Cash flow before debt paydown
Minimum cash cushion
Total cash available to pay down debt

Revolving line of credit


Revolving line of credit (beginning of year)
Mandatory issuances / (retirements)
Nonmandatory issuances / (retirements)
Revolving line of credit (end of year)
Revolving line of credit interest expense
Revolving line of credit interest rate

Term facilities
Term facilities (beginning of year)
Mandatory issuances / (retirements)
The Analyst Exchange
On - Line Training 473535625.xlsx, Financials
www.theanalystexchange.com 8 of 20
Nonmandatory issuances / (retirements)
Term facilities (end of year)
Term facilities interest expense
Term facilities interest rate

The notes
The notes (beginning of year)
Mandatory issuances / (retirements)
Nonmandatory issuances / (retirements)
The notes (end of year)
The notes interest expense
The notes interest rate

Total issuances / (retirements)

Total interest expense

Cash at the end of the year


Interest income
Interest rate
Match? (Y/N)

The Analyst Exchange


On - Line Training 473535625.xlsx, Financials
www.theanalystexchange.com 9 of 20
Last Twelve Months (LTM) Estimates

9Mo. 2012 9Mo. 2013 LTM 2014E 2015E 2016E 2017E 2018E

The Analyst Exchange


On - Line Training 473535625.xlsx, Financials
www.theanalystexchange.com 10 of 20
Last Twelve Months (LTM) Estimates

9Mo. 2012 9Mo. 2013 LTM 2014E 2015E 2016E 2017E 2018E

The Analyst Exchange


On - Line Training 473535625.xlsx, Financials
www.theanalystexchange.com 11 of 20
Pro Forma

Subtractions
(–) Total

The Analyst Exchange


On - Line Training 473535625.xlsx, Financials
www.theanalystexchange.com 12 of 20
Y

Pro Forma Estimates

2013PF 2014E 2015E 2016E 2017E 2018E

The Analyst Exchange


On - Line Training 473535625.xlsx, Financials
www.theanalystexchange.com 13 of 20
Y Y Y Y Y Y

Estimates

2014E 2015E 2016E 2017E 2018E

The Analyst Exchange


On - Line Training 473535625.xlsx, Financials
www.theanalystexchange.com 14 of 20
Pro Forma Estimates

2013PF 2014E 2015E 2016E 2017E 2018E

Y Y Y Y Y

Pro Forma Estimates

2013PF 2014E 2015E 2016E 2017E 2018E

The Analyst Exchange


On - Line Training 473535625.xlsx, Financials
www.theanalystexchange.com 15 of 20
Y Y Y Y Y

The Analyst Exchange


On - Line Training 473535625.xlsx, Financials
www.theanalystexchange.com 16 of 20
Investment Returns
(in US$ millions except per share amounts) Actuals Estima

Period Ending LTM 2014E

Exit multiple

Enterprise value [(EBITDA + management fee) × EBITDA multiple]


Plus cash
Less revolving line of credit
Less term facilities
Less the notes
Less preferred equity
Less preferred dividends
Equity to common

Return to 3G
IRR
Return multiple

Return to Berkshire Hathaway


Equity investment
Equity value
Preferred equity
Preferred dividends payable
Preferred dividends
Total return

IRR
Return multiple

The Analyst Exchange


On - Line Training 473535625.xlsx, Returns
www.theanalystexchange.com 17 of 20
Estimates

2015E 2016E 2017E 2018E

The Analyst Exchange


On - Line Training 473535625.xlsx, Returns
www.theanalystexchange.com 18 of 20
Consolidated Income Statements
(in US$ millions except per share amounts) Actuals Estima

Period Ending 2014A


Input Data
EBITDA
Less taxes
Less maintenance CAPEX
Operating Cash Flow (OCF)

Debt Service
Total cash interest expense
Total scheduled principal repayments
Total Debt Service

Total Debt

Coverage Ratios
EBITDA / Interest Expense
EBITDA / Total Senior Cash Debt Service
OCF / Interest Expense
OCF / Total Senior Cash Debt Service

Leverage Ratios
Total Debt / EBITDA
Total Debt / OCF

The Analyst Exchange


On - Line Training 473535625.xlsx, Senior Debt Covenants
www.theanalystexchange.com 19 of 20
Estimates

2015A 2016A 2017A 2018A

The Analyst Exchange


On - Line Training 473535625.xlsx, Senior Debt Covenants
www.theanalystexchange.com 20 of 20

You might also like