Use this simple illustrative spreadsheet to determine how the distribution of proceeds upon winding up works for an investment fund. For simplicity, the distribution here is done as a "European waterfall" i.e. liquidation is done at the end of the fund life and not on a deal-by-deal basis.
For more of these topics, click here.
The buyout builder is a simple and illustrative calculator/model that takes in a few high level parameters of a business and tries to solve for the lowest possible amount of equity required to fund an acquisition. The key inputs of the target company required are:
Sales: Current year and next year's sales, including the expected growth in sales going forward
EBITDA margin of the business: A measure of cash flow and assumes that this does not change over the forecast period
Cash: The amount of idle cash in the target company's bank that can be used towards repaying existing lenders in a buyout scenario
Fixed assets: The level of operating fixed assets such as plant, property & machinery in the business. Does not include cash
Current bank debt: Amount of borrowings currently owed by the company
Net working capital: Defined as operating current assets - operating current liabilities. If you don't know this number, the default based on the industry average will be used.
Be sure to read the limitations on using this spreadsheet below too.
What are the limitations?
Capital expenditures are driven based on the average industry sub-sector. It is driven based on a percentage of sales.
The book of equity is an implied number here based on your inputs on fixed assets, cash, bank debt and working capital. Simplify and calibrate the balance sheet numbers accordingly if you wish to use exact numbers.
There is an option of NOT repaying the existing debt. The model ignores any interest accrued and principal repayments from this. For the best results, always set this to repay all outstanding debt.
The debt sizing algorithm
The repayment schedule is configured as a "sweep" i.e. it is automatically sized based on:
Loan Tenor which is loosely implied from the Debt/EBITDA multiple. A higher multiple translates to longer tenor and hence, higher debt amount
DSCR (Debt Service Coverage Ratio) - which drives the CFADS (Cash Flow Available for Debt Service) in the model.
Interest rate. The higher the interest, the lower the amount of acquisition debt
Garbage in = garbage out.
The dynamics in every buyout deal is different. Every financial model has its limitations. This spreadsheet does not seek to solve all of these factors but rather to illustrate flow of thought and produce a "quick and dirty" solution to understanding the indicative funds required for the transaction that you are looking at.
(Can't view? Try this alternate page: https://www.kennyng.com/lbo-builder)