The question of how much debt should be paid down in a period is determined by a couple of factors. The first factor is a matter of cash flow. In the previous section we calculated the cash flow that would be available for debt repayments in pro forma Year 1. This figure is a function of net income plus our non-cash expense of depreciation, less any required capital expenditures, which we said would take place every two years, less any changes in net working capital. The result of this calculation will provide us with the dollar amount of debt that the target company is projected to have available from its pro forma cash flow. The second consideration when determining the amount of debt to pay down in a period is the actual amount of debt that is outstanding at the time of payment. If cash flow is available to pay down debt greater than the amount of debt currently outstanding, you would be well served to make a payment only for the amount of debt you actually owe. In other words, your debt repayment at the end of the period should be the smaller balance between 1) the cash flow available for debt repayment and 2) the balance of current outstanding debt.

“The question of how much debt should be paid down is determined by cash flow and debt outstanding at the time.”

Now the question is – How do we represent the logic of paying the smaller amount of cash available for debt paydown versus the current amount of total debt outstanding? The answer is the MIN function. The MIN function in Microsoft Excel chooses the smallest value among selected cells. In our example, we are making the comparison between cash flow available for repayment (Q38) and the beginning debt balance (Q41). It must be highlighted that in the event the target company is not profitable and instead has a negative value for cash flow available for debt repayment, the company would need to borrow additional debt in order to meet its financial obligations. This factor will also be incorporated into our formula.

The debt paydown or additional borrowing formula begins in pro forma Year 1 of our outstanding debt balance analysis (Q42). Debt paydowns will be represented as negative numbers, as these payments would reduce the size of the outstanding debt on the balance sheet, and additional borrowing will be represented as positive number, as these figures would increase the size of outstanding debt on the balance sheet. For this reason, whichever figure is selected as the smaller number we want to make sure it is correctly represented as an expense if it is a positive number and add the amount back to the debt balance if the result happens to be negative. In order to capture this, you must use a negative sign before the MIN function in your debt repayment or additional borrowing formula in cell Q42. (Example: -MIN) There are several aspects of logic that must be represented in your formula. However, the formula itself is short and fairly simple to look at. In cell Q42, debt repayment or additional borrowings, your formula should look like the following: =-MIN(Q38,Q41).

This formula will automatically decide whether the full amount of available cash flow should be used to pay down existing debt, whether only a portion of cash flow is required because there is more available cash flow than there is outstanding debt, or whether additional debt needs to be taken onto the balance sheet because cash flow was negative for the period. It is perhaps the most important decision point in the leveraged buyout model. Be sure to take the time to fully understand the formula and most importantly the logic behind it. Once we have completed the model, it may be worth the time to change some of the variables within the model and/or make direct changes in the debt repayment or additional borrowings line item in your leveraged buyout analysis to see the degree to which this item impacts the target company in our analysis.

It is worth noting that for virtually every prudent company there is a minimum cash amount the target company will keep on its balance sheet. As a matter of practicality, a company will almost always want to keep some cash on hand in order to make good on any short-term payables and operational costs that go hand in hand with running a business. This cash on hand is exempt from being used to pay down debts as it is essential to running the business on a short-term basis.

“It is worth noting that for virtually every prudent company there is a minimum cash amount they will keep on their balance sheet.”

Before we move any further on the topic of cash, we must first decide on the amount of cash that we will assume the target company will carry in the pro forma explicit forecast range. You can look at the recent historical financials of the target company to get a better sense of what the average cash balance carried on the balance sheet of the company has been over the years. It is, generally, safe to assume that the historical balances have been satisfactory in meeting the company’s short-term operating needs. For this reason, you may wish to assume that the pro forma cash balances carried on our projected balance sheet are approximately in line with historical value, while making any increase or decrease in the carried cash balance based on the projected growth or contraction of the business. We are assuming that net sales are growing at a rate of 7.0% annually, so we will assume our pro forma cash on hand is slightly larger than the cash balance of years past. We will assume that the target company will carry a cash-on-hand balance of $500 million. You may wish to make an annual adjustment to cash on hand to reflect the growth in the operations of the business. In our example, we will assume that the $500 million cash-on-hand balance is enough to cover the short-term business requirements in each year of our pro forma forecast range. That being the case, we will not need to make incremental changes to cash on hand in any of the following pro forma years.

