TOPIC: DISCOUNTED CASHFLOW

Consider reliability of data, business cycles, dividends policy, investment horizon and investors preference as well. Resort to the excel images provided and support your statements with numerical explanations derived from the available examples.

Part FOUR Security Analysis _DSHEET 13.1 stage growth model for Chevron A B D F G 1 Inputs for Chevron Year Dividend Div growth Term value 2 beta 1.15 2017 4.4 3 mkt_prem 0.06 2018 4.6 4 rf 0.025 2019 4.8 5 |K_equity 0.0940 2020 5 6 term_gwth 0.040 2021 5,28 0.0550 7 2022 5.56 0.0535 8 2023 5.85 0,0520 9 2024 6.14 0.0505 10 2025 6,44 0.0490 11 2026 6.75 0.0475 12 Value Line 2027 7.06 0,0460 13 forecasts of 2028 7.37 0.0445 14 Jannual dividiends 2029 7.69 0.0430 15 2030 8.01 0.0415 16 2031 8.33 0,0400 17 Transitional period 2032 8.66 0.0400 166.84 18 with slowing dividend 19 growth 20 Beginning of constant E17 (1+F17)/(B5-F17) | 21 growth period H Investor CF 4.40 4.60 4.80 5.00 5.28 5.5 5.85 6.14 6.44 6.75 7.06 7.37 7.69 8.01 8.33 175.50 87.01 NPV(B5,H2:H17) 13.4 PRICE-EARNINGS RAT The Price-Earnings Ratio and Grow Much of the real-world discussion of stock mark Chapter 13 Equity Valuation SPREADSHEET 13.2 Free cash flow valuation of Chevron 14.98 300,371 A B D E E F G H J K M 1 2017 2018 2019 2020 2021 2 A. Input data 3 P/E 17.90 16.93 15.95 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 LEPS 4.00 5.95 7.22 8.48 9.75 8 Working Capital 4,000 6,000 11,000 16,000 21,000 9 10 B. Cash flow calculations 11 Profits (SM, after tax 7,500 11,275 13,683 16,092 18,500 12 Interest ($M, after tdx) 728 728 758 789 819 (1-tax rate) Xr debt x LT Debt 2.000 13 Chg Working Cap (SM) 5,000 5,000 5,000 14 Depreciation (SM) 20,500 22.000 22,667 23,333 24,000 15 Cap Spending ($M) 21.770 22,841 23,913 24.985 16 Terminal value 17 FCFF ($M) 10,234 9,267 11.301 13,334 357,373 18 FCFE (SM 9,506 10.175 12,179 14,182 assumes fixed debt ratio after 2020 B1 20 ]c. Discount rate calculations 21 Current beta 1.15 from Value Line 22 Unlevered beta 0.963 current beta /1+(1-tax)"debt/equity 23 Terminal growth 0.04 24 Tax rate 0.35 25 r_debt YTM in 2017 on Moody's Aa2 rated LT de 26 Risk-free rate 0.025 27 Market risk prem 0.06 28 MV equity 134,250 259,000 Row 3 x Row 11 29 Debt/Value 0.23) 0.21 0.19 0.17 0.15 linear trend from initial to final value 30 Levered beta 1.150 1.129 1.109 1.090 1,072 unlevered beta x 11+(1-tax)"debt/equity 31k equity 0.094 0.093 0.092 0.090 0.089 0.089 from CAPM and levered beta 32 IWACC 0.077 0.077 0.078 0.078 0.079 0.079 (1-1)"r_debtD/V+k_equity*(1-D/V) 33 PV factor for FCFF 1.000 0.928 0,861 0.799 0.741 0.741 Discount each year at WACC 34 PV factor for FCFE 1.000 0.915 0.838 0.769 0,706 0.706 Discount each year at k equity 31 36 D. Present values Intrinsic val Equity val Intrin/share 37PVIFCFF) 9,501 7,983 9,028 9,875 264,657 301,044 261,044 138.12 38 PVIFCFE 8,699 8,5311 9,364 10,010 212,004 248,607 248,6071 131.54 * 2017 P/E ratio is from Yahoo! Finance. Final input is from Value Line, and intermediate values are interpolated, 0.028 As in the dividend discount model, free cash flow models use a terminal value to avoid adding the present values of an infinite sum of cash flows. That terminal value may simply be the present value of a constant-growth perpetuity (as in the formulas above), or it may be based on a multiple of EBIT, book value, earnings, or free cash flow. As a general rule, esti- mates of intrinsic value depend critically on terminal value. Spreadsheet 13.2 presents a free cash flow valuation of Chevron using the data supplied by Value Line in Figure 13.2. We start with the free cash flow to the firm approach given in Equation 13.9. Panel A of the spreadsheet lays out values supplied by Value Line. (Entries for middle years are interpolated from beginning and final values.) Panel B calculates free cash flow. The sum of after-tax profits in row 11 plus after-tax interest payments in row 12 [that is, interest expense x (1 - 1)] equals EBIT(1 - 1). In row 13 we subtract the change in net working capital, in row 14 we add back depreciation, and in row 15 we subtract capital expen- ditures. The result in row 17 is the free cash flow to the firm, FCFF, for each year between 2018 and 2021. To find the present value of these cash flows, we will discount at WACC, which is cal- culated in panel C. WACC is the weighted average of the after-tax cost of debt and the cost of equity in each year. When computing WACC, we must account for the change in leverage forecast by Value Line. To compute the cost of equity, we will use the CAPM as in our earlier Throughout the spreadsheet, we use a corporate tax rate of 35%. At the time of this valuation exercise, early 2017, it seems plausible that the market did not yet expect the reduction in the corporate tax rate to 21% that occurred at the end of the year. Indeed, one common explanation for the stock price run-up in the latter half of the year was an Part FOUR Security Analysis _DSHEET 13.1 stage growth model for Chevron A B D F G 1 Inputs for Chevron Year Dividend Div growth Term value 2 beta 1.15 2017 4.4 3 mkt_prem 0.06 2018 4.6 4 rf 0.025 2019 4.8 5 |K_equity 0.0940 2020 5 6 term_gwth 0.040 2021 5,28 0.0550 7 2022 5.56 0.0535 8 2023 5.85 0,0520 9 2024 6.14 0.0505 10 2025 6,44 0.0490 11 2026 6.75 0.0475 12 Value Line 2027 7.06 0,0460 13 forecasts of 2028 7.37 0.0445 14 Jannual dividiends 2029 7.69 0.0430 15 2030 8.01 0.0415 16 2031 8.33 0,0400 17 Transitional period 2032 8.66 0.0400 166.84 18 with slowing dividend 19 growth 20 Beginning of constant E17 (1+F17)/(B5-F17) | 21 growth period H Investor CF 4.40 4.60 4.80 5.00 5.28 5.5 5.85 6.14 6.44 6.75 7.06 7.37 7.69 8.01 8.33 175.50 87.01 NPV(B5,H2:H17) 13.4 PRICE-EARNINGS RAT The Price-Earnings Ratio and Grow Much of the real-world discussion of stock mark Chapter 13 Equity Valuation SPREADSHEET 13.2 Free cash flow valuation of Chevron 14.98 300,371 A B D E E F G H J K M 1 2017 2018 2019 2020 2021 2 A. Input data 3 P/E 17.90 16.93 15.95 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 LEPS 4.00 5.95 7.22 8.48 9.75 8 Working Capital 4,000 6,000 11,000 16,000 21,000 9 10 B. Cash flow calculations 11 Profits (SM, after tax 7,500 11,275 13,683 16,092 18,500 12 Interest ($M, after tdx) 728 728 758 789 819 (1-tax rate) Xr debt x LT Debt 2.000 13 Chg Working Cap (SM) 5,000 5,000 5,000 14 Depreciation (SM) 20,500 22.000 22,667 23,333 24,000 15 Cap Spending ($M) 21.770 22,841 23,913 24.985 16 Terminal value 17 FCFF ($M) 10,234 9,267 11.301 13,334 357,373 18 FCFE (SM 9,506 10.175 12,179 14,182 assumes fixed debt ratio after 2020 B1 20 ]c. Discount rate calculations 21 Current beta 1.15 from Value Line 22 Unlevered beta 0.963 current beta /1+(1-tax)"debt/equity 23 Terminal growth 0.04 24 Tax rate 0.35 25 r_debt YTM in 2017 on Moody's Aa2 rated LT de 26 Risk-free rate 0.025 27 Market risk prem 0.06 28 MV equity 134,250 259,000 Row 3 x Row 11 29 Debt/Value 0.23) 0.21 0.19 0.17 0.15 linear trend from initial to final value 30 Levered beta 1.150 1.129 1.109 1.090 1,072 unlevered beta x 11+(1-tax)"debt/equity 31k equity 0.094 0.093 0.092 0.090 0.089 0.089 from CAPM and levered beta 32 IWACC 0.077 0.077 0.078 0.078 0.079 0.079 (1-1)"r_debtD/V+k_equity*(1-D/V) 33 PV factor for FCFF 1.000 0.928 0,861 0.799 0.741 0.741 Discount each year at WACC 34 PV factor for FCFE 1.000 0.915 0.838 0.769 0,706 0.706 Discount each year at k equity 31 36 D. Present values Intrinsic val Equity val Intrin/share 37PVIFCFF) 9,501 7,983 9,028 9,875 264,657 301,044 261,044 138.12 38 PVIFCFE 8,699 8,5311 9,364 10,010 212,004 248,607 248,6071 131.54 * 2017 P/E ratio is from Yahoo! Finance. Final input is from Value Line, and intermediate values are interpolated, 0.028 As in the dividend discount model, free cash flow models use a terminal value to avoid adding the present values of an infinite sum of cash flows. That terminal value may simply be the present value of a constant-growth perpetuity (as in the formulas above), or it may be based on a multiple of EBIT, book value, earnings, or free cash flow. As a general rule, esti- mates of intrinsic value depend critically on terminal value. Spreadsheet 13.2 presents a free cash flow valuation of Chevron using the data supplied by Value Line in Figure 13.2. We start with the free cash flow to the firm approach given in Equation 13.9. Panel A of the spreadsheet lays out values supplied by Value Line. (Entries for middle years are interpolated from beginning and final values.) Panel B calculates free cash flow. The sum of after-tax profits in row 11 plus after-tax interest payments in row 12 [that is, interest expense x (1 - 1)] equals EBIT(1 - 1). In row 13 we subtract the change in net working capital, in row 14 we add back depreciation, and in row 15 we subtract capital expen- ditures. The result in row 17 is the free cash flow to the firm, FCFF, for each year between 2018 and 2021. To find the present value of these cash flows, we will discount at WACC, which is cal- culated in panel C. WACC is the weighted average of the after-tax cost of debt and the cost of equity in each year. When computing WACC, we must account for the change in leverage forecast by Value Line. To compute the cost of equity, we will use the CAPM as in our earlier Throughout the spreadsheet, we use a corporate tax rate of 35%. At the time of this valuation exercise, early 2017, it seems plausible that the market did not yet expect the reduction in the corporate tax rate to 21% that occurred at the end of the year. Indeed, one common explanation for the stock price run-up in the latter half of the year was an