Description of Cash Flow Valuation
Spreadsheet: FCF valuation example.xls
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.
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
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
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.
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
Otherwise, the cost of capital portion should be reasonably clear from the notes in the
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