please include appropiate cell references, thank you!
A F G putting together a portfolio made up of four different stocks. However, you are considering two possible weightings: You are 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 celll 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: 5 Portfolio Weightings E Second Portfolio Asset Beta First Portfolio 2.5 10% 40% A 1.0 10% 40% B 40% 10% 0.5 10 40% 10% 1.5 11 12 a. What is the beta on each portfolio? 13 14 Second Portfolio First Portfolie 15 Portfolio beta 17 b. Which portfolio is riskier? 18 19 is riskier According to the portfolios' betas, 20 c. If the risk-free rate of interest were 4 percent and the market risk premium were 5 percent, what rate of return would you expect to cam from cach of the portfolios? 21 22 4% 21 Risk-free rate 34 5%% Risk premium Second Portfolio First Portfolie 26 27 Expected rate of retum of the portfolio 20 SUme apps Update now A1 A D E 9 F 1.0 G 10% 10 40% 0.5 40% 11 10% 1.5 40% 10% 12 a. What is the beta on each portfolio? 13 14 15 First Portfolio Second Portfolio Portfolio beta 16 17 b. Which portfolio is riskier? 18 19 According to the portfolios' betas, 20 is riskier 21 rate of interest were 4 percent and the market risk premium were 5 percent, what rate of return c. If the risk- would you expect to eam from each of the portfolios? 22 23 4% Risk-free rate 24 Risk premium 5% 25 26 First Portfolio Second Portfolio 27 Expected rate of return of the portfolio 26 29 Requirements 1 Start Excel. In cell E16, by using cell references and the Excel SUMPRODUCT function, calculate the beta of the first 31 32 2 portfolio. (1 pt.) In cell F16, by using cell references and the Excel SUMPRODUCT function, calculate the beta of the second portfolio. (I pt.) 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 retan of the first portfolio. (I pt.) 6 In cell F28, by using cell references, calculate the expected rate of retam of the second portfolio (1 pt.) 7 Save the workbook. Close the workbook and then exit Excel. Subeait the workbook as directed 36 8-23 ead BCD
