123 A B D E F G H Problem 13-20 J 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. Consider the following stocks, all of which will pay a liquidating dividend in a year and nothing in the interim: 4567892 Market Capitalization Expected Liquidating Dividend ($ million) ($ million) Beta 10 Stock A 800 1,000 0.77 11 Stock B 750 1,000 1.46 12 Stock C 950 1,000 1.25 13 Stock D 900 1,000 1.07 14 15 16 17 18 a. Calculate the expected return of each stock. b. What is the sign of the correlation between the expected return and market capitalization of the stocks? a. Calculate the expected return of each stock. 19 20 Expected return Stock A 21 22 23 24 25 26 27 28 29 Expected return Stock B Expected return Stock C Expected return Stock D b. What is the sign of the correlation between the expected return and market capitalization of the stocks? Correlation coefficient The sign of the correlation is A B C D E F G H 30 31 Requirements 32 33 34 35 1. Start Excel - complete. 2. In cell E20, by using cell references, calculate the expected return of stock A (1 pt.). 3. To calculate the expected returns of stocks B thru D, copy cell E20 and paste it onto cells E21:E23 (1 pt.). 4. In cell E27, by using cell references and the function CORREL, calculate the correlation between the expected return and the market capitalization of the stocks (1 pt.). 5. In cell E28, select either Positive or Negative to discern the sign of the correlation between the expected return and the market capitalization of the stocks (1 pt.). 36 37 6. Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed. 38 39 40 41