Unformatted text preview: Transaction Analysis Chapter 16 What is a transaction?
A transaction alters the Capital Structure of the company, changing the amount and type of Debt and/or Equity that comprise the company Why might a company want to Why might a company want to refinance its debt? Lower interest rates Fewer restrictions on activities Fixed vs. floating interest rates Changes in maturity preferences The Intuition behind a Debt The Intuition behind a Debt Refinancing Transaction The company decides it needs to repay some existing debt (a use of funds). The funds for the transaction will come from borrowing some new debt (a source of funds). These result impact the firm’s beginning Balance Sheet The changes to the beginning Balance Sheet will ripple through the entire model. Building a Transaction Model
Building a Transaction Model Step 1: Build a Sources and Uses using lookup tables that will allow you to analyze a variety of potential transactions all in one model. Step 2: Link up the Sources and Uses section with the Balance Sheet So that the Sources and Uses drives the pro forma and projected Balance Sheet. Where do the transaction Where do the transaction adjustments come from? A transaction model includes a Sources and Uses schedule that shows what the money will be used for (Uses) and where it comes from (Sources). Every dollar we need for a transaction must come from somewhere, so the Sources always equal the Uses of funds Working through a model Don’t Delete: Column C
Don’t Delete: Column C One of the first things you're likely to notice about the Sources and Uses section is the phrase "don't delete" in column C. It's important not to delete these cells because we'll be building an Offset function that will refer to them Assume the firm has $89.5 million outstanding under a Revolver and has no longterm debt. The firm wants to refinance its Revolver with a Term Loan and/or Bullet Note. We'll look at three scenarios
We'll look at three scenarios Case 1: No Deal Case 2: Refinance all of the company's shortterm debt with term loans and senior subordinated notes Case 3: Refinance all of the company's shortterm debt with only senior subordinated notes Step 1 – enter the rate assumptions Step 1 – enter the rate assumptions as triggers Term Loan Financing Fee: 2.5% Sr. Subs. Financing Fee: 3.0% Term Loan Rate: 8.5% Sr. Subs. Coupon: 11.0% Step 2 – enter the source of funds Step 2 – enter the source of funds assumption Case 1: No deal is assumed, so the Sources and Uses should all be zeroed out Case 2: $30 million New Term Loan and $70 million Sr. Subs. Notes Case 3: $100 million Sr. Subs. Step 3 Sum the sources and uses
Step 3 Sum the sources and uses This will be a check to see that these two equal one another What if the firm wants to add $10 What if the firm wants to add $10 million of the proceeds of a deal to its cash balance for Cases 2 and 3. You'll note that Cash can be a source or a use of funds. In this case, we've found a USE for some of the money that we're borrowing add it to the cash balance. Cash would be a Source if we were reducing the amount of cash on the Balance Sheet. Step 4 – Calculate the fees
Step 4 – Calculate the fees Use the following formula to calculate the Term Loan and Sr. Sub. fees associated with the funds raised: Fee = Amount Raised * Financing Fee % These fees will eat us some of the cash that you’ve raised After raising $100 million and paying After raising $100 million and paying associated fees, how much is left over to pay down the Revolver? Repay Revolver = Total Sources – Sum of All OTHER uses (except Repay Revolver) Basically, we're making Repay Revolver the "plug" for the Sources and Uses NOTE:
NOTE: It is typical to plug at least one of the Sources or Uses to ensure that everything balances. Be careful though if you plug a debt piece for which fees are calculated as a percentage of the amount raised (e.g. Term Loan), you'll end up with a circular reference! Add lookup table functionality so that we can easily switch between the different cases. Transactions Lookup Table / Creating TRANSCASE Use the OFFSET function to Use the OFFSET function to choose the appropriate case The OFFSET function works as follows:
=OFFSET(Reference Cell, Vertical Offset, Horizontal Offset) Use Column C (the column just to the left of the first transaction case column) as the reference cell for your OFFSET function. The Closing Balance Sheet allows you to show the Balance Sheet effects of a transaction. There are three parts to a Closing There are three parts to a Closing Balance Sheet: Actual Column: This column contains the actual numbers from the appropriate starting Balance Sheet. Changes Column: This column shows the impact of the transaction. We show this all in one column. However, in more complicated models you may want to have two columns (debit and credit). Pro Forma: This column is the sum of both the actual column and the changes column and represents the Balance Sheet that flows through to the rest of the model. Now it's time to flow the Sources and Now it's time to flow the Sources and Uses into the Closing Balance Sheet. Flow each of our Sources and Uses into the Incr./Decr. column of our Balance Sheet, and the Pro Forma column will be the sum of the Actual and the Adjustments. First we'll link up the Sources, First we'll link up the Sources, starting with Cash. If cash is part of the financing for a transaction (a Source of Funds), the cash on the Balance Sheet will decrease. On the other hand, if a Use of Funds is adding cash to the Balance Sheet (as is the situation for Cases 2 and 3), cash should increase on the Balance Sheet. Next let's link up the Revolver Debt Next let's link up the Revolver Debt piece If a Revolver is a Source of Funds, then this liability will increase on the Balance Sheet. And if we're paying down the Revolver as part of a transaction, obviously we would decrease the Revolver. Term Loans and Notes
Term Loans and Notes The Term Loan works just like Revolver in that we have a space for a Term Loan in both the Sources and Uses sections. The Senior Subordinated Notes formula works in exactly the same way. Subtract Sr. Sub Notes in the Uses of Funds from Sr. Sub. Notes in the Sources of Funds to arrive at the appropriate Closing Balance Sheet adjustment. All that's left are the Financing All that's left are the Financing Fees. Financing costs associated with newly issued debt are Capitalized as assets and amortized over the life of the Financing. Note: Equity financing costs are expensed and would decrease the common equity balance. That's not our situation here Lump the financing fees together into Lump the financing fees together into one line: Capitalized Financing Costs In a more complicated transaction you may want to have separate line items so that the fees can be amortized separately according to the term of each debt piece. For instance, you should amortize each piece of financing costs over the term of the debt. Add the Term Loan Fee and Sr. Sub. Fee lines from the Sources and Uses to arrive at the adjustment to Capitalized Financing Costs on the Closing Balance Sheet. Sum the Actual and Adjustments Sum the Actual and Adjustments columns to the Pro Forma column. Do not change the subtotal formulas. The Pro Forma subtotals should work just like all the other rows to help you pick up any potential mistakes. When you're done, the Pro Forma Column should balance, and so should the Balance Sheet for every year going forward Change the transaction case trigger you Change the transaction case trigger you named "TRANSCASE" to cases 2 and 3. Make sure the Balance Sheet adjustments are changing appropriately for each case. Notice how the starting point for the model is different in each case and the proforma Balance Sheet ripples through the rest of the model. When we change the transaction case, the When we change the transaction case, the nonoperating items are also affected as the transaction ripples through the model Does that mean we're done? Not quite. We set up part of the lookup table to allow the interest rates to vary depending on which case is selected. These case triggers are not yet linked to the model! Just because we set up the lookup table for interest rates doesn't mean the rest of the model knows about it! Link up the Term Loan and Senior Link up the Term Loan and Senior Subordinated Notes Interest Rates to the output of the lookup table ALWAYS link to the output of the lookup table (the black Offset formulas). These case triggers can be set differently for each transaction case. Summary of Refinancing Summary of Refinancing Transaction The trick is to make sure that the Balance Sheet that starts off the model is pro forma (adjusted) for a transaction. These adjustments to the firm's actual balance sheet are shown as part of a Closing Balance Sheet. Closing Balance Sheet starts with the actual Balance Sheet, adds or subtracts any adjustments related to the transaction, and results in a proforma Balance Sheet. ...
View Full Document