Need help with Financeto construct the optimal portfolio comprising two risky assets (Portfolios A & B) while considering the clients risk tolerance.

Case Study Assignment - Finance 561 You have been assigned to construct the optimal portfolio comprising two risky assets (Portfolios A & B) while considering the client's risk tolerance. The attached spread sheet shows historical monthly returns of the two portfolios, the S&P 500 and 90-day Treasury Bills. Also shown are the annualized returns for each for the period specified. The first risky asset (Portfolio A) is a US equity strategy that uses publically available valuation, technical and sentiment factors to assess which stocks are over-priced and which are under-priced. Fundamental factors indicate the magnitude and quality of a company's earnings and the strength of its balance sheet. Examples of such factors include: cash flow growth, cash flow return on invested capital, price to cash flow, and accruals which assess earnings quality (low quality earnings indicate that management may be manipulating earnings by adjusting accruals). Companies with favorable fundamental factors tend to outperform those with less favorable factors. Technical and sentiment factors seek to identify mis-pricings resulting from investor behavior. Examples include: momentum and price reversals where investors tend to over-react to good and bad news; short interest on a stock which can indicate the investor sentiment about the company's prospects; share buybacks which can indicate a positive signal from management's optimism regarding a firm's future prospects; and earnings / revenue surprise. Firms with favorable technical and sentiment factors also tend to outperform. For example, firms whose earnings and revenue exceed analysts' expectations tend to continue to outperform vs. those firms that experience earnings surprise due to cost cutting. Starting with the market portfolio, the US equity strategy over-weights those stocks with more favorable fundamental, technical and sentiment factors and under-weights or avoids those stocks with less-favorable or un-favorable factors. The strategy seeks to out-perform the market portfolio as represented by the S&P 500. The monthly returns of the US equity strategy are shown in the attached spreadsheet (Portfolio A). The second risky asset (Portfolio B) is a global macro hedge fund. This strategy seeks to benefit from mis-pricings within and across broad asset classes by taking long and short positions in equity and bond markets and currencies. For example, if the manager believes that US equities will out-perform Japanese equities, the portfolio will go long S&P 500 futures and short TOPIX futures (TOPIX is a Japanese equity index). This long/short trade is not impacted by the overall direction of global equities, but rather the relative movement between US and Japanese equities. Similarly for bonds, if the manager believes that interest rates in the United Kingdom (UK) will decline more so than interest rates in Australia, then the manager will buy UK gilt futures (gilt is the 10year UK bond) and short Australian 10-year bond futures. Again, this trade is not impacted by the overall direction of global interest rates, but rather the relative movement between UK and Australian rates. Recall that bond prices rise as interest rates decline. The global macro hedge fund is mostly market neutral meaning that long positions equal short positions thereby dramatically reducing systematic exposure (low beta). Portfolios A & B are much more volatile than the risk free rate. You will find that their correlation is small indicating that there are diversification benefits to be had holding both in a portfolio (I don't show the correlation, but you will need to calculate this using the =correl(range 1, range2) function in excel. You will be meeting with a client that is looking for investment advice from you based on your two strategies A & B. In preparation for your upcoming meeting with the client, your boss asks that you respond to the questions below and be ready to discuss. Hint: You will need to determine the correlations and volatilities for each risk premium. Assignment 1. Plot in Excel the risky asset opportunity set for Portfolios A & B. Hint: create the following table in excel assuming weights of portfolio A & B in 10 percentage point increments. Then calculate expected return and standard deviation for each allocation to A & B. Weight Port A Weight Port B 0% 10 20 30 40 50 60 70 80 90 100 Return Standard Deviation Sharpe Ratio 100% 90 80 70 60 50 40 30 20 10 0 Determine the optimal allocation of A & B and draw in the Capital Allocation Line (CAL). Hint: use the formula from Chapter 7 (equation 7.13). When drawing the CAL on the efficient frontier graph plotted in Excel, you can manually draw a line starting at the risk free rate to the tangent point. 2. Find the optimal complete portfolio based on your client's indifference curve. Hint: Plot an indifference curve on the same graph you just created using the utility function formula from Chapter 6. Use the range of expected return and standard deviations shown in the table below. Assume U = 9% and a risk aversion coefficient (A) of 10 to complete the table below. Expected Return Standard Deviation 5% 7.5 10 12.5 15 17.5 20 22.5 25 3. Use the capital asset pricing model (CAPM) to determine the beta and alpha of Portfolio A & Portfolio B. Show the CAPM relationship graphically for Portfolio A and Portfolio B. The market portfolio is represented by the S&P 500 and the risk free rate is represented by 90 day Treasury Bill. Determine the beta for portfolio A & B using: i) the slope function in Excel; and ii) the formula for beta (refer to page 294 in the text). Calculate the expected alpha for each portfolio A & B using the intercept function in Excel and the index model of CAPM formula (equation 9.9 on page 302 - note that the terms are in excess return form). Ignore the error term and you have all the information to solve for alpha based on the monthly returns. Compare the betas and y-intercepts using the two different methods. 4. Additional questions: a. The client will notice that the Sharpe ratio of the hedge fund (Portfolio B) is much higher than that of the equity strategy (Portfolio A) and will ask why the optimal risky portfolio wouldn't be 100% of Portfolio B. How would you respond? b. Your client vehemently believes in the semi-strong form of market efficiency as it relates to security selection. Is the performance of Portfolio A sufficient justification to convince the client otherwise - that markets are inefficient or at least less efficient? Why or why not? c. Given your client's belief regarding market efficiency as it pertains to security selection, what portfolio substitution(s) would you make in your optimal portfolio? No calculations are necessary to answer this. d. Your client is expected to ask why you are recommending the optimal complete portfolio instead of the optimal portfolio even though the latter has a higher expected return. How will you respond? e. After meeting with the client, she appears to prefer the risk/return tradeoff of the optimal portfolio rather than that of the optimal complete portfolio. What does that indicate about your initial assumptions regarding the indifference curve? Additional Comments Organize and present your results neatly and be prepared to discuss in class. The case is designed to pull together investment principals you learned throughout the course and is based on an exercise I had done for a pension plan. Each step of the case builds upon the prior so it's important that you get each part correct before moving on. Mar 01 Apr 01 May 01 Jun 01 Jul 01 Aug 01 Sep 01 Oct 01 Nov 01 Dec 01 Jan 02 Feb 02 Mar 02 Apr 02 May 02 Jun 02 Jul 02 Aug 02 Sep 02 Oct 02 Nov 02 Dec 02 Jan 03 Feb 03 Mar 03 Apr 03 May 03 Jun 03 Jul 03 Aug 03 Sep 03 Oct 03 Nov 03 Dec 03 Jan 04 Feb 04 Mar 04 Apr 04 May 04 Jun 04 Jul 04 Aug 04 Sep 04 Oct 04 Nov 04 Dec 04 Jan 05 Feb 05 Mar 05 Apr 05 May 05 Jun 05 Jul 05 Aug 05 Sep 05 Oct 05 Nov 05 Dec 05 Jan-06 Feb-06 Mar-06 Annualized Return Annualized Volatility Portfolio A -4.50% 8.75% 1.03% -2.45% -1.87% -3.77% -7.43% 2.09% 5.94% 1.99% -1.18% -1.45% 4.95% -5.28% -0.68% -8.26% -7.03% 0.80% -10.07% 6.93% 4.13% -4.99% -2.11% -1.51% 1.66% 6.85% 5.13% 1.11% 1.96% 2.37% -0.23% 5.56% 1.48% 4.23% 2.24% 2.34% -0.86% -2.23% 1.88% 2.50% -3.94% -0.19% 1.42% 1.96% 5.21% 3.23% -2.72% 2.57% -0.58% -2.07% 3.84% 0.66% 4.16% -0.72% 1.96% -2.02% 4.75% 0.83% 4.15% 0.04% 1.00% Portfolio B -1.10% 1.38% -7.13% 3.68% 2.72% 13.44% -2.11% 2.51% 4.24% 10.09% 1.27% -0.56% -0.86% 3.56% -1.62% 7.47% -2.09% 3.40% -0.32% 2.82% 4.95% -5.70% 5.90% -6.23% 1.78% 3.24% 10.52% 1.09% -17.02% 1.77% 13.19% 12.80% 3.82% 4.50% 8.29% 7.74% 4.09% -9.22% 1.35% -1.96% 8.12% -2.86% 1.15% 0.62% 6.39% 0.44% 1.18% 9.27% -1.09% -3.82% -0.06% 5.31% 2.78% -1.15% 7.09% 2.84% 5.94% -4.20% 1.17% 2.23% -5.05% S&P 500 -6.34% 7.77% 0.67% -2.43% -0.98% -6.26% -8.08% 1.91% 7.67% 0.88% -1.46% -1.93% 3.76% -6.06% -0.74% -7.12% -7.79% 0.66% -10.87% 8.80% 5.89% -5.87% -2.62% -1.50% 0.97% 8.24% 5.27% 1.28% 1.76% 1.95% -1.06% 5.66% 0.88% 5.24% 1.84% 1.39% -1.51% -1.57% 1.37% 1.94% -3.31% 0.40% 1.08% 1.53% 4.05% 3.40% -2.44% 2.10% -1.77% -1.90% 3.18% 0.14% 3.72% -0.91% 0.81% -1.67% 3.78% 0.03% 2.65% 0.27% 1.24% 90dBill 0.44% 0.38% 0.37% 0.32% 0.31% 0.30% 0.29% 0.26% 0.21% 0.18% 0.16% 0.13% 0.15% 0.14% 0.15% 0.14% 0.15% 0.14% 0.14% 0.14% 0.13% 0.12% 0.11% 0.09% 0.10% 0.10% 0.10% 0.09% 0.09% 0.08% 0.08% 0.08% 0.08% 0.08% 0.08% 0.07% 0.08% 0.08% 0.08% 0.08% 0.10% 0.11% 0.12% 0.13% 0.14% 0.16% 0.18% 0.18% 0.21% 0.22% 0.24% 0.24% 0.25% 0.27% 0.27% 0.29% 0.30% 0.32% 0.33% 0.32% 0.37% 5.86% 24.64% 2.57% 2.19% 13.4% 19.0% 14.2% Rm - Rf 0.3% Weight B 0% 10% 20% 30% 40% 50% 60% 70% 80% 90% 100% 100% 90% 80% 70% 60% 50% 40% 30% 20% 10% 0% Exp Rtn Exp Risk Sharpe RB - Rf Beta = y-int = #DIV/0! A= U= Weight A RA - Rf 10 9.0% Indifference curve Exp Rtn #DIV/0! #VALUE! #VALUE! Beta = y-int = #VALUE! #VALUE! #DIV/0