please use cell references

A D F G You are putting together a portfolio made up of four different stocks. However, you are considering two possible weightings 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 green cells highlighted below. In all cases, unless otherwise directed, use the earliest appearance of the data in your formulas, usually the Given Data section. Given Data: Portfolio Weightings Asset Beta 7 First Portfolio Second Portfolio A 2.5 10% 40% B 1.0 10% 40% C 0.5 10 40% 10% 11 -1.5 40% 10% 12 a. What is the beta on each portfolio? 13 14 First Portfolio Second Portfolio 15 Portfolio beta 16 17 b. Which portfolio is riskier? 18 19 20 According to the portfolios' betas, is riskier 21 c. If the risk-free rate of interest were 4 percent and the market risk premim were 5 percent, what rate of return would you expect to earn from each of the portfolios? 22 23 Risk-free rate 24 4% Risk nremim 25 50% 8-23 Ready A C D F F Given Data: 5 Portfolio Weightings Asset 7 Beta First Portfolio Second Portfolio A 2.5 10% 40% B 1.0 10% 40% 10 C 0.5 40% 10% 11 -1.5 40% 10% 12 a. What is the beta on each portfolio? 13 14 15 First Portfolio Second Portfolio 16 Portfolio beta 17 b. Which portfolio is riskier? 18 19 20 According to the portfolios' betas, is riskier. 21 c. If the risk-free rate of interest were 4 percent and the market risk premim expect to earn from each of the portfolios? 5 percent, what rate of return would you were 22 23 24 Risk-free rate 4% 25 Risk premium 5% 26 27 First Portfolio Second Portfolio Expected rate of return of the portfolio 28 29 30 Requirements 1 Start Excel. 31 32 In cell E16, by using cell references and the Excel SUMPRODUCT function, calculate the beta of the first portfolio. (1 pt.) 2 33 In cell F16, by using cell references and the Excel SUMPRODUCT function, calculate the beta of the second portfolio. 8-23 A G 23 Risk-free rate 24 4% Risk premium 25 5% 26 27 First Portfolio Second Portfolio Expected rate of return of the portfolio 28 29 30 Requirements 1 Start Excel. In cell E16, by using cell references and the Excel SUMPRODUCT function, calculate the beta of the first portfolio. (1 31 32 2 pt.) 33 In cell F16, by using cell references and the Excel SUMPRODUCT function, calculate the beta of the second portfolio. 3 (1 pt.) 34 4 In cell E20, choose from the drop-down menu and interpret the betas of the two portfolios. (I pt.) 5 In cell E28, by using cell references, calculate the expected rate of return of the first portfolio. (l pt.) 6 In cell F28, by using cell references, caleulate the expected rate of return of the second portfolio. (1 pt.) 35 36 37 7 Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed 38 39 40 41