Chapter 8: Income Statement Forecast
Given our stated assumption that the percentage of revenue ratios for COGS, SG&A and depreciation and amortization for the explicit forecast range will be the same in years one through five as Year 0, we can now begin forecasting out our pro forma income statement.
Before we jump into our revenue projections, let’s first take care of an important number that we will come back to later on, depreciation and amortization. In order to arrive at our estimated depreciation and amortization for future periods we multiply the associated period’s projected revenue figure (F34) by our assumed depreciation and amortization-to-revenue ratio (E59), which in this example is 2%. Starting from cell (F54) the formula should be the following: =F34*F59. Upon pressing ENTER cell (F54) should be equal to zero, as we have yet to calculate the projections for net revenue.
You should not be overly concerned with the zero value in cell (F54) yet as this is merely the result of some prep work that is being done before we work through the full income statement projections. The pro forma estimates for depreciation and amortization will populate automatically once we bring in our forecasted revenue figures. For now, let’s finish our work for pro forma depreciation and amortization by applying the same formula used in Year 1, net revenue x depreciation and amortization-to-revenue ratio, in years two through five. This can be done by starting in cell (G54) and multiplying the forecasted net revenue figure for Year 2 by the depreciation and amortization-to-revenue ratio in the same year and repeating the same formula for the following years. You can also use a keyboard shortcut.
The keyboard shortcut begins by starting in cell (F54). From (F54) hold down the SHIFT key while using the arrow keys to expand the highlighted area across to cell (J54). Once cells (F54) to (J54) are highlighted press CTRL+R. This keyboard shortcut copies the formula located in the far-left highlighted cell, in this case cell (F54), and applies that formula to the highlighted cells to the right (cells G54 through J54). In this case, cell (F54) is equal to the product of Year 1 pro forma revenue and the Year 1 depreciation and amortization-to-revenue ratio. By highlighting (F54) through (J54) and pressing CTRL+R we mitigate the risk of entering a formula incorrectly in any of the following pro forma years; however, we must be certain that the original formula is correct, otherwise we will be using an incorrect formula throughout the explicit forecast range. Similar to the CTRL+D keyboard shortcut, CTRL+R can potentially save you a considerable amount of time over the course of creating complex financial analysis. When using any keyboard shortcut you must be aware of the inherent risks (e.g. copying errors) that go along with the benefits (e.g. time savings) of the shortcut.
We have set the table for building out the pro forma income statement forecast by laying out assumptions regarding annual revenue growth and the relative cost structure of the company over the explicit forecast range of five years.
Note that we did not change the relative cost structure of the firm from the current state; instead, we assumed that relative costs-to-revenue would remain unchanged over the next five-year period. In the case that we knew something in the future was going to affect one of the relative costs (either increase or decrease the relative cost-to-revenue ratio), at some point in the explicit forecast range and continue to do so into the future, we would adjust the affected ratio within the fiscal year of the change and continue to use the new cost-to-revenue ratio in the following years. (This assumes that the change in costs structure is of a more permanent nature and not a one-off occurrence that is the result of extraordinary circumstances for a brief period of time.)
“We have assumed that relative costs-to-revenue will remain unchanged over the next five years. If we know this won’t be the case, we adjust the affected ratio.”
Forecasting Year 1
All the pieces are in place to forecast out our income statement. The only thing left now is to do it. We start by focusing our attention on revenue for pro forma Year 1 (F34). We are assuming that revenue will grow at a rate of 7.0% per annum. In other words, net revenue in Year 1 should be equal to the prior period’s (Year 0) net revenue figure plus an additional 7.0%. In order to build a formula that does this correctly in our spreadsheet we start in cell (F34) and enter the following formula: =E34*(1+F52). Notice that we are simply making the revenue of Year 1 equal to that of Year 0 and multiplying by 1 + the assumed revenue growth rate of the respective year.
At this point, you could continue to fill out the remainder of the pro forma years for net revenue in Row 34, but in the spirit of best practice we will instead focus on working down the line items of the income statement. You will eventually see why we choose to go about creating our analysis in this way. For now, it is only important to know that this method should save time over the course of building the LBO analysis.
Focusing on the COGS and SG&A section of the income statement, we have already spent a considerable amount of time explaining cost structure and the logic that goes behind the respective cost-to-revenue ratios that have been assumed for the explicit forecast range. We can now use these assumed percentage of revenue ratios to forecast out COGS and SG&A expenses in Year 1. Since we already know our forecasted net revenue figure for Year 1, we know that Year 1 COGS and SG&A will be 61.5% and 31.0%, respectively, of net revenue.
In order to reflect this in our LBO analysis we begin in cell (F37) and make Year 1 COGS expense equal to Year 1 net revenue multiplied by the forecasted COGS-to-revenue ratio of Year 1. The formula used in cell (F37) should look like the following: =F34*F57. Similarly for SG&A you will make Year 1 SG&A equal to Year 1 net revenue multiplied by the forecasted SG&A-to-revenue ratio for Year 1. Your SG&A expense ratio should be entered in cell (F38) and should be expressed as the following: =F34*F58.
You may wish to use a keyboard shortcut in this situation. Given that we are using a common input (net revenue in cell F34) and that we have cleverly set up our percentage of revenue ratios for COGS and SG&A in the same order as they appear in the income statement, we can save valuable time and a few key strokes by taking a different approach to setting up our formulas from the very beginning. Starting from Year 1 COGS (F37), rather than jumping directly into entering the formula into the cell, we begin by pressing the SHIFT key and arrow down key to highlight both Year 1 COGS and SG&A, cells (F37) to (F38), respectively. After this we will enter our formula the same as we did in the prior explanation for COGS expense, but with one exception. This time we will enter the formula with an anchored row for the net revenue input, which is represented by the dollar symbol, $. This will allow the formula that we use for Year 1 COGS to be carried down one row to Year 1 SG&A expense while maintaining the same revenue input (F34), but adjusting down one row to take in the SG&A percentage of revenue input for SG&A expense. All other aspect of the formula will remain the same. Your shortcut formula should look like the following: =F$34*F57. After entering this altered version of the formula press CTRL+ENTER. This will ensure that the formula is applied across all of the highlighted section. You have now successfully cut the number of required formula inputs in half!
This brings us to the line item of other expenses. For the purpose of this analysis we will assume that other expenses remain constant throughout the explicit forecast period. This should be considered a reasonable assumption, as this expense item is not a core expense that is inextricably tied to the scale of business. Rather, this expense line item is made up of non-core expenses that should not fluctuate a great deal with any change in the size of the business. This is the simplest type of formula we will use in our LBO analysis. We will simply set our Year 1 other expenses equal to that of Year 0 other expenses. Starting from cell (F39), enter the equation for Year 1 other expenses as the following: =E39.
“We have assumed that relative costs-to-revenue will remain unchanged over the next five years. If we know this won’t be the case, we adjust the affected ratio.”
Now let’s calculate the total expenses by summing our COGS, SG&A and other expenses for Year 1. This can be done by entering the following equation: =SUM(F37:F39). As an alternative to writing out the entire equation for Year 1 total expenses you may simply wish to press CTRL+R from the Year 1 total expenses cell. This will apply the formula from Year 0 total expenses to Year 1 total expenses. This is an acceptable alternative only because we are using the exact same formula to calculate total expenses in Year 1 as we did in Year 0.
We have reached a milestone in our LBO analysis. We are now able to calculate operating income for our first pro forma year in the explicit forecast range. Since we already have calculated operating income in the historic Year 0 financials (operating income = net revenue - total operating expenses), we can use this same formula and apply it to our just completed Year 1 operating items. Rather than writing in the formula manually, let’s try using the keyboard shortcut, CTRL+R, to bring the Year 0 operating income formula over to the right by one cell. Doing this will complete the operating income section in our Year 1 pro forma income statement.
Extending Our Projections
Given that we have completed Year 1’s pro forma operating income we should be feeling fairly good about the work that has been done to this point. We only have four more pro forma years left before we complete the explicitly forecasted operating income section of our LBO analysis.
However, before we move on we have one last item to cover. The format and arrangement by which we originally set up our analysis was not by accident. Ideally, when setting up your leveraged buyout analysis (or any financial analysis), the format of your analysis should be arranged so that you are able to apply logic and formulas across any following pro forma projections as you desire, eliminating the need to duplicate your efforts in any following years of the explicit forecast range. Since we have taken care to ensure that we can apply our logic and formulas across all of the projected years in the explicit forecast range, we can complete all five years of net revenue, operating expense items, and operating income projections in a few key strokes. This can be done by copying the formulas we already put in place for pro forma Year 1 and pasting them in years two through five. This can either be done for each individual cell in the operating section of the income statement or a keyboard shortcut can be used.
Using the keyboard shortcut to this step begins with selecting or highlighting the entire operating section of the income statement. We start from Year 1 net revenue in cell (F34). While holding the SHIFT key, use the arrow keys to navigate down and to the right until reaching the final pro forma year of projected operating income (J42). At this point the entire operating section of the income statement should be highlighted. You are now ready to bring the formulas from pro forma Year 1 into pro forma Years 2–5. Press CTRL+R and all five years of pro forma operating projections should now be completed. Of course, these projections are based on our earlier assumptions and changing any of the assumptions that were laid out earlier should duly affect the projections that you see before you and ultimately affect the valuation of the company under consideration (to some degree) in our leveraged buyout analysis.
Checkpoint: Are We Right?
At this point in time we have completed a fair amount of work in the process of creating our leveraged buyout analysis. We have brought in historical financial information of the potential buyout target, ABC Company, and calculated some essential operating ratios, all of which plays a key role in shaping the pro forma financial projections. We have made some assumptions regarding the future growth rate and cost structure of the company over the explicit forecast range. We have also forecasted and calculated operating income in the explicit forecast range for the target company. Considering that we have come this far it may not be a bad idea for us to check our work at this point.
“We’ve now completed a fair amount of work in the LBO analysis, which makes it a good time to check our work so far.”
There is no one correct way to check the work of your leveraged buyout analysis, as long as it proves effective. One method you may consider for auditing the operating section of the income statement is spot-checking the formulas you have copied over from pro forma Year 1 by working through the line items of the final pro forma year, in this case, pro forma Year 5. Perhaps the most effective way to do this is by simply working backwards. Begin with the final calculation, in this case, operating income for the pro forma Year 5 (J42). Once your active cell is pro forma Year 5 operating income press the F2 key. This will place the active cell in formula editor mode and will make it easier to view the source input that makes up your formula for Year 5 operating income. Upon opening formula editor mode in pro forma Year 5 operating income you should see that the formula we have in place is correct. We are starting with the net revenues for the pro forma Year 5 and subtracting out the pro forma Year 5 operating expenses as we intended. This may seem like a time-consuming process, but with practice you will become very efficient in checking the source inputs of your formulas using the F2 key.
Before moving on, there is one other powerful check we will demonstrate. There is nothing worse in the process of building a leveraged buyout analysis than coming to the end of your financial model only to realize that a critical error has been made and then having to spend valuable time searching for the source of the error. To ensure that your time is not wasted, it is a good idea to periodically check your work and save different versions of your financial model (preferably in different locations) along the way. This next check will help ensure that your formulas are working as you intended and provides a quick means of auditing formulas of varying complexity.
A good way to check formulas are working as you intended is to substitute an input variable with the number one for multiplication and as the denominator for dividing within formulas. Likewise, the same is true for substituting a zero for added or subtracted input variables. This is particularly useful when dealing with complex formulas, when the result of the change in input variables is not so easily apparent.
Our simple example of this check put into practice begins with the pro forma Year 5 net revenue (J34). Open up the formula editor by pressing F2. Once in the formula editor, substitute the net revenue annual growth rate input variable (J52) with the number 0 (zero). (Note that this is a multiplication formula, but because the input variable is already being added to the number 1 we will substitute in a zero.) We are now multiplying the remaining input variable (pro forma Year 4 net revenue) by 1. Upon pressing ENTER we see that the result is, as expected, a number identical to that of pro forma Year 4 net revenue. This should not be a surprise as the formula we began with was a simple formula; however, in more complex formulas the answer may not be so easily recognizable and using a check similar to the previous example may prove an invaluable tool in auditing calculation in your analysis. Based on the result we know that our formula is working correctly and we can move onto the next course of business.