Please help make an excel for this problem! Due tonight! Thanks in advance.

qattachments_bd71cec5bdaa8bb8a67c3c7fc4ed6a215b64e0d6 Expected Returns Cnsmr Manuf HiTec Hlth Other 0.489479167 1.061979167 -0.288854167 0.3715625 0.527291667 Standard Deviations 3.586353776 3.931726986 Correlation Matrix Correlations Cnsmr Manuf HiTec Hlth Other Cnsmr Manuf 1 0.688662565 0.555233747 0.344888071 0.814748626 Covariance Matrix Covariances Cnsmr Manuf HiTec Hlth Other 7.696022468 3.701748187 4.161784405 x HiTec Hlth Other 1 0.501606837 1 0.3763978 0.350798253 1 0.729890614 0.608868549 0.408422313 1 Cnsmr Manuf HiTec Hlth Other 12.72795494 9.609379156 15.16518393 4.530963314 12.03396421 9.609379156 15.29745129 15.01984669 5.421128158 11.81879182 15.16518393 15.01984669 58.61179556 9.889697168 19.29842289 4.530963314 5.421128158 9.889697168 13.56020068 6.226561523 12.03396421 11.81879182 19.29842289 6.226561523 17.14002808 Risk-free rate 0.000416667 Portfolio Weights Cnsmr Manuf 0.2 HiTec 0.2 Hlth 0.2 Page 1 Other 0.2 0.2 1 Excel Exercise Professor M uhlhofer School of Business Administration University of Miami FIN320: Investments and Security Markets Spring 2017 This assignment is due in hard copy at the beginning of class (in the section in which you are enrolled) on Tuesday, February 14. Collaboration is allowed, and you may work on this assignment in groups of up to three students. Please only collaborate with students from your own section, and hand in a single solution for your group with all members' names on it. You must hand in your Excel output, which must be formatted for printing and have a professional appearance. No screen shots! Instructions You will use Excel to plot an efficient portfolio frontier and a CML, for a 5-asset portfolio. You will do this by using the Solver function, which performs constrained optimization. Step-by-step instructions are below. Generally, solver maximizes or minimizes the value in a target cell, by changing a set of allowable cells until the optimization is achieved. Further, this optimization can be made subject to certain constraints. The spreadsheet on Blackboard contains data for five industry portfolios and the risk-free rate. All units are in percentage points per month. I used .csv format for the dataset to avoid compatibility problems among different versions of Excel. Be sure to save your work in Excel format (.xls or .xlsx), or your formulas and graphs will be lost! First, you will set up cells for the portfolio mean and portfolio standard deviation. With many assets, it is useful to employ matrix multiplication to do this. 1. Cells A29-E29 contain the portfolio weights, which for now are all .2 (i.e. each asset gets equal weight). This is a 1n row-vector of weights T . In cell G29, construct the sum of the weights, using the function =SUM. 2. Set up a cell for the portfolio expected return. This is can be written as T 0 , where is the row vector of individual asset expected returns in A3-E3. To compute this, type into the cell which you want to contain this value =MMULT(A29:E29,TRANSPOSE(A3:E3)). Be sure to hit Ctrl-shift-enter after typing this, to make Excel understand that it is dealing with vectors and matrices. 3. Set up a cell for the portfolio variance. This can be written as T V T 0 where V is the covariance matrix for your assets, set up in B18-F22. To compute this, type into the cell which you want to contain this value =MMULT(MMULT(A29:E29,B18:F22),TRANSPOSE(A29:E29)). Again, be sure to hit Ctrl-shift-enter. 4. Set up a cell for portfolio standard deviation, computed as square root of portfolio variance. 5. Set up a cell for the Sharpe Ratio. 6. Set up a graph, and plot the five individual industry portfolios in a space that has Expected Return on the vertical and Standard Deviation on the horizontal axis. Use an X-Y scatter plot for this, and be sure to select the plot type that does not join the points with a line for this series. 1 7. Now use solver to plot the minimum variance portfolio, the efficient portfolio frontier, and the CML. To do this, click on Tools and open up Solver (install the add-in if you cannot find it in the menu. Using the most recent version of excel go to help and search for solver. Follow the directions under Load the Solver Add-in. Once it is loaded it is located under the data tab). Skip the target cell for now and enter your portfolio weights as the cells that need to be changed. Also, enter a constraint that the weights need to sum to 1 (i.e. constrain the cell that contains the sum of the weights to equal 1). Note: Solver has a check box to constrain the obtained solution to be positive. This would prevent short selling. You do not want this option checked (i.e. you want to allow short selling). (a) First, plot the efficient portfolio frontier. To do this, stipulate various levels of expected return, and minimize portfolio standard deviation for each. This means your target cell will be your portfolio standard deviation, and you will want to minimize it. Then add a constraint that sets the portfolio return equal to that of your lowest-yielding asset. Click solve. This will change the weights, the portfolio variance and standard deviation, as well as the portfolio expected return in your spreadsheet. Copy the current set of weights as well as the portfolio standard deviation and expected return and paste them as values underneath, so you have them recorded. This is the first point on your efficient portfolio frontier. Go back to solver and increment the value to which the portfolio expected return is constrained by a small amount. Click solve again. This will give you a new set of weights, portfolio standard deviation, and portfolio expected return. Record these again. This is the next point on your efficient portfolio frontier. Keep doing this until you get to setting the portfolio expected return equal to that of your highest-yielding asset. Do about 20 solver runs in between. Plot the combinations of expected returns and standard deviations on the same graph as your individual portfolios, as a new series. This time, link these points with a line. (b) Plot the minimum variance portfolio. To accomplish this, eliminate the constraint which sets portfolio expected return equal to a specific value and re-run solver. This way you are computing the unconditional minimum standard deviation. Record the resulting weights, expected return, and standard deviation, and plot the latter two on the graph as a new series. (c) Finally, plot the tangency portfolio and the CML. To do this, change your target cell to the cell containing your Sharpe Ratio, and instruct Solver to maximize this cell. You do not need any expected-return constraints here. To plot your CML, construct the following sets of points (i.e. E/S combinations): one consisting of a zero allocation to the tangency portfolio and a 100% allocation to the risk-free asset, one consisting of a 100% allocation to the tangency portfolio and a zero allocation to the risk-free asset, and one consisting of a -100% allocation to the risk-free asset and a 200% allocation to the tangency portfolio. You should be able to set these up by simply using the methodology discussed in class. To do this, think about how the CML is parameterized by the portfolio allocation between risky and risk-free asset. Plot the CML as an additional series on the same graph. If the tangency point for the CML lies beyond the portion of the efficient portfolio frontier which you plotted, consider plotting more points, beyond the expected return of the highest-yielding asset to show tangency. This last part, however, is optional. (d) Make sure your graph is well-labeled and has a legend. Excel can generate all of this for you. Do not do this by hand after printing. (e) Submit, in addition to the graph, that portion of your spreadsheet that contains the numbers you plotted for your efficient portfolio frontier, as well as those for the MVP and the three points of the CML. 2