A 1 Inputs for Chevron B C D E F G H Year Dividend Div growth...

Transcribed Image Text:

A 1 Inputs for Chevron B C D E F G H Year Dividend Div growth Term value Investor CF 2 beta 1.15 2017 4.4 4.40 3 mkt_prem 0.06 2018 4.6 4.60 4 rf 0.025 2019 4.8 4.80 5 k_equity 0.0940 2020 5 5.00 6 term_gwth 0.040 2021 5.28 0.0550 5.28 7 2022 5.56 0.0535 5.56 8 2023 5.85 0.0520 5.85 9 2024 6.14 0.0505 6.14 10 2025 6.44 0.0490 6.44 11 2026 6.75 0.0475 6.75 12 Value line 13 forecasts of 14 annual dividends 15 16 2027 7.06 0.0460 7.06 2028 7.37 0.0445 7.37 2029 7.69 0.0430 7.69 2030 8.01 0.0415 8.01 2031 8.33 0.0400 8.33 2032 8.66 0.0400 166.84 175.50 17 Transitional period 18 with slowing dividend 19 growth 20 21 Beginning of constant growth period * E17 (1+ F17)/(B5 - F17) 87.01 PV of CF NPV(85,H2:H17) A B C D E F G H J K 1 2017 2018 2019 2020 2021 2 A. Input data 3 P/E 17.90 16.93 15.95 14.98 14.00 4 Cap spending/shr 10.50 11.50 12.05 12.60 13.15 5 LT Debt ($M) 40,000 40,000 41,667 43,333 45,000 6 Shares (million) 1,890 1,893 1,895 1,898 1,900 7 EPS 4.00 5.95 7.22 8 Working Capital 4,000 6,000 11,000 8.48 16,000 9.75 21,000 9 B. Cash flow calculations 10 Profits ($M, after tax) 7,500 11,275 13,683 16,092 18,500 11 Interest ($M, after tax) 728 728 758 789 819 = (1-tax_rate) x r_debt x LT Debt 12 Chg Working Cap ($M) 2,000 5,000 5,000 5,000 13 Depreciation ($M) 20,500 22,000 22,667 23,333 24,000 14 Cap Spending ($M) 21,770 22,841 23,913 24,985 15 Terminal value 16 FCFF ($M) 10,234 9,267 11,301 13,334 357,373 17 FCFE ($M) 9,506 10,175 12,179 14,182 18 C. Discount rate calculations 19 Current beta 1.15 20 Unlevered beta 0.963 300,371 assumes fixed debt ratio after 2020 from Value Line current beta /[1 + (1-tax)*debt/equity)] 21 terminal growth 0.04 22 tax rate 0.35 23 r_debt 0.028 YTM in 2017 on Moody's Aa2 rated LT debt 24 risk-free rate 0.025 25 market risk prem 0.06 26 MV equity 134,250 27 Debt/Value 0.23 0.21 0.19 0.17 259,000 0.15 28 Levered beta 1.150 1.129 1.109 1.090 1.072 29 k_equity 0.094 0.093 0.092 0.090 0.089 Row 3 x Row 11 linear trend from initial to final value unlevered beta x [1 + (1-tax) * debt/equity] 0.089 from CAPM and levered beta 30 WACC 0.077 0.077 0.078 0.078 0.079 31 PV factor for FCFF 1.000 0.928 0.861 0.799 0.741 32 PV factor for FCFE 1.000 0.915 0.838 0.769 0.706 0.079 (1-t)*r_debt*D/V + k_equity*(1-D/V) 0.741 Discount each year at WACC 0.706 Discount each year at k_equity 33 D. Present values Intrinsic val Equity val Intrin/share 34 PV(FCFF) 9,501 7,983 9,028 9,875 264,657 301,044 261,044 138.12 35 |PV(FCFE) 8,699 8,531 9,364 10,010 212,004 248,607 248,607 131.54 A 1 Inputs for Chevron B C D E F G H Year Dividend Div growth Term value Investor CF 2 beta 1.15 2017 4.4 4.40 3 mkt_prem 0.06 2018 4.6 4.60 4 rf 0.025 2019 4.8 4.80 5 k_equity 0.0940 2020 5 5.00 6 term_gwth 0.040 2021 5.28 0.0550 5.28 7 2022 5.56 0.0535 5.56 8 2023 5.85 0.0520 5.85 9 2024 6.14 0.0505 6.14 10 2025 6.44 0.0490 6.44 11 2026 6.75 0.0475 6.75 12 Value line 13 forecasts of 14 annual dividends 15 16 2027 7.06 0.0460 7.06 2028 7.37 0.0445 7.37 2029 7.69 0.0430 7.69 2030 8.01 0.0415 8.01 2031 8.33 0.0400 8.33 2032 8.66 0.0400 166.84 175.50 17 Transitional period 18 with slowing dividend 19 growth 20 21 Beginning of constant growth period * E17 (1+ F17)/(B5 - F17) 87.01 PV of CF NPV(85,H2:H17) A B C D E F G H J K 1 2017 2018 2019 2020 2021 2 A. Input data 3 P/E 17.90 16.93 15.95 14.98 14.00 4 Cap spending/shr 10.50 11.50 12.05 12.60 13.15 5 LT Debt ($M) 40,000 40,000 41,667 43,333 45,000 6 Shares (million) 1,890 1,893 1,895 1,898 1,900 7 EPS 4.00 5.95 7.22 8 Working Capital 4,000 6,000 11,000 8.48 16,000 9.75 21,000 9 B. Cash flow calculations 10 Profits ($M, after tax) 7,500 11,275 13,683 16,092 18,500 11 Interest ($M, after tax) 728 728 758 789 819 = (1-tax_rate) x r_debt x LT Debt 12 Chg Working Cap ($M) 2,000 5,000 5,000 5,000 13 Depreciation ($M) 20,500 22,000 22,667 23,333 24,000 14 Cap Spending ($M) 21,770 22,841 23,913 24,985 15 Terminal value 16 FCFF ($M) 10,234 9,267 11,301 13,334 357,373 17 FCFE ($M) 9,506 10,175 12,179 14,182 18 C. Discount rate calculations 19 Current beta 1.15 20 Unlevered beta 0.963 300,371 assumes fixed debt ratio after 2020 from Value Line current beta /[1 + (1-tax)*debt/equity)] 21 terminal growth 0.04 22 tax rate 0.35 23 r_debt 0.028 YTM in 2017 on Moody's Aa2 rated LT debt 24 risk-free rate 0.025 25 market risk prem 0.06 26 MV equity 134,250 27 Debt/Value 0.23 0.21 0.19 0.17 259,000 0.15 28 Levered beta 1.150 1.129 1.109 1.090 1.072 29 k_equity 0.094 0.093 0.092 0.090 0.089 Row 3 x Row 11 linear trend from initial to final value unlevered beta x [1 + (1-tax) * debt/equity] 0.089 from CAPM and levered beta 30 WACC 0.077 0.077 0.078 0.078 0.079 31 PV factor for FCFF 1.000 0.928 0.861 0.799 0.741 32 PV factor for FCFE 1.000 0.915 0.838 0.769 0.706 0.079 (1-t)*r_debt*D/V + k_equity*(1-D/V) 0.741 Discount each year at WACC 0.706 Discount each year at k_equity 33 D. Present values Intrinsic val Equity val Intrin/share 34 PV(FCFF) 9,501 7,983 9,028 9,875 264,657 301,044 261,044 138.12 35 |PV(FCFE) 8,699 8,531 9,364 10,010 212,004 248,607 248,607 131.54