(Private Placement: Cash Deal Financed by Debt and Sponsor’s Equity, Structured as a Stock Sale)
Staples Inc. Financial Model is a financial model of a North American Retailer private placement by Sycamore Partners L.P., the US private equity firm. The company has been struggling for two years prior to the take private, has been selling assets, spinning off divisions and closing down stores. The deal is a cash deal financed by mostly debt and sponsor’s equity, and structured as a stock sale. The model is ideal for investment banking, equity research, retail and private equity finance professionals, valuation and transaction experts. The model includes the following features:
- A 3-statement financial model of this specialty store, retailer operations under the new management assigned by the private equity firm including: revenue schedule, cost schedule, depreciation schedule, working capital schedule, tax schedule, debt and interest schedule, shareholder’s equity schedule, covenants and credit statistics, income statement, cash flow statement and balance sheet for a four year forecast period.
- Internal rate of return (“IRR”) schedule, cash on cash return and value added at the operating company (“OPCO”) and the holding company (“HOLDCO”) levels given the initial investment in OPCO and HOLDCO by the sponsors in Q2/2017, the assumptions made with respect to the OPCO dividends and its use, HOLDCO debt payments, calculation of paid-in-kind and cash interest expense and its treatment in HOLDCO debt schedule, and quarterly exit throughout 2020 and 2021.
- Sensitivity analysis of the OPCO IRR’s with respect to exit multiple of EBITDA.
- Leveraged buyout model along with the sources and uses of funds at OPCO and HOLDCO given the stock sale financed mostly with long term debt, asset backed revolver, bridge loan and issued notes while refinancing the existing long term debt as of July 29, 2017 (end of 2017 second quarter date), transaction fees and purchase price allocation, and calculation of goodwill created on the pro forma balance sheet in the transaction given the deal tearsheet and financial disclosures.
- Recapitalized pro forma balance sheet of the company that is taken private.
- Three different methods and schedules for calculation of unlevered free cash flow (“Free Cash Flow to the Firm”, ”FCFF”, or “UFCF”).
- Valuation of the revamped company under the guidance of the new management team assigned by the private equity firm, based on intrinsic value approach.
- Implied share price as well as value of control per share under the management team assigned by the private equity firm as a result of their efforts to improve operating efficiency and capital structure.
- Quantification of risk measures for purposes of valuation including beta calculation, cost of equity and cost of capital calculation throughout the forecast period given the changing leverage of the company as debt is paid down.
- Calculation of basic and diluted shares outstanding given the offer price.
- Sensitivity analysis of the intrinsic valuation of the revamped company with respect to term year weighted average cost of capital and term year growth rate.
- Football Field chart of 52 week market share price high and lows and implied intrinsic value share price given a range of term growth rates and term cost of capital.
- Scenario analysis for Base, Best and Worst Case inflation and store growth rate and the resultant three statement financial model, implied valuation, and exit IRRs of OPCO and HOLDCO.
- An automated summary dashboard for Base, Best and Worst Case of revamped company financial report and credit statistics, market data, transaction and IRR data, implied valuation and value of control.
- The tabs of the models are: Cover, Summary, Assumptions, Sources and Uses, Scenarios, Model, Valuation, Shares, WACC and Notes.
- A report of the transaction and the financial model describing different steps and segments of the model, the transaction terms and company valuation, IRRs and sensitivities as of July 29, 2017, anatomy of a leveraged buyout (“LBO”) and criterias which make a successful private placement target along with the print ready format of the model is included with the package.
Staples Inc. Financial Model
- This financial model is a circular, 3-statement model along with a comprehensive leverage buyout model with many other features explained in the product description. Please ensure prior to opening the excel sheet, iterative calculations within the excel workbook environment has been enabled under: File/Options/Formulas/Enable iterative calculations.
- Set the Calculation options under File/Options/Formulas to ‘Automatic except data tables’. In case of the existence of data tables in the models, press F9 to refresh and update the table calculations.
- The workbook contains a Macro in the form of the product Disclaimer which should be accepted by the user; hence, please enable macros in the workbook. The financial model itself contains no macros.
- Please ensure the above steps are implemented prior to opening the excel sheet.
- If at any point #Value! appears in the Model and the results, toggle the circularity switch on the Assumptions tab to ON and then OFF to address the problem. This switch sets all interest expenses to zero when turned ON.
- If any changes are applied to the assumption inputs in the model, toggle through the Scenario Switch on the Assumptions tab to Base, Best and Worst Case so that the Summary tab results are updated.
- All blue font in the model are hardcoded inputs, black font is calculation, red font is the references to the blue inputs on the Assumptions tabs, and any orange cells are values sourced from Refinitiv.