Question

In: Finance

Appendix 1.1: Table 1.1 - Weekly discrete rate of returns Week Date Apple Inc. (AAPL) McDonald's...

Appendix 1.1: Table 1.1 - Weekly discrete rate of returns
Week Date Apple Inc. (AAPL) McDonald's Corporation (MCD) Southwest Airlines Co. (LUV) S&P 500 Index (GSPC)
1 30-Oct-17
2 6-Nov-17 1.2549% -1.8187% -1.1872% -0.2141%
3 13-Nov-17 -2.2355% 0.6847% 2.2714% -0.1336%
4 20-Nov-17 2.8316% 1.4330% 0.7893% 0.9140%
5 27-Nov-17 -2.2409% 2.2269% 8.6687% 1.5293%
6 4-Dec-17 -0.9841% 0.7554% 5.6980% 0.3512%
7 11-Dec-17 2.7198% 0.5289% 2.3625% 0.9168%
8 18-Dec-17 0.5968% -1.5205% 1.9672% 0.2814%
9 25-Dec-17 -3.3001% 0.4109% -0.7899% -0.3626%
10 1-Jan-18 3.4068% 1.1225% -1.7302% 2.6010%
11 8-Jan-18 1.1923% -0.2775% 1.6204% 1.5708%
12 15-Jan-18 0.7741% 1.4668% -0.4753% 0.8635%
13 22-Jan-18 -3.8916% 1.2742% -6.5013% 2.2265%
14 29-Jan-18 -6.4234% -5.0327% -3.4767% -3.8547%
15 5-Feb-18 -2.5435% -5.0677% -5.4626% -5.1620%
16 12-Feb-18 10.6908% -1.8712% 5.0018% 4.3011%
17 19-Feb-18 1.7815% 3.3355% -0.4815% 0.5519%
18 26-Feb-18 0.4004% -9.0662% -0.3110% -2.0402%
19 5-Mar-18 2.1421% 6.7192% 3.2588% 3.5418%
20 12-Mar-18 -1.0876% 3.2562% 2.2327% -1.2402%
21 19-Mar-18 -7.3475% -4.5455% -6.8637% -5.9502%
22 26-Mar-18 1.7224% 0.9033% 0.9873% 2.0326%
23 2-Apr-18 0.3530% 3.1142% -3.4742% -1.3783%
24 9-Apr-18 3.7739% 0.2977% -0.3979% 1.9900%
25 16-Apr-18 -5.1591% -1.8302% -0.5448% 0.5210%
26 23-Apr-18 -2.0475% -0.2960% -2.2640% -0.0086%
27 30-Apr-18 13.2468% 4.2514% -1.4011% -0.2431%
28 7-May-18 2.5939% 0.2181% -0.6063% 2.4142%

Generate a covariance and correlation matrix of these stocks. Interprete your results.

Solutions

Expert Solution

Formula sheet

