Forum Posts

Kenny
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
17
Kenny
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
10
Kenny
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:
De-complicating "LBO" content media
0
0
8
Kenny
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
2
Kenny
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
14
Kenny
Jun 20, 2021
In Excel
This excel file below shows you how to reconcile a convertible instrument with the P&L, balance sheet and cash flow statements. It assumes both scenarios in which the instrument is repaid as a debt or converted to shares (equity) in the business. View the excel sheet here.
0
0
9
Kenny
Jun 20, 2021
In Business Valuation
Most of the online literature involving NPV and IRR tends to be very academic - defined as tools used to decide if a project is worth pursuing. Consider the set of cash flows below: The result of "NPV-ing" those cash flows based on a 10% discount rate give $487. But does this mean that the value of the project is $487? In the practical sense, how do we then use this analysis and information? Next consider if we "plug" the 487 back into the set of cash flows as the initial investment: This gives us an IRR of 10.0% i.e. paying or investing $487 into this project will allow us to get a return of 10% annualized over the investment horizon. Therefore, using the discount rate when calculating the NPV allows us to determine what is the maximum value we should be paying for the business i.e. investing anything more than $487 will not allow us to achieve our 10% return. This is one parameter (or boundary) in the negotiation process. Another parameter here which might not be obvious is whether as an incoming investor, do we have the ability or conviction that the projected cash flows will turn out better than expected. All things being equal, using different assumptions for the projected cash flows will result in varying IRRs, which will determine the entry price (i.e. the NPV) and hence, also our negotiation strategy in the bidding process.
0
0
11
Kenny
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.
Simple accretion/dilution analysis content media
0
0
8
Kenny
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
4
Kenny
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
7
Kenny
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.
Using the Match() function content media
0
0
3
Kenny
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.
Using the offset() function content media
0
0
3

Kenny

Admin
More actions