Wall Street Prep

Sources and Uses of Funds Table: LBO Model Example Build

Sources and Uses Schedule Explained with Step-by-Step Example Calculation in Excel

Learn Online Now

Sources and Uses of Funds Table: LBO Model Example Build

Key Takeaways
  • What role does the sources & uses table have for leveraged buyouts?
  • When it comes to LBOs, what are the most frequent “sources” and “uses” of cash?
  • How does the capital structure (funding sources) impact the returns to private equity firms?
  • What are the major steps to modeling the sources & uses schedule?

Sources and Uses Table Summary

One of the main purposes of a LBO model is to evaluate how much an initial equity investment by a sponsor has grown, therefore we must evaluate the necessary initial equity contribution from the sponsor.

The proposed capital structure is among the most important return drivers in a LBO, and the investor usually has the role of “plugging” (with equity) the remaining gap between the sources and uses for the transaction to proceed and close.

Just like how the assets side must be equal to the liabilities & equity side on the balance sheet, the “sources” side (the total funding) must be equal to the “uses” side (the amount being spent).

From the perspective of the buyer in a LBO – most often financial sponsors (i.e., private equity firms) – one of the purposes of the sources & uses table is to derive the amount of equity that must be contributed towards the deal.

All else being equal, the less equity contributed by the PE firm, the higher the returns to the fund (and vice versa).

The returns to the investor are a direct function of the amount of equity required. Hence, the required equity contribution is among one of the most important considerations when deciding whether to proceed or pass on an investment opportunity.

Sponsors are incentivized to minimize the amount of cash equity required by limiting the purchase price and utilizing as much debt as possible to fund the deal – all while not placing an unmanageable level of risk onto the target company.

Uses Side

To begin, we recommended starting on the “Uses” side before completing the “Sources” side, as intuitively, you’d need to quantify how much something costs before thinking about how you’ll come up with the funds to pay for it in the first place.

The main cash outlay for all LBOs is going to be the purchase price (i.e., the total cost of acquiring the company). Here, the first step is to figure out the entry multiple and the appropriate financial metric.

For the vast majority of deals, EBITDA tends to be the metric that is used to determine the bid (purchase price), and this metric will either be on a last twelve months (LTM) or next twelve months (NTM) basis. By multiplying the entry multiple by the relevant financial metric, the purchase price can be calculated.

Besides the purchase price, the uses section also consists of the following two fees categories:

  • Transaction Fees: Costs associated with M&A advisory and legal expenses – with such fees typically estimated by multiplying the purchase enterprise value by a transaction fee percentage assumption (i.e. 2%) until more data is available to adjust accordingly
  • Financing Fees: Oftentimes called debt issuance costs, these are the payments to the 3rd parties involved in arranging the debt financing (i.e. administrative fees as charged by the lender, lender legal costs)

Transaction fees are expensed immediately upon transaction close whereas financing fees are capitalized on the balance sheet and amortized over the maturity of the debt despite the payments occurring upfront.

The “Cash to B/S” line item refers to the estimated amount of cash required to be on the balance sheet of the company upon the date of transaction close. This minimum cash balance must take into account the cash required by the acquired company to continue operating day-to-day without needing any external financing to fund its working capital requirements.

Sources Side

On the other side of the table, we have the sources of capital, which represent where the funding for the transaction comes from. The amount of debt used will normally be calculated as a multiple of EBITDA, while the amount of equity contributed by the private equity investor will be the remaining amount required to close the gap for both sides to balance.

The total leverage multiple will depend on the target company’s fundamentals such as the industry it operates within, competitive landscape, and historical trends (e.g., cyclicality, seasonality). When determining how much debt capacity a company has, investor judgment is required to gauge the amount of debt the company could handle – in addition to preliminary discussions with potential lenders, with whom there are typically pre-existing relationships and/or past experiences working together with the investor.

Other nuances such as management rollover are also going to show up in this section. In short, management rollover is when the prior management team uses their equity (i.e., their stake of the pre-LBO company and exit proceeds from the sale) to help fund the transaction.

Management rollover is usually perceived as a positive sign because it shows that management believes in the company’s ability to implement its growth strategy and its future trajectory. To calculate the rollover amount, the total buyout equity value and the total pro forma ownership % that will be rolled over must be determined.

In absence of sufficient data, the rollover amount can be roughly approximated by multiplying the rollover % assumption by the total equity required.

