BYDDF TLSA BYDDF TLSA The spreadsheet posted with this assignment contains historical monthly prices and dividends (paid during the month) for BYD Company Ltd. stock (ticker: BYDDF) and Tesla Inc. (ticker: TSLA). Use this data and the statistics functions of Excel to answer the following questions. Note that Tesla paid no dividend during the period studied. Dividend 0 0 Dividend 0 0 0 0 0 a. Compute the total return for each stock for each month. b. Calculate average monthly return and standard deviation for each stock. c. Calculate the covariance between the monthly total returns of the two stocks. d. Calculate the correlation between the monthly total returns of the two stocks. c. Explain why the covariance and correlation differ. 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 Closing price 5.79 8.07 609 6.08 5.29 4.91 4.05 3.82 3.66 3.25 3.24 3.26 2.07 1.63 2.4 2.42 2.12 3.15 3.22 2.78 2.6 2 1.93 1.7 1.7 1.72 1.97 2.52 3.03 3.37 34 3.16 3.54 437 3.34 3.94 3.57 4.42 4.93 0 0 0 0 0 0 0 0 0 0 0 0 0 0 Month 10-Aug 10-Sep 10-Oct 10-Nov 10-Dec 11-Jan 11-Feb 11- Mar 11-Apr 11-May 11-Jun 11-Jul 11-Aug 11-Sep 11-Oct 11-Nov 11-Dec 12-Jan 12-Feb 12-Mar 12-Apr 12-May 12-Jun 12-Jul 12-Aug 12-Sep 12-Oct 12-Nov 12-Dec 13-Jan 13-Feb 13-Mar 13-Apr 13-May 13-Jun 13-Jul 13-Aug 13-Sep 13-Oct 13-Nov 13-Dec 14-Jan 14-Feb 14-Mar 14-Apr 14-May 14-Jun 14-Jul 14-Aug 14-Sep - 14-Oct 14-Nov 14-Dec 15-Jan 0 0 0 0 Closing price 19.48 20.41 21.84 35.33 26.63 24.1 23.89 27.75 27.6 30.14 29.13 28.17 24.74 24.39 29.37 32.74 28.56 29.07 33.41 37.24 33.13 29.5 31.29 27.42 28.52 29.28 28.13 33.82 33.87 37.51 34.83 37.89 53.99 97.76 107.36 134.28 169 193.37 159.94 127.28 150.43 181.41 244.81 208.45 207.89 207.77 240.06 2233 269.7 242.68 241.7 244.52 222.41 203.6 Month Closing price Dividend Closing price Dividend 15-Feb 4.32 0 20334 0 15-Mar 5.11 0 188.77 0 15-Apr 6.03 0 226,05 0 15-May 6.84 0 0 250.8 0 15-Jun 5.97 0 268.26 0 15-Jul 4.42 0 o 266.15 0 15-Aug 4.04 0 249.06 0 15-Sep 5.28 0 248.4 0 15-Oct 6.22 0 206.93 0 15-Nov 5.46 0 230.26 0 15-Dec 5.45 0 o 240.01 0 16-Jan 4.52 0 191.2 0 16-Feb 4.97 0 191.93 0 16-Mar 5.74 0 229.77 0 16-Apr 5.87 0 240.76 0 16-M 5.81 0 223.23 0 16-Jun 5.99 0 212.28 16-Jul 6.42 0 Q 234.79 0 16-Aug 7 0 212.01 0 16-Sep 6.7 0 204.03 0 16-Oct 6.55 0 197.73 0 16-Nov 6.05 0.42022 189.4 0 16-Dec 5.25 0 213.69 0 17-Jan 5.58 0 251.93 0 17-Feb 5.91 0 249.99 0 17-Mar 5.53 0 2783 0 17-Apr 5.89 0 314.07 0 17-May 5.95 0 341.01 0 17-Jun 6.19 0 361.61 0 17-Jul 6.28 0 323.47 0 17-Aug 6.01 0 0 355.9 0 17-Seg 9.52 0 341.1 0 17-Oct 8.77 0 33153 0 17-Nov 8.85 0 308.85 0 17 Dec 8.79 0 0 311.35 0 18-Jan 9.44 0 35431 0 18-Feb 9.14 0 343.06 0 18-Mar 8.03 0 266.13 0 18-Apr 7.06 0 0 293.9 0 18-May 6.51 0 0 284.73 0 18-Jun 6.21 0.17248 342.95 0 18-Jul 5.7 0 298.14 0 18-Aug 5.9 0 301.66 0 18-Sep 7.13 0 264.77 0 18 Oct 6.47 0 33732 0 18 Nov 7.53 0 350.48 0 18-Dec 633 0 3328 0 19-Jan 5.92 0 307.02 0 19-Feb 6.42 0 319.85 0 19-Mar - 6.07 0 O 279.86 0 19-Apr 6.78 0 238.69 0 19-May 5.81 0 185.16 0 19-Jun 6.03 0.204 223.46 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 4.99 4.88 4.71 6.73 62 5.34 4.92 5.77 6.54 7.03 6.61 6.37 6.08 3.93 3.65 0 0 0 0 O 0.06297 0 0 0 0 0 0 0 0 0 0 0 0 0 0 BYDDF TLSA BYDDF TLSA The spreadsheet posted with this assignment contains historical monthly prices and dividends (paid during the month) for BYD Company Ltd. stock (ticker: BYDDF) and Tesla Inc. (ticker: TSLA). Use this data and the statistics functions of Excel to answer the following questions. Note that Tesla paid no dividend during the period studied. Dividend 0 0 Dividend 0 0 0 0 0 a. Compute the total return for each stock for each month. b. Calculate average monthly return and standard deviation for each stock. c. Calculate the covariance between the monthly total returns of the two stocks. d. Calculate the correlation between the monthly total returns of the two stocks. c. Explain why the covariance and correlation differ. 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 Closing price 5.79 8.07 609 6.08 5.29 4.91 4.05 3.82 3.66 3.25 3.24 3.26 2.07 1.63 2.4 2.42 2.12 3.15 3.22 2.78 2.6 2 1.93 1.7 1.7 1.72 1.97 2.52 3.03 3.37 34 3.16 3.54 437 3.34 3.94 3.57 4.42 4.93 0 0 0 0 0 0 0 0 0 0 0 0 0 0 Month 10-Aug 10-Sep 10-Oct 10-Nov 10-Dec 11-Jan 11-Feb 11- Mar 11-Apr 11-May 11-Jun 11-Jul 11-Aug 11-Sep 11-Oct 11-Nov 11-Dec 12-Jan 12-Feb 12-Mar 12-Apr 12-May 12-Jun 12-Jul 12-Aug 12-Sep 12-Oct 12-Nov 12-Dec 13-Jan 13-Feb 13-Mar 13-Apr 13-May 13-Jun 13-Jul 13-Aug 13-Sep 13-Oct 13-Nov 13-Dec 14-Jan 14-Feb 14-Mar 14-Apr 14-May 14-Jun 14-Jul 14-Aug 14-Sep - 14-Oct 14-Nov 14-Dec 15-Jan 0 0 0 0 Closing price 19.48 20.41 21.84 35.33 26.63 24.1 23.89 27.75 27.6 30.14 29.13 28.17 24.74 24.39 29.37 32.74 28.56 29.07 33.41 37.24 33.13 29.5 31.29 27.42 28.52 29.28 28.13 33.82 33.87 37.51 34.83 37.89 53.99 97.76 107.36 134.28 169 193.37 159.94 127.28 150.43 181.41 244.81 208.45 207.89 207.77 240.06 2233 269.7 242.68 241.7 244.52 222.41 203.6 Month Closing price Dividend Closing price Dividend 15-Feb 4.32 0 20334 0 15-Mar 5.11 0 188.77 0 15-Apr 6.03 0 226,05 0 15-May 6.84 0 0 250.8 0 15-Jun 5.97 0 268.26 0 15-Jul 4.42 0 o 266.15 0 15-Aug 4.04 0 249.06 0 15-Sep 5.28 0 248.4 0 15-Oct 6.22 0 206.93 0 15-Nov 5.46 0 230.26 0 15-Dec 5.45 0 o 240.01 0 16-Jan 4.52 0 191.2 0 16-Feb 4.97 0 191.93 0 16-Mar 5.74 0 229.77 0 16-Apr 5.87 0 240.76 0 16-M 5.81 0 223.23 0 16-Jun 5.99 0 212.28 16-Jul 6.42 0 Q 234.79 0 16-Aug 7 0 212.01 0 16-Sep 6.7 0 204.03 0 16-Oct 6.55 0 197.73 0 16-Nov 6.05 0.42022 189.4 0 16-Dec 5.25 0 213.69 0 17-Jan 5.58 0 251.93 0 17-Feb 5.91 0 249.99 0 17-Mar 5.53 0 2783 0 17-Apr 5.89 0 314.07 0 17-May 5.95 0 341.01 0 17-Jun 6.19 0 361.61 0 17-Jul 6.28 0 323.47 0 17-Aug 6.01 0 0 355.9 0 17-Seg 9.52 0 341.1 0 17-Oct 8.77 0 33153 0 17-Nov 8.85 0 308.85 0 17 Dec 8.79 0 0 311.35 0 18-Jan 9.44 0 35431 0 18-Feb 9.14 0 343.06 0 18-Mar 8.03 0 266.13 0 18-Apr 7.06 0 0 293.9 0 18-May 6.51 0 0 284.73 0 18-Jun 6.21 0.17248 342.95 0 18-Jul 5.7 0 298.14 0 18-Aug 5.9 0 301.66 0 18-Sep 7.13 0 264.77 0 18 Oct 6.47 0 33732 0 18 Nov 7.53 0 350.48 0 18-Dec 633 0 3328 0 19-Jan 5.92 0 307.02 0 19-Feb 6.42 0 319.85 0 19-Mar - 6.07 0 O 279.86 0 19-Apr 6.78 0 238.69 0 19-May 5.81 0 185.16 0 19-Jun 6.03 0.204 223.46 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 4.99 4.88 4.71 6.73 62 5.34 4.92 5.77 6.54 7.03 6.61 6.37 6.08 3.93 3.65 0 0 0 0 O 0.06297 0 0 0 0 0 0 0 0 0 0 0 0 0 0