OfficeHours LBO Modeling Test 1 (3 Statement)

Overview

Most Megafund / UMM PE firms have a 3 hour modeling test in which they provide an income statement, balance sheet, and black excel workbook. You are not allowed to use shortcuts or outside resources, and we’ve even heard of some firms monitoring your screen via Zoom screen share during the test (with some unlucky PE Associate spending 3 hours watching you model).
Can you do the attached prompt in 2-3 hours? If not, email us back and we will send you the password for the answer key.
 

Background

  • $100MM unfunded revolver at close (2% interest rate, 0.5% commitment fee)
  • 3.0x (based on LTM EBITDA)  Term Loan B 5% interest, 1% amortization per year)
  • 3.0x (based on LTM EBITDA) Unsecured Notes (8% interest, no amortization) 5 year hold period
  • Financing fees of $20 million and transaction costs of $15 million
  • Management incentive plan will be adopted consisting of an option pool representing 5% of the company (assume $0 strike price)
  • Existing owners will roll $50 MM of their equity stake
  • Assume transaction close of 12/31/2020
 

Operating Assumptions

  • 7% Annual revenue growth
  • Gross profit margin decline 1.5% per year
  • SG&A kept constant as a % of revenue
  • R&D spend of $75 million per year
  • 25% tax rate
  • NWC: Project AP as a % of OpEx and Inventory as a % of COGS
  • Assume Capex spend is 2% of revenue
  • Assume that the existing PP&E has a useful life of 10 years, and any new PP&E has a useful life of 10 years
  • Amortize financing fees over the weighted average life of debt (assume 7 years)
  • $15 million minimum cash (0.5% interest income from cash)
  • Existing intangible assets are amortized at $1 million per year
 
OfficeHours Note
 
Some modeling tests will not provide detailed assumptions for calculating NWC entry; multiple calculation; leverage calculation; What to do with existing debt (refinance / roll); Debt tenor. In these cases, it is best to be familiar with commonly used approaches: NWC can most easily be calculated as a % of Revenue, or based on the above; Entry multiple is usually calculated using NTM EBITDA; Leverage is usually calculated using LTM EBITDA; Existing debt on the balance sheet is usually paid off in full; Common debt tenor is 7 years.