Chapter 9: Interest
Setting the Stage
Looking down our income statement, we can see that the next section is interest income and interest expense. This section is central to the leveraged buyout analysis, but before we are able to project these figures we must first know more information regarding the leverage or debt to be used in the contemplated transaction. We will be able to forecast interest income and interest expense projections once we have done some work on the cash and debt balances we expect to be carrying over the explicit forecast range. For now, we can simply set up the interest portion of the income statement by entering the formulas that will eventually provide us with pre-tax income. To do this, we begin in pre-tax income for the historical period Year 0 (E45). In order to calculate pre-tax income in our example we will need to net all of the following: operating income, interest income, and interest expense. Notice in our example that interest expense is a negative number. From cell E45 your formula should look like the following: =sum(E42:E44). This formula will give you the pre-tax income for Year 0.
“Income and interest expense is central to the LBO analysis. We can forecast this section when we know the expected cash and debt balances going forward.”
We are able to use the SUM function in this case to calculate pre-tax income because interest expense is already a negative figure, so the addition of this negative figure has the same effect as subtracting expense represented as a positive number. In general, it is easier to quickly calculate through a financial model if line items that should ultimately be subtracted from revenue (such as expenses) are represented as negative numbers in the historical and pro forma financial statements.
During the course of constructing your leverage buyout analysis or any corporate finance model you will undoubtedly need to calculate the sums of sub-sections in financial statements, just as we did for total expenses in the income statement. The pre-tax income section of our example income statement presents us with an opportunity to demonstrate another keyboard shortcut that will without doubt save you a great deal of time over the course of building your leveraged buyout analysis.
As we mentioned earlier, the formula for calculating pre-tax income from our example income statement is: pre-tax income = operating income + interest income - interest expense
As a formula in your spreadsheet, we said the formula should look like the following: =sum(E42:E44). There are a number of different ways this formula could have been entered into your spreadsheet. You could have entered the formula in directly as notation taken from this book. You may have begun by pressing the equals sign and then typed in the SUM function followed by using the arrow keys to select the cells you desired to sum; you may even, Heaven forbid, have used the mouse at some point in the cell-selection process to reach the end formula: =sum(E42:E44). All of these options would get you to the desired end goal of having the correct formula in place to calculate pre-tax income. However, the keyboard shortcut that gives you the desired formula in the shortest amount of time would have been (starting from pre-tax income for Year 0, cell (E45)): ALT+EQUALS. This keyboard shortcut would give you the formula: =sum(E42:E44) in one combined key stroke.
This keyboard shortcut works in this case because the ALT+EQUALS shortcut automatically sums the contiguous cells that are directly above the cell that the formula is being entered into. This also means that the shortcut would not give us the desired equation if there were an empty row in between interest income and operating income, as we have between total expense and operating income. It pays to think ahead when constructing the format or layout of your leveraged buyout model’s financial statements because it will affect the way that you are able to use some of the keyboard shortcuts you learn in this book during the creation of your analysis. Of course, in order for this shortcut to be of any use to us we must also remember to represent expenses as negative numbers. Otherwise, in this case, we would have been adding interest expense to revenue, which would certainly have affected valuations in the end. There are many factors to consider when constructing the financial statements for analysis and using keyboard shortcuts, but over time these factors will become second nature as you develop your own style and favored shortcuts when constructing financial analysis.
Taxes
It is said that the only two certainties in this life are death and taxes. Unfortunately, LBOs do not escape the latter.
For the purposes of our example, we will assume a corporate tax rate of 35%. In the case of historical Year 0, we are entering 35% directly into cell (E61), but it should be noted that this is a simplification that is done only to allow us to instead focus on the fundamentals of building the LBO analysis. In practice, when dealing with historical financials, tax expense for the period will be explicitly outlined in the annual or quarterly financial statements. Based on the disclosure in the financial statements we can calculate the effective tax rate paid by the company by dividing the tax expense by pre-tax income:
effective tax rate = tax expense / pre-tax income
Determining the effective tax rate for a company over the past couple of years should provide a reasonable basis for making some assumptions about the tax rate the company is likely to be subject to in the near future (i.e. the explicit forecast range). However, this book is not dedicated to the subject of tax brackets, loopholes, or any other tax specialty and for that reason we will continue with the default US tax rate of 35%.
Now that we have brought in the tax rate of 35% for Year 0 we must also bring in the assumed tax rate for the target company in the pro forma years one through five. We will assume the same tax rate of 35% will apply in the explicit forecast range, as we have no reason to believe the tax rate will change for the company over the following five-year period. To do this we will set the values of pro forma tax rate assumptions in years one through five of our income statement equal to the historic Year 1 value or 35%.
Since we already have the value that we want to use in years one through five in Year 0 we can easily bring this number (the assumed corporate tax rate) across our explicit forecast range in only a few key strokes. We start off by moving to the Year 1 tax rate projection cell (F61). From here we highlight across to the Year 5 tax rate projection cell (J61) by holding the SHIFT key while using the arrow keys to navigate across. Enter the formula =E61 (while cells remain highlighted) and press CTRL+ENTER. Each of the highlighted cells now references the cell that is immediately to the left of itself. If we change the original cell (E61) all of five pro forma tax rates assumptions for years one through five in our explicit forecast range will change to that same number.
Although we have yet to bring interest income and interest expense into our pro forma analysis we must still continue to prepare the income statement to calculate our projections correctly once we get to that point. For that reason we will continue down the path of preparation, putting in place our formulas for calculating tax expense based on our newly formed tax rate assumptions for Year 1 through Year 5. We start by calculating the tax expense for Year 0. Tax expense is calculated based on the historical financial information we pulled from the target company’s annual filings that lead to the current year’s pre-tax income and our assumed corporate tax rate of 35%. The formula for calculating tax expense is:
tax expense = pre-tax income x corporate tax rate
In cell (E46) this equation is represented as the following: =-E45*E61. Tax expense is, after all, subtracted from pre-tax income and for that reason we enter this formula with a negative sign before cell (E45). This ensures that the product of this equation appears as a negative number in Year 0 of our income statement. This will also allow the easy use of the SUM function later on when we calculate net income.
“We have now arrived at the bottom line: net income.”
Now we have arrived at the bottom line, net income. You can go ahead and calculate net income using the SUM function we just talked about. We will be summing a positive pre-tax income number and a negative tax expense number. The SUM function will give us the net figure of these two numbers. Just as we demonstrated before, beginning in cell (E47), we enter the formula: sum(E45:E46). The keyboard shortcut ALT+EQUALS is not a perfect fit for this scenario, but you can still make it work by pressing ALT+EQUALS from cell (E47) and then holding the SHIFT key and pressing the up arrow key once, followed by ENTER. This will result in the correct formula to calculate net income in our pro forma income statement for Year 0.