Please excel where applicable.

Thank you

Question 4 (Risk and Return) (50%) The data below presents monthly closing prices of Standard & Poor's 500 Index, Wal-Mart, and Target to calculate the holding-period returns for the 24 months from May 2013 through May 2015. Month S&P500 Walmart Target 2013 May 1.631 74,84 69,50 June 1.606 74,49 68,86 July 1.684 77,94 71,25 August 1.633 72,98 63,31 September 1.682 73,96 63,98 October 1.757 76,75 64,79 November 1.806 81,01 63,93 December 1.848 78,69 63,27 January 1.783 74,68 56,64 February 1.859 74,70 62,54 March 1.872 76,43 60,51 April 1.884 79,71 61,75 May 1.924 76,77 56,76 June 1.960 75,07 57,95 July 1.931 73,58 59,59 August 2.003 75,50 60,07 September 1.972 76,47 62,68 October 2.018 76,27 61,82 November 2.068 87,54 74,00 December 2.059 85,88 75,91 2015 January 1.995 84,98 73,61 February 2.105 83,93 76,83 March 2.068 82,25 82,07 April 2.086 78,05 78,83 May 2.182 75,86 79,29 a. Use the price data from the table (use the excel posted in Week 4 in Assessment Part) for the Standard & Poor's 500 Index, Wal-Mart, and Target to calculate the holding-period returns for the 24 months from May 2013 through May 2015. Calculate the average monthly holding-period returns and the standard deviation of these returns for the S&P Index, Wal-Mart, and Target. Please compare the results obtained for these three series. (15 points) b. Plot (1) the holding-period returns for Wal-Mart against the Standard & Poor's 500 Index, and (2) the Target holding-period returns against the Standard & Poor's 500 Index. From your graphs, describe the nature of the relationship between stock returns for Wal-Mart and the returns for the S&P 500 Index. Make the same comparison for Target. (analyze if the returns of the individual stocks and the stock market index are positively or negatively related according to your graphs) (10 points) c. Assume that you have decided to invest one-half of your money in Wal-Mart and the remainder in Target. Calculate the monthly holding-period returns for your two-stock portfolio. (the monthly return for the portfolio is the average of the two stocks' monthly returns.) Plot the returns of your two-stock portfolio against the Standard & Poor's 500 Index as you did for the individual stocks in part b. How does this graph compare to the graphs for the individual stocks? Explain the difference. (10 points) d. Make a comparison of the average returns and the standard deviations for all the individual assets and the portfolio that was created. What conclusions can be reached by your comparison in the context of the risk and return (hint: think about the benefits of the diversification)? (5 points) e. According to Standard & Poor's, the betas for Wal-Mart and Target are 0.28 and 0.75, respectively. Compare the meaning of these betas relative to the standard deviations calculated above. Answer the question in the context of the systematic and unsystematic risk. (10 points) 2014 Question 4 (Risk and Return) (50%) The data below presents monthly closing prices of Standard & Poor's 500 Index, Wal-Mart, and Target to calculate the holding-period returns for the 24 months from May 2013 through May 2015. Month S&P500 Walmart Target 2013 May 1.631 74,84 69,50 June 1.606 74,49 68,86 July 1.684 77,94 71,25 August 1.633 72,98 63,31 September 1.682 73,96 63,98 October 1.757 76,75 64,79 November 1.806 81,01 63,93 December 1.848 78,69 63,27 January 1.783 74,68 56,64 February 1.859 74,70 62,54 March 1.872 76,43 60,51 April 1.884 79,71 61,75 May 1.924 76,77 56,76 June 1.960 75,07 57,95 July 1.931 73,58 59,59 August 2.003 75,50 60,07 September 1.972 76,47 62,68 October 2.018 76,27 61,82 November 2.068 87,54 74,00 December 2.059 85,88 75,91 2015 January 1.995 84,98 73,61 February 2.105 83,93 76,83 March 2.068 82,25 82,07 April 2.086 78,05 78,83 May 2.182 75,86 79,29 a. Use the price data from the table (use the excel posted in Week 4 in Assessment Part) for the Standard & Poor's 500 Index, Wal-Mart, and Target to calculate the holding-period returns for the 24 months from May 2013 through May 2015. Calculate the average monthly holding-period returns and the standard deviation of these returns for the S&P Index, Wal-Mart, and Target. Please compare the results obtained for these three series. (15 points) b. Plot (1) the holding-period returns for Wal-Mart against the Standard & Poor's 500 Index, and (2) the Target holding-period returns against the Standard & Poor's 500 Index. From your graphs, describe the nature of the relationship between stock returns for Wal-Mart and the returns for the S&P 500 Index. Make the same comparison for Target. (analyze if the returns of the individual stocks and the stock market index are positively or negatively related according to your graphs) (10 points) c. Assume that you have decided to invest one-half of your money in Wal-Mart and the remainder in Target. Calculate the monthly holding-period returns for your two-stock portfolio. (the monthly return for the portfolio is the average of the two stocks' monthly returns.) Plot the returns of your two-stock portfolio against the Standard & Poor's 500 Index as you did for the individual stocks in part b. How does this graph compare to the graphs for the individual stocks? Explain the difference. (10 points) d. Make a comparison of the average returns and the standard deviations for all the individual assets and the portfolio that was created. What conclusions can be reached by your comparison in the context of the risk and return (hint: think about the benefits of the diversification)? (5 points) e. According to Standard & Poor's, the betas for Wal-Mart and Target are 0.28 and 0.75, respectively. Compare the meaning of these betas relative to the standard deviations calculated above. Answer the question in the context of the systematic and unsystematic risk. (10 points) 2014