1. Use the following information to evaluate whether or not you should buy stock A (assume you already own a well-diversified portfolio and are debating whether or not to add this stock to your portfolio). Show your work and justify your decision appropriately with financial theory.

Stock A	Stock A	S&P 500 (Market)	S&P 500 (Market)
Probability	Return	Probability	Return
0.10	-70%	0.25	-20%
0.20	-20%	0.50	12%
0.50	14%	0.25	28%
0.20	60%

The current yield on the 10-year Treasury Note (risk-free rate) is 2.6%. The correlation between Stock A and the market is 0.50. Hint One Remember that the S&P 500 represents the market and that you can use the two probability distributions above to get the expected return and standard deviation for both Stock A and for the market (round calculations to 2 decimal places in percentage terms). Hint Two You will use the SML to get the required return and you will need to calculate beta from the data provided (round beta to two decimal places).

2. Complete the Calculating Beta handout from the Ch. 7 Handouts in Canvas. Note that this is an Excel problem and you need to submit your Excel spreadsheet for grading. When you are done with the calculations, the yellow highlighted cells should all be filled in and the values in the instruction handout should match up. Also, be sure to answer the questions about which stock is riskier by itself and which stock is riskier as part of a portfolio and not just stop after doing the calculations.

% Return Date 12/1/2015 1/1/2016 2/1/2016 3/1/2016 4/1/2016 5/1/2016 6/1/2016 7/1/2016 8/1/2016 9/1/2016 10/1/2016 11/1/2016 12/1/2016 1/1/2017 2/1/2017 3/1/2017 4/1/2017 5/1/2017 6/1/2017 7/1/2017 8/1/2017 9/1/2017 10/1/2017 11/1/2017 12/1/2017 1/1/2018 2/1/2018 3/1/2018 4/1/2018 5/1/2018 6/1/2018 7/1/2018 8/1/2018 9/1/2018 10/1/2018 11/1/2018 12/1/2018 1/1/2019 2/1/2019 3/1/2019 4/1/2019 5/1/2019 Netflix 114.38 91.84 93.41 102.23 90.03 102.57 91.48 91.25 97.45 98.55 124.87 117 123.8 140.71 142.13 147.81 152.2 163.07 149.41 181.66 174.71 181.35 196.43 187.58 191.96 270.3 291.38 295.35 312.46 351.6 391.43 337.45 367.68 374.13 301.78 286.13 267.66 339.5 358.1 356.56 370.54 343.28 Disney 98.51 90.4 90.12 93.69 97.42 93.61 92.29 90.52 89.77 88.24 88.08 94.19 99.04 105.96 105.42 108.58 110.7 103.37 101.75 105.27 97.63 95.09 94.36 101.12 103.72 105.68 100.32 97.68 97.57 96.74 101.93 110.44 109.82 114.64 112.57 113.22 107.49 110.17 111.48 109.69 135.32 130.45 % Return S&P 500 ETF % Return 184.01 175.89 175.74 186.6 188.3 191.5 191.18 199.18 199.42 198.43 195.97 203.19 206.09 211.02 219.31 218.63 221.76 224.89 225.23 230.98 231.65 235.15 241.89 249.28 251.02 266.53 256.84 248.8 251.09 257.19 257.51 268.25 276.81 277.21 259.22 264.03 239.38 260.06 268.49 272.15 284.51 266.37 5/1/2019 6/1/2019 7/1/2019 8/1/2019 9/1/2019 10/1/2019 11/1/2019 12/1/2019 1/1/2020 2/1/2020 3/1/2020 4/1/2020 5/1/2020 6/1/2020 7/1/2020 8/1/2020 9/1/2020 10/1/2020 11/1/2020 12/1/2020 1/1/2021 2/1/2021 St. Dev. Netflix St. Dev. Disney St. Dev. S&P 500 Corr Netflix/S&P Corr Disney/S&P Beta Netflix Beta Disney 343.28 367.32 322.99 293.75 267.62 287.41 314.66 323.57 345.09 369.03 375.5 419.85 419.73 455.04 488.88 529.56 500.03 475.74 490.7 540.73 532.39 538.85 130.45 137.95 141.28 136.44 129.54 129.15 150.68 143.77 138.31 117.65 96.6 108.15 117.3 111.51 116.94 131.87 124.08 121.25 148.01 181.18 168.17 189.04 266.37 283.53 289.21 284.37 288.57 296.31 307.04 314.41 315.83 290.83 253.02 286.83 300.5 304.49 323.84 346.44 332.14 325.15 360.52 372.29 370.07 380.36 Calculating Beta This is for the Risk Return Project. The data file "Beta Calculation Spreadsheet" can be found in the Ch. 7 module. Then follow the procedures below. In addition to the calculations, you should also include a brief statement of which stock is riskier by itself, which stock is riskier as part of a portfolio and why. Procedure 1) Open file in Excel. 2) Calculate the returns for Netflix, Disney, and the S&P 500. The formula for percentage change is (This Month - Last Month) Last Month. For example, assuming the stock price is in column B and row 2 contains your first observed price, the formula for C3 should be =b3/b2 - 1. Note that you will not have a return for the first month as you don't have a price for Last Month. Once you enter the formula for the first return (cell C3 in the example below), you do not have to retype it for each cell. Instead copy and paste down the column. Then, copy the entire column and paste it under Disney and the S&P 500 (start pasting at E3 and G3 in the example below). The formula will automatically update itself for each cell, 3) Once you have calculated the % Returns for each day, you must calculate the standard deviation of Netflix % Return, the standard deviation of Disney's % Return and the standard deviation of the S&P 500% Return. Then, you must calculate the correlation between Netflix and the S&P 500 and the correlation between Disney and the S&P 500. Make sure you use the % Return columns and NOT the Price columns A) Click on the cell to the right of St. Dev. Netflix. Then, click on the insert function () from the toolbar. When you click on it, it will open a dialog box. Select Statistical. This will allow you to choose from a number of statistical formulas. Scroll down the list of selections until you get STDEV.S (in the function name window of the dialog box - this is STDEV if you are using an earlier version of Excel). Highlight STDEV.S and select OK. Now, highlight the entire column of % Return for Netflix (in our example below -- C3 through C64). Then click on OK. Repeat A for the St. Deviation of Disney and S&P 500. Click on the cell to the right of Correlation Netflix/S&P. Then, click on the insert function. Select Statistical and choose CORREL. Highlight the % Return column for Netflix in the box labeled Array 1 (C3 through C64). Tab down to Array 2 and highlight the % Return column for the S&P 500 (G3 through G64). Click on OK. D) Repeat C to calculate the correlation between Disney/S&P Calculate beta for each stock. Remember that Beta is equal to (St. Dev. Stock)(Correlation) (St. Dev. S&P) A B D E F G Date Netflix % Return Disney % Return S&P 500 % Return ETF 2 12/1/2015 114.38 98.51 184.01 1/1/2016 91.84 -19.71% 90.40 -8.23% 175.89 -4.41% 2/1/2016 93.41 1.71% 90.12 -0.31% 175.74 -0.09% 1 2/28/2021 538,85 1.21% 189.04 12.41% 380.36 2.78% 65 66 67 68 69 70 71 72 St. Dev. Netflix St. Dev. Disney St. Dev. S&P 500 Corr Netflix/S&P Corr Disney/S&P Beta Netflix Beta Disney *Hint: Make sure you put all your formulas for calculating standard deviations, correlations, and betas in column Bas the spreadsheet is preformatted to display percentages or numbers already in these cells (B66-B72). Also, use Excel to calculate beta. Don't just take the standard deviations and correlations, punch them into your calculator and then type your answers into the beta cells (B71 and B72). Make sure that the file you submit has formulas and not just numbers in the cells. **The S&P 500 Exchange Traded Fund is used for the market instead of the S&P 500 index. The reason for this is that the index itself does not take into account dividends, so the ETF is a better measure of the returns to the market. The values for Netflix and Disney are adjusted for dividends and splits, so also better capture the real returns to investors holding these stocks. % Return Date 12/1/2015 1/1/2016 2/1/2016 3/1/2016 4/1/2016 5/1/2016 6/1/2016 7/1/2016 8/1/2016 9/1/2016 10/1/2016 11/1/2016 12/1/2016 1/1/2017 2/1/2017 3/1/2017 4/1/2017 5/1/2017 6/1/2017 7/1/2017 8/1/2017 9/1/2017 10/1/2017 11/1/2017 12/1/2017 1/1/2018 2/1/2018 3/1/2018 4/1/2018 5/1/2018 6/1/2018 7/1/2018 8/1/2018 9/1/2018 10/1/2018 11/1/2018 12/1/2018 1/1/2019 2/1/2019 3/1/2019 4/1/2019 5/1/2019 Netflix 114.38 91.84 93.41 102.23 90.03 102.57 91.48 91.25 97.45 98.55 124.87 117 123.8 140.71 142.13 147.81 152.2 163.07 149.41 181.66 174.71 181.35 196.43 187.58 191.96 270.3 291.38 295.35 312.46 351.6 391.43 337.45 367.68 374.13 301.78 286.13 267.66 339.5 358.1 356.56 370.54 343.28 Disney 98.51 90.4 90.12 93.69 97.42 93.61 92.29 90.52 89.77 88.24 88.08 94.19 99.04 105.96 105.42 108.58 110.7 103.37 101.75 105.27 97.63 95.09 94.36 101.12 103.72 105.68 100.32 97.68 97.57 96.74 101.93 110.44 109.82 114.64 112.57 113.22 107.49 110.17 111.48 109.69 135.32 130.45 % Return S&P 500 ETF % Return 184.01 175.89 175.74 186.6 188.3 191.5 191.18 199.18 199.42 198.43 195.97 203.19 206.09 211.02 219.31 218.63 221.76 224.89 225.23 230.98 231.65 235.15 241.89 249.28 251.02 266.53 256.84 248.8 251.09 257.19 257.51 268.25 276.81 277.21 259.22 264.03 239.38 260.06 268.49 272.15 284.51 266.37 5/1/2019 6/1/2019 7/1/2019 8/1/2019 9/1/2019 10/1/2019 11/1/2019 12/1/2019 1/1/2020 2/1/2020 3/1/2020 4/1/2020 5/1/2020 6/1/2020 7/1/2020 8/1/2020 9/1/2020 10/1/2020 11/1/2020 12/1/2020 1/1/2021 2/1/2021 St. Dev. Netflix St. Dev. Disney St. Dev. S&P 500 Corr Netflix/S&P Corr Disney/S&P Beta Netflix Beta Disney 343.28 367.32 322.99 293.75 267.62 287.41 314.66 323.57 345.09 369.03 375.5 419.85 419.73 455.04 488.88 529.56 500.03 475.74 490.7 540.73 532.39 538.85 130.45 137.95 141.28 136.44 129.54 129.15 150.68 143.77 138.31 117.65 96.6 108.15 117.3 111.51 116.94 131.87 124.08 121.25 148.01 181.18 168.17 189.04 266.37 283.53 289.21 284.37 288.57 296.31 307.04 314.41 315.83 290.83 253.02 286.83 300.5 304.49 323.84 346.44 332.14 325.15 360.52 372.29 370.07 380.36 Calculating Beta This is for the Risk Return Project. The data file "Beta Calculation Spreadsheet" can be found in the Ch. 7 module. Then follow the procedures below. In addition to the calculations, you should also include a brief statement of which stock is riskier by itself, which stock is riskier as part of a portfolio and why. Procedure 1) Open file in Excel. 2) Calculate the returns for Netflix, Disney, and the S&P 500. The formula for percentage change is (This Month - Last Month) Last Month. For example, assuming the stock price is in column B and row 2 contains your first observed price, the formula for C3 should be =b3/b2 - 1. Note that you will not have a return for the first month as you don't have a price for Last Month. Once you enter the formula for the first return (cell C3 in the example below), you do not have to retype it for each cell. Instead copy and paste down the column. Then, copy the entire column and paste it under Disney and the S&P 500 (start pasting at E3 and G3 in the example below). The formula will automatically update itself for each cell, 3) Once you have calculated the % Returns for each day, you must calculate the standard deviation of Netflix % Return, the standard deviation of Disney's % Return and the standard deviation of the S&P 500% Return. Then, you must calculate the correlation between Netflix and the S&P 500 and the correlation between Disney and the S&P 500. Make sure you use the % Return columns and NOT the Price columns A) Click on the cell to the right of St. Dev. Netflix. Then, click on the insert function () from the toolbar. When you click on it, it will open a dialog box. Select Statistical. This will allow you to choose from a number of statistical formulas. Scroll down the list of selections until you get STDEV.S (in the function name window of the dialog box - this is STDEV if you are using an earlier version of Excel). Highlight STDEV.S and select OK. Now, highlight the entire column of % Return for Netflix (in our example below -- C3 through C64). Then click on OK. Repeat A for the St. Deviation of Disney and S&P 500. Click on the cell to the right of Correlation Netflix/S&P. Then, click on the insert function. Select Statistical and choose CORREL. Highlight the % Return column for Netflix in the box labeled Array 1 (C3 through C64). Tab down to Array 2 and highlight the % Return column for the S&P 500 (G3 through G64). Click on OK. D) Repeat C to calculate the correlation between Disney/S&P Calculate beta for each stock. Remember that Beta is equal to (St. Dev. Stock)(Correlation) (St. Dev. S&P) A B D E F G Date Netflix % Return Disney % Return S&P 500 % Return ETF 2 12/1/2015 114.38 98.51 184.01 1/1/2016 91.84 -19.71% 90.40 -8.23% 175.89 -4.41% 2/1/2016 93.41 1.71% 90.12 -0.31% 175.74 -0.09% 1 2/28/2021 538,85 1.21% 189.04 12.41% 380.36 2.78% 65 66 67 68 69 70 71 72 St. Dev. Netflix St. Dev. Disney St. Dev. S&P 500 Corr Netflix/S&P Corr Disney/S&P Beta Netflix Beta Disney *Hint: Make sure you put all your formulas for calculating standard deviations, correlations, and betas in column Bas the spreadsheet is preformatted to display percentages or numbers already in these cells (B66-B72). Also, use Excel to calculate beta. Don't just take the standard deviations and correlations, punch them into your calculator and then type your answers into the beta cells (B71 and B72). Make sure that the file you submit has formulas and not just numbers in the cells. **The S&P 500 Exchange Traded Fund is used for the market instead of the S&P 500 index. The reason for this is that the index itself does not take into account dividends, so the ETF is a better measure of the returns to the market. The values for Netflix and Disney are adjusted for dividends and splits, so also better capture the real returns to investors holding these stocks