DCF-现金流贴现模型-Excel模版(dcf-估值模型).xlsx

1、GE (General Electric Co.)($ Millions)Income Statement - GAAP FY FY FY FY FY FY FY FY FY2009E 2008E 2007E 2006 2005 2004 2003 2002 2001Net Sales 210,646 191,444 172,936 163,391 147,956 134,291 113,421 111,967 126,373Cost of sales (94,791) (86,150) (77,821) (74,110) (66,814) (61,759) (51,206) (52,856)

2、 (49,097)R&D Expenses (4,845) (4,403) (3,632) (3,659) (3,425) (3,091) (2,656) (2,631) (2,349)SG&A (Selling, General, & Admin.) (17,694) (16,081) (14,527) (13,841) (13,279) (12,001) (9,870) (9,131) (8,637)Insurance Losses and Policyholder Benefits (3,160) (2,872) (2,421) (3,214) (3,374) (3,086) (16,3

3、69) (17,608) (15,602)Other Costs and Expenses (30,546) (26,898) (25,026) (23,753) (22,280) (21,713) (5,086) (1,323) (18,769)Amortization of Goodwill - - - - - - - - (1,252)Operating Profit (EBIT) 59,611 55,040 49,510 44,814 38,784 32,641 28,234 28,418 30,667Financial Income and Expenses (25,278) (23

4、,930) (22,482) (19,286) (15,102) (11,616) (10,432) (10,216) (11,062)Profit Before Tax and Minority Interests (EBT) 34,333 31,109 27,028 25,528 23,682 21,025 17,802 18,202 19,605Tax (5,837) (5,289) (3,200) (3,954) (4,035) (3,696) (4,315) (3,758) (5,573)Profit Before Minority Interests & Discontinued

5、Ops. 28,497 25,821 23,828 21,574 19,647 17,329 13,487 14,444 14,032Profit Attributable to Minority Interests (843) (766) (692) (908) (986) (728) (308) (326) (348)Profit Attributable to Discontinued Ops. (211) (191) (346) 163 (1,950) 559 2,057Profit attributable to Equity holders 27,443 24,864 22,791

6、 20,829 16,711 17,160 15,236 14,118 13,684EPS ($)Basic 2.65 2.40 2.20 2.01 1.58 1.65 1.52 1.42 1.38Diluted 2.64 2.39 2.19 2.00 1.57 1.64 1.51 1.41 1.36Consensus 2.70 2.43 2.20Avg number of shares (000s)Basic 10,359,320 10,359,320 10,359,320 10,359,320 10,569,805 10,399,629 10,018,587 9,947,113 9,932

7、,245Diluted 10,414,500 10,414,500 10,414,500 10,414,500 10,643,950 10,463,400 10,104,540 9,998,460 10,066,400Depreciation and amortization, total 12,639 11,487 10,376 9,158 8,537 8,348 6,864 6,511 7,125% of sales 6.00% 6.00% 6.00% 5.60% 5.77% 6.22% 6.05% 5.82% 5.64% 5.89%Inventories 14,745 13,401 12

8、,106 11,401 10,474 9,778 8,752 9,247% of sales 7.00% 7.00% 7.00% 6.98% 7.08% 7.28% 7.72% 8.26% 0.00% 7.46%Accts Receivable (*Excl. Financial Receivables) 18,958 17,230 15,564 13,954 14,851 14,233 10,732 10,681% of sales 9.00% 9.00% 9.00% 8.54% 10.04% 10.60% 9.46% 9.54% 0.00% 9.64%Accts Payable 29,49

9、0 26,802 24,211 21,697 21,183 19,137 19,824 18,874% of sales 14.00% 14.00% 14.00% 13.28% 14.32% 14.25% 17.48% 16.86% 0.00% 15.24%Chg in WC (384) (370) 199Sales growth 10.03% 10.70% 5.84% 10.43% 10.18% 18.40% 1.30% -11.40% n/aGross Margin 55.00% 55.00% 55.00% 54.64% 54.84% 54.01% 54.85% 52.79% 61.15%

10、R&D Expenses -2.30% -2.30% -2.10% -2.24% -2.31% -2.30% -2.34% -2.35% -1.86%SG&A (Selling, General, & Admin.) -8.40% -8.40% -8.40% -8.47% -8.97% -8.94% -8.70% -8.16% -6.83%Insurance Losses and Policyholder Benefits -1.50% -1.50% -1.40% -1.97% -2.28% -2.30% -14.43% -15.73% -12.35%Other Costs and Expen

11、ses -14.50% -14.05% -14.47% -14.54% -15.06% -16.17% -4.48% -1.18% -14.85%Amortization of Goodwill 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% -0.99%Operating Profit (EBIT) 28.30% 28.75% 28.63% 27.43% 26.21% 24.31% 24.89% 25.38% 24.27%Financial Income and Expenses -12.00% -12.50% -13.00% -11.80%

