## Forum Posts

K

Jun 26, 2021

In Merger Modelling

The below spreadsheet illustrates how the resulting consolidated balance sheet of the acquirer looks like based on acquiring an additional interest in an existing associate company.

0

0

20

K

Jun 20, 2021

In Templates & Builders

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.

0

0

18

K

Jun 20, 2021

In Leveraged Finance

Many people get caught up in the term LBO (Leveraged Buy Out) when it comes to modelling. Plain terms An LBO is nothing more than a buyout of a business that is heavily funded by debt, usually 65% or more. In contrast to traditional LBO deals in the US, the concept and structure of harnessing leverage in Asia are relatively simpler, sometimes involving a couple of debt tranches. From a capital budgeting point of view, most buyers will seek to use debt funding as it is less costly than using (or raising) equity, but more importantly, financing an acquisition using more debt means that the amount of cash outlay can potentially be lower i.e. enhancing the returns to equity. Cash flows are critical. The choice and structure of debt funding used in an acquisition is largely predicated on the nature and quality of underlying cash flows. This is the most critical and fundamental aspect of all LBOs. Ultimately, the increase in equity value from a buyout comes down to essentially three factors: Growth Multiple expansion Deleveraging Consider this illustrative scenario: A company valued at 6.0x EBITDA at the point of acquisition based on a $100m EBITDA results in a deal value of $600m. Assuming the buyout was financed with 80% debt, the corresponding value of equity is $120m. Profitability growth Upon exit, let's assume that the EBITDA has grown by 20% to $120 and the company is valued higher at 7.0x, the resulting EV would be $840m. Because part of the acquisition debt has been repaid over the investment period, the net debt on exit has been reduced to 30% or $252m, giving an exit equity value of $588m. From the above, we can easily observe that we have increased the value of equity from $120m to $588m, which translates to a nearly 5-fold return. To break this down, let's first look at EBITDA. The value we've created here from improving the operating cash profits can be quantified as: Multiple expansion Secondly, in the process of driving the top and bottom-lines i.e. size and profitability, the financial sponsors have also changed the overall "risk profile" of the business, such as: expanding to new markets, developing proprietary technology in new products, institutionalizing sales processes and improving the overall quality of customers, etc. These enhancement initiatives make the business better, or what bankers commonly call: equity positioning or equity re-rating. The idea behind this is to re-position the company more favourably amongst its competitors so as to justify a valuation multiple premium. In this case, we had assumed that on exit, the company will be valued at 7.0x EBITDA. The uplift in equity based on this can be calculated as: Deleveraging Lastly, it's the additional value created from financial engineering. This is nothing more than just the reduction in net debt of the company from entry to exit. The value created is simply the difference between the net debt amounts: Putting it all together, we have: We have therefore effectively quantified the three sources of value creation to be a total of $468m. Although deleveraging drives most the value here, collectively, both improvements to the EBITDA and re-rating of the business post investment is also significant. Feel free to play around with the parameters using the below spreadsheet:

0

0

11

K

Jun 20, 2021

In Leveraged Finance

The spreadsheet below aims to solve for the lowest possible equity outlay in a buyout based on a few high level parameters.

0

0

5

K

Jun 20, 2021

In Merger Modelling

Use the sheet below to see how the balance sheet is being adjusted in a merger scenario between the target company and buyer.

0

0

24

K

Jun 20, 2021

In Excel

This excel file below shows you how to a convertible instrument is reconciled with the P&L, balance sheet and cash flow. It can be toggled between both scenarios whereby the instrument is repaid as a debt or converted to shares (equity) in the business. Layman explanation: A convertible instrument embeds both debt and equity features. The investor takes on equity upside (and downside), hence the coupon of the convertible is typically lower than its straight bond counterpart. To simulate this, set the CB coupon in the sheet above to the straight bond coupon, the equity component should be zero - implying that both CB and a straight bond are basically the same. To get the value of the straight bond, we do a simple NPV on the straight bond coupon payments and the principal at maturity. Because the coupon rate of the straight bond is higher than the CB coupon, the resulting value would be lower. This difference in value of the straight bond and the CB issue size is basically the equity value of the CB.

0

0

20

K

Jun 20, 2021

In Business Valuation

The definitions of NPV and IRR can be very academic. To better appreciate how this can be applied in real-life, consider the set of cash flows below: whereby the NPV of those cash flows can be calculated as C7 = NPV(C6,D5:I5) The result of "NPV-ing" those cash flows based on a 10% discount rate gives $487. What does this $487 really mean? We know that this is the "value" of the project but how do we make sense of this? Next consider if we "plug" the $487 back into the same set of cash flows as the initial investment: whereby: C16 = IRR(C15:I15) This gives us an IRR of 10.0% which basically means: "if we invest $487 into this project, our annualised return on this investment would be 10%." While the above calculations are scientific, the results do not necessarily translate to how we should go about evaluating a project in real-life i.e. even if the model shows $487, it does not mean that as a buyer, we should pay $487. Many factors influence the outcome of NPV and IRR. Cashflows for example are driven based on the post investment strategy and will drive and entirely different NPV given the same 10% discount rate. The input price (upfront cash outlay) is also determined by negotiation dynamics between buyer and seller. Always remember that while the calculations are scientific, and the 'soft factors' such as post-investment execution are usually the ones that drive the discussions around pricing and returns.

0

0

31

K

Jun 20, 2021

In Merger Modelling

This simplified worksheet illustrates the basic principle behind the merger of two entities. It is common knowledge that a merger will always be accretive is the target's price-earnings-ratio ('PER') is lower than the buyer's PER. Think of PER as a reverse yield on the business - intuitively, buying a company with a higher income yield will always make a good deal for shareholders of the buyer. In reality, not all target companies have lower PER than the acquirer. In some cases, the target company's PER could be marginally lower, which dilutes the effect of accretion. And if the deal can be funded with bank debt, this will also help improve the case for an earnings accretive acquisition. See the spreadsheet below in which company A is the buyer and company B is the target.

0

0

8

K

Jun 20, 2021

In Excel

Use the SUM(), OFFSET() and MIN() functions to dynamically compute straight-line depreciation for capital expenditure in any year.

0

0

8

K

Jun 20, 2021

In Excel

Use the INDEX() and MATCH() functions together to toggle selection between different sets of data. This is useful when you are trying to observe the outcome of the model based on different scenarios.

0

0

9

K

Jun 20, 2021

In Excel

Search for values in a series of cells. The result is the position of the 'matched' value in the set. See how it works in excel here.

0

0

3

K

Jun 20, 2021

In Excel

Use the offset function to reference a cell or a group of cells in excel. The offset function itself isn't very helpful unless you combine this in application such as modelling straight line deprecation. View the editable spreadsheet here.

0

0

3

# K

Admin

Followers

Following

Follow

©Since 2004 | About kennyng.com