"Cash Flow Spreadsheet Description of Cash Flow Valuation Spreadsheet"
Description of Cash Flow Valuation Spreadsheet Spreadsheet: FCF valuation example.xls John Settle October 29, 2001 – Revised April 23, 2002 TAB: Income, Balance Sheet, Ten Year and Cash Flow These are copied from the Moneycentral site TAB: FCFF-FCFE This is constructed to calculate FCFF and FCFE using Damodoran’s definitions, as annotated on the spreadsheet. Notice that some interpretation is necessary to make the calculations. The following assumptions/methods are noteworthy. 1. To get after-tax EBIT (without the influence of interest), you need an effective tax rate. It is not enough to use taxes divided by pre-tax income, because taxes on the income statement are accrual taxes – i.e., they are not cash taxes. Cash taxes are the accrual taxes less that portion that is deferred: the change in deferred tax on the balance sheet. Thus, this entire preliminary calculation is made first. 2. The rest of the FCFF calculations are straightforward following the definition in footnote *: [(p 383 of Damodoran 2ed): FCFF = EBIT(1-t) + depreciation - capital expenditures - change in working capital] 3. For FCFE, the definition in footnote *** is from Damodoran 2ed, p. 382. After rearrangement, that equation is FCFE = FCFF - Interest Exp (1-t) - Principal repayments + new debt issues - preferred dividends. I am construing new debt issues minus principal repayments to include other liabilities. Changes in deferred taxes are not included because the deferred tax account is properly regarded as a part of equity. Even though it is, theoretically, an obligation to the government, it bears no financing cost, and it need only be paid if there is future income. The proper treatment for valuation is to project the timing of cash taxes paid; when capitalized, this will fully reflect the liability; there is no other liability to the government. 4. Notice that I’ve used a different effective tax rate for interest, on the grounds that tax deductions on interest are not likely to be deferred. This time, the tax rate is calculated as taxes divided by pretax income. TAB: FCF forecasts This is the most elaborate of the sheets, and provides the most opportunity for you to be creative. First, you need to tailor its structure to your own company. Next, you need to supply the critical assumptions that lead to the forecasts of the various items. Basic Arrangement Top panel This panel produces various ratios that will be useful for the forecast. On the left, the historical ratios are calculated from the relevant items in the financial statements. On the right, ratios that you provide are projected for each year into the future, and are used to make the forecasts. Bottom panel. This panel constructs FCFF and FCFE projections. It is almost identical in structure to the FCFF-FCFE sheet, differing only in (1) a couple of lines where the computation is nevertheless equivalent; and (2) by leading off with revenue and cost projections so as to calculate EBIT. The historical portion of this panel is rearranged from FCFF-FCFE so that the earliest year is the one farthest to the left. Top panel, Column B: “Forecast Parameters.” The purpose of this column is to provide a suggestion for the forecast ratios that might be used in each of the future years. In some cases, these ratios are used just as is; in others, they are modified in the short run but used in the long run. Where do these numbers come from? They come from you: you use the historical information and interpret it as best you can, along with whatever you’ve been able to find out about the current condition of the company and its plans for the future. Here are some noteworthy particulars: 1. For my purposes, I have divided the future into four parts: 2001-2002, when it is possible to make some fairly specific predictions, and particularly necessary to do so in the wake of September 11; 2003-2005, three years of exceptional growth for Southwest, as it expands to take over markets from weaker airlines; 2006-2013, when growth declines to 4%; and finally, beyond 2013, when I assume a constant growth rate of 4%. I have a period of declining growth because cash flow predictions are unreliable when growth changes abruptly (see more on this at point 4) These divisions are arbitrary. For your company, they should depend on what you know and are willing to predict. 2. The sticky-notes. I have inserted sticky-note comments into certain cells. These are really notes to myself, and generally accomplish one of two purposes. The first is to note the structure leading to the prediction of that particular item. For example, for 2001 gross capital expenditures, I find it easier to predict this number outright, then work backwards to get net P&E, whereas in the other years I just forecast net P&E. So I made a sticky-note to remind myself of this. The other type of note is a brief description of why I am making a particular prediction – for example, for 2001 growth rate of revenues, I put down –15%, with the note “Terrorist Effects,” indicating that this is the primary reason for the predicted drop this year. 3. Note that I am forecasting net P&E (other than 2001) as a predicted percentage of following year revenue. My reasoning for this relates to airlines – I figure aircraft are the primary P&E item, and they should be on hand with a small lead to the revenues. Also note that this forecast structure requires a 2014 forecast of revenues. That’s the only reason I have a 2014 column. I have highlighted the 2014 items in yellow, and I have also highlighted the 2013 forecast for net P&E in yellow. 92a2142b-5ece-49dd-a5d1-825075f12621.doc 2 4. The last column, the heading of which is highlighted in yellow, is an alternative forecast of 2013 in which all items just grow at 4%. This is designed to be a consistency check on the forecast. As I mentioned above, if there is an abrupt change in the growth rate, the cash flow consequences for particular items may suddenly pop up. For example, a sudden drop in growth will result in a large reduction of the cash flow into working capital, so that it would be less than the amount indicated by the long-run growth rate. The last column is designed to check on how far off the cash flow is in the last year from what it would have been if we had just inserted the constant growth rate for that year. In the case of Southwest, the discrepancy is not too bad, so this gives us comfort in going forward with the constant growth assumption for 2014 onward. TAB: Valuation This sheet pulls in the cash flows from the forecast sheet and makes the final valuation. Cost of Capital portion In this spreadsheet, the cost of equity is calculated using the 1926-2000 historical risk premium of large stocks (S&P 500) over long-term (i.e. 20-year) treasury bonds. The risk-free rate is the currently quoted yield to maturity on 20-year treasury bonds. The argument for using the long-term bond as a reference is that its return is more stable than short-term rates. This is particularly evident now, when the t-bill rate has reached very low levels. Otherwise, the cost of capital portion should be reasonably clear from the notes in the spreadsheet. Valuation portion The procedure is to get a value of the cash flows and terminal value, add cash, subtract debt, and divide by number of shares outstanding. For the second column using FCFE, skip the debt subtraction. Discounting is at the cost of capital. In this spreadsheet, the cash flows have been discounted to the beginning of 2001, then accumulated at the same cost of capital for 10 months to get to November 1, 2001. A couple of sticky-notes: 1. Cash is added back because either it will be used, thereby increasing the net cash flow in the forecast, or it will be paid out as a dividend, in which case it can be valued directly. (A third possibility, which can be real for some companies, is that the cash will just sit there forever – or a very long time. In this case, its value would have to be discounted for the time to wait until use.) 2. There is a circularity in this logic. To get a weighting for WACC, you need market value of stock, which means you need a market price. In the end, you produce an intrinsic value per share. You could, if you were so inclined, plug this back into the WACC formula and re-compute. The spreadsheet can do this for you. 92a2142b-5ece-49dd-a5d1-825075f12621.doc 3