12、-10.21% -8.65% -9.20% -9.12% -8.75%EBT Margin 16.30% 16.25% 15.63% 15.62% 16.01% 15.66% 15.70% 16.26% 15.51%Tax Rate -17.00% -17.00% -17.50% -15.49% -17.04% -17.58% -24.24% -20.65% -28.43%Profit Attributable to Minority Interests -0.40% -0.40% -0.40% -0.56% -0.67% -0.54% -0.27% -0.29% -0.28%Profit A

13、ttributable to Discontinued Ops. -0.10% -0.10% -0.20% -0.22% 2.92% -0.91% -4.02% 0.00% 0.00%GE (General Electric Co.) 206,550 191,185 169,900($ Millions) 210,646 191,444 172,936Income Statement - Segment FY FY FY FY FY FY FY FY FY2009E 2008E 2007E 2006 2005 2004 2003 2002 2001Net Sales 210,646 191,4

14、44 172,936 163,391 147,956 134,291 113,421 111,967 126,373Infrastructure 75,481 66,798 58,085 47,429 41,803 37,373 36,569 40,119 36,419Commercial Finance 36,075 32,500 29,018 23,792 20,646 19,524 16,927 15,688 38,498GE Money 28,057 26,221 24,737 21,759 19,416 15,734 12,845 10,266 9,508Healthcare 19,

15、083 17,919 16,825 16,562 15,153 13,456 10,198 8,955 8,409NBC Universal 21,101 18,191 15,416 16,188 14,689 12,886 6,871 7,149 5,769Industrial 26,848 25,815 24,586 33,494 32,631 30,722 24,988 26,154 26,101Corporate items and eliminations 4,000 4,000 4,269 4,167 3,618 4,596 5,023 3,636 1,669EBT (*No EB

16、IT information at Segment level) 34,333 31,109 27,028 25,080 22,843 19,553 17,889 19,035 18,682Infrastructure 13,964 12,358 10,811 9,040 7,769 6,797 7,362 9,178 7,869Commercial Finance 7,756 6,988 5,882 5,028 4,290 3,570 2,907 2,170 3,663GE Money 3,788 3,409 3,702 3,507 3,050 2,520 2,161 1,799 1,602

17、Healthcare 3,149 3,046 2,978 3,143 2,665 2,286 1,701 1,546 1,498NBC Universal 4,431 4,002 3,107 2,919 3,092 2,558 1,998 1,658 1,408Industrial 1,745 1,807 1,973 2,694 2,559 1,833 1,385 1,837 2,642Corporate items and eliminations (500) (500) (1,425) (1,251) (582) (11) 375 847 -%EBT (*No EBIT informati

18、on at Segment level) 16.30% 16.25% 15.63% 15.35% 15.44% 14.56% 15.77% 17.00% 14.78%Change YoY 0.05% 0.62% 0.28% -0.09% 0.88% -1.21% -1.23% 2.22%Infrastructure (%) 18.50% 18.50% 18.61% 19.06% 18.58% 18.19% 20.13% 22.88% 21.61%Change YoY 0.00% -0.11% -0.45% 0.48% 0.40% -1.94% -2.75% 1.27%Commercial Fi

19、nance (%) 21.50% 21.50% 20.27% 21.13% 20.78% 18.29% 17.17% 13.83% 9.51%Change YoY 0.00% 1.23% -0.86% 0.35% 2.49% 1.11% 3.34% 4.32%GE Money (%) 13.50% 13.00% 14.97% 16.12% 15.71% 16.02% 16.82% 17.52% 16.85%Change YoY 0.50% -1.97% -1.15% 0.41% -0.31% -0.81% -0.70% 0.67%Healthcare (%) 16.50% 17.00% 17.

20、70% 18.98% 17.59% 16.99% 16.68% 17.26% 17.81%Change YoY -0.50% -0.70% -1.28% 1.39% 0.60% 0.31% -0.58% -0.55%NBC Universal (%) 21.00% 22.00% 20.15% 18.03% 21.05% 19.85% 29.08% 23.19% 24.41%Change YoY -1.00% 1.85% 2.12% -3.02% 1.20% -9.23% 5.89% -1.21%Industrial (%) 6.50% 7.00% 8.02% 8.04% 7.84% 5.97%

21、 5.54% 7.02% 10.12%Change YoY -0.50% -1.02% -0.02% 0.20% 1.88% 0.42% -1.48% -3.10%Sales Growth 10.03% 10.70% 5.84% 10.43% 10.18% 18.40% 1.30% -11.40%Infrastructure 13.00% 15.00% 22.47% 13.46% 11.85% 2.20% -8.85% 10.16%Commercial Finance 11.00% 12.00% 21.97% 15.24% 5.75% 15.34% 7.90% -59.25%GE Money