A B C D E F G H I J K
2
3 Week Date Apple Inc. (AAPL) McDonald's Corporation (MCD) Southwest Airlines Co. (LUV) S&P 500 Index (GSPC)
4 1 43038
5 2 43045 0.012549 -0.018187 -0.011872 -0.002141
6 3 43052 -0.022355 0.006847 0.022714 -0.001336
7 4 43059 0.028316 0.01433 0.007893 0.00914
8 5 43066 -0.022409 0.022269 0.086687 0.015293
9 6 43073 -0.009841 0.007554 0.05698 0.003512
10 7 43080 0.027198 0.005289 0.023625 0.009168
11 8 43087 0.005968 -0.015205 0.019672 0.002814
12 9 43094 -0.033001 0.004109 -0.007899 -0.003626
13 10 43101 0.034068 0.011225 -0.017302 0.02601
14 11 43108 0.011923 -0.002775 0.016204 0.015708
15 12 43115 0.007741 0.014668 -0.004753 0.008635
16 13 43122 -0.038916 0.012742 -0.065013 0.022265
17 14 43129 -0.064234 -0.050327 -0.034767 -0.038547
18 15 43136 -0.025435 -0.050677 -0.054626 -0.05162
19 16 43143 0.106908 -0.018712 0.050018 0.043011
20 17 43150 0.017815 0.033355 -0.004815 0.005519
21 18 43157 0.004004 -0.090662 -0.00311 -0.020402
22 19 43164 0.021421 0.067192 0.032588 0.035418
23 20 43171 -0.010876 0.032562 0.022327 -0.012402
24 21 43178 -0.073475 -0.045455 -0.068637 -0.059502
25 22 43185 0.017224 0.009033 0.009873 0.020326
26 23 43192 0.00353 0.031142 -0.034742 -0.013783
27 24 43199 0.037739 0.002977 -0.003979 0.0199
28 25 43206 -0.051591 -0.018302 -0.005448 0.00521
29 26 43213 -0.020475 -0.00296 -0.02264 -0.000086
30 27 43220 0.132468 0.042514 -0.014011 -0.002431
31 28 43227 0.025939 0.002181 -0.006063 0.024142
32
33 Variance covariance matrix can be calculated using the co-variance formula in excel as follows:
34 Variance covariance matrix:
35 AAPL MCD LUV GSPC
36 AAPL =COVARIANCE.S($E$5:$E$31,E5:E31) =COVARIANCE.S($E$5:$E$31,F5:F31) =COVARIANCE.S($E$5:$E$31,G5:G31) =COVARIANCE.S($E$5:$E$31,H5:H31) =COVARIANCE.S($E$5:$E$31,H5:H31)
37 MCD =COVARIANCE.S($F$5:$F$31,E5:E31) =COVARIANCE.S($F$5:$F$31,F5:F31) =COVARIANCE.S($F$5:$F$31,G5:G31) =COVARIANCE.S($F$5:$F$31,H5:H31)
38 LUV =COVARIANCE.S($G$5:$G$31,E5:E31) =COVARIANCE.S($G$5:$G$31,F5:F31) =COVARIANCE.S($G$5:$G$31,G5:G31) =COVARIANCE.S($G$5:$G$31,H5:H31)
39 GSPC =COVARIANCE.S($H$5:$H$31,E5:E31) =COVARIANCE.S($H$5:$H$31,F5:F31) =COVARIANCE.S($H$5:$H$31,G5:G31) =COVARIANCE.S($H$5:$H$31,H5:H31)
40
41 Correlation matrix can be calculated using correlation matrix as follows:
42 Correlation matrix:
43 AAPL MCD LUV GSPC
44 AAPL =CORREL($E$5:$E$31,E5:E31) =CORREL($E$5:$E$31,F5:F31) =CORREL($E$5:$E$31,G5:G31) =CORREL($E$5:$E$31,H5:H31) =CORREL($E$5:$E$31,H5:H31)
45 MCD =CORREL($F$5:$F$31,E5:E31) =CORREL($F$5:$F$31,F5:F31) =CORREL($F$5:$F$31,G5:G31) =CORREL($F$5:$F$31,H5:H31)
46 LUV =CORREL($G$5:$G$31,E5:E31) =CORREL($G$5:$G$31,F5:F31) =CORREL($G$5:$G$31,G5:G31) =CORREL($G$5:$G$31,H5:H31)
47 GSPC =CORREL($H$5:$H$31,E5:E31) =CORREL($H$5:$H$31,F5:F31) =CORREL($H$5:$H$31,G5:G31) =CORREL($H$5:$H$31,H5:H31)
48
49 Variance of the stocks shows that AAPL has highest variance and hence most risky stock among the stocks.
50 Also covariance of AAPL with the market i.e. GSPC is highest and for MCD it is lowest,
51 which shows that APPL is most correlated with the market and MCD is least correlated with market.
52

