Screenshots attached for the Case Study Assignment and Case Analytical Part.

Please help with questions 2 and 3

• Your client believes in the weak form of market efficiency as it relates to security selection. Is Portfolio A's performance enough justification to prove or disprove this belief? Why or why not?
• After further discussions with your client, it turns out that she believes in the semi-strong form of market efficiency as it relates ONLY to security selection, what portfolio substitution(s) would you make to your optimal risky portfolio? No calculations are necessary.

As an extra credit point, students can obtain the precise optimal allocation of Portfolios A 3. After further discussions with your client, it turns out that she believes in the semi- & B using Excel's Solver function. You do not need to plot the CAL - rather you can strong form of market efficiency as it relates ONLY to security selection, what portfolio manually draw the CAL on the Excel graph (using Insert Shapes window in Excel) starting at substitution(s) would you make to your optimal risky portfolio? No calculations are the risk-free rate to the tangent point. necessary. 4. After meeting with the client, she informs you that she prefers a return higher than that 2) Find the optimal complete portfolio graphically using the client's indifference curve. of the optimal risky portfolio. Plot an indifference curve on the same graph you just created using the utility function a. Is this possible to achieve and if so, how? formula from Chapter 6. To make things easier, you can use the same portfolio risk b. What does that indicate about your initial assumptions regarding the numbers from the table above and then calculate the expected return based on the indifference curve? values of utility (U) and risk aversion coefficient (A) in provided in the Excel 5. Portfolio A returned 9.20% p.a. over the evaluation period compared to a 5.00% p.a, for spreadsheet. Plot the indifference curve AND the opportunity set of risky assets on the the S&P 500. This equates to a difference or outperformance of 4.20% p.a. According to same graph. CAPM, the annualized alpha of portfolio A is 4.74% p.a. Explain the difference between Next determine the optimal complete portfolio. You did this graphically above, but the two numbers. (Note: It is not due to rounding) you need to calculate the precise composition of the optimal risky portfolio (recall Upload the completed intuition questions in Canvas under Case Study Intuition (maxU) and T-Bills (1-ymaxU). Questions. One submission per group. 3) Plot the CAPM regressions of Portfolio A and Portfolio B (separate graphs) in the Excel Additional Information spreadsheet. The market portfolio is represented by the S&P 500 and the risk-free rate The case is designed to pull together investment principals you learned throughout the is represented by 90-day T-Bill. Calculate the beta for each portfolio using the course - specially Modules 4, 5, 6 & 7 and calculating geometric returns. It is based on a following methods real-life project I had done for a pension client. i. The slope function in Excel Each step of the case builds upon the previous, so it is important students get each part i. The beta formula (co-variance divided by the market variance) is explained in the of the analysis correct before moving on. If your group is struggling, please let me know Modules 6 & 7 Notes; ppt lecture notes; and textbook. Recall the covariance between ASAP and we can discuss via email or phone. Please do not wait until the last minute to two assets (A & B) is the volatility of asset A times the volatility of asset B times the start working on the case. correlation between A & B. Then calculate the alpha for each portfolio A & 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 y-intercept (or alpha) is a MONTHLY alpha. Calculate the annualized alpha using the CAPM formula, beta from above and the annualized returns. After completing, upload the spreadsheet in Canvas Assignments under the Case Study_Analytical Solution. One submission per group. 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. 2. Your client believes in the weak form of market efficiency as it relates to security selection. Is Portfolio A's performance enough justification to prove or disprove this belief? Why or why not?Portfolio A SAP 500 Bm - Bf Portfolio A Beta = 0.80906 Using Excel slope function far 01 -1.10%% 17 Intercept Function pr 01 8.755% 1.0059 0.67% 0.3756 0.305% -7 .5036 Annualized Alpha = 0.0474043 3.6856 0.3256 2.76%% ul D1 -1.87%% -1.50% 2.725 0.315% -6.26%6 0.305% -6.56%6 CAPM: Market vs Portfolio A y = 0.6091x + 0.0037 3.50%% 13.44 -8.06% 0.2956 -6.3636 -3.79% -2.39%% 2.095% 1.9156 1.6559 1.839 6.605 4.2456 0.2156 6.3956 4.0256 8:00% 10.09% 0.1856 0.7056 9.9156 1.2756 2.06%% Mar 02 -0.56%% 0.1856 -0.6936 -0.86% 3.7836 3.6156 4.8156 -1.0136 3.5856 6.06%% 0.1556 0.1456 -6.2136 -2.0436 3.4156 10.00% -BOON +6003 -4008 5-5071 5050 002 2.00% 4.005 6.00% 8.003 10.00% un 02 7.4756 -2.09% 7.7936 -7.94% -4.15% 0.6859 3.2856 -8:00% 0.1456 -7.74%% -0.46%% 20:00% -7.603 6.935 8.805 0.1456 2.6859 S&P 500 [market] 4.9556 -5.70% 5.695 0.1356 4.0056 4.6259 0.1259 -5.11%% Portfolio B Beta - 0.23430 Using Excel slope function -1.50%% -1.59% -18036 0.0175 Intercept Function 1.6856 0.9756 0.0956 0.1056 Annualized Alpha = 6.1156 3.15% 4.7039 5.0456 CAPM: Market vs Portfolio B 1.095% 1.0056 Y= 0.2343x + 0.0175 17.025% 17.10% 2.3756 0.085 1.8756 2.2956 1.6956 0.005% 13.19%% 0.08%% 10:00% 5.565 5.6856 5.585 5.4856 1272% 3.8256 0.0856 1.4056 3.7456 4.505% 0.0856 4 4356 Portfolio 0.0856 1.7856 2.1656 8.2159 40.00% AGON 6.00% 8.00% 10 2.3456 8.295 0.0756 1.3239 2 04 2 2 2 282 2 -9.30%% 1.2756 -15.00% -2.046 8.1256 0. 1056 -4.03% 8.0256 SAP 500 (market) -0.19% 2.86% 0.305% -0 3035 -2.97%6 1.4256 0.1256 0.935 1.305% 5.0856 6.2556 3.2356 0.165 3.2456 3.0756 0.18%6 6 05 1.9356 2.4056 -2.29%% -4.04% 3.8456 -0.06%% 0.2456 3.6054 -0.29% 5.315% 0.2456 -0.09%% 0.4259 5.0756 Jul 05 0.2556 3.4754 0.2756 7.0956 0.2759 1.6956 6.8256 -3.50%% 2.8456 0.2956 -3.293 $ 7936 1.005% 5.9456 0.3056 3.4856 0.705% 5.6459 -4.20%% -0.2836 0.5156 0.055% 1.925% 0.375% 0.6356 5.005% 2.19% volatility 1.4985 13.156 0.3% 13.156 11.52956 19.00256 0.9207 carrel A & B 0.2282 0.1576 Expected Risk Vs Return 30.00% Beta - Using Excel slope function 8.75 lyint - Intercept Function Table of Opportunity Set of Risky Assets Indifference 25,00% Weight A Weight Exp Ru Exp Risk Sharpe 24.64 18.96 1. 18372 15,0095 10% 23.09 24.19 20% 21.55% 10,00% 20.14 18.46% 13.21% 5,0015 (D,J. 1939 Curve 0.00%% 15.38 16.64%% 16.16% 0.00% 2.00% 4.00% 6.00% 8.60% 20.00% 12.60% 14.00% 16.00% 18.00% 20.00% Expected Risk 12.29% 90% 056 10.75% 10.94% 0.78195 100%% 0.60961 16.23% 9.2056 16.78% Optimal risky portfolio is: A = 30% and B = 705% Optimal Complete Portfolio 97.439 2.575% allocation to optimal risky portfolio allocation to cashCase Study Assignment - FNCE 561 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. (e.g. You have been assigned to construct an optimal portfolio comprising two risky assets low beta). Portfolios A & B) while considering your client's risk tolerance. The attached spread sheet shows historical monthly returns of the two portfolios (A&B); S&P 500 index; and 90-day Combining Portfolios, A & B Treasury Bills. Also shown are the annualized returns for each for the period specified. Portfolios A & B are much more volatile than the risk-free rate. You will also find that their correlation is small indicating that there is a diversification benefit to be had from holding Portfolio A: Active Stock Selection Strategy both in a portfolio (The correlation is now shown in the spreadsheet. You will need to Portfolio A is an actively managed US equity strategy that uses publicly available calculate this using the excel function "=correllrange 1, range2)". fundamental, 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 You will be meeting with a client that is looking for investment advice from you based on company's earnings and the strength of its balance sheet. Examples of such factors include these two portfolios. In preparation for your upcoming meeting with the client, your boss cash flow growth, cash flow return on invested capital, debt to equity ratio, price to cash asks that you perform the necessary calculations outlined below and be able to respond to flow, and accruals which assess earnings quality (low quality earnings indicate that the questions that follow. Hint: You will need to determine the correlations and volatilities management may be manipulating earnings by adjusting accruals). Companies with for A & B. favorable fundamental factors tend to outperform those with less favorable factors. Analytical Assignment Portfolio A uses technical and sentiment factors to identify mispriced stocks by exploiting investor behavioral biases. Examples include: momentum and price reversals where Complete the analytical portion of the case assignment in the excel template which can be Investors tend to over-react to good news by bidding up prices ABOVE fair value and over- found in Canvas. Formulas must reference parameters in other cells using absolute or react to bad news by bidding down prices BELOW fair value; short interest on a stock which relative cell references. DO NOT HARD CODE ANY NUMBERS. indicates investor sentiment about a company's future prospects; share buybacks and dividend changes which can indicate a positive signal from management's optimism 1) Plot in Excel the opportunity set for Portfolios A & B. To do this you will need to calculate regarding a firm's future prospects; and earnings surprise. Firms with favorable technical the missing information in the table found in the Excel spreadsheet that accompanies the and sentiment factors also tend to outperform those stocks having less favorable factors. case using weights of portfolio A & B in 10 percentage point increments. To do this you For example, firms whose earnings and revenue exceed analysts' expectations tend to will need to know how to program formulas in Excel using absolute and relative cell continue to outperform vs. those firms that experience earnings surprise due to cost references from the data provided. (The table below already exists in the Excel file). cutting. Weight Port A Weight Port B Return Standard Sharpe Ratio Starting with the market portfolio, the US equity strategy over-weights those stocks having Deviation more favorable fundamental, technical and sentiment factors and under-weights or avoids 0% 100% 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 10 90 20 80 strategy are shown in the attached spreadsheet (Portfolio A). 30 70 40 60 Portfolio B: Global Macro Hedge Fund 50 Portfolio B is a global macro hedge fund that seeks to benefit from mis-pricings within and 50 60 40 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 out- 70 30 perform Japanese equities, the portfolio will be long S&P 500 futures and short TOPIX 20 futures (TOPIX is a Japanese equity index). This long/short trade is not impacted by the 10 100 overall direction of global equities, but rather the relative return between US and Japanese ola equities. Determine the optimal risky portfolio (e.g. the optimal allocation of A & B) using the Similarly for bonds, if the manager believes that interest rates in the United Kingdom (UK) concepts from Modern Portfolio Theory and draw in the Capital Allocation Line (CAL). The will decline more so than interest rates in Australia, the portfolio will be long UK gilt futures approximate optimal allocation can be determined using the table in Excel like the one (gilt is the 10-year UK bond) and short Australian 10-year bond futures. Again, this trade is shown above. not impacted by the overall direction of global interest rates, but rather the relative