Question: Please solve using excel please and show formula used in excel please Problem Five (15 marks) You work for a large investment management firm. The

Please solve using excel please and show formula used in excel please

Please solve using excel please and show formula
Problem Five (15 marks) You work for a large investment management firm. The analysts with your firm have made the following forecasts for the returns of stock A and stock B: Probability Stock A Stock B EXTREMELY WEAK 2% 65% -80% VERY VERY WEAK 7% 55% 65% VERY WEAK 1% 30% -50% WEAK 4% 20% -25% SOMEWHAT WEAK 16% 18% 15% Somewhat strong 8% 10% 25% STRONG 4% -10% 50% VERY STRONG 0% -17% 75% VERY VERY STRONG 6% -45% 80% EXTREMELY STRONG 2% -55% 95% 100.0% a) Calculate the expected returns, variance and the standard deviations for stock A and B. b) What is the covariance of returns for Stock A and Stock B? What is the correlation coefficient for Stock A and Stock B? c) What is the expected return and standard deviation of a portfolio where 30% of the portfolio is in stock A and 70% of the portfolio is in stock B? d) Create a table that has the expected return and standard deviation for different weights in each stock. This can be done using an excel data table. Start with 100% in A and zero in B, and increments of 10%, complete the table. The last row, will have 0% in A and 100% in B. Then chart (or graph) your results. weight in weight in A Portfolio standard deviation portfolio expected return Link to the answer for 20% and 80%, let the weight in A Link to the answer for 20% and change. 80%, let the weight in A change. 0% 100% 10% 90% 20% 80% 30% 70% 10% 50% 50% 50% 50% 40% 70% 30% 80% 20% 20% 10% 100% 0% Note: All calculations should be rounded to one decimal place if you are using percentages, if you are using decimals then the answer should be rounded to three decimal places. 7

Step by Step Solution

There are 3 Steps involved in it

1 Expert Approved Answer
Step: 1 Unlock blur-text-image
Question Has Been Solved by an Expert!

Get step-by-step solutions from verified subject matter experts

Step: 2 Unlock
Step: 3 Unlock

Students Have Also Explored These Related Finance Questions!