Sign in or start a free trial to avail of this feature.
13. Cash Flow and Debt Analysis Part 2
In this lesson, we calculate the cash and debt balance projections for the combined entity. We also learn how to accelerate the debt principal payments if there's excess cash on the balance sheet.
To explore more Kubicle data literacy subjects, please refer to our full library.

Lesson Goal (00:04)
The goal of this lesson is to calculate the cash and debt balances for the combined business after the merger.

Debt Payments on New Debt (00:08)
After we calculate the cash available for debt repayment, we need to calculate the amount of these debt repayments. In our model, we break this down into payments on new debt and payments on existing debt.
To calculate the annual principal payment on new debt, we multiply the amount of debt issued in the transaction by the principal percentage paid back each year. Both these figures are found in the transaction assumptions.
We can also calculate the amount of principal remaining to be repaid at the end of each year by subtracting the principal repayment from the beginning principal amount for the year. In the first year, the beginning amount of debt is the amount of debt issued as part of the transaction. In later years, the beginning amount of debt is the ending amount of debt from the previous year.

Debt Payments on Existing Debt (01:47)
We also need to calculate the payments on existing debt. To calculate the beginning balance of existing debt, we add the debt balances for the acquirer and the target. These figures can be found in the Source and Uses table. However, the existing debt may have been refinanced. As a result, we use an IF statement to find the beginning balance of existing debt. This returns zero if the existing debt has been refinanced, and returns the appropriate amount if existing debt has not been refinanced.
The debt paydown on existing debt can be found by linking to the existing debt repayment item in the outgoing cashflows section of the Debt Paydown Schedule. The ending balance of existing debt for each year is then the beginning balance minus the repayment, and the beginning balance for the next year is the ending balance for the previous year.

Calculating Cash Balances (03:14)
After calculating debt repayments, we can calculate the opening and closing cash balance for each year. We can find the opening cash balance for the first year using our transaction assumptions. We take the combined cash balance of the two companies, and subtract the cash used in the transaction.
To calculate the cash increase or decrease for each year, we take the cash available for debt repayment, then subtract the cash spent on repayment of new debt and cash spent on the repayment of existing debt. The ending cash balance for each year is the beginning cash balance plus the change in cash, and the ending cash balance for each year becomes the opening cash balance for the following year.

Adjusting the Net Interest Expense (04:26)
After completing the cash balances, we can adjust the Net Interest Expense from the combined Income Statement. Previously, this calculation multiplied the total debt issued in the transaction by the interest rate on debt. We modify this calculation so that it multiplies the beginning debt balance for each year by the interest rate. As a result, the Net Interest Expense reduces each year as the company pays off debt.

