Form a correlation matrix and a variance-covariance matrix from the excess returns of the three stocks and the Russell 1000.

Calculate betas for all three of the stocks in two ways: (i) using the entries from the variance-covariance matrix, and (ii) using the SLOPE() function.

Link to Excel File: https://docs.google.com/spreadsheets/d/1WjtfWuJuzDOWiXEtrpEaItooKOGvXF4NyGtjnJDpKbU/edit#gid=0

CoursHeroTranscribedText

Transcribed Image Text:

Share Price and Yield Data BBY Best Buy 30.45 27.93 32.39 32.44 32.08 32.17 30.60 33.60 38.48 38.18 38.91 45.70 Date 12/31/15 1/31/16 2/29/16 3/31/16 4/30/16 5/31/16 6/30/16 7/31/16 8/31/16 9/30/16 10/31/16 11/30/16 12/31/16 1/31/17 2/28/17 3/31/17 4/30/17 5/31/17 6/30/17 7/31/17 8/31/17 9/30/17 10/31/17 11/30/17 12/31/17 1/31/18 2/28/18 3/31/18 4/30/18 5/31/18 6/30/18 7/31/18 8/31/18 9/30/18 10/31/18 11/30/18 12/31/18 1/31/19 2/28/19 3/31/19 4/30/19 5/31/19 6/30/19 7/31/19 8/31/19 9/30/19 10/31/19 11/30/19 12/31/19 1/31/20 2/29/20 3/31/20 4/30/20 5/31/20 6/30/20 7/31/20 8/31/20 9/30/20 10/31/20 11/30/20 12/31/20 42.67 44.52 44.13 49.15 51.81 59.39 57.33 58.34 54.26 56.96 55.98 59.61 68.47 73.06 72.44 69.99 76.53 68.25 74.58 75.03 79.56 79.36 70.16 64.59 52.96 59.24 68.84 71.06 74.41 62.67 69.73 76.53 63.65 68.99 71.83 80.64 87.80 75.65 57.00 76.73 78.09 87.27 99.59 110.91 111.29 111.55 108.80 99.79 114.93 NKE Nike 62.50 62.01 61.59 61.47 58.94 55.22 55.20 55.50 57.64 52.65 50.18 50.07 50.83 52.90 57.16 55.73 55.41 52.99 59.00 59.05 52.81 51.85 54.99 60.42 62.55 68.22 67.03 66.44 68.39 71.80 79.68 76.91 82.20 84.72 75.04 75.12 74.14 81.88 85.73 84.21 87.83 77.14 83.95 86.03 84.50 93.92 89.55 93.49 101.31 96.30 89.38 82.74 87.18 98.58 98.05 97.61 111.89 125.54 120.08 134.70 141.47 STT State Street 66.36 55.73 54.78 58.52 62.30 63.06 53.92 65.78 70.24 69.63 70.21 78.80 77.72 76.20 79.71 79.61 83.90 81.46 89.73 93.23 92.49 95.54 92.00 95.35 97.61 110.17 106.15 99.73 99.78 96.11 93.09 88.31 86.91 83.78 68.75 73.02 63.07 70.90 71.87 65.81 67.66 55.25 56.06 58.09 51.31 59.19 66.07 75.10 79.10 68.11 53.27 63.04 60.96 63.55 63.79 68.09 59.33 58.90 70.48 72.78 74.17 RUI Russell 1000 5796.184 5521.7100 6170.5150 6233.9150 6337.7490 6354.6020 6559.5550 6603.0760 6585.0350 6479.2330 6740.9920 6829.8910 6999.1720 7266.2060 7241.3780 7347.9370 7442.1340 7495.3270 7602.8240 7662.7620 7786.9020 8001.2940 8242.8290 8308.8020 8796.1390 8470.4620 8246.4150 8302.3120 8516.0160 8569.5820 8825.5770 9165.8880 9159.0930 8548.3600 8712.7960 7891.1890 8587.5160 8879.9080 9001.2140 9392.7100 8798.1260 9351.9220 9556.6940 9378.2100 9504.1290 9745.7430 10117.0310 10350.8270 10413.1170 9573.1430 8249.9350 9390.7000 9874.8590 10063.0130 10698.3390 11479.5220 11008.9290 10800.5880 12063.4620 12512.9690 12903.5800 1-Year T-Bill Annual YTM 0.5200 0.6400 0.5400 0.5700 0.5400 0.5000 0.5500 0.5800 0.6400 0.7100 0.8400 0.8000 0.7900 0.9800 1.0100 1.0900 1.1700 1.1900 1.2000 1.2500 1.3600 1.5100 1.6500 1.7500 1.9000 2.0000 2.0800 2.2000 2.2500 2.3100 2.3600 2.4700 2.5600 2.6000 2.5700 2.5000 2.4700 2.4100 2.3400 2.2700 1.9400 1.9100 1.7300 1.7500 1.5700 1.5300 1.5100 1.4900 1.3700 0.3200 0.1800 0.1600 0.1800 0.1500 0.1300 0.1300 0.1300 0.1200 0.1000 0.1000 0.0700 Best Buy Ex-Dividend Date 3/15/16 6/10/16 9/9/16 12/6/16 3/20/17 6/13/17 9/18/17 12/6/17 3/21/18 6/13/18 9/17/18 12/10/18 3/19/19 6/12/19 9/18/19 12/16/19 3/18/20 6/10/20 9/14/20 12/14/20 Amount 0.28 0.28 0.28 0.28 0.34 0.34 0.34 0.34 0.45 0.45 0.45 0.45 0.50 0.50 0.50 0.50 0.55 0.55 0.55 0.55 Nike Ex-Dividend Date 3/3/16 6/2/16 9/1/16 12/1/16 3/2/17 6/1/17 8/31/17 12/1/17 3/2/18 6/1/18 8/31/18 11/30/18 3/1/19 5/31/19 8/30/19 11/29/19 2/28/20 5/29/20 8/28/20 12/4/20 Amount 0.16 0.16 0.16 0.18 0.18 0.18 0.18 0.20 0.20 0.20 0.20 0.22 0.22 0.22 0.22 0.25 0.25 0.25 0.25 0.28 State Street Ex-Dividend Date 3/30/16 6/29/16 9/29/16 12/29/16 3/30/17 6/29/17 9/29/17 12/29/17 3/29/18 6/29/18 9/28/18 12/31/18 3/29/19 6/28/19 9/30/19 12/31/19 3/31/20 6/30/20 9/30/20 12/31/20 Amount 0.34 0.34 0.38 0.38 0.38 0.38 0.42 0.42 0.42 0.42 0.47 0.47 0.47 0.47 0.52 0.52 0.52 0.52 0.52 0.52 Share Price and Yield Data BBY Best Buy 30.45 27.93 32.39 32.44 32.08 32.17 30.60 33.60 38.48 38.18 38.91 45.70 Date 12/31/15 1/31/16 2/29/16 3/31/16 4/30/16 5/31/16 6/30/16 7/31/16 8/31/16 9/30/16 10/31/16 11/30/16 12/31/16 1/31/17 2/28/17 3/31/17 4/30/17 5/31/17 6/30/17 7/31/17 8/31/17 9/30/17 10/31/17 11/30/17 12/31/17 1/31/18 2/28/18 3/31/18 4/30/18 5/31/18 6/30/18 7/31/18 8/31/18 9/30/18 10/31/18 11/30/18 12/31/18 1/31/19 2/28/19 3/31/19 4/30/19 5/31/19 6/30/19 7/31/19 8/31/19 9/30/19 10/31/19 11/30/19 12/31/19 1/31/20 2/29/20 3/31/20 4/30/20 5/31/20 6/30/20 7/31/20 8/31/20 9/30/20 10/31/20 11/30/20 12/31/20 42.67 44.52 44.13 49.15 51.81 59.39 57.33 58.34 54.26 56.96 55.98 59.61 68.47 73.06 72.44 69.99 76.53 68.25 74.58 75.03 79.56 79.36 70.16 64.59 52.96 59.24 68.84 71.06 74.41 62.67 69.73 76.53 63.65 68.99 71.83 80.64 87.80 75.65 57.00 76.73 78.09 87.27 99.59 110.91 111.29 111.55 108.80 99.79 114.93 NKE Nike 62.50 62.01 61.59 61.47 58.94 55.22 55.20 55.50 57.64 52.65 50.18 50.07 50.83 52.90 57.16 55.73 55.41 52.99 59.00 59.05 52.81 51.85 54.99 60.42 62.55 68.22 67.03 66.44 68.39 71.80 79.68 76.91 82.20 84.72 75.04 75.12 74.14 81.88 85.73 84.21 87.83 77.14 83.95 86.03 84.50 93.92 89.55 93.49 101.31 96.30 89.38 82.74 87.18 98.58 98.05 97.61 111.89 125.54 120.08 134.70 141.47 STT State Street 66.36 55.73 54.78 58.52 62.30 63.06 53.92 65.78 70.24 69.63 70.21 78.80 77.72 76.20 79.71 79.61 83.90 81.46 89.73 93.23 92.49 95.54 92.00 95.35 97.61 110.17 106.15 99.73 99.78 96.11 93.09 88.31 86.91 83.78 68.75 73.02 63.07 70.90 71.87 65.81 67.66 55.25 56.06 58.09 51.31 59.19 66.07 75.10 79.10 68.11 53.27 63.04 60.96 63.55 63.79 68.09 59.33 58.90 70.48 72.78 74.17 RUI Russell 1000 5796.184 5521.7100 6170.5150 6233.9150 6337.7490 6354.6020 6559.5550 6603.0760 6585.0350 6479.2330 6740.9920 6829.8910 6999.1720 7266.2060 7241.3780 7347.9370 7442.1340 7495.3270 7602.8240 7662.7620 7786.9020 8001.2940 8242.8290 8308.8020 8796.1390 8470.4620 8246.4150 8302.3120 8516.0160 8569.5820 8825.5770 9165.8880 9159.0930 8548.3600 8712.7960 7891.1890 8587.5160 8879.9080 9001.2140 9392.7100 8798.1260 9351.9220 9556.6940 9378.2100 9504.1290 9745.7430 10117.0310 10350.8270 10413.1170 9573.1430 8249.9350 9390.7000 9874.8590 10063.0130 10698.3390 11479.5220 11008.9290 10800.5880 12063.4620 12512.9690 12903.5800 1-Year T-Bill Annual YTM 0.5200 0.6400 0.5400 0.5700 0.5400 0.5000 0.5500 0.5800 0.6400 0.7100 0.8400 0.8000 0.7900 0.9800 1.0100 1.0900 1.1700 1.1900 1.2000 1.2500 1.3600 1.5100 1.6500 1.7500 1.9000 2.0000 2.0800 2.2000 2.2500 2.3100 2.3600 2.4700 2.5600 2.6000 2.5700 2.5000 2.4700 2.4100 2.3400 2.2700 1.9400 1.9100 1.7300 1.7500 1.5700 1.5300 1.5100 1.4900 1.3700 0.3200 0.1800 0.1600 0.1800 0.1500 0.1300 0.1300 0.1300 0.1200 0.1000 0.1000 0.0700 Best Buy Ex-Dividend Date 3/15/16 6/10/16 9/9/16 12/6/16 3/20/17 6/13/17 9/18/17 12/6/17 3/21/18 6/13/18 9/17/18 12/10/18 3/19/19 6/12/19 9/18/19 12/16/19 3/18/20 6/10/20 9/14/20 12/14/20 Amount 0.28 0.28 0.28 0.28 0.34 0.34 0.34 0.34 0.45 0.45 0.45 0.45 0.50 0.50 0.50 0.50 0.55 0.55 0.55 0.55 Nike Ex-Dividend Date 3/3/16 6/2/16 9/1/16 12/1/16 3/2/17 6/1/17 8/31/17 12/1/17 3/2/18 6/1/18 8/31/18 11/30/18 3/1/19 5/31/19 8/30/19 11/29/19 2/28/20 5/29/20 8/28/20 12/4/20 Amount 0.16 0.16 0.16 0.18 0.18 0.18 0.18 0.20 0.20 0.20 0.20 0.22 0.22 0.22 0.22 0.25 0.25 0.25 0.25 0.28 State Street Ex-Dividend Date 3/30/16 6/29/16 9/29/16 12/29/16 3/30/17 6/29/17 9/29/17 12/29/17 3/29/18 6/29/18 9/28/18 12/31/18 3/29/19 6/28/19 9/30/19 12/31/19 3/31/20 6/30/20 9/30/20 12/31/20 Amount 0.34 0.34 0.38 0.38 0.38 0.38 0.42 0.42 0.42 0.42 0.47 0.47 0.47 0.47 0.52 0.52 0.52 0.52 0.52 0.52

Answer rating: 100% (QA)

To form the correlation matrix and variancecovariance matrix and calculate the betas for the three stocks I will use the following steps 1 Calculate the Excess Returns The excess return for each stock ... View the full answer