In this post we will cover the process of adding a new debt schedule to a three-statement model. For this exercise we will be using the three-statement model built at the beginning of the Integrating Financial Statements video series (template available for download at the bottom of this post).
Before we get started we are going to make some minor adjustments to the layout of the current debt schedule. Please see the two images that follow.
Old Debt Schedule:
New Debt Schedule:
The new debt schedule groups all of the variables related to any particular tranche of debt together. To make it a little easier to navigate the worksheet, each debt tranche now has its own header as well. The new debt schedule is visible in the bottom of the image (indicated by the red rectangle).
Changes are indicated by a small x on the left-hand side of the image.
This is an updated approach to projecting debt balances, which is more common in LBO models (as you will see in the LBO Video Series). Below you will see the new debt schedule populated for this new debt raise.
Sequence for Linking the New Debt Schedule to the Model:
Once you have built the new debt schedule, all that is left is to link it back to the three financial statements. This can be accomplished in four steps.
- Link Total Interest Expense on the debt schedule to Interest Expense on the income statement.
- Adjust the Cash balance in period 20X2 to reflect the New Long Term Debt raise.
- Link New Long Term Debt on the debt schedule to the balance sheet.
- Link the changes in the debt balance in each period to the cash flow statement.
Please note that this update does not address the associated financing fees. This is addressed in the LBO Video Series.
Template available for download: New Loan in a Three-Statement Model.