Task 1: Use the monthly returns of MSFT and Walmart between June 2007 and June 2022 to do the following (include June 2022 in your calculation): a) Calculate each stock's arithmetic average using the AVERAGE function. b) Calculate each stock's geometric (compounded) average using the GEOMEAN function. Note: the GEOMEAN function does not recognize negative numbers. To complete this task you need to create a separate column where you have to add 1 to each line and then use the GEOMEAN function. Don't forget to subtract one from the result. c) Calculate each stock's sample standard deviation using the STDEV function. d) Calculate the sample covariance of MSFT and Walmart using the COVAR function. You also need the COUNT function. In general, the sample covariance of stock X and Y is given by (however, use the Excel function): (x (X-X)(Y-Y) Cov(X, Y)= i=1 n-I e) Calculate the correlation coefficient of MSFT and Walmart. f) Using the Pivot Tables function, please prepare a chart showing the frequency distribution of monthly returns of MSFT and WMT. Discuss what you observe and how it relates what you found in a-c of this task. Presentation of your work: provide a file (Word or PDF would be best) with the results from your Excel file (no need to send the data; make sure to clearly label the cells where your answers are displayed), graphs and discussion. Task 2: Investment Opportunity Set To create the investment opportunity set, we need expected returns, standard deviation and the correlation coefficient. We let the expected return of MSFT (E(TMSFT)) be equal to its arithmetic average calculated in Task 1. Similarly, we let the standard deviation of MSFT (MSFT) be equal to its sample standard deviation from Task 1. Do the same for Walmart. Finally, for correlation coefficient (PMSFT, WMT), use the correlation coefficient calculated in Task 1. Let WMSFT be MSFT's portfolio weight and let WwMT Walmart's portfolio weight. a) Compute the portfolio expected return and standard deviation using the following weights: WMSFT WWMT 1.0 0.0 0.9 0.1 0.8 0.2 0.7 0.3 0.6 0.4 0.5 0.5 0.4 0.6 0.3 0.7 0.2 0.8 0.1 0.9 0.0 1.0 b) Compute the portfolio expected return and standard deviation of the minimum variance portfolio consisting of MSFT and Walmart. c) Draw the investment opportunity set obtained in Task 2a using Scatter diagram Excel tool. d) Compute the weights of MSFT and WMT in a minimum variance portfolio that can be constructed using these two stocks. Compute the expected return and the standard deviation of the minimum variance portfolio. Presentation of your work: Provide a one-page printout (Word or PDF) of the various combinations of expected return and standard deviation and the scatter diagram. Note that the diagram should resemble the graph that I drew on the board in terms of its shape. Task 3: Regression Analysis Use regression analysis to estimate the single index model for MSFT. For input data, use monthly excess returns from January 1990 through November 2022. The Y-variable is the monthly excess return of MSFT and the X-variable is the monthly excess return of the S&P 500 stock index (remember the definition of excess return and use the risk free rate to calculate it). Performing regression analysis using Excel's Data Analysis package; feel free to use any other software for this analysis. i) ii) iii) On the Tool menu, click Data Analysis. If Data Analysis is not available, load the Analysis ToolPak. How? In Excel - In File Manu click Options and click Add-Ins. At the bottom of the screen, chose Manage Excel Add-ins. Then, chose the Analysis TollPak and click Ok. In the Data Analysis (under Data tab) dialog box click Regression and then click OK. To run a simple (univariate) regression, in the Regression dialog box, you must specify the inputs. For Input Y Range, highlight the monthly excess returns of MSFT (including the column heading) as the Y-axis variable. For Input X Range, highlight the monthly excess returns of SP500 (including the column heading) as the X-axis variable. For a multivariate regression, highlight more than one variable. Check the Labels box. iv) v) Click OK. If you have questions at any stage, you can use the Help button on the dialog box to get more information about the options. Presentation of your work: Provide a one-page printout of the regression analysis results and a discussion of what you found. In the discussion please elaborate on what the coefficients of the regression mean and what they imply regarding the performance of MSFT as compared to the overall stock market. Please explain how: you identified the highest monthly return and, more importantly, how did you test whether such a return was abnormal (show the model you used). Multiple Choice Questions Instructions: 1. There are 11 questions. Choose the best answer to each question and write your answer choice (A, B, C, D or E) on the answer sheet (page 9). Questions are equally weighted. Answering all of these questions correctly does not result in a top grade on the assignment. The graphs and the write up will be considered when the total project grade is assigned. 2. Your Excel printouts will serve as evidence of your work. 3. Submit the answer sheet together with your Excel printouts and write ups. Questions 1 to 6 are directed at Task 1 1. What is MSFT's arithmetic average and geometric average monthly return between June 2007 and June 2022 (inclusive of June 2022)? A. Arithmetic average is 1.652% and Geometric average is 1.427% B. Arithmetic average is 0.876% and Geometric average is 0.746% C. Arithmetic average is 5.608% and Geometric average is 5.497% D. Arithmetic average is 1.003% and Geometric average is 0.740% E. Arithmetic average is 1.146% and Geometric average is 0.865% 2. What is Walmart's arithmetic average and geometric average monthly return between June 2007 and June 2022 (inclusive of June 2022)? A. Arithmetic average is 1.754% and Geometric average is 1.533% B. Arithmetic average is 0.876% and Geometric average is 0.746% C. Arithmetic average is 5.608% and Geometric average is 5.497% D. Arithmetic average is 1.003% and Geometric average is 0.740% E. Arithmetic average is 1.146% and Geometric average is 0.865% 3. What is MSFT's sample standard deviation between June 2007 and June 2022? A. 1.754% B. 0.955% C. 6.777% D. 5.678% E. 5.951% 4. What is Walmart's sample standard deviation between June 2007 and June 2022? A. 1.306% B. 0.740% C. 5.609% D. 5.090% E. 5.951% 5. What is the sample covariance of MSFT and Walmart between June 2007 and June 2022 (multiply the result that you get in your calculation by 10,000 to get a number displayed in below answer choices? A. 1.31 B. 0.74 C. 5.61 D. 5.50 E. 8.29 6. What is the sample correlation of MSFT and Walmart between June 2007 and June 2022? A. 0.3447 B. 0.4532 C. 0.2415 D. 0.2759 E. 0.3121 Questions 7 to 9 are directed at Task 2 use data computed in previous task 7. If you form a portfolio with equal weights on MSFT and Walmart, what will be the expected return and standard deviation? A. Expected return = 1.021% and Standard deviation = 5.608 % B. Expected return = 1.136 % and Standard deviation = 4.542% C. Expected return = 1.264% and Standard deviation = 4.704% D. Expected return = 0.909% and Standard deviation = 4.487% E. Expected return = 1.016% and Standard deviation = 4.288% 8. What are the weights on MSFT and Walmart in the minimum variance portfolio? A. Weight on MSFT = 0.841, weight on Walmart = 0.159 B. Weight on MSFT = 0.493, weight on Walmart = 0.507 C. Weight on MSFT = 0.5, weight on Walmart = 0.5 D. Weight on MSFT = 0.319, weight on Walmart = 0.681 E. Weight on MSFT = 0.16, weight on Walmart = 0.84 9. What is the expected return and standard deviation of the minimum variance portfolio? A. Expected return = 1.306% and Standard deviation = 5.608% B. Expected return = 1.136 % and Standard deviation = 4.542% C. Expected return= 1.023% and Standard deviation D. Expected return = 0.909% and Standard deviation = 4.289% = 4.487% E. Expected return = 1.123% and Standard deviation = 4.506% Questions 10 to 11 are directed at Task 3 10. What is MSFT's beta between January 1990 and November 2022? A. 1.203 B. 0.7521 C. 0.6583 D. 1.4512 E. 0.0121 11. What is the intercept of the single-index model (the intercept is provided based on the results using decimals in the regression; move your decimal point two digits to the left if you used percent in your calculations; round your answer)? A. 1.203 B. 0.7521 C. 0.6583 D. 1.4512 E. 0.0121 12. Using the sample of January 1990 through November 2022, what is the likelihood of losing more than 20% of your money in one month when investing in the MSFT stock? A. 0.60% B. 0.75% C. 0.96% D. 1.50% E. 1.26% 13. When was the highest one month return for MSFT observed? A. October, 1998 B. September, 2009 C. December, 2000 D. June, 2012 E. January, 1994