1. Compare the values calculated to each of the market prices for the top twenty stock picks. Are the calculations close approximations of the market prices? Why do you think there are differences?

 

2. What are the implications of your analysis to these 20 stock?

 

3. Think about these results in terms of claims of market efficiency. On average, over the long term, do you think that this model used to calculate these stock values really work? Why or why not? The Capital Asset pricing model and the constant growth formula was used to find the price.

4. In light of your answer in number 3 above, how would you advise someone thinking about investing in these stocks?

Transcribed Image Text:

J31 Home Insert Draw Page Layout fx ? Insert AutoSum Recently Financial Logical Function Used 27 28 29 30 31 2 32 33 34 35 36 37 38 39 40 41 42 42 43 44 45 46 47 48 49 50 51 52 Excel File Edit View Insert Format Tools Data Window Help A ●● AutoSave OFF 53 54 55 56 57 58 59 60 61 62 ţ X B ✓ fx 8 Stock Rank 1 2 3 4 5 6 G 7 19 9 P 10 20 11 *** 12 p 13 pe 14 P 15 29 16 p 17 E 18 19 20 < A 892 ... M . с JAN 23 0 0 Total dividend 0.95 1.25 0.78 0.06 0 0 6.22 1 * 0 6 0 0.25 0.98 2.25 5.8 1.02 10 Formulas Data Review View Tell me A. Ꮎ Text Date & Lookup & Math & Time Reference Trig 1 E 0 D ✓ Beta 0.74 2.3 1.89 1.31 1.52 1.04 1.99 0.89 1.06 1.00 1.76 2.70 0.91 0.04 1.8 Lave 0.82 Live 1.12 0.99 Live 1.01 LIVE 0.67 1.11 0.78 1.2 E Qhow to take a screenshot on mac Advanced Business Finance Presentation 1 More Functions Define Name ✔ Create from Selection F Required Return =$C$4+E32*(($E$4-$C$4)) =$C$4+E33*(($E$4-$C$4)) =$C$4+E34 (($E$4-$C$4)) 0 0.085 0.0125 -0.09 -1 0 =$C$4+E35*(($E$4-$C$4)) =$C$4+E36*(($E$4-$C$4)) =$C$4+E37*(($E$4-$C$4)) =$C$4+E38*(($E$4-$C$4)) =$C$4+E39*(($E$4-$C$4)) =$C$4+E40*(($E$4-$C$4)) 0.0098 =$C$4+E41*(($E$4-$C$4)) -0.0048 =$C$4+E42*(($E$4-$C$4)) -1 =$C$4+E43*(($E$4-$C$4)) 0.021 =$C$4+E44*(($E$4-$C$4)) -0.02 =$C$4+E45*(($E$4-$C$4)) -0.02 0.04 ponov =$C$4+E46 (($E$4-$C$4)) =$C$4+E47*(($E$4-$C$4)) =$C$4+E48*(($E$4-$C$4)) =$C$4+E49*(($E$4-$C$4)) =$C$4+E50*(($E$4-$C$4)) =$C$4+E51*(($E$4-$C$4)) -0.08 -0.00 5-Year Dividend Growth Price Calculated using Constant Growth Formula -0.0058 -1 |=D32*(1+G32)/(F32+G32) =D33*(1+G33)/(F33+G33) =D34*(1+G34)/(F34+G34) =D35*(1+G35)/(F35+G35) =D36*(1+G36)/(F36+G36) =D37*(1+G37)/(F37+G37) =D38*(1+G38)/(F38+G38) 0.13 10.19 0.05 0 0.09 -1 Vfx A x Isa Trace Precedents Trace Dependents Show Error Watch Remove Arrows Formulas Checking Window 8 tv G H =D39*(1+G39)/(F39+G39) =D40*(1+G40)/{F40+G40) =D41*(1+G41)/(F41+G41) =D42*(1+G42)/(F42+G42) =042*(1+G42)/1742+G42) =D43*(1+G43)/(F43+G43) =D44*(1+G44)/(F44+G44) =D45*(1+G45)/(F45+G45) =D45*(1+945)/(F45+G45) =D46*(1+G46)/(F46+G46) =D46*(1+G46)/(F46+G46) =D47*(1+G47)/(F47+G47) =D48*(1+G48)/(F48+G48) =D49*(1+G49)/(F49+G49) =D50*(1+G50)/(F50+G50) =D51*(1+G51)/(F51+G51) Calculate Now Calculation Calculate Sheet Options - + Current Price 14.89 29.02 18.83 93.48 67.29 3.28 P 9 55.91 98.47 43.07 10:07 37.55 37:39 38.3 76.33 10:30 67.09 porce 193.05 195.09 38.33 30:00 71.11 71.II 9.23 19.35 29.92 I Ⓒ Share J O Sun Jan 23 6:27 PM Q Comments J31 Home Insert Draw Page Layout fx ? Insert AutoSum Recently Financial Logical Function Used 27 28 29 30 31 2 32 33 34 35 36 37 38 39 40 41 42 42 43 44 45 46 47 48 49 50 51 52 Excel File Edit View Insert Format Tools Data Window Help A ●● AutoSave OFF 53 54 55 56 57 58 59 60 61 62 ţ X B ✓ fx 8 Stock Rank 1 2 3 4 5 6 G 7 19 9 P 10 20 11 *** 12 p 13 pe 14 P 15 29 16 p 17 E 18 19 20 < A 892 ... M . с JAN 23 0 0 Total dividend 0.95 1.25 0.78 0.06 0 0 6.22 1 * 0 6 0 0.25 0.98 2.25 5.8 1.02 10 Formulas Data Review View Tell me A. Ꮎ Text Date & Lookup & Math & Time Reference Trig 1 E 0 D ✓ Beta 0.74 2.3 1.89 1.31 1.52 1.04 1.99 0.89 1.06 1.00 1.76 2.70 0.91 0.04 1.8 Lave 0.82 Live 1.12 0.99 Live 1.01 LIVE 0.67 1.11 0.78 1.2 E Qhow to take a screenshot on mac Advanced Business Finance Presentation 1 More Functions Define Name ✔ Create from Selection F Required Return =$C$4+E32*(($E$4-$C$4)) =$C$4+E33*(($E$4-$C$4)) =$C$4+E34 (($E$4-$C$4)) 0 0.085 0.0125 -0.09 -1 0 =$C$4+E35*(($E$4-$C$4)) =$C$4+E36*(($E$4-$C$4)) =$C$4+E37*(($E$4-$C$4)) =$C$4+E38*(($E$4-$C$4)) =$C$4+E39*(($E$4-$C$4)) =$C$4+E40*(($E$4-$C$4)) 0.0098 =$C$4+E41*(($E$4-$C$4)) -0.0048 =$C$4+E42*(($E$4-$C$4)) -1 =$C$4+E43*(($E$4-$C$4)) 0.021 =$C$4+E44*(($E$4-$C$4)) -0.02 =$C$4+E45*(($E$4-$C$4)) -0.02 0.04 ponov =$C$4+E46 (($E$4-$C$4)) =$C$4+E47*(($E$4-$C$4)) =$C$4+E48*(($E$4-$C$4)) =$C$4+E49*(($E$4-$C$4)) =$C$4+E50*(($E$4-$C$4)) =$C$4+E51*(($E$4-$C$4)) -0.08 -0.00 5-Year Dividend Growth Price Calculated using Constant Growth Formula -0.0058 -1 |=D32*(1+G32)/(F32+G32) =D33*(1+G33)/(F33+G33) =D34*(1+G34)/(F34+G34) =D35*(1+G35)/(F35+G35) =D36*(1+G36)/(F36+G36) =D37*(1+G37)/(F37+G37) =D38*(1+G38)/(F38+G38) 0.13 10.19 0.05 0 0.09 -1 Vfx A x Isa Trace Precedents Trace Dependents Show Error Watch Remove Arrows Formulas Checking Window 8 tv G H =D39*(1+G39)/(F39+G39) =D40*(1+G40)/{F40+G40) =D41*(1+G41)/(F41+G41) =D42*(1+G42)/(F42+G42) =042*(1+G42)/1742+G42) =D43*(1+G43)/(F43+G43) =D44*(1+G44)/(F44+G44) =D45*(1+G45)/(F45+G45) =D45*(1+945)/(F45+G45) =D46*(1+G46)/(F46+G46) =D46*(1+G46)/(F46+G46) =D47*(1+G47)/(F47+G47) =D48*(1+G48)/(F48+G48) =D49*(1+G49)/(F49+G49) =D50*(1+G50)/(F50+G50) =D51*(1+G51)/(F51+G51) Calculate Now Calculation Calculate Sheet Options - + Current Price 14.89 29.02 18.83 93.48 67.29 3.28 P 9 55.91 98.47 43.07 10:07 37.55 37:39 38.3 76.33 10:30 67.09 porce 193.05 195.09 38.33 30:00 71.11 71.II 9.23 19.35 29.92 I Ⓒ Share J O Sun Jan 23 6:27 PM Q Comments

Answer rating: 100% (QA)

Analysis of Stock Valuation and Market Efficiency 1 Comparison of Calculated Prices and Market Prices The calculated prices using the constant growth formula Gordon Growth Model are not always close a... View the full answer