Two friends Harley and Davidson agree on a company deal with an implied enterprise value of $5M. Harley invests $3M. The deal is structured as all-common, and American Private Equit
I need a Microsoft Excel assignment and LBO Analysis. all the resources and contents are attached in the attachments. Please text me only if you are expert in Excel and the other techincal things, otherwise please don't waste my time.
All assignments should be written in your own words and provide examples and opinions beyond the textbook or any other source you get them from as listed in the online assignment guidelines. This is an individual assignment. For calculations show ALL your steps including calculator/Excel keystrokes and/or formulas. It is important for full credit to explain and briefly discuss your final answers and NOT just provide a number answer only or textbook definitions or any other supplements. USE YOUR OWN WORDS. Plagiarism software will be used to compare to those and to each other. Please label your uploaded assignment file with the course and your name on the file to the online assignment link. It is important to show you work for partial credit. Remember to show all work including calculator keystrokes if using excel submit the original excel file. Also, include a brief discussion of your results using your OWN examples and opinions where applicable.
NAME: ___________________________________________________
Complete Questions Questions 1 and 2 These are the only Choices.
—————————————————————————————————————
1. (30% REQUIRED)
Two friends Harley and Davidson agree on a company deal with an implied enterprise value of $5M. Harley invests $3M. The deal is structured as all-common, and American Private Equity comes in and offers $8.5M million for the company. Harley owns 60% and Davidson owns 40%.
a. Set up the All-Common structure for the two partners. Calculate the payout table and discuss
b. If the deal is structured as redeemable preferred with $1,000 cheap common for the two partners, calculate the payout table and discuss
c. The deal is Convertible Preferred for the two partners. Calculate the payout table and discuss
d. What is the importance of capital structure ownership in private equity.
2. (70%) VALUATION: Perform an LBO analysis of small cap company using different types of debt to value a purchase of another company. It is important to discuss the details of what you are doing and your conclusion. If there is no debt, propose hypothetical debt that you would need to purchase the company outright. You can follow and use the Excel Spreadsheet provided on Blackboard Chapter 5 Rosenbaum Excel. Make sure this is detailed and discuss your reasoning and results.
=========================================================
,
TS
ValueCo Corporation | |||||||||||||||||
Leveraged Buyout Analysis | Financing Structure: | Structure 1 | |||||||||||||||
($ in millions, fiscal year ending December 31) | Operating Scenario: | Base | |||||||||||||||
Transaction Summary | |||||||||||||||||
Sources of Funds | Uses of Funds | Purchase Price | Return Analysis | ||||||||||||||
% of Total | Multiple of EBITDA | % of Total | Offer Price per Share | -0 | Exit Year | 2017 | |||||||||||
Amount | Sources | 9/30/2012 | Cumulative | Pricing | Amount | Uses | Fully Diluted Shares | -0 | Entry Multiple | 8.0x | |||||||
Revolving Credit Facility | -0 | – % | – x | – x | L+425 bps | Purchase ValueCo Equity | $4,350.0 | 72.5% | Equity Purchase Price | $4,350.0 | Exit Multiple | 8.0x | |||||
Term Loan A | -0 | – % | – x | – x | NA | Repay Existing Debt | 1,500.0 | 25.0% | Plus: Existing Net Debt | 1,250.0 | IRR | 20% | |||||
Term Loan B | 2,150.0 | 35.8% | 3.1x | 3.1x | L+450 bps | Tender / Call Premiums | 20.0 | 0.3% | Enterprise Value | $5,600.0 | Cash Return | 2.5x | |||||
Term Loan C | -0 | – % | – x | 3.1x | NA | Financing Fees | 90.0 | 1.5% | |||||||||
2nd Lien | -0 | – % | – x | 3.1x | NA | Other Fees and Expenses | 40.0 | 0.7% | Transaction Multiples | Options | |||||||
Senior Notes | 1,500.0 | 25.0% | 2.1x | 5.2x | 8.500% | Enterprise Value / Sales | Financing Structure | 1 | |||||||||
Senior Subordinated Notes | -0 | – % | – x | 5.2x | NA | LTM 9/30/2012 | $3,385.0 | 1.7x | Operating Scenario | 1 | |||||||
Equity Contribution | 2,100.0 | 35.0% | 3.0x | 8.2x | 2012E | 3,450.0 | 1.6x | Cash Flow Sweep | 1 | ||||||||
Rollover Equity | -0 | – % | – x | 8.2x | Enterprise Value / EBITDA | Cash Balance | 1 | ||||||||||
Cash on Hand | 250.0 | 4.2% | 0.4x | 8.6x | LTM 9/30/2012 | $700.0 | 8.0x | Average Interest | 1 | ||||||||
Total Sources | $6,000.0 | 100.0% | 8.6x | 8.6x | Total Uses | $6,000.0 | 100.0% | 2012E | 725.0 | 7.7x | Financing Fees | 1 | |||||
Summary Financial Data | |||||||||||||||||
Historical Period | Projection Period | ||||||||||||||||
LTM | Pro forma | Year 1 | Year 2 | Year 3 | Year 4 | Year 5 | Year 6 | Year 7 | Year 8 | Year 9 | Year 10 | ||||||
2009 | 2010 | 2011 | 9/30/2012 | 2012 | 2013 | 2014 | 2015 | 2016 | 2017 | 2018 | 2019 | 2020 | 2021 | 2022 | |||
Sales | $2,600.0 | $2,900.0 | $3,200.0 | $3,385.0 | $3,450.0 | $3,708.8 | $3,931.3 | $4,127.8 | $4,293.0 | $4,421.7 | $4,554.4 | $4,691.0 | $4,831.8 | $4,976.7 | $5,126.0 | ||
% growth | NA | 11.5% | 10.3% | NA | 7.8% | 7.5% | 6.0% | 5.0% | 4.0% | 3.0% | 3.0% | 3.0% | 3.0% | 3.0% | 3.0% | ||
Gross Profit | $988.0 | $1,131.0 | $1,280.0 | $1,350.0 | $1,380.0 | $1,483.5 | $1,572.5 | $1,651.1 | $1,717.2 | $1,768.7 | $1,821.8 | $1,876.4 | $1,932.7 | $1,990.7 | $2,050.4 | ||
% margin | 38.0% | 39.0% | 40.0% | 39.9% | 40.0% | 40.0% | 40.0% | 40.0% | 40.0% | 40.0% | 40.0% | 40.0% | 40.0% | 40.0% | 40.0% | ||
EBITDA | $491.4 | $580.0 | $672.0 | $700.0 | $725.0 | $779.4 | $826.1 | $867.4 | $902.1 | $929.2 | $957.1 | $985.8 | $1,015.4 | $1,045.8 | $1,077.2 | ||
% margin | 18.9% | 20.0% | 21.0% | 20.7% | 21.0% | 21.0% | 21.0% | 21.0% | 21.0% | 21.0% | 21.0% | 21.0% | 21.0% | 21.0% | 21.0% | ||
Capital Expenditures | 136.4 | 114.0 | 144.0 | 152.3 | 155.3 | 166.9 | 176.9 | 185.8 | 193.2 | 199.0 | 204.9 | 211.1 | 217.4 | 224.0 | 230.7 | ||
% sales | 5.2% | 3.9% | 4.5% | 4.5% | 4.5% | 4.5% | 4.5% | 4.5% | 4.5% | 4.5% | 4.5% | 4.5% | 4.5% | 4.5% | 4.5% | ||
Cash Interest Expense | 252.5 | 246.6 | 233.8 | 218.8 | 201.7 | 182.8 | 163.7 | 141.2 | 128.9 | 128.9 | 128.9 | ||||||
Total Interest Expense | 263.9 | 258.0 | 245.1 | 230.1 | 213.1 | 194.2 | 175.0 | 151.9 | 135.0 | 128.9 | 128.9 | ||||||
Free Cash Flow | |||||||||||||||||
EBITDA | $779.4 | $826.1 | $867.4 | $902.1 | $929.2 | $957.1 | $985.8 | $1,015.4 | $1,045.8 | $1,077.2 | |||||||
Less: Cash Interest Expense | (246.6) | (233.8) | (218.8) | (201.7) | (182.8) | (163.7) | (141.2) | (128.9) | (128.9) | (128.9) | |||||||
Plus: Interest Income | -0 | -0 | -0 | -0 | -0 | -0 | 0.0 | 1.1 | 3.2 | 5.4 | |||||||
Less: Income Taxes | (113.6) | (131.2) | (148.1) | (164.0) | (178.5) | (193.3) | (209.9) | (224.8) | (236.2) | (245.5) | |||||||
Less: Capital Expenditures | (166.9) | (176.9) | (185.8) | (193.2) | (199.0) | (204.9) | (211.1) | (217.4) | (224.0) | (230.7) | |||||||
Less: Increase in Net Working Capital | (47.6) | (41.0) | (36.2) | (30.4) | (23.7) | (24.4) | (25.1) | (25.9) | (26.7) | (27.5) | |||||||
Free Cash Flow | $204.6 | $243.3 | $278.7 | $312.9 | $345.2 | $370.7 | $398.4 | $419.4 | $433.3 | $450.0 | |||||||
Cumulative Free Cash Flow | 204.6 | 448.0 | 726.7 | 1,039.5 | 1,384.7 | 1,755.4 | 2,153.8 | 2,573.2 | 3,006.5 | 3,456.6 | |||||||
Capitalization | |||||||||||||||||
Cash | -0 | -0 | -0 | -0 | -0 | -0 | -0 | $3.8 | $423.2 | $856.5 | $1,306.6 | ||||||
Revolving Credit Facility | -0 | -0 | -0 | -0 | -0 | -0 | -0 | -0 | -0 | -0 | -0 | ||||||
Term Loan A | -0 | -0 | -0 | -0 | -0 | -0 | -0 | -0 | -0 | -0 | -0 | ||||||
Term Loan B | 2,150.0 | 1,945.4 | 1,702.0 | 1,423.3 | 1,110.5 | 765.3 | 394.6 | -0 | -0 | -0 | -0 | ||||||
Term Loan C | -0 | -0 | -0 | -0 | -0 | -0 | -0 | -0 | -0 | -0 | -0 | ||||||
Existing Term Loan | -0 | -0 | -0 | -0 | -0 | -0 | -0 | -0 | -0 | -0 | -0 | ||||||
2nd Lien | -0 | -0 | -0 | -0 | -0 | -0 | -0 | -0 | -0 | -0 | -0 | ||||||
Other Debt | -0 | -0 | -0 | -0 | -0 | -0 | -0 | -0 | -0 | -0 | -0 | ||||||
Total Senior Secured Debt | $2,150.0 | $1,945.4 | $1,702.0 | $1,423.3 | $1,110.5 | $765.3 | $394.6 | -0 | -0 | -0 | -0 | ||||||
Senior Notes | 1,500.0 | 1,500.0 | 1,500.0 | 1,500.0 | 1,500.0 | 1,500.0 | 1,500.0 | 1,500.0 | 1,500.0 | 1,500.0 | 1,500.0 | ||||||
Total Senior Debt | $3,650.0 | $3,445.4 | $3,202.0 | $2,923.3 | $2,610.5 | $2,265.3 | $1,894.6 | $1,500.0 | $1,500.0 | $1,500.0 | $1,500.0 | ||||||
Senior Subordinated Notes | -0 | -0 | -0 | -0 | -0 | -0 | -0 | -0 | -0 | -0 | -0 | ||||||
Total Debt | $3,650.0 | $3,445.4 | $3,202.0 | $2,923.3 | $2,610.5 | $2,265.3 | $1,894.6 | $1,500.0 | $1,500.0 | $1,500.0 | $1,500.0 | ||||||
Shareholders' Equity | 2,040.0 | 2,225.3 | 2,439.3 | 2,680.9 | 2,948.4 | 3,239.7 | 3,555.1 | 3,897.6 | 4,264.4 | 4,649.7 | 5,050.4 | ||||||
Total Capitalization | $5,690.0 | $5,670.7 | $5,641.3 | $5,604.3 | $5,558.9 | $5,505.0 | $5,449.8 | $5,397.6 | $5,764.4 | $6,149.7 | $6,550.4 | ||||||
% of Bank Debt Repaid | -0 | 9.5% | 20.8% | 33.8% | 48.3% | 64.4% | 81.6% | 100.0% | 100.0% | 100.0% | 100.0% | ||||||
Credit Statistics | |||||||||||||||||
% Debt / Total Capitalization | 64.1% | 60.8% | 56.8% | 52.2% | 47.0% | 41.1% | 34.8% | 27.8% | 26.0% | 24.4% | 22.9% | ||||||
EBITDA / Cash Interest Expense | 2.9x | 3.2x | 3.5x | 4.0x | 4.5x | 5.1x | 5.8x | 7.0x | 7.9x | 8.1x | 8.4x | ||||||
(EBITDA – Capex) / Cash Interest Expense | 2.3x | 2.5x | 2.8x | 3.1x | 3.5x | 4.0x | 4.6x | 5.5x | 6.2x | 6.4x | 6.6x | ||||||
EBITDA / Total Interest Expense | 2.7x | 3.0x | 3.4x | 3.8x | 4.2x | 4.8x | 5.5x | 6.5x | 7.5x | 8.1x | 8.4x | ||||||
(EBITDA – Capex) / Total Interest Expense | 2.2x | 2.4x | 2.6x | 3.0x | 3.3x | 3.8x | 4.3x | 5.1x | 5.9x | 6.4x | 6.6x | ||||||
Senior Secured Debt / EBITDA | 3.0x | 2.5x | 2.1x | 1.6x | 1.2x | 0.8x | 0.4x | – x | – x | – x | – x | ||||||
Senior Debt / EBITDA | 5.0x | 4.4x | 3.9x | 3.4x | 2.9x | 2.4x | 2.0x | 1.5x | 1.5x | 1.4x | 1.4x | ||||||
Total Debt / EBITDA | 5.0x | 4.4x | 3.9x | 3.4x | 2.9x | 2.4x | 2.0x | 1.5x | 1.5x | 1.4x | 1.4x | ||||||
Net Debt / EBITDA | 5.0x | 4.4x | 3.9x | 3.4x | 2.9x | 2.4x | 2.0x | 1.5x | 1.1x | 0.6x | 0.2x |
IS
($ in millions, fiscal year ending December 31) | ||||||||||||||||||
Income Statement | ||||||||||||||||||
Historical Period | Projection Period | |||||||||||||||||
LTM | Pro forma | Year 1 | Year 2 | Year 3 | Year 4 | Year 5 | Year 6 | Year 7 | Year 8 | Year 9 | Year 10 | |||||||
2009 | 2010 | 2011 | 9/30/2012 | 2012 | 2013 | 2014 | 2015 | 2016 | 2017 | 2018 | 2019 | 2020 | 2021 | 2022 | ||||
Sales | $2,600.0 | $2,900.0 | $3,200.0 | $3,385.0 | $3,450.0 | $3,708.8 | $3,931.3 | $4,127.8 | $4,293.0 | $4,421.7 | $4,554.4 | $4,691.0 | $4,831.8 | $4,976.7 | $5,126.0 | |||
% growth | NA | 11.5% | 10.3% | NA | 7.8% | 7.5% | 6.0% | 5.0% | 4.0% | 3.0% | 3.0% | 3.0% | 3.0% | 3.0% | 3.0% | |||
Cost of Goods Sold | 1,612.0 | 1,769.0 | 1,920.0 | 2,035.0 | 2,070.0 | 2,225.3 | 2,358.8 | 2,476.7 | 2,575.8 | 2,653.0 | 2,732.6 | 2,814.6 | 2,899.1 | 2,986.0 | 3,075.6 | |||
Gross Profit | $988.0 | $1,131.0 | $1,280.0 | $1,350.0 | $1,380.0 | $1,483.5 | $1,572.5 | $1,651.1 | $1,717.2 | $1,768.7 | $1,821.8 | $1,876.4 | $1,932.7 | $1,990.7 | $2,050.4 | |||
% margin | 38.0% | 39.0% | 40.0% | 39.9% | 40.0% | 40.0% | 40.0% | 40.0% | 40.0% | 40.0% | 40.0% | 40.0% | 40.0% | 40.0% | 40.0% | |||
Selling, General & Administrative | 496.6 | 551.0 | 608.0 | 650.0 | 655.0 | 704.1 | 746.4 | 783.7 | 815.0 | 839.5 | 864.7 | 890.6 | 917.3 | 944.9 | 973.2 | |||
% sales | 19.1% | 19.0% | 19.0% | 19.2% | 19.0% | 19.0% | 19.0% | 19.0% | 19.0% | 19.0% | 19.0% | 19.0% | 19.0% | 19.0% | 19.0% | |||
Other Expense / (Income) | -0 | -0 | -0 | -0 | -0 | -0 | -0 | -0 | -0 | -0 | -0 | -0 | -0 | -0 | -0 | |||
EBITDA | $491.4 | $580.0 | $672.0 | $700.0 | $725.0 | $779.4 | $826.1 | $867.4 | $902.1 | $929.2 | $957.1 | $985.8 | $1,015.4 | $1,045.8 | $1,077.2 | |||
% margin | 18.9% | 20.0% | 21.0% | 20.7% | 21.0% | 21.0% | 21.0% | 21.0% | 21.0% | 21.0% | 21.0% | 21.0% | 21.0% | 21.0% | 21.0% | |||
Depreciation | 116.0 | 121.5 | 145.0 | 150.0 | 155.3 | 166.9 | 176.9 | 185.8 | 193.2 | 199.0 | 204.9 | 211.1 | 217.4 | 224.0 | 230.7 | |||
Amortization | 39.0 | 43.5 | 48.0 | 50.0 | 51.8 | 55.6 | 59.0 | 61.9 | 64.4 | 66.3 | 68.3 | 70.4 | 72.5 | 74.7 | 76.9 | |||
EBIT | $336.4 | $415.0 | $479.0 | $500.0 | $518.0 | $556.9 | $590.3 | $619.8 | $644.6 | $663.9 | $683.8 | $704.3 | $725.5 | $747.2 | $769.6 | |||
% margin | 12.9% | 14.3% | 15.0% | 14.8% | 15.0% | 15.0% | 15.0% | 15.0% | 15.0% | 15.0% | 15.0% | 15.0% | 15.0% | 15.0% | 15.0% | |||
Interest Expense | ||||||||||||||||||
Revolving Credit Facility | -0 | -0 | -0 | -0 | -0 | -0 | -0 | -0 | -0 | -0 | -0 | |||||||
Term Loan A | -0 | -0 | -0 | -0 | -0 | -0 | -0 | -0 | -0 | -0 | -0 | |||||||
Term Loan B | 123.6 | 117.7 | 104.9 | 89.9 | 72.8 | 53.9 | 34.8 | 12.3 | -0 | -0 | -0 | |||||||
Term Loan C | -0 | -0 | -0 | -0 | -0 | -0 | -0 | -0 | -0 | -0 | -0 | |||||||
Existing Term Loan | -0 | -0 | -0 | -0 | -0 | -0 | -0 | -0 | -0 | -0 | -0 | |||||||
2nd Lien | -0 | -0 | -0 | -0 | -0 | -0 | -0 | -0 | -0 | -0 | -0 | |||||||
Senior Notes | 127.5 | 127.5 | 127.5 | 127.5 | 127.5 | 127.5 | 127.5 | 127.5 | 127.5 | 127.5 | 127.5 | |||||||
Senior Subordinated Notes | -0 | -0 | -0 | -0 | -0 | -0 | -0 | -0 | -0 | -0 | -0 | |||||||
Commitment Fee on Unused Revolver | 1.3 | 1.3 | 1.3 | 1.3 | 1.3 | 1.3 | 1.3 | 1.3 | 1.3 | 1.3 | 1.3 | |||||||
Administrative Agent Fee | 0.2 | 0.2 | 0.2 | 0.2 | 0.2 | 0.2 | 0.2 | 0.2 | 0.2 | 0.2 | 0.2 | |||||||
Cash Interest Expense | $252.5 | $246.6 | $233.8 | $218.8 | $201.7 | $182.8 | $163.7 | $141.2 | $128.9 | $128.9 | $128.9 | |||||||
Amortization of Deferred Financing Fees | 11.3 | 11.3 | 11.3 | 11.3 | 11.3 | 11.3 | 11.3 | 10.7 | 6.1 | -0 | -0 | |||||||
Total Interest Expense | $263.9 | $258.0 | $245.1 | $230.1 | $213.1 | $194.2 | $175.0 | $151.9 | $135.0 | $128.9 | $128.9 | |||||||
Interest Income | -0 | -0 | -0 | -0 | -0 | -0 | (0.0) | (1.1) | (3.2) | (5.4) | ||||||||
Net Interest Expense | $258.0 | $245.1 | $230.1 | $213.1 | $194.2 | $175.0 | $151.9 | $133.9 | $125.7 | $123.5 | ||||||||
Earnings Before Taxes | 298.9 | 345.2 | 389.7 | 431.5 | 469.7 | 508.8 | 552.4 | 591.5 | 621.5 | 646.2 | ||||||||
Income Tax Expense | 113.6 | 131.2 | 148.1 | 164.0 | 178.5 | 193.3 | 209.9 | 224.8 | 236.2 | 245.5 | ||||||||
Net Income | $185.3 | $214.0 | $241.6 | $267.5 | $291.2 | $315.5 | $342.5 | $366.8 | $385.3 | $400.6 | ||||||||
% margin | 5.0% | 5.4% | 5.9% | 6.2% | 6.6% | 6.9% | 7.3% | 7.6% | 7.7% | 7.8% | ||||||||
Income Statement Assumptions | ||||||||||||||||||
Sales (% YoY growth) | NA | 11.5% | 10.3% | NA | 7.8% | 7.5% | 6.0% | 5.0% | 4.0% | 3.0% | 3.0% | 3.0% | 3.0% | 3.0% | 3.0% | |||
Cost of Goods Sold (% margin) | 62.0% | 61.0% | 60.0% | 60.1% | 60.0% | 60.0% | 60.0% | 60.0% | 60.0% | 60.0% | 60.0% | 60.0% | 60.0% | 60.0% | 60.0% | |||
SG&A (% sales) | 19.1% | 19.0% | 19.0% | 19.2% | 19.0% | 19.0% | 19.0% | 19.0% | 19.0% | 19.0% | 19.0% | 19.0% | 19.0% | 19.0% | 19.0% | |||
Other Expense / (Income) (% of sales) | – % | – % | – % | – % | – % | – % | – % | – % | – % | – % | – % | – % | – % | – % | – % | |||
Depreciation (% of sales) | 4.5% | 4.2% | 4.5% | 4.4% | 4.5% | 4.5% | 4.5% | 4.5% | 4.5% | 4.5% | 4.5% | 4.5% | 4.5% | 4.5% | 4.5% | |||
Amortization (% of sales) | 1.5% | 1.5% | 1.5% | 1.5% | 1.5% | 1.5% | 1.5% | 1.5% | 1.5% | 1.5% | 1.5% | 1.5% | 1.5% | 1.5% | 1.5% | |||
Interest Income | 0.5% | 0.5% | 0.5% | 0.5% | 0.5% | 0.5% | 0.5% | 0.5% | 0.5% | 0.5% | 0.5% | |||||||
Tax Rate | 38.0% | 38.0% | 38.0% | 38.0% | 38.0% | 38.0% | 38.0% | 38.0% | 38.0% | 38.0% |
BS
($ in millions, fiscal year ending December 31) | |||||||||||||||||
Balance Sheet | |||||||||||||||||
Projection Period | |||||||||||||||||
Opening | Adjustments | Pro Forma | Year 1 | Year 2 | Year 3 | Year 4 | Year 5 | Year 6 | Year 7 | Year 8 | Year 9 | Year 10 | |||||
2012 | + | – | 2012 | 2013 | 2014 | 2015 | 2016 | 2017 | 2018 | 2019 | 2020 | 2021 | 2022 | ||||
Cash and Cash Equivalents | $250.0 | (250.0) | -0 | -0 | -0 | -0 | -0 | -0 | -0 | $3.8 | $423.2 | $856.5 | $1,306.6 | ||||
Accounts Rec
Collepals.com Plagiarism Free Papers Are you looking for custom essay writing service or even dissertation writing services? Just request for our write my paper service, and we'll match you with the best essay writer in your subject! With an exceptional team of professional academic experts in a wide range of subjects, we can guarantee you an unrivaled quality of custom-written papers. Get ZERO PLAGIARISM, HUMAN WRITTEN ESSAYS Why Hire Collepals.com writers to do your paper? Quality- We are experienced and have access to ample research materials. We write plagiarism Free Content Confidential- We never share or sell your personal information to third parties. Support-Chat with us today! We are always waiting to answer all your questions. All Rights Reserved Terms and Conditions |