# Discounted Cash Flow - Excel

### Pages to are hidden for

"Discounted Cash Flow - Excel"

```					                                                               A special note of thanks goes to John Kish, Devon
FocusInvestor.com: The Focused Few                        Everhart, Futile France, and Kuang-Yu Liu for their
assistance on this project.
Version 3.0
Decent overview of the workings of a DCF Model click here

Introduction to the FocusInvestor.com Discounted Cash Flow Excel Calculator
One method an investor can use to determine the approximate value of a company involves conducting a discounted cash flow analysis.
An example of a series of assumptions an investor could use to calculate a companies intrinsic value under the three-stage DCF, for example, is listed below:

1:   an initial cash flow of \$10,000
2:   a discount rate of 15% (the user should insert his required return here)
3:   cash flows grow at a 14% rate for the first 5 years, a 10% rate for the next 5 years, and a 5% rate for the final 5 year period
4:   the investor uses a terminal growth rate of 3%
5:   the company has 10,000 shares outstanding and pays no dividends.

Boxes that are highlighted in black throughout the spreadsheets are where users can plug in their own assumptions

Discounted cash flow (DCF) As the name implies, a companies future cash flows are discounted back to the present to derive a valuation for the company in question. The DCF
Model is certainly not a perfect valuation tool (as it is very difficult to predict what a company will grow its cash flows at), but it can provide one estimate of value that, when
combined with all the other components of a users research on a company, can help give an idea of what a company is worth. The user should also keep in mind that the Intrinsic
Value that is produced is only as good as the numbers put into the model. If you assume unrealistic growth rates (or terminal value) you will get an unrealistic intrinsic value result.
The investor should think of the Intrinsic Value figure given in this light: you need to pay the price given, or lower, in order to achieve a given return (i.e. the discount rate) on your
investment if your assumptions are correct.

Limitations of my excel DCF Models: You can only use 5 year time periods unless you modify the spreadsheet. I will continue to work on this issue.

Terminal Value: The PV of the all the companies future cash flows.

Terminal Value Growth Rate: The rate that you expect the company to indefinitely grow its cash flows by.

Discount Rate: The investors required rate of return. NOTE: (If your growth rate is higher than your discount rate, the model will not function properly)

Note: This is a beta copy and as such may have some bugs. If any are found please email focusinvestor@yahoo.com so they can be corrected.
FocusInvestor.com: The Focused Few

Initial Cash Flow:          \$15,000

Growth Rate:                10%

Discount Rate:               15%

Shares Outstanding:            10,000

Present Value of All Cash Flows:   \$330,000

Intrinsic Value:            \$33.00
FocusInvestor.com: The Focused Few

Cash Flow From Operation
Initial Cash Flow:               \$774,039,000

Year Range:                            1-5          6-10
Growth Rate:                           7%            5%

Terminal Growth Rate:                  1%                   Discount Rate:      10%

Shares Outstanding:               478,564,000               Margin of Safety:   25%
Debt Level:                      \$1,356,000,000

Year                   Flows        Growth          Value
1                 828,221,730     7%          \$752,928,845
2                 886,197,251     7%          \$732,394,422
3                 948,231,059     7%          \$712,420,029
4                1,014,607,233    7%          \$692,990,392          \$3,564,824,343
5                1,085,629,739    7%          \$674,090,654          \$2,937,768,524
6                1,139,911,226    5%          \$643,450,170
7                1,196,906,787    5%          \$614,202,435
8                1,256,752,127    5%          \$586,284,142
9                1,319,589,733    5%          \$559,634,863
10                1,385,569,220    5%          \$534,196,915

Terminal Year          \$1,399,424,912                                                        Scenario Likelihood   Intrinsic Value of Scenario
Scenario   1:            75%                      \$41.60
PV of Year 1-10 Cash Flows:                                 \$6,502,592,867           Scenario   2:            15%                      \$35.00
Scenario   3:             5%                      \$20.00
Terminal Value:                                     \$5,994,876,487           Scenario   4:             5%                      \$18.00
100%
Total PV of Cash Flows:                                 \$12,497,469,354

Number of Shares:                                       478,564,000

Intrinsic Value (IV):                                       \$23.28

Margin of Safety IV:                                        \$17.46

Percentage of IV that derives
from the Terminal Value:                                         48%
FocusInvestor.com: The Focused Few

Initial Cash Flow:                     \$44,185,000

Years:       1-5         6-10           11-15
Growth Rate:                               15%         13%             15%

Terminal Growth Rate:                      1%                  Discount Rate:      12%

Shares Outstanding:                     29,200,000             Margin of Safety:   30%
Debt Level:                            \$225,000,000

Year                        Flows      Growth          Value
1                       50,812,750     15%         \$45,368,527
2                       58,434,663     15%         \$46,583,755
3                       67,199,862     15%         \$47,831,534
4                       77,279,841     15%         \$49,112,736
5                       88,871,817     15%         \$50,428,256
6                      100,425,154     13%         \$50,878,508
7                      113,480,424     13%         \$51,332,781
8                      128,232,879     13%         \$51,791,109
9                      144,903,153     13%         \$52,253,530
10                     163,740,563     13%         \$52,720,079
11                     188,301,647     15%         \$54,132,224
12                     216,546,894     15%         \$55,582,194
13                     249,028,928     15%         \$57,071,003
14                     286,383,268     15%         \$58,599,691
15                     329,340,758     15%         \$60,169,325

Terminal Year                \$332,634,165                                                      Scenario Likelihood   Intrinsic Value of Scenario
Scenario   1:           60%                      \$19.85
PV of Year 1-15 Cash Flows:                                    \$783,855,251           Scenario   2:           25%                      \$10.00
Scenario   3:           10%                       \$7.50
Terminal Value:                                          \$552,463,803           Scenario   4:            5%                       \$3.00
100%
Total PV of Cash Flows:                                     \$1,336,319,054

Number of Shares:                                           29,200,000

Intrinsic Value:                                            \$38.06

Margin of Safety IV:                                          \$26.64

Percentage of IV that derives                                        41%
from the Terminal Value:
FocusInvestor.com: The Focused Few
(in thousands)
Assumptions:
Sales Growth (Years 1-5):                      15.0%
Sales Growth (Years 6-10):                     13.0%
Terminal Value Growth Rate:                     1.0%
Tax Rate:                                      30.0%
Operating Profit Margins:                      18.7%
Incremental Fixed Capital Investment:          19.0%
Incremental Growth in Working Capital:         22.5%
Discount Rate:                                  8.0%
Total Debt:                                   330,224
Shares Outstanding:                           147,438
Margin of Safety:                              25.0%

(in thousands)                                  Year 1       Year 2      Year 3
Sales                                         1,706,602    1,962,592   2,256,981
Operating Profit                                319,135      367,005     422,055
Less: Cash Tax Rate                              95,740      110,101     126,617
NOPAT                                           223,394      256,903     295,439

Incremental Fixed-Capital Investment            60,636       69,731      80,191
Incremental Working-Capital Investment          71,805       82,576      94,962
132,441      152,307     175,153

Free Cash Flow                                   90,953     104,596     120,286
Present Value of FCF                             84,216      89,675      95,487
Cumulative Present Value of FCF               1,100,638
Terminal Year                                   296,022

Terminal Value                                1,958,791

Total PV of Cash Flows                        3,059,429

Cash and Cash Equivalents                      156,197

Less:
Total Debt                                     330,224
Stock Options
Pension fund underfunding*                             0

Intrinsic Value                               2,885,402

Intrinsic Value Per Share                       \$19.57

Margin of Safety Intrinsic Value                \$14.68
*(Included in total debt)
Year 4      Year 5      Year 6      Year 7      Year 8      Year 9      Year 10
2,595,528   2,984,858   3,372,889   3,811,365   4,306,842   4,866,731   5,499,407
485,364     558,168     630,730     712,725     805,379     910,079   1,028,389
145,609     167,451     189,219     213,818     241,614     273,024     308,517
339,755     390,718     441,511     498,908     563,766     637,055     719,872

92,219     106,052     119,839     135,418     153,022     172,915     195,394
109,207     125,588     141,914     160,363     181,210     204,768     231,388
201,426     231,640     261,753     295,781     334,232     377,683     426,781

138,329     159,078     179,758     203,127     229,533     259,372     293,091
101,676     108,266     113,278     118,522     124,010     129,751     135,758
FocusInvestor.com: The Focused Few

2 Stage DCF Scenario Analysis

Scenario Likelihood Intrinsic Value Expected Value
Scenario   1                              75%              \$41.60         \$31.20
Scenario   2                              15%              \$35.00          \$5.25
Scenario   3                               5%              \$20.00          \$1.00
Scenario   4                               5%              \$18.00          \$0.90
Totals:                   100%                             \$38.35

Expected Instrinsic Value:         \$38.35

3 Stage DCF Scenario Analysis

Scenario Likelihood Intrinsic Value Expected Value
Scenario   1                              60%              \$19.85         \$11.91
Scenario   2                              25%              \$10.00          \$2.50
Scenario   3                              10%              \$7.50           \$0.75
Scenario   4                               5%              \$3.00           \$0.15
Totals:                   100%                             \$15.31

Expected Instrinsic Value:         \$15.31

```
DOCUMENT INFO
Shared By:
Categories:
Stats:
 views: 124 posted: 6/30/2010 language: English pages: 8
Description: Discounted Cash Flow document sample