Sign in or start a free trial to avail of this feature.
12. Cash Flow and Debt Analysis Part 1
To project the cash and debt balance for the next 5 years, we need to create a simplified Statement of Cash Flows for the combined entity. In this lesson, we calculate the amount of cash available for debt repayment
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 available for debt repayments in the combined company.

The Debt Paydown Schedule (00:26)
When evaluating a proposed merger or acquisition, we need to consider the projected debt position and cash position of the combined company. This helps us identify if the company can meet its debt repayments. We can create a Debt Paydown Schedule to analyze a company’s cash position. This is a simplified Statement of Cashflows that gauges a company’s ability to pay back debt.

Calculating Operating Cashflows (00:56)
The first section of the debt paydown schedule deals with operating cashflows. We take the Net Income, which is found in the combined Income Statement, then add adjustments for noncash expenses. In our model, there are two such expenses: depreciation and amortization, and amortization of financing fees. The relevant amounts for both are found in the combined Income Statement.
Next we add the changes in operating assets and liabilities. This works very similarly to a normal Statement of Cashflows. For each asset or liability we add the change for both the target and the acquirer, which we can find from their individual Balance Sheets. Once we add all these changes, we can calculate the total cashflow from operations for the combined entity.

Calculating Outgoing Cashflows (03:14)
After calculating operating cashflows, we must subtract various possible outgoings to identify the cash available for debt repayment. First, we subtract capital expenditure. We can identify projected capital expenditure in the Balance Sheets for both the acquirer and the target.
Second, we must subtract any dividends that will be paid. In our case, neither company plans to pay a dividend, so this item is zero.
Third, we subtract integration costs. These can be found as part of our synergy calculations. These costs should only be incurred for a small number of years following the transaction.
Finally, we must subtract existing debt repayments. If existing debt is not refinanced, then we must make principal repayments on the debt. If the debt is refinanced, then we do not need to account for existing debt here. Our model includes a switch identifying if existing debt is refinanced, so we can use an IF statement to identify if there is a cashflow here. In our model, we make the assumption that all existing debt from both companies will be repaid in the first year if the debt is not refinanced. This is a particularly conservative assumption, but it’s better to be prudent than to be overly optimistic.
In the previous lesson we performed accretion, dilution analysis to understand how earnings per share would react to doing this deal. While the earnings per share reacted very positively, we also learned that earnings per share is only part of the story because the deposition and the cash position of the company are neglected in earnings per share analysis. Under our income statement, I've included a new section call the Debt Paydown Schedule. And this is essentially a simplified statement of cashflows that enables us to gage the ability to pay back debt, and ensure that the company isn't taking on too much risk in doing this transaction. Although the earnings per share journal looks very healthy, we have implementation costs, debt principle payments, and working capital to worry about as well. So let's get started. I'll begin with net operating income, adding noncash adjustments and changes in operating assets and liabilities to create cash flow from operations. This projection will then be added to my cashflow from investing activities such as capital expenditure, and other payments to calculate my cash available for debt repayment. I will then include my principal repayments, and paydown of existing debt to finally calculate my closing cash balance. And I can compare my closing cash balance to the minimum cash balance that I've set above, which is 80 million.
Let's start by including net income on this row. So I'll scroll up and link to net income.
Next, I'm going to include my noncash adjustments, which is depreciation and amortization, and the amortization of financing fees.
So again, scrolling up. I'll find these numbers.
Now I need to include the changes in upwarding assets and liabilities. These are easily pulled from the acquirer and the target sheets. I'll begin by doing one for accounts receivable. To calculate my accounts receivable change is quite straightforward. I'll first look at the acquirer, and grab the change between 2017 and 2016.
And then I'll do the same for the target.
And I'll copy across for the remaining cells.
And off camera, I'll complete these calculations in a similar fashion for both operating assets, and operating liabilities. From our calculations, we can see that the combined density generates quite a lot of cash from operations each year. But there will be quite a lot of outgoings as well, including capital expenditure, integration costs, and also, potentially, existing debt repayments. For capital expenditure I'm simply going to link to the capital expenditure line in both tabs for the acquirer and the target.
So there's the acquirer, and the target.
Next we have dividends. And a far as we can tell, neither company is currently paying a dividend to shareholders. So I'll enter zero for each of these cells. For integration costs, we can find insynergies.
And this will only be the case for the first two years.
And then lastly, we have existing debt repayments. I'm going to assume that if the debt is not to be refinanced, that the existing debt will be paid off in the first year. Well, this is quite a conservative assumption, but it's always better to be conservative than optimistic when building financial models. When we're calculating existing debt repayment, we must first check if this debt is refinanced or not. To do so, I'll create an ffunction and I'll scroll up to the top of my model.
And here, I'll anchor the cell, and I'll check if the cell is equal to one. If the cell is equal to one, then I'm going to return zero, because the debt has already been refinanced. Otherwise, I'm going to return a combination of the outstanding debt in the acquirer and in the target. Which I can find on the balance sheet.
There's the acquirer, then here's the balance sheet.
And here's the target.
And then I copy across for the remaining cells.
And as you can see, all these values are zero, because we're refinancing the debt currently. Let's now find the amount of cash available for debt repayment.
And in the first year, this equals to 84.9 million.
We now need to pay down some of the principle on our debt balance. And I'll show you how to do this in the next lesson.
Sign in or start a free trial to avail of this feature.