“You may wish to make an annual adjustment to cash on hand to reflect the growth in the operations of the business.”

If anyone takes objection with the assumption of a stable cash-on-hand balance, you can easily make an adjustment in the balance sheet. The real question is – Where will the increased cash amount come from? There are primarily two sources from which the assumed increased cash on the balance sheet could come from; either positive cash flow or from borrowing the money. To make this adjustment in your pro forma financial statements, you could first denote the assumed increase in the cash-on-hand balance and then via formula direct the positive cash flow to the cash-on-hand section of the balance sheet. Once the cash balance requirement is met you can use the rest of the cash flow to pay down debt. In the event that you would prefer to use debt as the source for increasing the cash on hand, you would add the additional cash requirement to the debt balance, boost cash on hand to the new assumed required level and continue to pay down debt with any positive cash flow available to pay down debt.

We have explained the different ways in which you can handle cash on the balance sheet. In our example, we are using a stable cash on hand assumption over the pro forma explicit forecast range, as we believe this to be a sufficient amount of cash to meet the short-term business obligations of the company. It will also help in demonstrating the key points of our leveraged buyout analysis by not muddying the waters with unnecessary complication. The key aspects of cash on the balance sheet have been explained above; feel free to incorporate this logic into your leveraged buyout analysis once you have completed the example model.

Just as we did for the outstanding debt balance, we can think of one year’s ending cash balance as the following year’s beginning cash balance. This is important to realize because we will assume the target company will be earning interest income on the cash balances it is holding at the bank. (Presumably the target company is not storing its cash in a vault, deep in the basement at corporate headquarters and earning no interest.) Let’s enter our assumed cash-on-hand balance of $500 million into the pro forma cash analysis. The $500 million should be entered as the ending cash-on-hand balance of Year 0 (P48). Just as we did for outstanding debt, let’s now go ahead and make the following year’s beginning cash-on-hand balance equal to the prior year’s ending cash-on-hand balance. We will set Year 1 beginning cash balance equal to Year 0 ending cash balance. Your formula in cell (Q46) should look like the following: =P48.

“Where cash flow available to pay down debt is greater than the outstanding debt balance, the excess cash flow will be added to the cash-on-hand balance on the balance sheet.”

In the case where cash flow available to pay down debt is greater than the outstanding debt balance, the excess cash flow will be added to the cash-on-hand balance on the balance sheet. We will not assume the target company will use this excess cash flow for any purpose other than storing it away for a rainy day. In practice, once the target company has reached the point where cash is beginning to be stockpiled on the balance sheet as cash on hand, it may be time to look at other options for using the cash towards the profitable expansion of the business.

However, that is not our concern at the moment. Right now we must first focus on implementing the correct logic to effectively locate any excess cash from cash flow available for debt paydown and the outstanding debt balance into cash on hand. Remember, from a dollar and cents perspective, the reason this is a big deal is because interest income will be calculated based on the amount of cash that is carried on our balance sheet. For that reason, we certainly want to make sure that we are taking into consideration every dollar of interest income that we have coming to us. After all, we are in business to make money.

To do this, we begin in the additions to cash on hand line item in our cash analysis. We are not concerned with Year 0 at this point, so let’s start by looking at the line item in Year 1 (Q47). Here we can say that additions to cash on hand will be equal to the sum of cash flow available for debt paydown (Q38) and the paydown of outstanding debt (Q42) (which is represented as a negative number).

We have put logic in place with the debt additional borrowing or paydown (Q42) debt line item in such a way that if cash flow is negative, the additional debt borrowing line item will become a positive kick in new debt equal to the negative cash flow of the period. For that reason, we need not be concerned with ever getting a negative result for our additions to cash-on-hand line item.

Essentially, in calculating additions to cash on hand we are saying that if the target company has a positive cash flow, whatever balance of that positive cash flow is not used to pay down debt, take the remaining balance and put it in the bank as a cash deposit. Alternatively, if there is a negative cash flow for the period, we are not concerned with there being additions to cash on hand because we know from the logic we entered into the line item for additional borrowings or debt paydowns that cash on hand will not be affected. This is because any negative cash flow amount will result in a debt borrowing of an amount equal to the negative cash flow amount.

