Yes, we’re back with Part 3 of this case study… just in time for this deal itself to come crashing down (maybe).
And, of course, if you’re already a member of the Fundamentals or Advanced Modeling courses on BIWS or some combination thereof, you can get the complete, 25-part case study over there.
We’re just covering a few small segments of it on this site.
This time around, we’re jumping into the debt schedules and how to calculate net interest expense in an LBO model. Among other points, you’ll learn why many formulas for these calculations in other models are not particularly good – and how to improve upon them.
Recap of Part 1 and Part 2
In part 1 of this case study, you learned where to find data on the company’s financial performance, the market, and the deal itself and how to start setting up the model.
The main points: the setup for Sources & Uses and the deal funding are somewhat different because of Michael Dell’s rollover equity and cash contribution and the company’s repatriated cash from overseas subsidiaries.
Then in part 2 of the case study, we went through how to think about revenue and expenses and how to estimate revenue by segment, based on a combination of consensus estimates, past historical data, and our own channel checks.
Expenses were more challenging because the company does not disclose much useful information on a segment-by-segment basis.
As we’ll see in Part 5 of this case study, the lack of detailed information on margins by segment will be one of the key issues in this deal that might sway our final opinion on it.
I am skipping over many steps in between those parts and this part (the debt schedules) because they are less interesting and/or covered in the full case study linked to above.
Also, there are some differences between the Excel files here in Part 3 and the ones in previous parts – it was too confusing to maintain slightly different versions throughout multiple steps, so I’ve “synchronized” everything and the full case study and the files here are now identical.
Please go by these Part 3 files and ignore these small differences.
The main update since last time is the shareholder vote on the deal: as I write this, it has been postponed because the company didn’t have the votes to get the deal done.
Some institutional investors simply want a better price; few, if any, are likely to agree with Southeastern Asset Management that the company is worth close to $24.00 per share (fuzzy math).
Carl Icahn continues to pursue the company and put forth marginally better offers.
But the problem is that they’re not better enough to convince all opposing shareholders to go with his terms instead.
So things continue to drag on, and who knows where it will end up.
Debt Schedules and Interest Expense: Your Video Tutorial
(I highly recommend full-screening this video in 720p so you can see everything better.)
If you’re reading this via email, click here to view the video.
Table of Contents:
- 1:50: Why We Switched the Case Study Order
- 4:16: How to Find and Enter Information on the Debt
- 8:47: How to Calculate Sources of Funding and Revolver Borrowing Required
- 12:43: Mandatory Debt Repayment Formulas
- 15:02: Revolver – Optional Debt Repayment and Formula Logic
- 19:53: How to Handle Existing Debt
- 21:38: Fleshing Out and Linking the Debt Schedules
- 23:45: Interest Expense Settings & Setup in Excel
- 26:14: Calculate Interest Income and Expense and Link to Income Statement
Here are the documents you’ll need for Part 3:
That video explains everything and shows you all these formulas in Excel.
But maybe you would prefer to read, or you only watch YouTube videos when they consist of cats lighting each other on fire… so let’s move through this in text form as well.
Why We Switched the Case Study Order
First, notice that we switched the order from what was laid out in the case study instructions – instead of building in post-buyout add-on acquisitions first, we’re doing the debt schedules first.
That is mostly because it’s 10x easier to do it this way – as you’ll see in Part 4, the post-buyout add-on acquisitions make the model very, very difficult to interpret and modify because we need to add a bunch of extra columns to support them.
It is also more difficult to write and copy across the debt formulas with the acquisitions in place; it’s much easier to write them the standard way first and then modify them later on.
How to Find and Enter Information on the Debt
Technically, the financing is not 100% finalized yet but we can get a good indication by looking in the merger agreement on pages 102 – 110 (see the ZIP file above to get the documents and excerpts).
Here are the types of terms you’d look for (example for Term Loans highlighted below):
A few things to note here:
- All the spreads (yes, still relative to LIBOR even after LIBOR has been discredited). The Subordinated Notes have fixed interest, as is standard, but everything else is floating.
- Mandatory principal repayments each year are fairly low; 1% of principal for TL B and 10% for TL C and nothing for the rest.
- LIBOR Floor: You may not have seen this one before. The idea is to guarantee that the interest stays above a certain level, even when prevailing interest rates and/or LIBOR are extremely low (i.e. as in the current market environment).
Floors can be structured in different ways, but with the ones here they’re essentially saying, “If LIBOR is above 1.0%, great, use it – if, on the other hand, it’s below 1.0%, bump it up to 1.0%” (depending on which option is used to calculate the interest rate).
For a L + 350 bps Term Loan, for example, the interest rate would get bumped from 3.8% to 4.5% in Year 1 due to these terms.
How to Calculate Sources of Funding and Revolver Borrowing Required
Once you’ve entered this information, next you need to figure out how much debt the company can repay each year – both the “mandatory” payments (what the terms of the debt stipulate) and the “optional” payments (not required, but possible if early repayment is allowed).
The logic isn’t too complicated: the Sources of Funds are equal to the initial cash balance in a year, plus cash flow generated, less the minimum cash balance.
That way, we ensure that all excess cash can be used to repay debt and that we factor in cash generated over the course of the year… but also that cash never drops below the minimum required number.
Revolver Borrowing is trickier to explain, but the company draws on the Revolver if it cannot afford to make the mandatory repayment on debt and needs to borrow extra to do that.
It would be like if you couldn’t afford to repay your student loans in real life and had to use your credit card for temporary funding: not ideal, but required when you have a cash flow shortfall.
Notice how we are using a MAX function to calculate this. MAX and MIN are cleaner and more efficient than IF statements, so we prefer to use those whenever possible.
Mandatory Debt Repayment Formulas
These formulas are straightforward: you take the minimum of the previous year’s debt balance and the annual repayment amount.
So if you have $500 in debt left and the annual repayment is $50, you repay $50.
If, on the other hand, you only have $30 in debt left, you repay that entire remaining amount of $30.
Again, a MIN function is cleaner than an IF statement and better to use here.
The only tricky part is the formula for Total Existing Debt (i.e. if Silver Lake does not refinance Dell’s existing debt after acquiring them) – there, you have to add several items from the Cash Flow Statement to the prior year’s debt balance because the existing debt may have changed via those items on the CFS.
In the end, you calculate the Mandatory Repayment Total each year – this is the key number you’re comparing to the Sources of Funds to see whether or not Revolver Borrowing is required.
In the Base Case scenario, we never even come close to requiring it. For all its faults, Dell generates a lot of cash flow.
Revolver – Optional Debt Repayment and Formula Logic
At first glance, the formulas here seem very complex if you haven’t seen them before… but they are actually not that complicated.
There are only 3 cases you must handle:
- You have some cash flow left after making the mandatory repayments, but not enough to repay an entire debt balance – in this case, just repay what you can.
- You have enough left to repay the entire debt balance – in this case, do that.
- You have already used all your cash flow or have had to borrow something extra to make the minimum required payments – in this case, repay $0.
I’ve seen some models where people use ugly nested IF statements to check for these conditions. That is NOT an ideal solution because it makes the model harder to maintain and modify.
Instead, we always use variants of the following formula in all the modeling courses and in this case study:
- =MAX(MIN(Remaining Debt Balance, Cash Flow Available – Total Amount Spent on Repaying Debt So Far), 0)
Here’s how it corresponds to those 3 cases above, with sample numbers:
- Case #1: Remaining Debt = $100, Cash Flow Available = $100, Debt Repaid So Far = $50.
- =MAX(MIN($100, $100 – $50), 0) = MAX($50, 0) = $50
- Case #2: Remaining Debt = $20, Cash Flow Available = $100, Debt Repaid So Far = $50.
- =MAX(MIN($20, $100 – $50), 0) = MAX($20, 0) = $20
- Case #3: Remaining Debt = $100, Cash Flow Available = $100, Debt Repaid So Far = $120.
- =MAX(MIN($20, $100 – $120), 0) = MAX(-$20, 0) = $0
Case #3 is the tricky one – and that one only happens when there’s Revolver Borrowing (that’s the only way for the Debt Repaid So Far to exceed Cash Flow Available).
How to Handle Existing Debt
The idea is largely the same as what is covered above. The main difference is that we’re also factoring in changes from the CFS as well as mandatory repayments before this to figure out the remaining existing debt balance at this point in time.
Also, “Debt Repaid So Far” now equals all the Mandatory Repayments plus the Optional Repayment of the Revolver just before this.
A similar formula then extends down to all the rest of the debt tranches, except for the ones where early prepayments are not allowed (the ABL Facility, Bridge Loans, and Subordinated Notes).
Fleshing Out and Linking the Debt Schedules
It’s best to finish linking everything first before calculating interest, because the interest calculations make it harder to fix problems in the model.
This linking step is not too complicated, but it’s easy to make simple mistakes if you’re not careful.
Start by linking everything on the CFS – rows 270 through 277 – and pulling in both the mandatory and optional repayments from the debt schedules, making all debt payments negative.
The Revolver may increase if additional borrowing is required, but everything else should be $0 or negative.
Then, make sure the Net Change in Cash at the bottom reflects all of this and link all these items to the appropriate line items on the Balance Sheet each year.
Interest Expense Settings & Setup in Excel
Before you even think about doing this, press Alt + T + O in Excel (or Cmd + Comma on the Mac) and go to Formulas and set Workbook Calculations to Automatic or Automatic Except Data Tables, and make sure Enable Iterative Calculations is selected (Max Iterations = 100, Maximum Change = 0.001).
If you don’t do that, the universe will implode… or at least your Excel program will crash or become unstable / hard to use.
Calculating the annual interest rates in cells M294 through Q302 is straightforward: you’re just taking LIBOR in the given year and adding the spread, using MAX to factor in the floor, where applicable, or you’re linking to a fixed rate all the way across.
Before moving on, you might want to move through everything here and press F2 (or Ctrl + U on the Mac) in each cell to verify that your formulas are linked correctly – it’s easy to make careless mistakes since each formula is slightly different.
Calculate Interest Income and Expense and Link to Income Statement
So this is one case where we finally use the dreaded IF statement, mostly because MAX/MIN would not work as well here.
It’s a simple switch: if we’re allowing circular references, use the average balance in each year (e.g. for 2014, make it the average of the 2013 ending balance and the 2014 ending balance); if not, use the beginning balance, e.g. the 2013 ending balance.
Then, as the final step, you go back up to the Income Statement and link in the net interest expense there.
If you go to cell O16, you can toggle circular references and see the difference it makes.
In most cases, circular references reduce the net interest expense because debt gets paid off over time (but if the Revolver keeps going up and no debt is ever repaid, they could also increase the interest expense).
What to Do Next
If you haven’t already done so, download the documents above and start from the “Blank” file and try to recreate this on your own.
This part is actually easier than the other parts of the case study, so no excuses (OK, it’s still more than a “beginner-level” modeling test, though).
See if you can understand intuitively why and how these formulas work… and if you can improve upon what is here.
Swapping the order slightly, in Part 4 you’ll learn how to factor in post-buyout add-on acquisitions – good luck finding another freely available model or tutorial online that shows you that one.
And then in Part 5 you’ll get a complete 20-slide presentation on the deal that you can copy and paste and use in your own interviews when making investment recommendations.
Time frame: This case study is already finished, so these next 2 parts will be easy (easier). August or September at the latest.
The Rest of the Series:
- Part 1: Data Gathering, Setup, and Model Assumptions
- Part 2: Creating Revenue & Expense Scenarios
- Part 3: Completing the Debt Schedules and Interest Expense Calculation
- Part 4: Building In Support for Post-LBO Add-On Acquisitions
- Part 5: Answering the Case Study Questions and Presenting Your Findings