Recalculate the intrinsic value of Toyota shares using the free cash flow model of Spreadsheet(above) under each of the following assumptions. Treat each scenario independently.

a. Toyota's P/E ratio starting in 2025 (cell G3) will be 11.5.

b. Toyota's unlevered beta (cell B22) is .65.

c. The market risk premium (cell B27) is 7.5%

Transcribed Image Text:

D42 A 1 2 A. Input data 3 P/E 4 Cap spending/shr 5 LT Debt ($M) 6 Shares (million) 7 EPS 8 Working capital 9 10 B. Cash flow calculations 11 Profits ($M, after tax) 12 Interest ($M, after tax) 13 Chg working cap ($M) 14 Depreciation ($M) 15 Cap spending ($M) 16 17 FCFF ($M) 18 FCFE ($M) 19 20 21 Current beta C. Discount rate calculations 22 Unlevered beta 23 Terminal growth 24 tax rate 25 r_debt 26 Risk-free rate 27 Market risk prem 28 MV equity 29 Debt/value 30 Levered beta B PV factor for FCFF PV factor for FCFE D. Present values 0.8 0.603 0.05 0.21 0.025 0.02 0.08 2021 12.50 25.80 109,000 1,375 15.15 7,000 21,100 2,153 16,000 D 2022 E 2023 F G 2024 2025 12.38 12.25 12.13 12.00 20.20 26.05 8.50 14.35 26,000 53,833 81,667 109,500 1,707 1,533 1,360 10.52 13.43 16.35 9,000 1,880 7.60 3,000 5,000 7,000 14,325 17,083 19,842 22,600 514 1,063 1,613 2,163 -4,000 2,000 2,000 2,000 18,000 17,833 17,667 17,500 15,980 22,463 28,945 35,428 20,859 11,517 8,176 4,835 -62,655 38,287 34,396 30,505 263,750 271,200 0.2924 0.2912 0.2900 0.2888 0.2876 0.8000 0.7989 0.7977 0.7966 0.7955 0.0840 0.0839 0.0838 0.0837 0.0836 0.0652 0.0652 0.0652 0.0652 0.0653 1.0000 0.9388 0.8813 0.8273 0.7766 1.0000 0.9226 0.8512 0.7855 0.7248 H 19,581 10,150 6,764 3,755 -57,805 32,592 27,017 22,112 Terminal value 332,621 210,868 0.0836 0.0653 0.7766 0.7248 I 258,318 152,847 J 31 k_equity 32 WACC 33 34 35 36 37 PV(FCFF) 38 PV(FCFE) 39 40 41 42 43 * 2025 P/E ratio is from Yahoo! Finance. Final input is from Value Line, and intermediate values are interpolated. 44 45 K = (1-tax_rate) x r_debt x LT Debt L assumes fixed debt ratio after 2023 YTM on LT debt from Value Line current beta /[1 + (1-tax)*debt/equity)] Row 3 x Row 11 linear trend from initial to final value unlevered beta x [1 + (1-tax)*debt/equity] from CAPM and levered beta (1-t)*r_debt*D/V + k_equity*(1-D/V) M Discount each year at WACC Discount each year at k_equity Intrinsic val Equity val Intrin/share 298,568 189,568 137.87 176,763 176,763 128.56 N O D42 A 1 2 A. Input data 3 P/E 4 Cap spending/shr 5 LT Debt ($M) 6 Shares (million) 7 EPS 8 Working capital 9 10 B. Cash flow calculations 11 Profits ($M, after tax) 12 Interest ($M, after tax) 13 Chg working cap ($M) 14 Depreciation ($M) 15 Cap spending ($M) 16 17 FCFF ($M) 18 FCFE ($M) 19 20 21 Current beta C. Discount rate calculations 22 Unlevered beta 23 Terminal growth 24 tax rate 25 r_debt 26 Risk-free rate 27 Market risk prem 28 MV equity 29 Debt/value 30 Levered beta B PV factor for FCFF PV factor for FCFE D. Present values 0.8 0.603 0.05 0.21 0.025 0.02 0.08 2021 12.50 25.80 109,000 1,375 15.15 7,000 21,100 2,153 16,000 D 2022 E 2023 F G 2024 2025 12.38 12.25 12.13 12.00 20.20 26.05 8.50 14.35 26,000 53,833 81,667 109,500 1,707 1,533 1,360 10.52 13.43 16.35 9,000 1,880 7.60 3,000 5,000 7,000 14,325 17,083 19,842 22,600 514 1,063 1,613 2,163 -4,000 2,000 2,000 2,000 18,000 17,833 17,667 17,500 15,980 22,463 28,945 35,428 20,859 11,517 8,176 4,835 -62,655 38,287 34,396 30,505 263,750 271,200 0.2924 0.2912 0.2900 0.2888 0.2876 0.8000 0.7989 0.7977 0.7966 0.7955 0.0840 0.0839 0.0838 0.0837 0.0836 0.0652 0.0652 0.0652 0.0652 0.0653 1.0000 0.9388 0.8813 0.8273 0.7766 1.0000 0.9226 0.8512 0.7855 0.7248 H 19,581 10,150 6,764 3,755 -57,805 32,592 27,017 22,112 Terminal value 332,621 210,868 0.0836 0.0653 0.7766 0.7248 I 258,318 152,847 J 31 k_equity 32 WACC 33 34 35 36 37 PV(FCFF) 38 PV(FCFE) 39 40 41 42 43 * 2025 P/E ratio is from Yahoo! Finance. Final input is from Value Line, and intermediate values are interpolated. 44 45 K = (1-tax_rate) x r_debt x LT Debt L assumes fixed debt ratio after 2023 YTM on LT debt from Value Line current beta /[1 + (1-tax)*debt/equity)] Row 3 x Row 11 linear trend from initial to final value unlevered beta x [1 + (1-tax)*debt/equity] from CAPM and levered beta (1-t)*r_debt*D/V + k_equity*(1-D/V) M Discount each year at WACC Discount each year at k_equity Intrinsic val Equity val Intrin/share 298,568 189,568 137.87 176,763 176,763 128.56 N O

Answer rating: 100% (QA)

To recalculate the intrinsic value of Toyota shares using the free cash flow model under each of ... View the full answer