• Forecasting Debt and Interest

    Debt

    The issuance and repayment of debt is a balancing act that takes into account the cash available to the company. Holding on to too much cash is detrimental to the company because of the opportunity cost while too little cash could result in a company being unable to pay off its debt obligations.

    The steps to calculate debt repayments are as follows:

    • Determine the free cash flow at the start of the year (everything but financing activities) and add it to the cash balance at the start of the year
    • Determine a minimum cash balance that the company would like to have on hand and subtract it. This figure represents the FCF available for financing activities.
    • Add/Subtract the cash flows from financing activities apart from debt. By now, these should all have been calculated.
    • Sum up the cash flows and that is what is available to repay the debt.
    • Determine the effects of net debt (issuance and repayment). Look in the management footnotes to determine when debt is due and when it is required.
    • Is the cash available enough to repay the net debt?
      • If so, then pay it off. The remainder is what is available to repay short term debt.
      • If the number is negative, then you must draw from the short term debt to pay off the long term debt.
      • Add back your cash reserves.

    Interest

    Using the management notes, calculate the historical interest rates charged on the debt. This amount may not match up with the interest expense shown in the income statement. This can be caused by the payment and repayment of short term debt or through the use of derivatives to convert floating to fixed or fixed to floating. It’ll be too difficult to reconcile the differences, so add a plug.

    Forecast the future interest rates based on debt and make reasonable assumptions for Other interest.

    Example: Cisco Debt and Interest

    Step 1: Create a new sheet and link the free cash flow and cash balance from the statement of cash flows and balance sheet. Estimate the minimum cash balance that the company should have on hand.

    Step 2: Add the cash flows from financing that are unrelated to debt. The sum of the FCF and cash from financing activities is the cash that the company has to service its debt.

    Step 3: Add the debt that the company has on its books and forecast it out. Go through previous year’s financial statements to get the figures. FY2010 and FY2009 figures can be found on page 62 of the FY2010 AR. 2008 figures can be found on page 66 of the FY2008 AR. Forecast future debt levels based on each note’s due date.

    Step 4:  Each year, a portion of LT debt becomes current. This is linked from the balance sheet to arrive at the net debt number. Link the historical figures first.

    Step 5: The financing cash flows are the respective sums of proceeds from issuance and repayment of debt. We can use a formula to calculate them instead of manually entering the figures.

    Step 6: Add conditional sums to determine the total issuance and repayment of LT debt. This will be linked to the cash flow statement. Only sum the lines that are related to long term debt.

    Step 7: Calculate the historical other line. This is the sum of the other line on the CF statement and the remaining debt line items.

    Step 8: We need to reconcile the differences between the cash flows from financing on the CF sheet with what we have calculated here.

    Step 9: Forecast debt repayment based on each note’s due date. For FY2011, 3000 will pay off the 5.25% notes due 2011. This leaves an extra $96 from the short-term debt that is unpaid. We will forecast this in the other line.

    The year before long term debt is paid off, that portion becomes current. Total long term debt must then go down and short term debt must go up. Make sure you understand why we are using the next year’s repayment forecast to predict the current year’s current portion of long term debt.

    Note the formula of the other line has also changed.

    Step 10:  Sum the free cash flow available for debt and the net proceeds from debt to determine the cash available to pay down the revolver.

    Add/subtract borrowing/paydown of ST debt and add your minimum cash balance to determine your ending cash balance. If the ending cash balance value is negative, then you may either issue more debt above or increase usage of ST debt. This is very much a judgment call.

    Step 11: Link the cash flow statement.

    Step 12: Link the debt line items to the balance sheet.

    Step 13: Link the ending cash balance from the CF statement to the balance sheet.

    Step 14: The last thing we’ll need to do is to estimate the company’s interest payments. Start by entering the interest rates of the notes. For 2008, you will need to refer to the financial statements to determine what the floating rate note is based on. Pg 40 of the FY2008 AR shows the effective rate the company is paying on its notes.

    Step 15: Estimate the interest payments by multiplying the amount of debt by the interest rate. Note that there are 53 weeks in 2010 which is why you need the week adjustment.

    Step 16: Once you have calculated interest expense, calculate the other interest line by finding the difference between what is on the income statement and what you calculate. We have elected to straight-line the other interest line item, but you may elect to leave it blank.

    Step 17: Finally, link the interest payments to the income statement. We have also straight-line forecast the Interest Income.

    About