Cash Flow Spreadsheet Description of Cash Flow Valuation Spreadsheet by bjf20887


More Info
									          Description of Cash Flow Valuation
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

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

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

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

92a2142b-5ece-49dd-a5d1-825075f12621.doc                                                       3

To top