Fluent Essays

Finance Excel Assignment: I need a Microsoft  Excel assignment with structure 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 dont waste my time.Assignment 2 FIN Due by WEDNESDAY 8/10/22 @ 11:59 pm. NO EXTENSIONS 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 1,2 and 3. --------------------------------------------------------------------------------------------------------------- 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. (35\%) CASE STUDY: Hertz LBO. The Case is Posted Online. Complete the following: a. How realistic are the key assumptions that underlay the Bidding Groups projections in case Exhibits 8, 9, and 10? Which assumptions are most likely to have the largest impact on returns? Look at the pro forma, are these assumptions realistic, such as travel increase in price and vehicles, recession at the time and any others. Briefly discuss the status of Hertz company in today’s economy. b. Based on the base-case estimates in case Exhibits 8, 9, and 10 and your estimate(s) of terminal value if the sponsors put up $2.3 billion in equity, what return can they expect to earn? Calculate the IRR. Discuss your results. c. Calculate the Interest Coverage Ratio and Leverage Ratio and discuss its importance in the LBO. d. What do you conclude about this buyout and why? What is one success and one failure about this LBO in your opinion and why. e. Using this case briefly discuss the 5 steps used in the LBO process that would be important tThe Panera Bread LBO Case Author: David Stowell & Alexander Katz Online Pub Date: January 04, 2021 | Original Pub. Date: 2019 Subject: Financial Investment/Analysis, Valuation, Mergers & Acquisitions Level: | Type: Indirect case | Length: 5872 Copyright: © 2019 Kellogg School of Management, Northwestern University Organization: Panera Bread | Organization size: Large Region: Northern America | State: Industry: Food and beverage service activities Originally Published in: Stowell, D. , & Katz, A. ( 2019). The Panera Bread LBO. 5-219-250. Evanston, IL: Kellogg School of Management, Northwestern University. Publisher: Kellogg School of Management DOI: https://dx.doi.org/10.4135/9781529741759 | Online ISBN: 9781529741759 www.princexml.com Prince - Non-commercial License This document was created with Prince, a great way of getting web content onto paper. javascript: void(0); javascript: void(0); javascript: void(0); javascript: void(0); javascript: void(0); https://dx.doi.org/10.4135/9781529741759 © 2019 Kellogg School of Management, Northwestern University This case was prepared for inclusion in SAGE Business Cases primarily as a basis for classroom discussion or self-study, and is not meant to illustrate either effective or ineffective management styles. Nothing herein shall be deemed to be an endorsement of any kind. This case is for scholarly, educational, or personal use only within your university, and cannot be forwarded outside the university or used for other commercial purposes. 2022 SAGE Publications Ltd. All Rights Reserved. The case studies on SAGE Business Cases are designed and optimized for online learning. Please refer to the online version of this case to fully experience any video, data embeds, spreadsheets, slides, or other resources that may be included. This content may only be distributed for use within CUNY Baruch College. https://dx.doi.org/10.4135/9781529741759 SAGE © 2019 Kellogg School of Management, Northwestern University Business Cases Page 2 of 16 The Panera Bread LBO https://dx.doi.org/10.4135/9781529741759 Abstract This case considers the buyout of Panera Bread from the perspective of a private equity fund. In early 2017, KLG Managing Director Tom Denning is considering a leveraged buyout of Panera Bread, a rapidly growing fast-casual restaurant company. A surprising Bloomberg News story signals that the deal process is broadening and KLG will have to act quickly if it hopes to buy Panera Bread. Students assume the role of Tom Denning as he prepares an investment recommendation for KLG’s investment committee. In doing so, students are required to consider a very large and expensive investment. Students are challenged to create an investment recommendation by performing due diligence, determining additional questions to ask, and pricing a buyout bid that incorporates an optimal capital structure and meets KLG’s return requirements. The PaUVA-F-1560 Rev. Oct. 5, 2009 This case was prepared by Susan Chaplinsky, Professor of Business Administration, Darden Graduate School of Business, and Felicia Marston, Professor, McIntire School of Commerce. It was written as a basis for class discussion rather than to illustrate effective or ineffective handling of an administrative situation. Copyright © 2008 by the University of Virginia Darden School Foundation, Charlottesville, VA. All rights reserved. To order copies, send an e-mail to [email protected] No part of this publication may be reproduced, stored in a retrieval system, used in a spreadsheet, or transmitted in any form or by any means—electronic, mechanical, photocopying, recording, or otherwise—without the permission of the Darden School Foundation. Rev. 10/09. ◊ BIDDING FOR HERTZ: LEVERAGED BUYOUT Overview In late summer 2005, Greg Ledford, managing director and head of automotive and transportation buyouts at the Carlyle Group, found himself examining his BlackBerry atop the Great Wall of China. Though he had planned to be sightseeing with his daughter, his immediate focus was to finalize the terms of the second-largest leveraged buyout in history. The target in question was Hertz, a subsidiary of the Ford Motor Company, which was up for sale. Ledford needed to decide the price he and his co-investors would offer for Hertz as well as assess the potential returns and risks of the deal. Already months of work, many dollars of due diligence, and arrangement of tentative financing had gone into the bid. Complicating matters, he knew he faced tough competition from a rival buyout group, no doubt engaged in a similar process. The race to win Hertz had been set in motion several months earlier, when William Clay Ford Jr., the chairman and CEO of Ford, announced plans to explore “strategic alternatives” for Hertz in April 2005. That announcement was followed in June 2005 by the filing of an S-1 registration statement setting up a “dual track process” that would result in a Hertz IPO should other sale prospects fail. Ledford, who spoke to senior Ford managers on a regular basis, had gleaned that there was interest on Ford’s part for an outright sale of Hertz. He believed a private sale that was competitive with an IPO would be viewed favorably by Ford due to its greater up- front cash proceeds and certainty of execution. When no strategic buyer surfaced, Carlyle, Clayton, Dubilier & Rice (CD&R), and Merrill Lynch Global Private Equity (collectively “Bidding Group”) joined forces to bid on Hertz. It faced competition from another buyout consortium that included Texas Pacific Group, Blackstone, Thomas H. Lee Partners LP, and Bain Capital LLC. -2- UVA-F-1560 Hertz Ownership History Hertz’s ownership history was characterized by a series of sales, public offerings, and leveraged buyouts (Exhibit 1).1 The company was first established in 1918 by 22-year-old Walter L. JaTS 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 EBITDAApollo discussion v13.ppt STRICTLY CONFIDENTIAL Investment Banking Valuation, Leveraged Buyouts, and Mergers & Acquisitions Chapter 5: LBO Analysis JOSHUA ROSENBAUM & JOSHUA PEARL GEN0190n.ppt * Apollo discussion v13.ppt Copyright © 2013 by Joshua Rosenbaum and Joshua Pearl. All rights reserved. Published by John Wiley & Sons, Inc., Hoboken, New Jersey. Published simultaneously in Canada. No part of this publication may be reproduced, stored in a retrieval system, or transmitted in any form or by any means, electronic, mechanical, photocopying, recording, scanning, or otherwise, except as permitted under Section 107 or 108 of the 1976 United States Copyright Act, without either the prior written permission of the Publisher, or authorization through payment of the appropriate per-copy fee to the Copyright Clearance Center, Inc., 222 Rosewood Drive, Danvers, MA 01923, (978) 750-8400, fax (978) 750-4470, or on the web at www.copyright.com. Requests to the Publisher for permission should be addressed to the Permissions Department, John Wiley & Sons, Inc., 111 River Street, Hoboken, NJ 07030, 201) 748-6011, fax (201) 748-6008, or online at http://www.wiley.com/go/permissions. Limit of Liability/Disclaimer of Warranty: While the publisher and author have used their best efforts in preparing this book, they make no representations or warranties with respect to the accuracy or completeness of the contents of this book and specifically disclaim any implied warranties of merchantability or fitness for a particular purpose. No warranty may be created or extended by sales representatives or written sales materials. The advice and strategies contained herein may not be suitable for your situation. You should consult with a professional where appropriate. Neither the publisher nor author shall be liable for any loss of profit or any other commercial damages, including but not limited to special, incidental, consequential, or other damages. For general information on our other products and services or for technical support, please contact our Customer Care Department within the United States at (800) 762-2974, outside the United States at (317) 572-3993 or fax (317) 572-4002. Wiley also publishes its books in a variety of electronic formats. Some content that appears in print may not be available in electronic books. For more information about Wiley products, visit our web site at www.wiley.com. Library of Congress Cataloging-in-Publication Data: ISBN-13 978-0-470-44220-3 Printed in the United States of America 10 9 8 7 6 5 4 3 2 1 GEN0190n.ppt * Apollo discussion v13.ppt Overview of LBO Analysis Core analytical tool used to assess financing structure, investment returns, and valuation in leveraged buyout scenarios Same techniques can also be used to assess refinancing opportunities and restructuring alternatives for corporate issuers Requires specialized knowledge of financial modeling, leveraged debt capital markets, M&Venture Capital, Private Equity, and the Financing of Entrepreneurship Josh Lerner – Ann Leamon – Felda Hardymon Chapter 5: Deal Structuring—Private Equity Securities and Their Motivation BASIC PRIVATE EQUITY SECURITIES Chapter 5: Deal Structuring—Private Equity Securities and Their Motivation PREFERRED STOCK AND ITS VARIATIONS Redeemable Preferred Convertible Preferred Stock Participating Convertible Preferred Stock Multiple Liquidation Preferences Exotic Securities (“Gingerbread”) Seniority and the Interplay of Multiple Securities Dividends Chapter 5: Deal Structuring—Private Equity Securities and Their Motivation TERMS Vesting Covenants Antidilution Provisions Chapter 5: Deal Structuring—Private Equity Securities and Their Motivation A TOUR OF THE TERM SHEET Chapter 5: Deal Structuring—Private Equity Securities and Their Motivation VENTURE AND MEZZANINE DEBT SECURITIES Chapter 5: Deal Structuring—Private Equity Securities and Their Motivation STRUCTURING A BUYOUT Debt CovenantsThis case considers the buyout of Panera Bread from the perspective of a private equity fund. In early 2017, KLG Managing Director Tom Denning is considering a leveraged buyout of Panera Bread, a rapidly growing fast-casual restaurant company. A surprising Bloomberg News story signals that the deal process is broadening and KLG will have to act quickly if it hopes to buy Panera Bread. Students assume the role of Tom Denning as he prepares an investment recommendation for KLG’s investment committee. In doing so, students are required to consider a very large and expensive investment. Students are challenged to create an investment recommendation by performing due diligence, determining additional questions to ask, and pricing a buyout bid that incorporates an optimal capital structure and meets KLG’s return requirements. The Panera Bread case is designed to give students insight into the private equity investment process. Resources Exhibit 1: US Limited-Service Revenues ($ in Billions) Long Description 2011 2012 2013 2014 2015 2016 Fast food sales $199 $206 $212 $218 $231 $244      YoY \% growth 3.7\% 2.6\% 3.0\% 5.9\% 5.6\% Fast casual sales $29 $32 $36 $40 $44 $47      YoY \% growth 10.3\% 12.5\% 11.1\% 10.0\% 6.8\%      as a \% of fast-food sales 14.6\% 15.5\% 17.0\% 18.4\% 19.1\% 19.3\% Source: Technomic State of the Fast Casual Industry, IBISWorld—Fast Food Report. Exhibit 2: Fast-Casual 2016 US Sales by Menu Type ($ in Billions) Source: Technomic State of the Fast Casual Industry. Long Description Exhibit 3: Fast-Casual Growth by Menu Type Menu Type 2016 Growth (\%) Pizza 37\% Seafood 25\% Healthy 21\% BBQ 18\% Chicken 17\% Burger 13\% Asian/Noodle 12\% Sandwich 11\% Bakery-Café 4\% Mexican −5\% Source: Technomic State of the Fast Casual Industry. Exhibit 4: Panera Bakery-Cafés 2012 2013 2014 2015 2016 Company-owned 809 867 925 901 902 Franchise-operated 843 910 995 1,071 1,134 System-wide 1,652 1,777 1,920 1,972 2,036 Source: Panera Bread FYE 2016 10 K SEC filing. Exhibit 5: PE Dry Powder ($ in Billions) Source: PitchBook 2017 PE & VC Fundraising Annual Report. Long Description Exhibit 6: Number of US PE Deals by Type and Year 2006 2007 2008 2009 2010 2011 2012 2013 2014 2015 2016 Q1 2017 Buyout/LBO 1,218 1,392 1,028 606 936 1,012 1,179 972 1,178 1,249 1,175 313 Add-on 1,058 1,491 1,132 792 1,192 1,404 1,587 1,528 1,999 2,086 2,163 573 Recap 12 14 8 6 13 19 28 56 48 39 24 8 PE growth/expansion 498 620 542 470 608 664 693 829 965 991 988 291 Platform creati