Use the data provided in Sheet 3 named "Raw Data" to complete the missing items in the sheets 4 and 5 named "Monthly Return Template and "Solution Template" respectively. Sheet 3 " Raw Data" Date Closing Market Index value Closing Price for GOOG Closing Price for NFLX 2/1/2017 2278.87 823.21 142.13 GOOG is Google 3/1/2017 2363.64 829.56 147.81 NFLX is Netflix 4/1/2017 2362.72 905.96 152.20 5/1/2017 2384.20 964.86 163.07 6/1/2017 2411.80 908.73 149.41 7/1/2017 2423.41 930.50 181.66 8/1/2017 2470.30 939.33 174.71 9/1/2017 2471.65 959.11 181.35 10/1/2017 2519.36 1016.64 196.43 11/1/2017 2575.26 1021.41 187.58 12/1/2017 2647.58 1046.40 191.96 1/1/2018 2673.61 1169.94 270.30 2/1/2018 2823.81 1104.73 291.38 3/1/2018 2713.83 1031.79 295.35 4/1/2018 2640.87 1017.33 312.46 5/1/2018 2648.05 1084.99 351.60 6/1/2018 2705.27 1115.65 391.43 7/1/2018 2718.37 1217.26 337.45 8/1/2018 2816.29 1218.19 367.68 9/1/2018 2901.52 1193.47 374.13 10/1/2018 2913.98 1076.77 301.78 11/1/2018 2711.74 1094.43 286.13 12/1/2018 2760.17 1035.61 267.66 1/1/2019 2506.85 1116.37 339.50 2/1/2019 2704.10 1119.92 358.10 3/1/2019 2784.49 1173.31 356.56 4/1/2019 2834.40 1188.48 370.54 5/1/2019 2945.83 1103.63 343.28 6/1/2019 2752.06 1080.91 367.32 7/1/2019 2941.76 1216.68 322.99 8/1/2019 2980.38 1188.10 293.75 9/1/2019 2926.46 1219.00 267.62 10/1/2019 2976.74 1260.11 287.41 11/1/2019 3037.56 1304.96 314.66 12/1/2019 3140.98 1337.02 323.57 1/1/2020 3230.78 1434.23 345.09 Sheet 4 Monthly Return Google & Netflix Date Closing Price for GOOG Monthly Return for GOOG Closing Price for NFLX Monthly Return for NFLX Closing Market Index value Monthly Market Return 2/1/2017 823.21 142.13 2278.87 3/1/2017 829.56 147.81 2363.64 4/1/2017 905.96 152.20 2362.72 5/1/2017 964.86 163.07 2384.20 6/1/2017 908.73 149.41 2411.80 7/1/2017 930.50 181.66 2423.41 8/1/2017 939.33 174.71 2470.30 9/1/2017 959.11 181.35 2471.65 10/1/2017 1016.64 196.43 2519.36 11/1/2017 1021.41 187.58 2575.26 12/1/2017 1046.40 191.96 2647.58 1/1/2018 1169.94 270.30 2673.61 2/1/2018 1104.73 291.38 2823.81 3/1/2018 1031.79 295.35 2713.83 4/1/2018 1017.33 312.46 2640.87 5/1/2018 1084.99 351.60 2648.05 6/1/2018 1115.65 391.43 2705.27 7/1/2018 1217.26 337.45 2718.37 8/1/2018 1218.19 367.68 2816.29 9/1/2018 1193.47 374.13 2901.52 10/1/2018 1076.77 301.78 2913.98 11/1/2018 1094.43 286.13 2711.74 12/1/2018 1035.61 267.66 2760.17 1/1/2019 1116.37 339.50 2506.85 2/1/2019 1119.92 358.10 2704.10 3/1/2019 1173.31 356.56 2784.49 4/1/2019 1188.48 370.54 2834.40 5/1/2019 1103.63 343.28 2945.83 6/1/2019 1080.91 367.32 2752.06 7/1/2019 1216.68 322.99 2941.76 8/1/2019 1188.10 293.75 2980.38 9/1/2019 1219.00 267.62 2926.46 10/1/2019 1260.11 287.41 2976.74 11/1/2019 1304.96 314.66 3037.56 12/1/2019 1337.02 323.57 3140.98 1/1/2020 1434.23 345.09 3230.78 Sheet 5 G & N Date GOOG Return NFLX Return Market Return Risk-Free Return GOOG Excess Return* NFLX Excess Return* Market Excess Return* 3/1/2017 0.0050 4/1/2017 0.0050 5/1/2017 0.0050 6/1/2017 0.0050 7/1/2017 0.0050 8/1/2017 0.0050 9/1/2017 0.0050 10/1/2017 0.0050 11/1/2017 0.0050 12/1/2017 0.0050 1/1/2018 0.0050 2/1/2018 0.0050 3/1/2018 0.0050 4/1/2018 0.0050 5/1/2018 0.0050 6/1/2018 0.0050 7/1/2018 0.0050 8/1/2018 0.0050 9/1/2018 0.0050 10/1/2018 0.0050 11/1/2018 0.0050 12/1/2018 0.0050 1/1/2019 0.0050 2/1/2019 0.0050 3/1/2019 0.0050 4/1/2019 0.0050 5/1/2019 0.0050 6/1/2019 0.0050 7/1/2019 0.0050 8/1/2019 0.0050 9/1/2019 0.0050 10/1/2019 0.0050 11/1/2019 0.0050 12/1/2019 0.0050 1/1/2020 0.0050 SECTION 3 Average Excess Return Std. Deviation of Return Beta Covariance**
Use the data provided in Sheet 3 named "Raw Data" to complete the missing items in the sheets 4 and 5 named "Monthly Return Template and "Solution Template" respectively. Sheet 3 " Raw Data" Date Closing Market Index value Closing Price for GOOG Closing Price for NFLX 2/1/2017 2278.87 823.21 142.13 GOOG is Google 3/1/2017 2363.64 829.56 147.81 NFLX is Netflix 4/1/2017 2362.72 905.96 152.20 5/1/2017 2384.20 964.86 163.07 6/1/2017 2411.80 908.73 149.41 7/1/2017 2423.41 930.50 181.66 8/1/2017 2470.30 939.33 174.71 9/1/2017 2471.65 959.11 181.35 10/1/2017 2519.36 1016.64 196.43 11/1/2017 2575.26 1021.41 187.58 12/1/2017 2647.58 1046.40 191.96 1/1/2018 2673.61 1169.94 270.30 2/1/2018 2823.81 1104.73 291.38 3/1/2018 2713.83 1031.79 295.35 4/1/2018 2640.87 1017.33 312.46 5/1/2018 2648.05 1084.99 351.60 6/1/2018 2705.27 1115.65 391.43 7/1/2018 2718.37 1217.26 337.45 8/1/2018 2816.29 1218.19 367.68 9/1/2018 2901.52 1193.47 374.13 10/1/2018 2913.98 1076.77 301.78 11/1/2018 2711.74 1094.43 286.13 12/1/2018 2760.17 1035.61 267.66 1/1/2019 2506.85 1116.37 339.50 2/1/2019 2704.10 1119.92 358.10 3/1/2019 2784.49 1173.31 356.56 4/1/2019 2834.40 1188.48 370.54 5/1/2019 2945.83 1103.63 343.28 6/1/2019 2752.06 1080.91 367.32 7/1/2019 2941.76 1216.68 322.99 8/1/2019 2980.38 1188.10 293.75 9/1/2019 2926.46 1219.00 267.62 10/1/2019 2976.74 1260.11 287.41 11/1/2019 3037.56 1304.96 314.66 12/1/2019 3140.98 1337.02 323.57 1/1/2020 3230.78 1434.23 345.09 Sheet 4 Monthly Return Google & Netflix Date Closing Price for GOOG Monthly Return for GOOG Closing Price for NFLX Monthly Return for NFLX Closing Market Index value Monthly Market Return 2/1/2017 823.21 142.13 2278.87 3/1/2017 829.56 147.81 2363.64 4/1/2017 905.96 152.20 2362.72 5/1/2017 964.86 163.07 2384.20 6/1/2017 908.73 149.41 2411.80 7/1/2017 930.50 181.66 2423.41 8/1/2017 939.33 174.71 2470.30 9/1/2017 959.11 181.35 2471.65 10/1/2017 1016.64 196.43 2519.36 11/1/2017 1021.41 187.58 2575.26 12/1/2017 1046.40 191.96 2647.58 1/1/2018 1169.94 270.30 2673.61 2/1/2018 1104.73 291.38 2823.81 3/1/2018 1031.79 295.35 2713.83 4/1/2018 1017.33 312.46 2640.87 5/1/2018 1084.99 351.60 2648.05 6/1/2018 1115.65 391.43 2705.27 7/1/2018 1217.26 337.45 2718.37 8/1/2018 1218.19 367.68 2816.29 9/1/2018 1193.47 374.13 2901.52 10/1/2018 1076.77 301.78 2913.98 11/1/2018 1094.43 286.13 2711.74 12/1/2018 1035.61 267.66 2760.17 1/1/2019 1116.37 339.50 2506.85 2/1/2019 1119.92 358.10 2704.10 3/1/2019 1173.31 356.56 2784.49 4/1/2019 1188.48 370.54 2834.40 5/1/2019 1103.63 343.28 2945.83 6/1/2019 1080.91 367.32 2752.06 7/1/2019 1216.68 322.99 2941.76 8/1/2019 1188.10 293.75 2980.38 9/1/2019 1219.00 267.62 2926.46 10/1/2019 1260.11 287.41 2976.74 11/1/2019 1304.96 314.66 3037.56 12/1/2019 1337.02 323.57 3140.98 1/1/2020 1434.23 345.09 3230.78 Sheet 5 G & N Date GOOG Return NFLX Return Market Return Risk-Free Return GOOG Excess Return* NFLX Excess Return* Market Excess Return* 3/1/2017 0.0050 4/1/2017 0.0050 5/1/2017 0.0050 6/1/2017 0.0050 7/1/2017 0.0050 8/1/2017 0.0050 9/1/2017 0.0050 10/1/2017 0.0050 11/1/2017 0.0050 12/1/2017 0.0050 1/1/2018 0.0050 2/1/2018 0.0050 3/1/2018 0.0050 4/1/2018 0.0050 5/1/2018 0.0050 6/1/2018 0.0050 7/1/2018 0.0050 8/1/2018 0.0050 9/1/2018 0.0050 10/1/2018 0.0050 11/1/2018 0.0050 12/1/2018 0.0050 1/1/2019 0.0050 2/1/2019 0.0050 3/1/2019 0.0050 4/1/2019 0.0050 5/1/2019 0.0050 6/1/2019 0.0050 7/1/2019 0.0050 8/1/2019 0.0050 9/1/2019 0.0050 10/1/2019 0.0050 11/1/2019 0.0050 12/1/2019 0.0050 1/1/2020 0.0050 SECTION 3 Average Excess Return Std. Deviation of Return Beta Covariance**
Essentials Of Investments
11th Edition
ISBN:9781260013924
Author:Bodie, Zvi, Kane, Alex, MARCUS, Alan J.
Publisher:Bodie, Zvi, Kane, Alex, MARCUS, Alan J.
Chapter1: Investments: Background And Issues
Section: Chapter Questions
Problem 1PS
Related questions
Concept explainers
Dividend Valuation
Dividend refers to a reward or cash that a company gives to its shareholders out of the profits. Dividends can be issued in various forms such as cash payment, stocks, or in any other form as per the company norms. It is usually a part of the profit that the company shares with its shareholders.
Dividend Discount Model
Dividend payments are generally paid to investors or shareholders of a company when the company earns profit for the year, thus representing growth. The dividend discount model is an important method used to forecast the price of a company’s stock. It is based on the computation methodology that the present value of all its future dividends is equivalent to the value of the company.
Capital Gains Yield
It may be referred to as the earnings generated on an investment over a particular period of time. It is generally expressed as a percentage and includes some dividends or interest earned by holding a particular security. Cases, where it is higher normally, indicate the higher income and lower risk. It is mostly computed on an annual basis and is different from the total return on investment. In case it becomes too high, indicates that either the stock prices are going down or the company is paying higher dividends.
Stock Valuation
In simple words, stock valuation is a tool to calculate the current price, or value, of a company. It is used to not only calculate the value of the company but help an investor decide if they want to buy, sell or hold a company's stocks.
Topic Video
Question
100%
| Use the data provided in Sheet 3 named "Raw Data" to complete the missing items in the sheets 4 and 5 named "Monthly Return Template and "Solution Template" respectively. |
Sheet 3 " Raw Data"
| Date | Closing Market Index value | Closing Price for GOOG | Closing Price for NFLX | |||
| 2/1/2017 | 2278.87 | 823.21 | 142.13 | GOOG is Google | ||
| 3/1/2017 | 2363.64 | 829.56 | 147.81 | NFLX is Netflix | ||
| 4/1/2017 | 2362.72 | 905.96 | 152.20 | |||
| 5/1/2017 | 2384.20 | 964.86 | 163.07 | |||
| 6/1/2017 | 2411.80 | 908.73 | 149.41 | |||
| 7/1/2017 | 2423.41 | 930.50 | 181.66 | |||
| 8/1/2017 | 2470.30 | 939.33 | 174.71 | |||
| 9/1/2017 | 2471.65 | 959.11 | 181.35 | |||
| 10/1/2017 | 2519.36 | 1016.64 | 196.43 | |||
| 11/1/2017 | 2575.26 | 1021.41 | 187.58 | |||
| 12/1/2017 | 2647.58 | 1046.40 | 191.96 | |||
| 1/1/2018 | 2673.61 | 1169.94 | 270.30 | |||
| 2/1/2018 | 2823.81 | 1104.73 | 291.38 | |||
| 3/1/2018 | 2713.83 | 1031.79 | 295.35 | |||
| 4/1/2018 | 2640.87 | 1017.33 | 312.46 | |||
| 5/1/2018 | 2648.05 | 1084.99 | 351.60 | |||
| 6/1/2018 | 2705.27 | 1115.65 | 391.43 | |||
| 7/1/2018 | 2718.37 | 1217.26 | 337.45 | |||
| 8/1/2018 | 2816.29 | 1218.19 | 367.68 | |||
| 9/1/2018 | 2901.52 | 1193.47 | 374.13 | |||
| 10/1/2018 | 2913.98 | 1076.77 | 301.78 | |||
| 11/1/2018 | 2711.74 | 1094.43 | 286.13 | |||
| 12/1/2018 | 2760.17 | 1035.61 | 267.66 | |||
| 1/1/2019 | 2506.85 | 1116.37 | 339.50 | |||
| 2/1/2019 | 2704.10 | 1119.92 | 358.10 | |||
| 3/1/2019 | 2784.49 | 1173.31 | 356.56 | |||
| 4/1/2019 | 2834.40 | 1188.48 | 370.54 | |||
| 5/1/2019 | 2945.83 | 1103.63 | 343.28 | |||
| 6/1/2019 | 2752.06 | 1080.91 | 367.32 | |||
| 7/1/2019 | 2941.76 | 1216.68 | 322.99 | |||
| 8/1/2019 | 2980.38 | 1188.10 | 293.75 | |||
| 9/1/2019 | 2926.46 | 1219.00 | 267.62 | |||
| 10/1/2019 | 2976.74 | 1260.11 | 287.41 | |||
| 11/1/2019 | 3037.56 | 1304.96 | 314.66 | |||
| 12/1/2019 | 3140.98 | 1337.02 | 323.57 | |||
| 1/1/2020 | 3230.78 | 1434.23 | 345.09 |
Sheet 4 Monthly Return Google & Netflix
| Date | Closing Price for GOOG | Monthly Return for GOOG | Closing Price for NFLX | Monthly Return for NFLX | Closing Market Index value | Monthly Market Return |
| 2/1/2017 | 823.21 | 142.13 | 2278.87 | |||
| 3/1/2017 | 829.56 | 147.81 | 2363.64 | |||
| 4/1/2017 | 905.96 | 152.20 | 2362.72 | |||
| 5/1/2017 | 964.86 | 163.07 | 2384.20 | |||
| 6/1/2017 | 908.73 | 149.41 | 2411.80 | |||
| 7/1/2017 | 930.50 | 181.66 | 2423.41 | |||
| 8/1/2017 | 939.33 | 174.71 | 2470.30 | |||
| 9/1/2017 | 959.11 | 181.35 | 2471.65 | |||
| 10/1/2017 | 1016.64 | 196.43 | 2519.36 | |||
| 11/1/2017 | 1021.41 | 187.58 | 2575.26 | |||
| 12/1/2017 | 1046.40 | 191.96 | 2647.58 | |||
| 1/1/2018 | 1169.94 | 270.30 | 2673.61 | |||
| 2/1/2018 | 1104.73 | 291.38 | 2823.81 | |||
| 3/1/2018 | 1031.79 | 295.35 | 2713.83 | |||
| 4/1/2018 | 1017.33 | 312.46 | 2640.87 | |||
| 5/1/2018 | 1084.99 | 351.60 | 2648.05 | |||
| 6/1/2018 | 1115.65 | 391.43 | 2705.27 | |||
| 7/1/2018 | 1217.26 | 337.45 | 2718.37 | |||
| 8/1/2018 | 1218.19 | 367.68 | 2816.29 | |||
| 9/1/2018 | 1193.47 | 374.13 | 2901.52 | |||
| 10/1/2018 | 1076.77 | 301.78 | 2913.98 | |||
| 11/1/2018 | 1094.43 | 286.13 | 2711.74 | |||
| 12/1/2018 | 1035.61 | 267.66 | 2760.17 | |||
| 1/1/2019 | 1116.37 | 339.50 | 2506.85 | |||
| 2/1/2019 | 1119.92 | 358.10 | 2704.10 | |||
| 3/1/2019 | 1173.31 | 356.56 | 2784.49 | |||
| 4/1/2019 | 1188.48 | 370.54 | 2834.40 | |||
| 5/1/2019 | 1103.63 | 343.28 | 2945.83 | |||
| 6/1/2019 | 1080.91 | 367.32 | 2752.06 | |||
| 7/1/2019 | 1216.68 | 322.99 | 2941.76 | |||
| 8/1/2019 | 1188.10 | 293.75 | 2980.38 | |||
| 9/1/2019 | 1219.00 | 267.62 | 2926.46 | |||
| 10/1/2019 | 1260.11 | 287.41 | 2976.74 | |||
| 11/1/2019 | 1304.96 | 314.66 | 3037.56 | |||
| 12/1/2019 | 1337.02 | 323.57 | 3140.98 | |||
| 1/1/2020 | 1434.23 | 345.09 | 3230.78 |
Sheet 5 G & N
| Date | GOOG Return | NFLX Return | Market Return | Risk-Free Return | GOOG Excess Return* | NFLX Excess Return* | Market Excess Return* | |
| 3/1/2017 | 0.0050 | |||||||
| 4/1/2017 | 0.0050 | |||||||
| 5/1/2017 | 0.0050 | |||||||
| 6/1/2017 | 0.0050 | |||||||
| 7/1/2017 | 0.0050 | |||||||
| 8/1/2017 | 0.0050 | |||||||
| 9/1/2017 | 0.0050 | |||||||
| 10/1/2017 | 0.0050 | |||||||
| 11/1/2017 | 0.0050 | |||||||
| 12/1/2017 | 0.0050 | |||||||
| 1/1/2018 | 0.0050 | |||||||
| 2/1/2018 | 0.0050 | |||||||
| 3/1/2018 | 0.0050 | |||||||
| 4/1/2018 | 0.0050 | |||||||
| 5/1/2018 | 0.0050 | |||||||
| 6/1/2018 | 0.0050 | |||||||
| 7/1/2018 | 0.0050 | |||||||
| 8/1/2018 | 0.0050 | |||||||
| 9/1/2018 | 0.0050 | |||||||
| 10/1/2018 | 0.0050 | |||||||
| 11/1/2018 | 0.0050 | |||||||
| 12/1/2018 | 0.0050 | |||||||
| 1/1/2019 | 0.0050 | |||||||
| 2/1/2019 | 0.0050 | |||||||
| 3/1/2019 | 0.0050 | |||||||
| 4/1/2019 | 0.0050 | |||||||
| 5/1/2019 | 0.0050 | |||||||
| 6/1/2019 | 0.0050 | |||||||
| 7/1/2019 | 0.0050 | |||||||
| 8/1/2019 | 0.0050 | |||||||
| 9/1/2019 | 0.0050 | |||||||
| 10/1/2019 | 0.0050 | |||||||
| 11/1/2019 | 0.0050 | |||||||
| 12/1/2019 | 0.0050 | |||||||
| 1/1/2020 | 0.0050 | |||||||
| SECTION 3 | Average Excess Return | |||||||
| Std. Deviation of Return | ||||||||
| Beta | ||||||||
| Covariance** | ||||||||
| Portfolio | SECTION 4 | SECTION 5 | SECTION 6 | SECTION 7 | ||||
| Weight on GOOG | Weight on NFLX | Portfolio Return | Portfolio Beta | Portfolio Standard Deviation**** | Portfolio Nature: Defensive/Aggressive | |||
| 0 | 1 | |||||||
| 0.1 | 0.9 | |||||||
| 0.2 | 0.8 | |||||||
| 0.3 | 0.7 | |||||||
| 0.4 | 0.6 | |||||||
| 0.5 | 0.5 | |||||||
| 0.6 | 0.4 | |||||||
| 0.7 | 0.3 | |||||||
| 0.8 | 0.2 | |||||||
| 0.9 | 0.1 | |||||||
| 1 | 0 | |||||||
|
|
||||||||
| SECTION 8 | Draw a Characteristic Line*** for GOOG | |||||||
| SECTION 9 | Draw a Characteristic Line*** for NFLX |
Notes:
* Excess return is the return that is earned over and above the risk-free return
** Covariance measures the combined risk of holding two stocks at the same time. It is computed by using the Covariance function in Excel
*** A characteristic line is the line of best fit which describes the scatter plot when a stock's excess return is measured on the X-Axis (Dependent variable) and the market's excess return is measured on the Y-Axis (Independent variable).
Expert Solution
This question has been solved!
Explore an expertly crafted, step-by-step solution for a thorough understanding of key concepts.
This is a popular solution!
Trending now
This is a popular solution!
Step by step
Solved in 3 steps with 8 images
Knowledge Booster
Learn more about
Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, finance and related others by exploring similar questions and additional content below.Recommended textbooks for you
Essentials Of Investments
Finance
ISBN:
9781260013924
Author:
Bodie, Zvi, Kane, Alex, MARCUS, Alan J.
Publisher:
Mcgraw-hill Education,
Essentials Of Investments
Finance
ISBN:
9781260013924
Author:
Bodie, Zvi, Kane, Alex, MARCUS, Alan J.
Publisher:
Mcgraw-hill Education,
Foundations Of Finance
Finance
ISBN:
9780134897264
Author:
KEOWN, Arthur J., Martin, John D., PETTY, J. William
Publisher:
Pearson,
Fundamentals of Financial Management (MindTap Cou…
Finance
ISBN:
9781337395250
Author:
Eugene F. Brigham, Joel F. Houston
Publisher:
Cengage Learning
Corporate Finance (The Mcgraw-hill/Irwin Series i…
Finance
ISBN:
9780077861759
Author:
Stephen A. Ross Franco Modigliani Professor of Financial Economics Professor, Randolph W Westerfield Robert R. Dockson Deans Chair in Bus. Admin., Jeffrey Jaffe, Bradford D Jordan Professor
Publisher:
McGraw-Hill Education