• 107 06/22/2020

    In this post we will cover a simple debt recapitalization in a three-statement model. This is a continuation of the post titled “Adding a Loan to a Three Statement Model,” and relies on the same workbook, which can be downloaded here

    This post does not include the financing fee related to the new debt raise, but there are two worksheets in the workbook that explain the treatment of financing fees in a financial model. This post breaks down the process of updating a three-statement model for a debt recapitalization into two steps outlined below in bold text.
    Update the Debt Schedule for this New Debt Issuance.
    In this example we will be raising the exact same amount of capital as the business has outstanding at the time of the debt recapitalization. Changes are indicated by a small x on the left-hand side of the image. 

    Debt Schedule in a Three Statement Model

    Add the Balance Sheet Adjustments.

    Step two requires making an adjustment to the balance sheet in the transaction period (20X2). The Excel file may appear intimidating, but the process is simple. To adjust the balance sheet for the change in capital structure complete the steps that follow. Please refer to the image below the bulleted list.
    1. Link the historical values to the column marked by a rectangle with a red-dashed line. 
    2. Input the balance sheet adjustments visible under the columns titled “Debt” and “Oldco Debt.” The values under "Debt" (indicated with a light blue shaded header) link to the new debt schedule. The values under "Oldco Debt" link directly to the pre-closing balance sheet; the only difference is that the values are now negative. 
    3. Link the post-closing balance sheet back to the model.

    Balance Sheet Adjustments for Debt Recapitalization

    You will notice that the oldco debt balances have been zeroed out, and that only "New Long Term Debt" remains as a long term liability on the balance sheet in the projected period. The elimination of "Current Maturities of Long Term Debt" is expained under "Additional Commentary" below.
    In step 2 above the =SUM() function is used to calculate the post-closing balance sheet. The formula is visible in the image below. 

    Balance Sheet Adjustments Formula in Excel

    Current Maturities of Long-Term Debt: In this model, and in most LBO Models, the current portion of long term debt is not listed as a current liability. Instead the entire debt balance is listed under Long Term Liabilities. This makes the model easier to work with as the capital structure expands to include additional debt tranches. 
    Dividends: In this debt recapitalization we are using the new debt issuance to retire the old debt balance. This post will not include a dividend, but you can explore how a dividend payment would then be made by visiting this post.

  • 106 06/09/2020

    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:

    Debt Schedule in a Three Statement Model

    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.

    Debt Schedule in a Three Statement Model

    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. 

    Updated Debt Schedule in a Three Statement Model

    Sequence for Linking the New Debt Schedule to the Model (click on the image below for a full-size view):

    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.

    1. Link Total Interest Expense on the debt schedule to Interest Expense on the income statement.
    2. Adjust the Cash balance in period 20X2 to reflect the New Long Term Debt raise.
    3. Link New Long Term Debt on the debt schedule to the balance sheet.
    4. Link the changes in the debt balance in each period to the cash flow statement.

    Adding a Debt Schedule to a Three-Statement Model

    Please note that this update does not address the associated financing fees. This is addressed in the LBO Video Series

    Template available for download: Download Template.



  • 105 05/21/2020

    ***PDF available for download at the bottom of the post.***

    This section of the stock purchase agreement contains statements of fact and assurances made by the Seller that must be true and correct as of the closing date. In an aggressively summarized format, the Buyer is looking to have the following confirmed: 

    1. That the Seller has the authority to enter into the purchase agreement and sell the securities described, and that the share count and capitalization represented are accurate.
    2. That the Company is duly organized, validly existing and in good standing.
    3. That the financial statements provided are complete and correct and fairly present the financial condition of the Company.
    4. That there are no undisclosed liabilities.
    5. That the company has all required permits and operates within the boundaries of the law.
    6. That taxes have been paid and returns have been filed. 
    7. That Seller has provided information regarding the Company's litigation matters, employee matters, as well as the material contracts of the Company.
    Summarized even further into one sentence: The Buyer needs to know that the Company’s key assets are covered, that it can legally operate and that the Buyer is protected from potential liabilities that might have been the Seller’s responsibility or that grew under Seller’s ownership.
    Beyond this summarized format, the Representations and Warranties of the Seller or the Company can cover somewhere between 20 and 50 separate topics.  
    Focus of the Article: The type of business being acquired and the industry it operates in will have a big impact on which representations and warranties are of focus. For example, a manufacturer of lead-acid batteries for forklifts will be more focused on compliance with labor and environmental laws and the permits required to operate. In contrast, the acquisition of a business that has developed proprietary technology, say a database to manage hospital patients and predict outcomes, will place an emphasis on representations and warranties surrounding intellectual property.
    Stock Purchase Agreement Language: The pages that follow contain hypothetical language detailing the above sequence as it might appear in a stock purchase agreement. DOWNLOAD PDF




Models are:
A) really boring
B) pretty sweet
C) super important
D) somewhat easy
E) kind of hard
F) fun
G) all of the above



*Answers a, b, c, d, e, f and g are all correct.