Please answer the questions using the excel charts below. Include formulas in the cells for each part plz!

Part 1 1. Calculate the compound Annual Growth Rate (CAGR) for the five stocks for the two scenarios in the Excel file. Scenario 1: A. For the entire dataset, how many years of monthly data do you have? (If it's a fractional number of years, round up to the nearest whole year. For example if its 13.4 years round up to 14 years). B. In row 62 Calculate the CAGR for each stock Scenario 2: A. Using the monthly data from January 31, 2014 to October 31, 2016 how many years of monthly data do you have? (If it's a fractional number of years, round up to the nearest whole year) B. In row 63 calculate the CAGR for each stock in Scenario 2 2. In columns G to K calculate the monthly returns for each month for all five stocks. a. The first return should be in row 5. The last return should be in row 61 (Note it takes two months of monthly prices to get a monthly return). b. The returns should be in percentage format to two decimal places. 3. Calculate the average return, variance, and standard deviation for the returns of each stock. Place the results in rows 65, 66, & 67 in the same columns as the stock returns. Use the "S" function for variance and standard deviation. 4. Calculate the covariance (use ".s) and correlation and enter the results in cells B71 to 874 for the following: a. MSFT to AMZN b. AMZN to TGT Monthly Prices Returns 2 3 Date BA AMZN FDX TGT 4 5 6 7 8 9 10 11 107.36 12 13 14 15 16 17 18 19 20 21 22 23 24 MSFT BA AMZN FDX TGT MSFT 1/31/13 27.45 73.87 265.5 101.45 60.41 2/28/13 27.8 76.9 264.27 105.43 62.96 3/29/13 28.605 85 85 266.49 98.2 68.45 4/30/13 33.1 91.41 253.81 94.01 70.56 5/31/13 34.9 99.02 269.2 96.34 69.5 6/28/13 34.545 102.44 277.69 98.58 68.86 7/31/13 31.84 105.1 301.22 106 71.25 8/30/13 33.4 103.92 280.98 63.31 9/30/13 33.28 117.5 312.64 114.11 63.98 10/31/13 35.405 130.5 364.03 131 64.79 11/29/13 38.13 134.25 393.62 138.7 63.93 12/31/13 37.41 136.49 398.79 143.77 63.27 1/31/14 37.84 125.26 358,69 133.32 56.64 2/28/14 38.31 128.92 362.1 133.33 62.54 3/31/14 40.99 125.49 336.365 132.56 60.51 4/30/14 40.4 129.02 304.13 136.25 61.75 5/30/14 40.94 135.25 312.55 144.16 56.76 6/30/14 41.7 127.23 324.78 151.38 57.95 7/31/14 43.16 120.48 312.99 146.88 59.59 8/29/14 45.43 126.8 339.04 147.88 60.07 9/30/14 46.36 127.38 322.44 161.45 62.68 10/31/14 46.95 124.91 305.46 167.4 61.82 11/28/14 47.81 134.36 338.64 178.18 74 12/31/14 46.45 129.98 310.35 173.66 75.91 1/30/15 40.4 145.37 354.53 169.11 73.61 2/27/15 43.85 150.85 380.16 176.98 76.83 3/31/15 40.655 150.08 372.1 165.45 82.07 4/30/15 48.64 143.34 421.78 169.57 78.83 5/29/15 46.86 140.52 429.23 173.22 79.32 6/30/15 44.15 138.72 434.09 170.4 81.63 7/31/15 46.7 144.17 536.15 171.42 81.85 8/31/15 43.52 130.68 512.89 150.61 77.71 9/30/15 44.26 130.95 511.89 143.98 10/30/15 52.64 148.07 625.9 156.05 77.18 11/30/15 54.35 145.45 664.8 158.54 72.5 12/31/15 55.48 144.59 675.89 148,99 72.61 1/29/16 55.09 120.13 587 132.88 72.42 2/29/16 50.88 118.18 552.52 136.88 78.45 3/31/16 55.23 126.94 593.64 162.72 82.28 4/29/16 49.87 134.8 659.59 165.11 79.5 5/31/16 53 126.15 722.79 164.97 68.78 6/30/16 51.17 129.87 715.62 151.78 69.82 7/29/16 56.68 133.66 758.81 161.9 75.33 25 26 27 28 29 30 31 32 33 34 35 36 78.66 37 38 39 40 41 42 43 44 45 46 56.68 133.66 758.81 161.9 75.33 7 57.46 129.45 769.16 164.93 70.19 B 57.6 131.74 837.31 174.68 68.68 59.92 142.43 789.82 174.32 68.73 ) 60.26 150.56 750.57 191.67 77.24 1 62.14 155.68 749.87 186.2 72.23 2 64.65 163.42 823.48 189.11 64.48 3 63.98 180.23 845.04 192.98 58.77 1/29/16 8/31/16 9/30/16 10/31/16 11/30/16 12/30/16 1/31/17 2/28/17 3/31/17 4/28/17 5/31/17 6/30/17 7/31/17 8/31/17 9/29/17 10/31/17 1 65.86 176.86 886.54 195.15 55.19 5 68.46 184.83 924.99 189.7 55.85 5 69.84 187.63 994.62 193.84 55.15 7 68.93 197.75 968 217.33 52.29 3 72.7 242.46 987.78 208.03 56.67 74.77 239.66 980.6 214.38 54.53 74.49 254.21 961.35 225.58 59.01 1 83.18 257.98 1105.28 225.81 59.04 2 CAGR (scenario 1) 3 CAGR (scenario 2) 1 5 Average 6 Variance 7 Standard Deviation 3 2 Covariance MSFT to AMZN 1 Correaltion MSFT to AMZN 2 Covariance AMZN to TGT 3 Correlation AMZN to TGT 7 5 Part 1 1. Calculate the compound Annual Growth Rate (CAGR) for the five stocks for the two scenarios in the Excel file. Scenario 1: A. For the entire dataset, how many years of monthly data do you have? (If it's a fractional number of years, round up to the nearest whole year. For example if its 13.4 years round up to 14 years). B. In row 62 Calculate the CAGR for each stock Scenario 2: A. Using the monthly data from January 31, 2014 to October 31, 2016 how many years of monthly data do you have? (If it's a fractional number of years, round up to the nearest whole year) B. In row 63 calculate the CAGR for each stock in Scenario 2 2. In columns G to K calculate the monthly returns for each month for all five stocks. a. The first return should be in row 5. The last return should be in row 61 (Note it takes two months of monthly prices to get a monthly return). b. The returns should be in percentage format to two decimal places. 3. Calculate the average return, variance, and standard deviation for the returns of each stock. Place the results in rows 65, 66, & 67 in the same columns as the stock returns. Use the "S" function for variance and standard deviation. 4. Calculate the covariance (use ".s) and correlation and enter the results in cells B71 to 874 for the following: a. MSFT to AMZN b. AMZN to TGT Monthly Prices Returns 2 3 Date BA AMZN FDX TGT 4 5 6 7 8 9 10 11 107.36 12 13 14 15 16 17 18 19 20 21 22 23 24 MSFT BA AMZN FDX TGT MSFT 1/31/13 27.45 73.87 265.5 101.45 60.41 2/28/13 27.8 76.9 264.27 105.43 62.96 3/29/13 28.605 85 85 266.49 98.2 68.45 4/30/13 33.1 91.41 253.81 94.01 70.56 5/31/13 34.9 99.02 269.2 96.34 69.5 6/28/13 34.545 102.44 277.69 98.58 68.86 7/31/13 31.84 105.1 301.22 106 71.25 8/30/13 33.4 103.92 280.98 63.31 9/30/13 33.28 117.5 312.64 114.11 63.98 10/31/13 35.405 130.5 364.03 131 64.79 11/29/13 38.13 134.25 393.62 138.7 63.93 12/31/13 37.41 136.49 398.79 143.77 63.27 1/31/14 37.84 125.26 358,69 133.32 56.64 2/28/14 38.31 128.92 362.1 133.33 62.54 3/31/14 40.99 125.49 336.365 132.56 60.51 4/30/14 40.4 129.02 304.13 136.25 61.75 5/30/14 40.94 135.25 312.55 144.16 56.76 6/30/14 41.7 127.23 324.78 151.38 57.95 7/31/14 43.16 120.48 312.99 146.88 59.59 8/29/14 45.43 126.8 339.04 147.88 60.07 9/30/14 46.36 127.38 322.44 161.45 62.68 10/31/14 46.95 124.91 305.46 167.4 61.82 11/28/14 47.81 134.36 338.64 178.18 74 12/31/14 46.45 129.98 310.35 173.66 75.91 1/30/15 40.4 145.37 354.53 169.11 73.61 2/27/15 43.85 150.85 380.16 176.98 76.83 3/31/15 40.655 150.08 372.1 165.45 82.07 4/30/15 48.64 143.34 421.78 169.57 78.83 5/29/15 46.86 140.52 429.23 173.22 79.32 6/30/15 44.15 138.72 434.09 170.4 81.63 7/31/15 46.7 144.17 536.15 171.42 81.85 8/31/15 43.52 130.68 512.89 150.61 77.71 9/30/15 44.26 130.95 511.89 143.98 10/30/15 52.64 148.07 625.9 156.05 77.18 11/30/15 54.35 145.45 664.8 158.54 72.5 12/31/15 55.48 144.59 675.89 148,99 72.61 1/29/16 55.09 120.13 587 132.88 72.42 2/29/16 50.88 118.18 552.52 136.88 78.45 3/31/16 55.23 126.94 593.64 162.72 82.28 4/29/16 49.87 134.8 659.59 165.11 79.5 5/31/16 53 126.15 722.79 164.97 68.78 6/30/16 51.17 129.87 715.62 151.78 69.82 7/29/16 56.68 133.66 758.81 161.9 75.33 25 26 27 28 29 30 31 32 33 34 35 36 78.66 37 38 39 40 41 42 43 44 45 46 56.68 133.66 758.81 161.9 75.33 7 57.46 129.45 769.16 164.93 70.19 B 57.6 131.74 837.31 174.68 68.68 59.92 142.43 789.82 174.32 68.73 ) 60.26 150.56 750.57 191.67 77.24 1 62.14 155.68 749.87 186.2 72.23 2 64.65 163.42 823.48 189.11 64.48 3 63.98 180.23 845.04 192.98 58.77 1/29/16 8/31/16 9/30/16 10/31/16 11/30/16 12/30/16 1/31/17 2/28/17 3/31/17 4/28/17 5/31/17 6/30/17 7/31/17 8/31/17 9/29/17 10/31/17 1 65.86 176.86 886.54 195.15 55.19 5 68.46 184.83 924.99 189.7 55.85 5 69.84 187.63 994.62 193.84 55.15 7 68.93 197.75 968 217.33 52.29 3 72.7 242.46 987.78 208.03 56.67 74.77 239.66 980.6 214.38 54.53 74.49 254.21 961.35 225.58 59.01 1 83.18 257.98 1105.28 225.81 59.04 2 CAGR (scenario 1) 3 CAGR (scenario 2) 1 5 Average 6 Variance 7 Standard Deviation 3 2 Covariance MSFT to AMZN 1 Correaltion MSFT to AMZN 2 Covariance AMZN to TGT 3 Correlation AMZN to TGT 7 5