Chapter 11: Cash Flow
As you will see in this section, cash flow will dictate the target company’s ability to pay down the debt of the contemplated leveraged buyout capital structure and ultimately drive returns for the potential buyers of the company. We will start our cash flow analysis with net income and add back non-cash charges from the income statement. In our example we will also make some simple assumptions about the operating requirements of the company as they relate to net working capital as well as capital expenditure requirements. These assumptions are made so that rather than get entangled in the details of basic corporate finance we can move quickly to focus on the unique aspects of leveraged buyout analysis.
“Cash flow dictates a target company’s ability to pay down debt – and ultimately drive returns for the investor.”
Cash flow and debt sweep analysis
Begin your simplified (but sufficient) cash flow by bringing in net income information from your income statement directly into the cash flow section for pro forma Year 1. As you may have expected, we are not interested in Year 0 at this time. In order to transfer the pro forma Year 1 net income result into your cash flow, be sure to reference the income statement directly by using the following formula in cell (Q34): =F47. Do not copy and paste the formula into the Year 1 cash flow section of your analysis, as this will result in an incorrect figure. By referencing the net income cell for pro forma Year 1 in the income statement your cash flow will automatically adjust to any changes in our net income projections going forward.
As we covered earlier, depreciation is a non-cash expense. In other words, depreciation is deducted from net revenues on the income statement, but no one at the company ever makes out a payment for the depreciation or amortization expense the company incurred for the period. As that is the case, we need to add back depreciation and amortization expense to net income as part of deriving cash flow for the period.
“In our example, we represent expenses as negative items because they are being subtracted from net revenue.”
In your simple cash flow section, add back the depreciation and amortization expense that is listed in the income statement. Make sure that you are aware of which items are represented as negative numbers and which items are represented as positive numbers, as this will affect the way you add items back.
In our example, we are representing expenses as negative items because they are being subtracted from net revenue. For this reason, we add depreciation and amortization back to net income by using a negative before the referenced depreciation figure. Just as we did for net income, be sure that you reference the depreciation and amortization expense directly from the income statement (while using a negative sign). This will allow your cash flow to automatically update with any changes that occur within the income statement. Your formula for adding back depreciation and amortization within our cash flow section (Q35) should look like the following: =-F54.
Looking down our cash flow analysis we next have to consider any capital expenditures we believe the target company is going to make over the course of the explicit forecast range. In this example, let’s assume that we believe the target company is going to make capital expenditure equal to the depreciation of the equipment is currently has. To represent this, begin by setting cell (Q36) as =-Q35. Apply this formula across the rest of the row and be sure to keep the negative sign in front of your equation. Doing this effectively says that the company will invest in new equipment at a relatively steady rate that mirrors the depreciation of existing assets.
The final item for consideration in the cash flow analysis is change in net working capital. Net working capital is defined as current assets less current liabilities and can be thought of as a measure of a company’s short term financial health.
net working capital = current assets - current liabilities
As a business grows, the requirements for working capital typically expand with the growth of the business. We assume that the target company in our example is no exception. For that reason, we assume that the change in net working capital will be 1.5% of net revenue. This simple assumption follows the fundamental understanding that net working capital requirements will grow with the expansion of the business, but it bears mentioning that in practice you may wish for a breakdown of each item that makes up net working capital and do an individual forecast for each based on the believed requirements of the company over the forecast period. For our example and for the purposes of staying within the scope of this book, we will make a safe and conservative assumption that net working capital requirements grow in line with the business as 1.5% of net revenues.
“As a business grows, the requirements for working capital typically expand.”
In order to reflect the pro forma projection for changes in net working capital correctly within the cash flow analysis portion of your model, begin in Year 1 (Q37) and multiply net revenue (taken from the income statement) by our assumed 1.5%, which we explained in great detail above. It is important to note that because the requirement for net working capital is increasing it means that cash will be spent to meet this requirement. For this reason, it is important to ensure that increases in net working capital are reflected as cash outflows (negative) and decreases in net working capital are reflected as cash inflows. In our example, we will use a negative sign before the 1.5% in cell (P37). In addition to this, because the 1.5% of net revenue assumption for change in net working capital will apply across all five years in the explicit forecast range, as a matter of good practice, we anchor the column when referencing the 1.5% net working capital assumption. At this point in time your formula should look like the following in cell (Q37): =F49*-$P37.
Anchoring is a command that, in the context of financial modeling, tells the spreadsheet not to change the anchored reference cell when the formula is copied and applied in different cells. During the course of constructing this leveraged buyout analysis or any other financial analysis dealing with assumptions across multiple pro forma years you will most likely want to use anchoring in your formulas so that your formulas can be applied across different rows or columns in just a few key strokes. (The alternative is re-entering the formula a separate time for each individual cell.)
A dollar sign is used in Microsoft Excel to represent anchoring. A dollar sign in front of the first portion of a cell reference name (the letter) means, ‘Do not change the referenced column for this input in the case of applying the formula in different cells’. (Example: $A1)
We used this kind of anchoring in our formula for projecting changes in net working capital. In that example we anchored column P with a dollar sign because we want to eventually apply the 1.5% portion of the formula to the following pro forma net revenues in Years 2 –5, which are placed in the following columns. So how do we go about applying anchoring to our formulas? The most obvious way is by simply entering a dollar sign at the point of entering the formula.
This is a fine way if you know where you will need anchoring at every turn in your model. However, it may become tiresome any time you realize after the formula has already been entered that anchoring should have been used with one of the input variables and you then have to enter the formula editor, move to the column or row section of the cell reference name and then press SHIFT+4 to get your anchoring dollar sign in place. A quicker way to get your reference cell anchoring in place is to use the F4 key. You will still need to be in formula editor and navigate to the cell you wish to anchor. Once your cursor is touching any part of the cell reference name (e.g. A1) hit the F4 key and dollar signs will appear in front of both the row and column indicators of the reference name. That cell is now being instructed to change neither row nor column in the event the formula is copied and pasted in another cell. Hit F4 one more time and the dollar sign only appears in front of the row indicator. Hit the F4 key a third time and the anchor is applied only to the column, allowing the reference row to move up and down with the copying and pasting of the formula in different cells. Finally a fourth stroke on F4 will remove all anchoring. The F4 key is a quick way to toggle through anchoring in the formula editor. You may wish to consider using it if you notice yourself doing a fair amount of anchoring in your financial modeling.
Some common examples of this are accounts payable and accounts receivable. When a company pays down its accounts payable (a current liability) it is, obviously, a cash outflow. Conversely, if accounts payable continue to increase without being paid down it is considered a cash inflow because we are able to buy such things as inventory with having to decrease the company’s cash balance. The same holds true for accounts receivable (a current asset). If accounts receivable is reduced, it is a cash inflow to the company. However, if accounts receivable is not reduced, but rather it increases as inventory continues to leave the store on credit – that would most certainly be considered a cash outflow.
Now that we have estimated all the components of pro forma Year 1 cash flow available for debt repayments we can sum these line items to project the amount of debt the target company will likely be able to pay down in pro forma Year 1 based on our assumptions. (Try using the ALT+EQUALS keyboard shortcut in this case to get your answer.)
“Summing Year 1 pro forma cash flow available for debt repayment gives us the figure that could be used to pay down the debt incurred in buying the company.”
In summing the Year 1 pro forma cash flow available for debt repayment components we calculate a figure of $372 million. This is the amount of cash generated by the business that, after cash expenditures required to grow and operate the business, could be used to pay down the debt that would be used by our buyout group to purchase the target company. This is an important figure in the leveraged buyout analysis because as a potential buyer of the target company it is important to know to what degree the target company would be able to pay down the proposed debt that would be used to purchase the company. Larger debt balances generally mean larger interest expense. Potential bidders for the target company would want to avoid entering into a transaction where they end up being the owners of a company that is drowning in its own interest expense and unable to pay down its debts. For that reason, the sooner the target company is expected to be able to pay down debt, the more attractive the leveraged buyout may appear to potential buyers.