Question

17.30 Mini Case This Mini Case is available in MyFinanceLab. Note: Although not absolutely necessary, you are advised to use a computer spread- sheet to work the following problem. a. Use the price data from the table that follows 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 MONT SEP 500 TAASET 2013 May $1,631 $74.84 $69.50 June 1,606 74.49 68.86 July 1,686 71.25 August 1,633 7298 63.31 September 1,682 73.96 63.98 October 1,757 76.75 64.79 November 1.806 81.01 6393 December 1,848 78.69 63.27 2014 January 1.783 14.68 56.64 February 14.70 March 1,872 76.13 50.51 April 1.884 79.71 61.75 May 1.924 75.77 56.76 June 1.960 7507 57.95 July 1.931 73.58 59.59 August 2.003 15.50 5007 September 1.972 75.47 62.68 October 2018 1527 61 82 November 2.068 8154 14.00 December 2.059 75.91 2015 January 1.995 73.61 February 2.105 7583 March 2.068 April 2.085 May 2.128 75.80 7929 b. Calculate the average monthly holding period returns and the standard de viation of these retums for the S&P 500 Index, Wal-Mart, and Target c. 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. (Use Figure -5 as the format for your graph) d. From your graphs in parte, describe the nature of the relationship between the stock returns for Wal-Mart and the returns for the S&P 500 Index. Make the same comparison for Target e. Assume that you have decided to invest one-hallod your money in Wal-Mart and the remainder in Target Calculate the monthly holdins-period returns for your two-stock portfolio Hout: The monthly return for the portfolio is the

Answer 1

- . Cla Book1 - Microsoft Excel Home Insert Page Layout Formulas Data Review View X AutoSum Fill Insert Delete Format 2 Clear Conditional Format Cell Formatting as Table Styles Styles Sort & Find & Filter - Select Editing Cells J K L M N O P Q R S T V 73.96 * Cut Calibri 11 Α Α΄ Wrap Text General En Copy Paste BI U - +0.00 $ - % Merge & Center Format Painter .00 0 Clipboard Font Alignment Number 114 foc A B С Τ Ε F G H 1 a Calculation of Holding Period Return 2 Year S&P 500 Wal - mart Target 3 Month Price Return (in % Price Return (in %) Price Return in % 4 2013 May 1631 74.84 69.5 5 June 1606 -1.53 74.49 -0.47 68.86 -0.92 6 July 1686 4.98 77.94 4.63 71.25 3.47 7 August 1633 -3.14 72.98 -6.36 63.31 - 11.14 8 September 1682 3.00 1.34 63.98 1.06 9 October 1757 4.46 76.75 3.77 64.79 1.27 10 November 1806 2.79 81.01 5.55 63.93 -1.33 11 December 1848 2.33 78.69 -2.86 63.27 -1.03 12 2014 January 1783 -3.52 74.68 -5.10 56.64 -10.48 13 February 1859 4.26 74.7 0.031 62.54 10.42 14 March 1872 0.70 76.45 2.32 60.5 -3.25 15 April 1884 0.64 79.71 4.29 61.75 2.05 16 May 1924 2.12 76.77 3.69 56.76 -8.08 17 June 1960 1.87 75.07 -2.21 57.95 2.10 18 July 1931 -1.48 73.58 -1.98 59.59 2.83 19 August 2003 3.73 75.5 2.61 60.07 0.81 20 September 1972 -1.55 76.47 1.28 62.68 4.34 21 October 2018 2.33 76.27 -0.26 61.82 -1.37 22 November 2068 2.48 87.54 14.78 74 19.70 23 December 2059 -0.44 85.88 -1.90 75.91 2.58 24 2015 January 1995 -3.11 84.98 -1.05 73.61 -3.03 25 February 2105 5.51 83.93 -1.24 76.83 4.37 26 March 2068 -1.76 82.25 -2.00 82.07 6.82 27 April 2086 0.87 78.05 -5.11 78.83 -3.95 28 May 2128 2.01 75.86 -2.81 79.29 0.58 29 30 Retun of curent month = 31 Price of current month - Price of previous month / Price of previous month * 100 32 33 Return of June, 2013 of S&P 500 34 Price of June - Price of May / Price of May * 100 35 ( calcuated in same way of all the months) 36 Sheet20 Sheet21 Sheet22 Sheet23 Sheet24 Sheet25 Sheet26 Sheet27 Sheet28 Sheet29 Ready IIII @ 72% - + O Start a search e ENG US 5:26 PM 6/12/2020 10

. X AutoSum Fill Insert Delete Format Conditional Format Cell Formatting as Table Styles Styles 2 Clear Sort & Find & Filter Select Editing Cells - Cla Book1 - Microsoft Excel Home Insert Page Layout Formulas Data Review View * Cut Calibri 11 Α Α΄ Wrap Text General En Copy Paste BIU Merge & Center +0.00 $ - % Format Painter .00 0 Clipboard Font Alignment Number K56 foc A B с D E F G 38 b Calculation of average return and standard deviation 39 S&P 500 (in %) Wal-mart (in %) Target (in %) 40 Average Return 1.15 0.15 0.74 41 42 Standard Deviation 2.68 4.47 6.42 43 44 To calucate average return in excel, use formula -average() and select the range of which you want to take average 45 To calucate standard deviation in excel, use formula Estdev() and select the range of which you want to take standard deviation 46 H - K L M N o P 47 с 48 49 0.15 Wal-Mart 50 51 52 0.1 53 54 55 0.05 56 57 O 58 59 60 -0.1 -0.05 0 0.05 0.1 0.15 61 S&P 500 -0.05 62 63 64 KI Sheet20 Sheet21 Sheet22 Sheet23 Sheet24 Sheet25 Sheet26 Sheet27 Sheet28 Sheet29 Ready BIS DU 91% + O Start a search o jät e ENG IN 5:30 PM 6/12/2020 10

d
There is no established relationship as per graph(Below 0%)
Above 0%, Wal mart return increases with increase in S&P 500 return

If you have any query, feel free to ask and if you are satisfied with the solution then please give me positive rating