Translating this into a formula within your leveraged buyout analysis, starting in cell (Q47) your formula should look like the following: =Q38+Q42. Again, because debt paydown in cell (Q42) is represented as a negative number and additional borrowing is represented as a positive number (in the same cell), we are adding both rows together in order to determine how much cash should be added to the target company’s cash on hand, should cash flow for the period exceed total outstanding debt.

With the knowledge that the logic for additions to cash on hand is functioning properly we can now complete the cash portion of our analysis by calculating the ending balance for the period. To do this, we will simply add the beginning cash-on-hand balance and the additions to cash line item to arrive at the ending cash balance. This should seem fairly intuitive.

To calculate the cash-on-hand ending balance, we focus our attention on Year 1, cell (Q48). There, add the beginning balance for cash on hand for Year 1 plus the additions for Year 1. Your formula for the ending balance for cash on hand should look like the following in cell (Q48), =sum(Q46:Q47). Doing this completes the fundamental logic and framework for the cash, debt and income portion of our analysis, which is a significant milestone in the leveraged buyout analysis process! But we still have not applied the logic across five years in our explicit forecast range. Let’s do that now.

However, we are not finished with our leveraged buyout model yet! Based on the estimates for cash and debt that you were just able to construct we can now go back and determine what the estimated net interest expense will likely be for our target company.

A company’s ability to meet regular interest expense obligations and pay down debt is determined by the firm ability to make money. Ironically, we could not compute net interest expense earlier in our income statement because you cannot determine interest expense without knowing outstanding debt and the interest rate that is being charged on the debt. Yet we cannot forecast the target company’s debt balance (with the exception of Year 0) without knowing what net interest expense impact will be net income (because net income is the first component of determining cash flow available to pay down debt).

In short, the reason we skip interest income and expense in our original build of the income statement is because the net interest expense is dependent on outstanding debt, which happens to depend on the cash flow that is available to pay down debt. Essentially, each individual year’s forecast for the net interest expense will rely on the prior period’s earnings and the degree to which they were used to pay down debt. Each year interest expense is directly affected by what happened the previous year.

“The reason we skip interest income and expense in our original build of the income statement is because the net interest expense is dependent on outstanding debt.”

We can now revisit our income statement and bring in the final pieces that make up net interest expense. We first turn our attention to the interest income portion of our calculation. As you may recall, we calculate interest income as the beginning balance of cash on hand multiplied by the paying interest rate. (Note: If you are not able to find the interest rate paid on the target company’s cash-on-hand deposits explicitly identified in the annual or quarterly reports, you may also arrive at the interest rate paid by dividing interest income by cash and cash equivalents on the balance sheet.) Our example model starts from Year 0 and ending balances for Year 0 are the same as the beginning balances of Year 1. Interest begins accruing from the beginning of each year. In other words, we will be calculating interest from the beginning balance of each period. Our example does not have a period prior to Year 0, so we will calculate interest income starting in Year 1, but will assume that the cash balance at the beginning of Year 0 was the same as the ending balance.

To calculate interest income in Year 1 of the income statement multiply the beginning cash-on-hand balance of Year 1 by the interest rate paid on cash:

interest income = cash on hand (beginning balance) x interest rate

The formula for Year 1 interest income in the income statement should look like the following: =$P$46*P48. Make sure that you anchor both the row and column identifiers of the interest rate portion of the formula as we will want to use this same component of the formula when we apply it across the different beginning cash balances of the following forecast years.

Behind these seemingly simple calculations for interest income are the more complex calculations for debt paydown and additions to cash on hand, which will have a direct impact on the amount of interest income that is earned over a period. While the face of our interest calculation is quite straightforward, there is significant calculation going on in prior calculations.

Interest expense, as we mentioned before, is a key component of the leveraged buyout analysis. We can now forecast the target company’s interest expense on the income statement in a similar fashion to how we calculated interest income:

interest expense = outstanding debt (beginning balance) x interest rate