Making Early Repayments (05:27)
In a situation where a company has a large amount of cash and debt, they may want to pay down the debt faster than required. In our model, we previously assumed that 10% of the principal amount must be paid off each year. As the company has a healthy cash balance, we will assume that any cash available for debt repayment is used for that purpose, with a minimum annual payment of 10% of the principal.
To achieve this, we use an IF statement to calculate the repayment on new debt. This IF statement checks if the cash available for debt repayment is greater than the minimum debt repayment. If so, the debt paydown is equal to the cash available for debt repayment, otherwise the debt paydown is equal to the minimum payment. Finally, we place this IF statement in a MIN function. If the debt paydown calculated previously is greater than the remaining debt balance, the MIN function ensures that the debt paydown is equal to the remaining debt balance only.
After adjusting the formula, we find that our debt balance reduces much more quickly than before. The closing cash balance does not change, because any cash generated is used to pay down debt. The one exception is in a year where there is not enough cash generated to make the minimum debt payment. In this case, some of the existing cash balance is used to make this minimum payment.
In the previous lesson we calculated the cash available for debt repayment. Now we need to find out how much of the principal must be paid back each year. Let's start by finding the debt balance at the top of the page.
Here we are and under this I have the amount of debt to be paid down each year. As I mentioned earlier in the course, this is going to be 10% of the debt amount.
So, I'll multiply total debt by 10%.
And if I scroll up to the top, you can see that I've created a new assumption of 10% in my transactions assumption. And I'll anchor this and multiply it by the total debt which again I can find at the top.
This is going to subtract from my cash available for debt repayment, so I'll make it a negative number.
And I will also subtract from the debt balance.
So, at the end of 2017, this will be my debt balance. So that I can copy across effectively, I'll make sure that both my cells are anchored for the debt repayment and that the debt at the end of 2017 is the same at the beginning of 2018 and I'll copy across all of these formulas.
And as you can see, the transaction debt will go from 462 million down to 256.7 over the fiveyear period.
Next I want to take a look at my existing debt balance and this is a little harder to create because my existing debt may already have been refinanced, so we'll start by creating an if function that will check if the debt has been refinanced.
And if this is equal to one, we'll respond with zero, otherwise we'll include the debt which I can find in my sources and uses of funds.
Here I have the acquirer's debt and the cell underneath, K28, will be the target's debt.
And as you can see, because the debt is going to be refinanced, our existing debt balance is zero.
As I mentioned earlier, we're going to pay down the existing debt balance in the first year if there is an existing debt balance. So, the pay down is simply going to be equal to this formula that we wrote earlier, so I'll simply link to this cell.
And the existing debt balance will simply be the sum.
And I'll copy across for the remaining cells.
Now we can calculate our cash balances starting with the opening cash balance. And again, I'll scroll up to the top and I can find the opening cash balance by taking the predeal combined cash balance and subtracting the cash used in the transaction which I can find in my sources and uses table and I'll link to both cells.
The cash increase or decrease is going to be the cash available for debt repayment plus the amount repaid and then the closing debt balance will be the sum of these two numbers.
Again, I'll link for the opening cash balance and copy across.
From our analysis, it appears that the combined company will have a very healthy cash balance by 2021 and what's more, the debt will have reduced substantially. Now that we have the transaction debt balance for each year, we can go back to our income statement and fix our interest expense calculation.
And as you might remember, I had this highlighted in yellow.
And to fix this calculation, I'll go back and select the interest rate which will be anchored and multiplied by the new transaction debt balance.
And this will serve to increase my earnings over time.
I'll make sure this is a negative number and then copy across.
And of course, I can also change the color now that this has been amended.
When a company has a situation like this, where the cash balance is growing rapidly but still with a sizeable amount of debt it may want to pay down the debt faster than is necessary. Based on our knowledge of the type of debt the Shirt Shop can garner for this transaction, I'm going to assume that this debt can be paid down quickly and faster than the 10% principal payment that we've already modeled and in my formula, I'm going to assume that any cash available for debt repayment is used with the proviso being that the minimum payment must be the 51.3 million. In addition, I'm going need to check to see what the ending transaction balance is to make sure that I don't pay additional money on the debt pay down when this balance is zero. So, let's jump into our formula and begin. So, the first thing we're going to do is create a min function and the min function will check if our current formula is greater than the transaction debt balance which I'll select here.
If it is smaller, then we need to write an if function and the if function will check if the cash available for debt repayment is less than our current formula.
If it is less than our current formula, then we'll accept our current formula which is the 51.3.
And if the cash available for debt repayment is greater, then we'll simply accept the cash available for debt repayment.
And then I'll close my brackets.
And now I copy across for the remaining cells.
When I do this, you can see that the ending transaction debt balance is now much smaller and what's more, our closing cash balance stays constant at 200 million for the last four years and essentially any cash that I'm generating each year from the business is going to pay down any debt that's owed. In the second year of our projection, we have an interesting anomaly where the amount of cash generated is less than the amount of debt required. In this scenario, our cash balance decreases.
If you look at our model, the reasons for this low amount of cash is due to integration costs but also a problem with inventory.
With our model now complete, in the next lesson we'll examine the implications for Shirt Shop and Trouser Town and run sensitivity analysis as part of our final recommendation.
Sign in or start a free trial to avail of this feature.