| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
1 | Intel (INTC) | ||||||||||||||
2 | |||||||||||||||
3 | |||||||||||||||
4 | |||||||||||||||
5 | Historical FCF Growth (Standard Calculation): Cash from Operations minus Capital Expenditures | Historical FCF Growth, Recent Only (Still Stanard Calcultion) | Joel Greenblatt Growth Co. Method (Cash from Operations - the lesser of depreciation or CapEx) | ||||||||||||
6 | Ending FCF Value (12/2011) (Billions) | 10.199 | 12/2011 (Billions) | 10.199 | 12/2011 (Billions) | 14.919 | 12/2011 (Billions) | 14.919 | |||||||
7 | Beginning FCF Value (12/2002) (Billions) | 4.426 | 12/2007 (Billions) | 7.625 | 12/2002 (Billions) | 4.426 | 12/2007 (Billions) | 7.827 | |||||||
8 | |||||||||||||||
9 | Compound Annual Growth Rate (CAGR) (%) | 9.72% | (CAGR %) | 7.54% | (CAGR %) | 7.43% | 17.50% | ||||||||
10 | |||||||||||||||
11 | |||||||||||||||
12 | Two Stage Discounted Free Cash Flow Spreadsheet | Year 1 FCF | Year 2 | Year 3 | Year 4 | Year 5 | Year 6 | Year 7 | Year 8 | Year 9 | Year 10 | ||||
13 | |||||||||||||||
14 | % | ||||||||||||||
15 | FCF & % Growth (Billions) | 0.08 | 12 | 12.96 | 13.9968 | 15.116544 | 16.32586752 | 17.63193692 | 19.04249188 | 20.56589123 | 22.21116252 | 23.98805553 | |||
16 | Discount Factor | 1 | 0.11 | 1.11 | 1.2321 | 1.367631 | 1.51807041 | 1.685058155 | 1.870414552 | 2.076160153 | 2.30453777 | 2.558036924 | 2.839420986 | ||
17 | Discounted (FCF) to Present Value | 10.81081081 | 10.51862673 | 10.23433953 | 9.957735755 | 9.688607761 | 9.426753498 | 9.171976376 | 8.924085123 | 8.682893633 | 8.448220832 | ||||
18 | |||||||||||||||
19 | Sum 10 Year DFCF (Billions) | 95.86405005 | |||||||||||||
20 | |||||||||||||||
21 | |||||||||||||||
22 | Post-10 Year Perpetuity DFCF Value (2% Growth) | 0.02 | 271.8646293 | ||||||||||||
23 | Discounted to Present Value (Billions) | 2.839420986 | 95.74650276 | ||||||||||||
24 | |||||||||||||||
25 | Present Intrinsic Value all FCF (BILLIONS) | 191.6105528 | |||||||||||||
26 | |||||||||||||||
27 | Shares Outstanding (BILLIONS) (diluted) | 5.411 | |||||||||||||
28 | Present Intrinsic Value Per Share (DCF ONLY) | 35.41130157 | |||||||||||||
29 | PLUS CASH/CASH EQUIV. ON HAND (35% tax disc.) (billions) | 0 | |||||||||||||
30 | MINUS SHORT AND LONG TERM DEBT (BILLIONS) | 0 | |||||||||||||
31 | NET CASH (BILLIONS) | 0 | Note: D34 is auto-updating. The price of Intel as of the date of creation of this sheet was $27.90/share. | ||||||||||||
32 | Value of Cash on Hand, Per Share | 0 | |||||||||||||
33 | TOTAL INTRINSIC VALUE PER SHARE | $35.4113 | |||||||||||||
34 | Present Actual Share Price (April 18, 2012) | $38.08 | |||||||||||||
35 | |||||||||||||||
36 | |||||||||||||||
37 | |||||||||||||||
38 | Amount Undervalued (Percent) | -7.54% | |||||||||||||
39 | |||||||||||||||
40 | Buy Price | $28.33 | |||||||||||||
41 | Sell Price | $42.49 | |||||||||||||
42 | |||||||||||||||
43 | |||||||||||||||
44 | Legend: | ||||||||||||||
45 | Calculation for Years 1-10 = Future CF Value/((1+i)^N); | ||||||||||||||
46 | |||||||||||||||
47 | Calculation for Perpetuity Growth After That: (CFn*(1+g))/(R-g) | ||||||||||||||
48 | where: CFn = Cash Flow in the Last Individual Year Estimated; g= Long-Term Growth Rate; R= Discount Rate, or Cost of Capital, or Cost of Equity | ||||||||||||||
49 | |||||||||||||||
50 | Discount Perpetuity Growth to Present Value Using: Present Value of Cash Flow in Year N = CF at Year N / (1 + R)^N | ||||||||||||||
51 | |||||||||||||||
52 | |||||||||||||||
53 | |||||||||||||||
54 | |||||||||||||||
55 | |||||||||||||||
56 | |||||||||||||||
57 | |||||||||||||||
58 | |||||||||||||||
59 | |||||||||||||||
60 | |||||||||||||||
Loading...