4. You need to collect input data for this project. Obtain the monthly returns for Microsoft Corporation (symbol: MSFT), Walmart (symbol: WMT), and S&P500 index (symbol: ^GSPC) from January 1, 1990 through December 31, 2022. Put these into a spreadsheet. You can easily obtain monthly prices (split- and dividend-adjusted) from Yahoo Finance by choosing to view a table of monthly prices from January 1, 1990 through December 31, 2022 (see Historical Data tab). Then choose to 'Download Spreadsheet Format'. Convert the prices to returns, then use a spreadsheet to do the tasks and answer the questions bellow. Make sure to use the Adjusted Close Prices in all of your calculations. Also, pay attention to how the data are sorted. I recommend sorting the data from oldest to newest to make sure that the calculated returns are lined up with the correct date (month). 5. You will also need risk free interest data for this project. Go to Canvas course website, click on the Projects folder and then click on "Rf_monthly" file. The risk free rate is already converted to decimals, so you should not need to convert them in any way. Use the column that provides the risk free rate based on the monthly return because you are dealing with monthly returns for the stock and for the S&P 500. 6. Use Microsoft Excel to perform the tasks described below. Once you have accomplished the tasks, answer the questions on pages 6-8 of this document. Record your answers on the Answer sheet on page 9. ritten explanation of the submitted answer and how it
4. You need to collect input data for this project. Obtain the monthly returns for Microsoft Corporation (symbol: MSFT), Walmart (symbol: WMT), and S\&P500 index (symbol: GSPC) from January 1, 1990 through December 31, 2022. Put these into a spreadsheet. You can easily obtain monthly prices (split- and dividend-adjusted) from Yahoo Finance by choosing to view a table of monthly prices from January 1,1990 through December 31 , 2022 (see Historical Data tab). Then choose to 'Download Spreadsheet Format'. Convert the prices to returns, then use a spreadsheet to do the tasks and answer the questions bellow. Make sure to use the Adjusted Close Prices in all of your calculations. Also, pay attention to how the data are sorted. I recommend sorting the data from oldest to newest to make sure that the calculated returns are lined up with the correct date (month). 5. You will also need risk free interest data for this project. Go to Canvas course website, click on the Projects folder and then click on " Rf monthly" file. The risk free rate is already converted to decimals, so you should not need to convert them in any way. Use the column that provides the risk free rate based on the monthly return because you are dealing with monthly returns for the stock and for the S\&P 500 . 6. Use Microsoft Excel to perform the tasks described below. Once you have accomplished the tasks, answer the questions on pages 6-8 of this document. Record your answers on the Answer sheet on page 9. 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): Cov(X,Y)=n1i=1n(XiX)(YiY) 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 retums, standard deviation and the correlation coefficient. We let the expected return of MSFT (E(MsFr)) be equal to its arithmetic average calculated in Task 1. Similarly, we let the standard deviation of MSFT ( MSFT) be equal to its sample standand deviation from Task 1. Do the same for Walmart. Finally, for correlation coefficient ( MSF, WMa), use the correlation coefficient calculated in Task 1. Let WMSr be MSFT's portfolio weight and let WwMr Walmart's portfolio weight. a) Compute the portfolio expected return and standard deviation using the following weights: 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 Fack 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 retum 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) 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. ii) In the Data Analysis (under Data tab) dialog box click Regression and then click OK. iii) 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 exeese returns of SP 500 (including the column heading) as the X-axis variable. For a multivariate regression, highlight more than one variable. iv) Check the Labels box. 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)
