Can anyone complete the following question?

Objective: This assignment is designed to demonstrate the importance of correlation in reducing risk. Directions: - Click on the hyperlink, Module 7 risk vs returrn exercisexls i, to open and download the excel spreadsheet. Part 1 of the assignment - Complete the tasks listed below: 1. In the first worksheet, there are a number of examples computing the expected returns and standard deviations of the different investment. 2. You are required to compute the following in Worksheet 1: Handout: 1. Expected Return for High Tech 2. Standard deviation for High Tech 3. Expected return for 50% HT and 50% Collections 4. Standard Deviation for a portfolio of 50% HT and 50% Collections 5. Discuss the standard deviation for this last portfolio. Part 2 of assignment Go to Correlation worksheet 6. Discuss what happens when the following correlations are used in cell E52-1. 1.0.0.3 7. Describe why portfolio diversication is important. - Submit your spreadsheet and responses to this assignment. Exercise for Calculating Expected Return and Risk for an Individual Investment and a Portfolio Assume that you recently graduated and have taken a position working as a financial planner. Your first assignment is to advise a client who wishes to invest $100,000. The information below has been gathered by financial analysts and economists working for your corporation. Your supervisor has restricted you to the following investment alternatives: T-bills, High Tech, Collections, U.S. Rubber, and the Market Portfolio. An analyst working for your investment firm assigned the following probabilities for five possible states of the economy and expected returns for each investment for each of the five states. Using the information below make a recommendation. Estimated Rate of Return on Alternative Investments State of Probability High U.S. Market 2-Stocks Economy of State T-Bills Tech Collections Rubber Portfolio HT&Coll Recession 0.1 8.0% -22.0% 28.0% 10.0% -13.0% Below Average 0.2 8.0% -2.0% 14.7% -10.0% 1.0% Average 0.4 8.0% 20.0% 0.0% 7.0% 15.0% Above Average 0.2 8.0% 35.0% -10.0% 45.0% 29.0% Boom 0.1 8.0% 50.0% -20.0% 30.0% 43.0% E(R) 8.0% 1.7% 13.8% 15.0% Standard Deviation 0.0% 13.4% 18.8% 15.3% I. Calculate the expected return E(R) for High Tech in the space below. E(R;) = _ P.(E(R,)) where: n = the number of states in the economy (5 in this example) P; is the probability of state i ocurring ( is recession, below avg, etc.) E(Ri) is the expected return for the investment for state i. T-bills E(RHTbills) = 1(8%) + 2(8%) + .4(8%) + .2(8%) + .1(8%) = 8.0% High Tech Collections E(Rcall) = 1(28%) + .2(14.7%) + 4(0%) + .2(-10%) + .1(-20%) = 1.7% U.S. Rubber E(RUSR) = .1(10%) + .2(-10%) + .4(7%) + 2(45%) + .1(30%) = 13.8% Market Portfolio E(RMkt) = 1(-13%) + .2(1%) + .4(15%) + 2(29%) + .1(43%) = 15.0%Il. Calculate the standard deviation for an individual security (High Tech). where: n = the number of states in the economy (5 in this example) O; = PR; - E(R;) ) P; is the probability of state / ocurring ( is recession, below avg, etc.) 1-I E(R ) is the expected return for the investment calculated in step I above. They = V-1(8%-8%) + 2(8%-8%) +.4(8%-8%) +.2(8%-8%) +.1(8%-8%) =0% call = V-1(28%-1.7%) +.2(14.7%-1.7%) +.4(0%-1.7%) + 2(-10%-1.7%) +.1(-20%-1.7%} =13.4% USR = 1/1(10%-13.8%} + 2(-10%-13.8%) +.4(7%-13.8%) + 2(45%-13.8%) +.1(30-13.8%} =188% V-1(-13%-15%) +.2(1%-15%) +.4(15%-15%) +.2(29%-15%) +.1(43%-15%) =15.3% HighTech Ill. Calculate the E(R) of a portfolio consisting of 50% in High Tech and 50% in Collections. IV. Calculate the standard deviation for the portfolio of High Tech and Collections. V. Why is the standard deviation of the potfolio of High Tech and Collections so small