You have been assigned to construct an optimal portfoliocomprising two risky assets (Portfolios A & B) whileconsidering your client’s risk tolerance. The attached spread sheetshows historical monthly returns of the two portfolios; the S&P500 index; and 90-day Treasury Bills. Also shown are the annualizedreturns for each for the period specified. The first risky asset(Portfolio A) is a US equity strategy that uses publicallyavailable valuation, technical and sentiment factors to assesswhich stocks are over-priced and which are under-priced.Fundamental factors indicate the magnitude and quality of acompany’s earnings and the strength of its balance sheet. Examplesof such factors include: cash flow growth, cash flow return oninvested capital, price to cash flow, and accruals which assessearnings quality (low quality earnings indicate that management maybe manipulating earnings by adjusting accruals). Companies withfavorable fundamental factors tend to outperform those with lessfavorable factors. Technical and sentiment factors seek to identifymispriced stocks resulting from investor behavior. Examplesinclude: momentum and price reversals where investors tend toover-react to good news by bidding up prices ABOVE fair value andbad news by bidding down prices BELOW fair value; short interest ona stock which can indicate the investor sentiment about thecompany’s prospects; share buybacks which can indicate a positivesignal from management’s optimism regarding a firm’s futureprospects; and earnings / revenue surprise. Firms with favorabletechnical and sentiment factors also tend to outperform. Forexample, firms whose earnings and revenue exceed analysts’expectations tend to continue to outperform vs. those firms thatexperience earnings surprise due to cost cutting.Starting with themarket portfolio, the US equity strategy over-weights those stockswith more favorable fundamental, technical and sentiment factorsand under-weights or avoids those stocks with less-favorable orun-favorable factors. The strategy seeks to out-perform the marketportfolio as represented by the S&P 500. The monthly returns ofthe US equity strategy are shown in the attached spreadsheet(Portfolio A).The second risky asset (Portfolio B) is a globalmacro hedge fund. This strategy seeks to benefit from mis-pricingswithin and across broad asset classes by taking long and shortpositions in equity markets, bond markets and currencies. Forexample, if the manager believes that US equities will out-performJapanese equities, the portfolio will go long S&P 500 futuresand short TOPIX futures (TOPIX is a Japanese equity index). Thislong/short trade is not impacted by the overall direction of globalequities, but rather the relative movement between US and Japaneseequities. Similarly for bonds, if the manager believes thatinterest rates in the United Kingdom (UK) will decline more so thaninterest rates in Australia, then the manager will buy UK giltfutures (gilt is the 10-year UK bond) and short Australian 10-yearbond futures. Again, this trade is not impacted by the overalldirection of global interest rates, but rather the relativemovement between UK and Australian rates. Recall that bond pricesrise as interest rates decline. The global macro hedge fund ismostly market neutral meaning that long positions equal shortpositions thereby dramatically reducing systematic exposure (lowbeta). Portfolios A & B are much more volatile than the riskfree rate. You will find that their correlation is small indicatingthat there is a diversification benefit to be had from holding bothin a portfolio (I don’t show the correlation, but you will need tocalculate this using the excel function “=correl(range 1, range2)”.You will be meeting with a client that is looking for investmentadvice from you based on your two strategies A & B. Inpreparation for your upcoming meeting with the client, your bossasks that you respond to the questions below and be ready todiscuss. Hint: You will need to determine the correlations andvolatilities for each risk premium. Analytical AssignmentTheanalytical portion of the case assignment should be completed inthe excel template which can be found in Canvas.1. Plot in Excelthe risky asset opportunity set for Portfolios A & B. To dothis you will need to calculate the missing information in thetable from the Excel spreadsheet that accompanies the case usingweights of portfolio A & B in 10 percentage point increments.To do this you will need to know how to program formulas in Excelusing absolute and relative cell references from the data provided.(The table below already exists in the Excel file). Weight PortAWeight Port BReturn Standard DeviationSharpeRatio0%100%1090208030704060505060407030802090101000Determine theoptimal risky portfolio (e.g. the optimal allocation of A & B)using the concepts from Modern Portfolio Theory draw in the CapitalAllocation Line (CAL). The approximate optimal allocation can bedetermined using the table in Excel like the one shown above. Oryou can obtain a more precise optimal allocation using the formulashown in Chapter 7 (equation 7.13). Students are also encouraged touse Excel’s Solver function to find the optimal risky portfolio –that is also acceptable. When drawing the CAL on the efficientfrontier graph plotted in Excel, you can manually draw a linestarting at the risk free rate to the tangent point.2.Find theoptimal complete portfolio based on your client’s indifferencecurve. Hint: Plot an indifference curve on the same graph you justcreated using the utility function formula from Chapter 6. To makethings easier, you can use the same portfolio risk numbers from thetable above and then calculate the expected return based on U = 9%and a risk aversion coefficient A = 10. Plot the indifference curveAND the opportunity set of risky assets on the same graph. Nextdetermine the optimal complete portfolio. While this can be donegraphically, you need to use utility theory concepts to determine amore precise allocation of the optimal risky portfolio (ymaxU) andT-Bills (1-ymaxU). 3.Use the capital asset pricing model (CAPM) todetermine the beta and alpha of Portfolio A & Portfolio B. Showthe CAPM relationship graphically for BOTH Portfolio A andPortfolio B (separate graphs). The market portfolio is representedby the S&P 500 and the risk free rate is represented by 90 dayT-Bills. Determine the beta for portfolio A & B using thefollowing methods:i.The slope function in Excel, and ii.The betaformula (co-variance divided by the market variance) is explainedin the Modules 6 & 7 Notes; ppt lecture notes; and text book.Recall the covariance between two assets (A & B) is thevolatility of asset A times the volatility of asset B times thecorrelation between A & B. Then calculate the alpha for eachportfolio A & B using the intercept function in Excel and theCAPM formula solving for alpha. Note the two CAPM regressions arebased on monthly returns so the y-intercept (or alpha) is a MONTHLYalpha. If you plug the annualized returns of the respectiveportfolio (A or B); the S&P 500; and T-Bills, the alpha youcalculate will be an ANNUALIZED alpha. Intuition Questions a. Yourclient asks why you would combine the lower returning portfolio (A)with portfolio (B) in arriving at the optimal risky portfolio. Whatis your response? b. Your client believes in the weak form ofmarket efficiency as it relates to security selection. Is PortfolioA’s performance sufficient justification to prove this belief? Whyor why not?c. Assume your client believes in the strong-form ofmarket efficiency as it relates ONLY to security selection, whatportfolio substitution(s) would you make to your optimal riskyportfolio? No calculations are necessary.d. After meeting with theclient, she informs you that she prefers a return higher than thatof the optimal risky portfolio. i. Is this possible to achieve andif so, how? ii. What does that indicate about your initialassumptions regarding the indifference curve?e. Portfolio Areturned 5.86% p.a. over the evaluation period compared to a 2.57%p.a. for the S&P 500. This equates to a difference oroutperformance of 3.29% p.a. According to the CAPM, the annualizedalpha of portfolio A is 3.32% p.a. Explain the difference betweenthe two numbers. (Note: It’s not due to rounding)AdditionalRequirementsOrganize and present your results neatly and beprepared to discuss.