22、7.00% 6.00% 13.69% 12.07% 23.40% 22.49% 25.12% 7.97%Healthcare 6.50% 6.50% 1.59% 9.30% 12.61% 31.95% 13.88% 6.49%NBC Universal 16.00% 18.00% -4.77% 10.20% 13.99% 87.54% -3.89% 23.92%Industrial 4.00% 5.00% -26.60% 2.64% 6.21% 22.95% -4.46% 0.20%DCF Valuation2008/1/27Ticker: GE Terminal Discount Rate

23、= 10.0%Analyst: Terminal FCF Growth = 4.0%Forecast TerminalYear 2007E 2008E 2009E 2010E 2011E 2012E 2013E 2014E 2015E 2016E 2017E ValueRevenue 172,936 191,444 210,646 229,604 250,269 270,290 291,913 309,428 327,994 344,393 361,613% Growth n/a. 10.7% 10.0% 9.0% 9.0% 8.0% 8.0% 6.0% 6.0% 5.0% 5.0%Opera

24、ting Income (EBIT) 49,510 55,040 59,611 64,289 70,075 75,681 81,736 86,640 91,838 96,430 101,252Operating Margin 28.6% 28.7% 28.3% 28.0% 28.0% 28.0% 28.0% 28.0% 28.0% 28.0% 28.0%Interest and Other- net (22,482) (23,930) (25,278) (27,552) (30,032) (32,435) (35,030) (37,131) (39,359) (41,327) (43,394)

25、Interest % of Sales -13.0% -12.5% -12.0% -12.0% -12.0% -12.0% -12.0% -12.0% -12.0% -12.0% -12.0%Taxes 3,200 5,289 5,837 6,245 6,807 7,352 7,940 8,416 8,921 9,367 9,836Tax Rate 15.5% 17.5% 17.0% 17.0% 17.0% 17.0% 17.0% 17.0% 17.0% 17.0% 17.0%Minority Interest (692) (766) (843) (918) (1,001) (1,081) (

26、1,168) (1,238) (1,312) (1,378) (1,446)Discontinued Ops. (346) (191) (211) (211) (211) (211) (211) (211) (211) (211) (211)Net Income 22,791 24,864 27,443 29,362 32,024 34,603 37,388 39,644 42,035 44,147 46,365% Growth n/a. 9.1% 10.4% 7.0% 9.1% 8.1% 8.0% 6.0% 6.0% 5.0% 5.0%Add Depreciation/Amort 10,37

27、6 11,487 12,639 13,776 15,016 16,217 17,515 18,566 19,680 20,664 21,697% of Sales 6.0% 6.0% 6.0% 6.0% 6.0% 6.0% 6.0% 6.0% 6.0% 6.0% 6.0%Plus/(minus) Changes WC 199 -370 -384 -1,148 -1,251 -1,351 -1,460 -1,547 -1,640 -1,722 -1,808% of Sales 0.12% -0.19% -0.18% -0.50% -0.50% -0.50% -0.50% -0.50% -0.50

28、% -0.50% -0.50%Subtract CapEx 17,877 17,230 18,958 20,664 22,524 21,623 23,353 24,754 26,239 27,551 28,929CapEx % of sales 10.3% 9.0% 9.0% 9.0% 9.0% 8.0% 8.0% 8.0% 8.0% 8.0% 8.0%Free Cash Flow 15,489 18,750 20,740 21,326 23,265 27,845 30,090 31,908 33,835 35,537 37,325YoY growth n/a. 21% 11% 3% 9% 2

29、0% 8% 6% 6% 5% 5%Terminal Value 646,963 Terminal: 646,963NPV of free cash flows 161,993 44% P/E: 14.0NPV of terminal value 206,142 56% EV/EBITDA: 8.7Projected Equity Value 368,136 Free Cash Yield: 5.77%Free Cash Flow Yield 4.21%Shares Outstanding 10,415 M*Sensitivity MatrixCurrent Price $35.14 r (di

30、scount rate)*Avg. Jan 2008 (range $33.2 36.8) 9% 10% 11%Implied equity value/ share $35.35g (growthrate)5% $49.79 $39.54 $32.7541.7% 12.5% -6.8%Upside/(Downside) to DCF 0.6% 4% $42.85 $35.35 $30.04 Implied Equity Value21.9% 0.6% -14.5% Upside (Downside)Total Debt $491,623 M 3% $38.22 $32.36 $28.01Total Cash $65,057 M 8.8% -7.9% -20.3%Cash / share 6.25