We will again be using the beginning balance, this time for outstanding debt, and multiplying it by the appropriate interest rate, or cost of debt. For the same reasons mentioned regarding interest income, make sure you are calculating interest expense on the beginning balances of outstanding debt. With active debt paydowns in effect, we can expect that debt balances at the beginning of each year will vary significantly and that means our interest expense figures will be changing as well. Double checking that you have the correct balance referenced will save you time and embarrassment if you find you are calculating interest expense on an incorrect debt balance, not to mention it may dramatically affect the outlook of the contemplated transaction.

“Double checking that you have the correct balance referenced will save you time and embarrassment.”

Let’s go ahead and enter our formula for interest expense. In our example, we assume that the debt from the leveraged buyout transaction will come onto the balance sheet in the end of Year 0. Obviously, there is no paying down of debt that occurs in Year 0. Year 0 is displayed to provide a historical reference against pro forma projections and provides a starting point at which we assume the debt comes onto the balance sheet (i.e. end of Year 0). In Year 1 (F43), interest expense should be calculated as the following: =$P$41*-Q43. Notice in the example that interest expense is represented as a negative on our income statement. Since outstanding debt balance and the interest rate on debt are both positive figures, you will need to be sure to include a negative sign before one of the reference inputs. This will ensure that you are representing interest expense correctly as a reduction from earnings, rather than as a contributor to earnings.

Now that you have completed the Year 1 interest income and expense portion of the income statement there is only one item left outstanding within the income statement. Let’s apply our interest income and expense formulas across the explicit forecast range to finish up the income statement. We can do this by holding down the SHIFT key and using the arrow keys to navigate and highlight the area from Year 1 interest income to Year 5 interest expense. Once the area (F43:J44) is highlighted, use the keyboard shortcut CTRL+R to apply our Year 1 formulas across the explicit forecast range. You have now completed the pro forma financial estimates for the target company in our example leveraged buyout transaction.

As you may have guessed, the scenario of annual cash flow available for debt paydown exceeding total outstanding debt will not occur during the explicit forecast range of the example leveraged buyout model that we are building. This is not the case because the amount of debt from the transaction that needs to be paid down is larger than the sum of the five years of cash flow available for debt paydown. However, you may wish to experiment with some of the model’s assumptions to see how this important aspect of the model works under different scenarios. First, try adjusting the revenue growth assumptions that we assumed to be 7.0%. Rather than the approximate historical growth rate of 7.0%, let’s assume that the revenue growth is that of the next hot technology start-up with estimated growth of 200% every year for the next five years. It may not be realistic, but it gets the point across. With these new revenue growth estimates we see in our cash analysis that additions to cash on hand are being recognized, as we desired. The target company will be able to both pay down its debt and begin storing away cash in no time. More precisely, before the end of Year 3 the target company will have paid down the complete amount of debt taken on during the buyout transaction and by the end of Year 5 the company will have stored away cash on hand of an estimated $234 billion. Not bad for our target company. Unfortunately for management, this is not a realistic scenario given historic performance and what we know about the company, but it proves to us that our logic for additions to cash on hand works correctly and also demonstrates the mechanics of what will take place on the target company’s balance sheet once debt is paid down and additions to cash on hand commence.

In order to do this we want to make sure that we are comparing apple to apples and not apples to oranges. For that reason, let’s set our Year 0 interest income and expense to that of Year 1 interest income and expense, respectively. We know that this change does not reflect historical reality, but it will allow us to compare the performance of the company under the same capital structure conditions. That is, by making the adjustment to Year 0 interest income and expense we get the chance to compare the historic performance of the company as if it was carrying the same approximate debt on its balance sheet as assumed under the contemplated leveraged buyout transaction. (You may want to compare both the adjusted and non-adjusted capital structure returns when conducting your analysis. In our example, we will look at the historical-adjusted returns because it requires more work and the non-adjusted returns do not require anything other than comparing historically given data.)

As we mention above, let’s adjust Year 0 interest income (E44) by setting it equal to Year 1 interest income. You can do this by simply entering the formula: =F44. After that, do the same thing for interest expense in Year 0 (E43) by setting it equal to Year 1 interest expense using the formula: =F43. The Year 0 income statement is now adjusted for the assumed change in capital structure. This can be done in different ways and the manner in which you choose to make the adjustment is not important. What is important is that you understand that in order to have an apples to apples comparison when it comes to financial performance, historical information should be shown on an adjusted basis (as well as a non-adjusted basis).