70.2K

Verified Solution

Link Copied!

	f.	Would you recommend that Jane invest in the single stock B or the portfolio consisting of stocks B and C? Explain your answer from a riskreturn viewpoint.
		For portfolio BC, both risk and return are				than for stock B alone.
		Considering the CV, however, Jane can determine that					should be recommended.
Requirements									Points
	1	In cells E30, F30, and G30, by using cell references and the function AVERAGE, calculate the expected return of stocks A, B, and C, respectively.							2
	2	In cells E35, F35, and G35, by using cell references and the function STDEV.S, calculate the standard deviation of stocks A, B, and C, respectively.							2
	3	In cells E36, F36, and G36, by using cell references, calculate the coefficient of variation of stocks A, B, and C, respectively.							2
	4	In cell range E41:E47, by using cell references, calculate the expected return of portfolio AB for years 2015:2017.							2
	5	In cell range F41:F47, by using cell references, calculate the expected return of portfolio AC for years 2015:2017.							2
	6	In cell range G41:G47, by using cell references, calculate the expected return of portfolio BC for years 2015:2017.							2
	7	In cells E48, F48, and G48, by using cell references and the function AVERAGE, calculate the expected return of portfolios AB, AC, and BC, respectively.							2
	8	In cells E53, F53, and G53, by using cell references and the function STDEV.S, calculate the standard deviation of portfolios AB, AC, and BC, respectively.							2
	9	In cells E54, F54, and G54, by using cell references, calculate the coefficient of variation of portfolios AB, AC, and BC, respectively.							2
	#	In cell F58, determine what the standard deviations for Stock A and the portfolio AB demonstrate.							1
	#	In cells E59 and I59, determine which option should be recommended to Jane and why.							2
	#	In cell F64, determine what the standard deviations for Stock B and the portfolio BC demonstrate.							1
	#	In cell G65, determine the portfolio which Jane will prefer based on the CV value.							1
	#	Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed.							0

1 Spreadsheet Exercise: Problem 5.34 2 Jane is considering investing in three different stocks or creating three distinct two-stock portfolios. Jane views herself as a rather conservative investor. She is able to obtain historical returns for the three securities for the years 2012 through 2018. The data are given in the following table. 3 1 4 5 6 7 8 Year 2012 2013 2014 2015 2016 2017 2018 Stock A 10% 13% 15% 14% 16% 14% 12% Stock B 10% 11% 8% 12% 10% 15% 15% Stock C 12% 14% 10% 11% 9% 9% 10% 9 10 11 12 13 In any of the possible two-stock portfolios, the weight of each stock in the portfolio will be 50%. The three possible portfolio combinations are AB, AC, and BC. Complete the steps below using cell references to given data or previous calculations. In some cases, a simple cell reference is all you need. To copy/paste a formula across a row or down a column, an absolute cell reference or a mixed cell reference may be preferred. If a specific Excel function is to be used, the directions will specify the use of that function. Do not type in numerical data into a cell or function. Instead, make a reference to the cell in which the data is found. Make your computations only in the blue cells highlighted below. In all cases, unless otherwise directed, use the earliest appearance of the data in your formulas, usually the Given Data section. 14 15 16 17 18 19 20 21 To Do Create a spreadsheet to answer the following: a. Calculate the average return for each individual stock. b. Calculate the standard deviation for each individual stock. c. Calculate the average returns for portfolios AB, AC, and BC. d. Calculate the standard deviations for portfolios AB, AC, and BC. e. Would you recommend that Jane invest in the single stock A or the portfolio consisting of stocks A and B? Explain your answer from a risk-return viewpoint. f. Would you recommend that Jane invest in the single stock B or the portfolio consisting of stocks B and C? Explain your answer from a risk-return viewpoint. 22 23 24 B D E F G H 25 Solution 26 27 a. Calculate the average return for each individual stock. 28 29 Stock A Stock B Stock C 30 Expected return 31 32 b. Calculate the standard deviation for each individual stock. 33 34 Stock A Stock B Stock C 35 Standard deviation Coefficient of variation 36 37 38 c. Calculate the average returns for portfolios AB, AC, and BC. 39 40 Port AB Port. AC Port. BC 41 42 43 Year 2012 2013 2014 2015 2016 2017 2018 44 45 46 47 48 Expected return 49 50 d. Calculate the standard deviations for portfolios AB, AC, and BC. 51 52 Port AB Port. AC Port. BC 53 Standard deviation Coefficient of variation 54 55 e. Would you recommend that Jane invest in the single stock A or the portfolio consisting of stocks A and B? Determine the answer from a risk-return viewpoint. 56 57 58 For portfolio AB, both risk and return are The CV of the portfolio is should be recommended. than for stock A alone. than that of stock A alone, so 59 60

Answer & Explanation Solved by verified expert

See Answer