Excel Template Download

Thus far, we’ve discussed the objective of the sources & uses table and its major line items. We’re now ready to move onto an example in Excel. To get started, download the file using the form below to follow along.

dl
Submitting ...

Purchase Price Calculation

In the first step, we’ll calculate the purchase price by multiplying the LTM EBITDA by the entry multiple assumption, which in this case comes out to $250.0m ($25.0m LTM EBITDA × 10.0x Entry Multiple).

Here, we’ll use the total enterprise value (TEV) because we’re assuming the transaction is done on a cash-free, debt-free (CFDF) basis. If a deal is structured as CFDF, the purchase price is the enterprise value to the buyer.

From the opposite end of the table, CFDF from the seller’s perspective means the seller gets to retain the excess cash on the balance sheet (excluding the cash required to continue operating), but in return, must pay off any outstanding debt liabilities using the proceeds from the sale.

Transaction & Financing Assumptions

Transaction and Financing Assumptions

Next, we can calculate the transaction fees. For illustrative purposes, the assumption for the transaction fees related to advisory fees paid to investment banks, consultants and lawyers, is that the amount will be equal to 2.0% of the total enterprise value. By multiplying $250.0m by the 2.0% transaction fees assumption, we get approximately $5.0m.

On a similar note, the financing fees can be calculated by adding up the total initial debt raised and multiplying by the 3.5% financing fee assumption. For modeling on the job, the financing fees are calculated individually for each tranche, but for this exercise, we’re using simplified assumptions and just using the total debt. Therefore the sum of the debt ($175.0m) is multiplied by the 3.5% financing fees assumption for us to get $6.1m for the financing fees.

To wrap up the uses section, the final line item is the “Cash to B/S”, which directly links to the hardcoded input of $5.0m.

Debt Financing

Moving onto the other side, the “sources” will list out where the funding for the transaction comes from. The predominant source of funds will be in the form of debt capital. Typically, a private equity firm attempts to raise as much senior debt (i.e., from bank lenders) before raising any other types of debt that tend to be costlier.

The relevant assumptions here are the total leverage multiple and senior leverage multiple. In our example scenario, the total leverage ratio will be 7.0x – meaning, the total debt raised will be assumed to be seven times EBITDA. Since the total leverage ratio is 7.0x while the senior leverage is 4.0x, the debt allocatable to subordinated debt is going to be 3.0x.

  • Senior Debt = $25.0m x 4.0x = $100.0m
  • Sub Debt = $25.0m × 3.0x = $75.0m

Rollover Equity & Sponsor Equity

Now that we have the debt portion filled out, we can now calculate the equity contributions. The two providers are going to be the existing management team (rollover equity) and the private equity firm (sponsor equity).

The total required equity contribution can be calculated by deducting the total debt from the total uses.

  • Total Equity Needed = $266.1m – $175.0m = $91.1m

Then, the management rollover can be calculated by multiplying the rollover assumption (pro forma ownership) by the required equity contribution.

  • Management Rollover = 10.0% × $91.1m = $9.1m

For the final step, we must calculate the sponsor equity (i.e., the size of the equity check from the PE firm) now that we have the values of the total debt raised and the management rollover.

  • Sponsor Equity Contribution = $266.1m – $184.1m = $82.0m

Alternatively, we could’ve just multiplied the total required equity ($91.1m) by the implied ownership in the post-LBO company (90.0%).

Formulas

We’ve now completed filling out the sources & uses table and can wrap up by making sure both sides are equal to each other.

Because our total sources cell links directly to the total uses, it’ll be more practical for our formula to sum up all of the line items for each side as opposed to subtracting the bottom cell from each side. After doing so, we get zero as the output for our check, which confirms that both sides are equal in our model.

Comments
guest
0 Comments
Inline Feedbacks
View all comments
3-Day Private Equity Masterclass
Wednesday-Friday 10/21-23
9am-5pm ET

Learn to build advanced LBO models and deep dive into the nuances of the private equity deal process. Access the same program used to train PE Associates at the world’s largest PE firms.

Learn More
X

The Wall Street Prep Quicklesson Series

7 Free Financial Modeling Lessons

Get instant access to video lessons taught by experienced investment bankers. Learn financial statement modeling, DCF, M&A, LBO, Comps and Excel shortcuts.