See scenario and questions below...

Question 56 I You have been assigned to construct an optimal portfolio comprising two risky assets (Portfolios A St B) while considering your client's risk tolerance. The attached spread sheet shows historical monthly returns ofthe two portfolios (ASCB); S&P 500 index; and 907day Treasury Bills. Also shown are the annualized returns for each {Of the period specied. Portfolio A: Active Stock Selection Strategy Portfolio A is an actively managed US equity strategy that uses publicly available fundamental, technical and sentiment factors to assess which stocks are overpriced and which are under priced. Fundamental factors indicate the magnitude and quality of a company's earnings and the strength ofits balance sheet. Examples of such factors include cash flow growth, cash flow return on invested capital, debt to equity ratio, 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. portfolio A uses technical and sentiment factors to identify mispriced stocks by exploiting investor behavioral biases. Examples include: momentum and price reversals where investors tend to overreact to good news by bidding up prices ABOVE fair value and overreact to bad news by bidding down prices BELOW fair value; short interest on a stock which indicates investor sentiment about a company's future prospects; share buybacks and dividend changes which can indicate a positive signal from management's optimism regarding a firm's future prospects; and earnings surprise. Firms with favorable technical and sentiment factors also tend to outperform those stocks having less favorable factors. 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 overeweights those stocks having more favorable fundamental, technical and sentiment factors and underweights or avoids those stocks with less favorable or unfavorable factors. The strategy seeks to outperform the market portfolio as represented by the $5513 500. The monthly returns of the US equity strategy are shown in the attached spreadsheet (Portfolio A). Portfolio B: Global Macro Hedge Fund portfolio B is a global macro hedge fund that seeks to benefit from mispricings within and across broad asset classes by taking long and short positions in equity markets, bond markets and currencies. For example, if the manager believes that US equities will outperform Iapanese equities, the portfolio will be long SBCP 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 return 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, the portfolio will be long UK gilt futures (gilt is the IOAyear UK bond) and short Australian IOAyear 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. As a macro hedge fund, portfolio B is market neutral meaning that the long positions equal short positions thereby dramatically reducing systematic exposures. (erg. low beta). Combining Portfolios, A, 8; B Portfolios A SC B are much more volatile than the riskefree rate. You will also find that their correlation is small indicating that there is a diversication benefit to be had from holding both in a portfolio (The correlation is now shown in the spreadsheet You will need to calculate this using the excel function \":correl(range I, rangeZ)\". You will be meeting with a client that is looking for investment advice from you based on these two portfolios. In preparation for your upcoming meeting with the client; your boss asks that you perform the necessary calculations outlined below and be able to respond to the questions that follow. Hint: You will need to determine the correlations and volatilities for A 8513. Analytical Assignment Complete the analytical portion of the case assignment in excel template which. Formulas must reference parameters in other cells using absolute or relative cell references. DO NOT HARD CODE ANY NWBERS. 1) Plot in Excel the opportunity set for Portfolios A 86 B. To do this you will need to calculate the missing information in the table found in the Excel spreadsheet that accompanies the case using weights of portfolio A 86 B in IO percentage point increments. To do this you will need to know how to program formulas in Excel using absolute and relative cell references from the data provided. (The table below already exists in the Excel file). 2) Weight Port A Weight Port B Return Standard Sharpe Ratio Deviation Determine the optimal risky portfolio (eg. the optimal allocat'on ofA SC B) using the concepts from Modern Portfolio Theory and draw in the Capital Allocation Line (CAL) The approximate optimal allocation can be determined using tne table in Excel like the one Sl'lOWl'l above 3) 4) 11. Find the optimal complete portfolio graphically using the client's indifference curve. Plot an indifference curve on the same graph you just created using the utility function formula from Chapter 6. To make things easier, you can use the same portfolio risk numbers from the table above and then calculate the expected return based on the values of utility (U) and risk aversion coefficient (A) in provided in the Excel spreadsheet. Plot the indifference curve AND the opportunity set of risky assets on the same graph. Plot the CAPM regressions of Portfolio A and Portfolio B (separate graphs) in the Excel spreadsheet. The market portfolio is represented by the S&P 500 and the riskfree rate is represented by 90day TBill. Calculate the beta for each portfolio using the following methods: The slope function in Excel The beta formula (covariance divided by the market variance) is explained in the Modules 6 SC 7 Notes; ppt lecture notes; and textbook. Recall the covariance between two assets (A 8L B) is the volatility of asset A times the volatility of asset B times the correlation between A SE B. Then calculate the alpha for each portfolio A 8L B using the intercept function in Excel and the CAPM formula solving for alpha. Note the two CAPM regressions are based on monthly returns so the yintercept (or alpha) is a MONTHLY alpha. Calculate the annualized alpha using the CAPM formula, beta from above and the annualized returns. Intuition Questions After receiving a corrected analytical solution, should you then proceed with answering the intuition questions below to prepare for the meeting with the client 1. Your client asks why you would combine Portfolio (A), which has a lower Sharpe ratio, with Portfolio (B) to arrive at the optimal risky portfolio. Prepare a clear and concise response to your client. Your client believes in the weak form ofmarket efficiency as it relates to security selection. ls Portfolio A's performance enough justication to prove or disprove this belief? Why or why not? After further discussions with your client, it turns out that she believes in the semiform of market efciency as it relates ONLY to security selection, what portfolio substitution