LBO Case Study - Debt Servicing
- 04:20
Calculate the debt servicing for the senior debt tranche in a leveraged buyout model. Use the min function, the average function, and the absolute reference to make the model flexible and accurate.
Transcript
Now for the debt servicing, we're going to do a little base calculation here in rows 47 through 49. But it's a very simple base calculation. And the first thing I need to do is I need to anchor the number in the his last historical year, which is column F. And I'm gonna anchor that senior a debt balance by taking the balance from the sources and uses of funds from the senior debt. And my beginning balance is going to equal the ending balance of the prior year. And then I've got to calculate the issuance or repayment. Now for this line in row 48, what we want to do is try and make this as flexible as possible. We want to use every single dime of cashflow in cell G44, this 1,910 to repay the debt. But clearly, because the beginning balance is 12,536, you can't repay all that all at once. You're gonna pay as much as you possibly can do, but actually you want to pick the smaller number between the 12,536, and the 1,910. And we can do that by using a min function, the minimum of the beginning balance or the cash available. And I'm gonna make that negative by multiplying by minus 1. So in a situation where we have more debt than we can pay off, what will happen? The min function will take just the cash available where the cash available is bigger than the beginning balance. We won't overpay the debt. We'll just take the beginning balance. And if I just copy this right, you will see this because in the year that the debt can be paid off in 2029, the cash available is actually 3,332.6, but it's being compared with 57.9. And the 57.9 is smaller. So that is what gets repaid. So we actually have some surplus cash in that year to be waterfall down to repaying the next tranche of debt. Now, if we ran out of cash, let me make a kind of silly CapEx assumption of making that 10,000. So we're run out of cash. Can you see how the min function is so beautiful? Because what it does, it compares this negative 7,305 with the beginning balance that's smaller. It's a min function, but then it flips its sign. So that means it will actually be an issuance of debt. So the min function is just such a powerful feature. I'm going to control Z that change to go back to normal. And I'll calculate the interest expense. And the interest expense. I'm gonna go and calculate or pick up my assumption, which is the cost of senior debt. I'll absolute reference that and I'll multiply it by the average of the ending balances of last year. And this year I want to make it negative, so I multiply it by minus 1. So we've done our debt servicing for the senior debt tranche, or typically known as the term B tranche. So then we can calculate the cash flow of all for debt repayment after senior debt. And I'm gonna take the cash flow that we had originally, and I'm gonna add to what has been used for the issuance or repayment of the senior tranche. Now you may be thinking, why aren't we also including interest expense? Because surely we've gotta pay that. And the answer is yes, we do have to pay for that. But notice in row 40 when we calculated the cash flows to service debt, we did not start with EBITDA. We started with net income. And that 2290 in cell G40 is after, or at least will be when we wire the model up after deducting the 462 of interest expense. So I'm gonna hit enter and obviously we don't have any available cash because we are using the cash suite mechanism and every single dime is being used to service that senior debt. So then when I copy this across to the final year, you'll see that eventually once we've cleared the senior debt, then we get some surplus cash and that can be applied to the next tranche of debt.