compute residual income valuations using the Intel-related variables shared below. Perform your calculations using the spreadsheet. Assume the following information for Intel:

Forecasted earnings per share for 2023 = $0.53 Forecasted earnings per share for 2024 = $1.87 Five-year forecasted earnings growth rate = 3.16% Intel's cost of equity = 9.45% (I've already set this parameter) Intel's book value per share at the start of 2022 = $24.52 Intel's dividend payout ratio = 30% 

1. Compute Intel's residual income valuation using the three methods in the attached spreadsheet. 

2. Compare the residual income values to Intel's current market price. Is the current price higher or lower than the current book value (shareholders' equity) per share? Is this relation expected based on the valuation spreadsheets?

Transcribed Image Text:

Method 1: Assumes (1) T=5 ROE is ROE in t=6 through t=10 and (2) residual income in year t=10 is a perpetuity Other parameters 10yr bill Mkt Rp 5yr_growth div payout 7.00% 1.0658 0.5556 3.99% ke = 9.450% t=0 t=1 t=2 t=3 t=4 2022 2023 2024 2025 2026 t=5 t=6 t=7 t=8 t=9 t=10 t=11 t=12 2027 2028 2029 2030 2031 2032 2033 2034 beta 0.78 Forecasted NI Forecasted DIV end. book value residual income 3.48 3.41 1.93 1.89 12.90 3.63 3.87 4.13 2.02 2.15 2.29 17.58 19.30 21.13 2.13 2.21 2.30 Forecasted ROE 4.52 4.95 5.42 5.94 6.50 2.51 2.75 3.01 3.30 3.61 14.45 15.96 23.14 25.34 27.75 30.39 33.28 2.26 2.04 2.52 2.76 3.03 3.31 3.63 26.98% 23.60% 22.77% 22.04% 21.39% 21.39% 21.39% 21.39% 21.39% 21.39% Assume t=10 RI is a perpetuity 38.40 PV(residual income) Sum PV (residual income) 2.07 31.32 1.71 1.62 1.54 1.47 1.47 1.47 1.47 1.47 17.04 Residual Income Model Value 1/1/23 44.22 Market Price 1/1/2023 ? Method 2: Assumes ROE in year t=5 fades down to cost of equity in year t=10 ke = 9.450% t=0 2022 t=1 t=2 t=3 t=4 2023 2024 2025 2026 t=5 2027 t=6 t=7 t=8 t=9 2028 2029 2030 2031 t=10 t=11 2032 2033 t=12 2034 3.48 3.41 1.93 1.89 12.90 14.45 15.96 17.58 19.30 2.26 2.04 2.13 2.21 26.98% 23.60% 22.77% 22.04% 21.39% Forecasted NI Forecasted DIV end. book value residual income Forecasted ROE After 2027, generate forecasts of NI, BV, and DIV from ROE forecasts Assume ROE gradually goes to ke over the next 5 years (21.39% -5.100%)/5= Forecasted ROE Implied NI Forecasted DIV end. book value residual income PV(residual income) Sum DVresidual incomet -2.39% ROE decrease per year 19.00% 16.62% 14.23% 11.84% 9.45% 4.02 3.81 3.50 3.10 2.60 2.23 2.12 1.95 1.72 1.45 22.92 24.61 26.17 27.54 28.70 2.02 1.64 1.18 0.62 0.00 2.07 1.71 1.62 1.54 1.47 1.17 0.87 0.57 0.28 0.00 11.20 3.63 3.87 4.13 2.02 2.15 2.29 21.13 2.30 Method 1: Assumes (1) T=5 ROE is ROE in t=6 through t=10 and (2) residual income in year t=10 is a perpetuity Other parameters 10yr bill Mkt Rp 5yr_growth div payout 7.00% 1.0658 0.5556 3.99% ke = 9.450% t=0 t=1 t=2 t=3 t=4 2022 2023 2024 2025 2026 t=5 t=6 t=7 t=8 t=9 t=10 t=11 t=12 2027 2028 2029 2030 2031 2032 2033 2034 beta 0.78 Forecasted NI Forecasted DIV end. book value residual income 3.48 3.41 1.93 1.89 12.90 3.63 3.87 4.13 2.02 2.15 2.29 17.58 19.30 21.13 2.13 2.21 2.30 Forecasted ROE 4.52 4.95 5.42 5.94 6.50 2.51 2.75 3.01 3.30 3.61 14.45 15.96 23.14 25.34 27.75 30.39 33.28 2.26 2.04 2.52 2.76 3.03 3.31 3.63 26.98% 23.60% 22.77% 22.04% 21.39% 21.39% 21.39% 21.39% 21.39% 21.39% Assume t=10 RI is a perpetuity 38.40 PV(residual income) Sum PV (residual income) 2.07 31.32 1.71 1.62 1.54 1.47 1.47 1.47 1.47 1.47 17.04 Residual Income Model Value 1/1/23 44.22 Market Price 1/1/2023 ? Method 2: Assumes ROE in year t=5 fades down to cost of equity in year t=10 ke = 9.450% t=0 2022 t=1 t=2 t=3 t=4 2023 2024 2025 2026 t=5 2027 t=6 t=7 t=8 t=9 2028 2029 2030 2031 t=10 t=11 2032 2033 t=12 2034 3.48 3.41 1.93 1.89 12.90 14.45 15.96 17.58 19.30 2.26 2.04 2.13 2.21 26.98% 23.60% 22.77% 22.04% 21.39% Forecasted NI Forecasted DIV end. book value residual income Forecasted ROE After 2027, generate forecasts of NI, BV, and DIV from ROE forecasts Assume ROE gradually goes to ke over the next 5 years (21.39% -5.100%)/5= Forecasted ROE Implied NI Forecasted DIV end. book value residual income PV(residual income) Sum DVresidual incomet -2.39% ROE decrease per year 19.00% 16.62% 14.23% 11.84% 9.45% 4.02 3.81 3.50 3.10 2.60 2.23 2.12 1.95 1.72 1.45 22.92 24.61 26.17 27.54 28.70 2.02 1.64 1.18 0.62 0.00 2.07 1.71 1.62 1.54 1.47 1.17 0.87 0.57 0.28 0.00 11.20 3.63 3.87 4.13 2.02 2.15 2.29 21.13 2.30

Answer rating: 100% (QA)

Unfortunately I cannot view the spreadsheet directly or perform calculations within it However I can provide you with instructions on how to compute t... View the full answer