jeff jeffy answered 1 year ago
Next > < Previous

Related Solutions

Appendix 1.1: Table 1.1 - Weekly discrete rate of returns Week Date Apple Inc. (AAPL) McDonald's...
Appendix 1.1: Table 1.1 - Weekly discrete rate of returns Week Date Apple Inc. (AAPL) McDonald's Corporation (MCD) Southwest Airlines Co. (LUV) S&P 500 Index (GSPC) 1 30-Oct-17 2 6-Nov-17 1.2549% -1.8187% -1.1872% -0.2141% 3 13-Nov-17 -2.2355% 0.6847% 2.2714% -0.1336% 4 20-Nov-17 2.8316% 1.4330% 0.7893% 0.9140% 5 27-Nov-17 -2.2409% 2.2269% 8.6687% 1.5293% 6 4-Dec-17 -0.9841% 0.7554% 5.6980% 0.3512% 7 11-Dec-17 2.7198% 0.5289% 2.3625% 0.9168% 8 18-Dec-17 0.5968% -1.5205% 1.9672% 0.2814% 9 25-Dec-17 -3.3001% 0.4109% -0.7899% -0.3626% 10 1-Jan-18 3.4068% 1.1225%...
Appendix 1.1: Table 1.1 - Weekly discrete rate of returns Week Date Apple Inc. (AAPL) McDonald's...
Appendix 1.1: Table 1.1 - Weekly discrete rate of returns Week Date Apple Inc. (AAPL) McDonald's Corporation (MCD) Southwest Airlines Co. (LUV) S&P 500 Index (GSPC) 1 30-Oct-17 2 6-Nov-17 1.2549% -1.8187% -1.1872% -0.2141% 3 13-Nov-17 -2.2355% 0.6847% 2.2714% -0.1336% 4 20-Nov-17 2.8316% 1.4330% 0.7893% 0.9140% 5 27-Nov-17 -2.2409% 2.2269% 8.6687% 1.5293% 6 4-Dec-17 -0.9841% 0.7554% 5.6980% 0.3512% 7 11-Dec-17 2.7198% 0.5289% 2.3625% 0.9168% 8 18-Dec-17 0.5968% -1.5205% 1.9672% 0.2814% 9 25-Dec-17 -3.3001% 0.4109% -0.7899% -0.3626% 10 1-Jan-18 3.4068% 1.1225%...
Appendix 1.1: Table 1.1 - Weekly discrete rate of returns Week Date Apple Inc. (AAPL) McDonald's...
Appendix 1.1: Table 1.1 - Weekly discrete rate of returns Week Date Apple Inc. (AAPL) McDonald's Corporation (MCD) Southwest Airlines Co. (LUV) S&P 500 Index (GSPC) 1 30-Oct-17 2 6-Nov-17 1.2549% -1.8187% -1.1872% -0.2141% 3 13-Nov-17 -2.2355% 0.6847% 2.2714% -0.1336% 4 20-Nov-17 2.8316% 1.4330% 0.7893% 0.9140% 5 27-Nov-17 -2.2409% 2.2269% 8.6687% 1.5293% 6 4-Dec-17 -0.9841% 0.7554% 5.6980% 0.3512% 7 11-Dec-17 2.7198% 0.5289% 2.3625% 0.9168% 8 18-Dec-17 0.5968% -1.5205% 1.9672% 0.2814% 9 25-Dec-17 -3.3001% 0.4109% -0.7899% -0.3626% 10 1-Jan-18 3.4068% 1.1225%...
Week Date Apple Inc. (AAPL) McDonald's Corporation (MCD) Southwest Airlines Co. (LUV) S&P 500 Index (GSPC)...
Week Date Apple Inc. (AAPL) McDonald's Corporation (MCD) Southwest Airlines Co. (LUV) S&P 500 Index (GSPC) 1 30-Oct-17 2 6-Nov-17 1.2549% -1.8187% -1.1872% -0.2141% 3 13-Nov-17 -2.2355% 0.6847% 2.2714% -0.1336% 4 20-Nov-17 2.8316% 1.4330% 0.7893% 0.9140% 5 27-Nov-17 -2.2409% 2.2269% 8.6687% 1.5293% 6 4-Dec-17 -0.9841% 0.7554% 5.6980% 0.3512% 7 11-Dec-17 2.7198% 0.5289% 2.3625% 0.9168% 8 18-Dec-17 0.5968% -1.5205% 1.9672% 0.2814% 9 25-Dec-17 -3.3001% 0.4109% -0.7899% -0.3626% 10 1-Jan-18 3.4068% 1.1225% -1.7302% 2.6010% 11 8-Jan-18 1.1923% -0.2775% 1.6204% 1.5708% 12 15-Jan-18...
Please answer the following questions for the Apple, Inc. (NASDAQ: AAPL) . Balance Sheet 1) Did...
Please answer the following questions for the Apple, Inc. (NASDAQ: AAPL) . Balance Sheet 1) Did its total assets increase or decrease over the last year? Cash Flow Statement 1) Did the company have a cash inflow or outflow from operating activities? What two major item(s) reported in operating activities caused the major inflow and the major outflow 2)Was there total inflow or outflow of cash, what did this most relate to (Investing, Financing or Operating)?
Please answer the following questions for the Apple, Inc. (NASDAQ: AAPL) . General information: 1)What was...
Please answer the following questions for the Apple, Inc. (NASDAQ: AAPL) . General information: 1)What was the market value for one share of the company’s common stock at the conclusion of trading on February 28, 2013? 2)Who is the corporation’s independent auditor and in what city is it located? 3) Does the auditor believe the financial statements were presented fairly? What statements in the auditor’s report support your conclusion? 4) Does the auditor think the company’s internal control system is...
The table below is a discrete probability distribution of study hours for mathematics in a given week.
The table below is a discrete probability distribution of study hours for mathematics in a given week.    Hours (x) 1 2 3 4 5 P(x) 0.16 0.22 ? 0.20 0.14 Find the probability of x=3. Find the mean and the standard deviation of this probability distribution. Find the probability that x is at most 4 hours.
TABLE 1.1: Factual Basis for Evaluating the Economic Losses of Jackson L. Brown 1 Date of...
TABLE 1.1: Factual Basis for Evaluating the Economic Losses of Jackson L. Brown 1 Date of Birth 2/15/1960 2 Date of Death 9/6/1988 3 Age at time of death 28.66 4 Worklife Expextancy as of 9/6/88 31 5 Life Expectancy as of 9/6/88 44.54 6 Marital Status Married 7 Sex Male 8 Race White 9 Education High School Graduate 10 Occupation Window Washer 11 Expected Trial Date 12/1/1989 12 Earning at time of death 25,000 13 Benefits 28.00% 14 Expected...
Apple Inc. has the following outstanding bond : coupon rate: 6% Maturity date : 10/15/2041 what...
Apple Inc. has the following outstanding bond : coupon rate: 6% Maturity date : 10/15/2041 what is is the size of each of the company's coupon payment? Assume annual coupons. round to nearest $.
Q#1.  According to the Wall Street Journal (WSJ), Apple, Inc. (AAPL) announced in 2013 a 7-for-1 stock...
Q#1.  According to the Wall Street Journal (WSJ), Apple, Inc. (AAPL) announced in 2013 a 7-for-1 stock split. Similarly, many other publicly traded companies have announced stocks splits. Most stock holders as well as some analysts and traders believe that a stock split would definitely benefit current shareholders and raise the firm’s overall market value. You read in chapter 14 that, in theory, a 7:1 split would increase the number of outstanding shares seven fold and cut down the post-split stock...
ADVERTISEMENT
Subjects
ADVERTISEMENT
Latest Questions
ADVERTISEMENT