Business Finance Homework Help

The following are the assignmenttaskthat you need to completebefore you write up the report:1.Re–compute return statistics from Assignment #2(Template 1) if they are incorrect.2.Re–run the optimization assignment from Assignment #2if you have to. You should be using monthly input for all data (including the risk–free rate);the expected returnshould bethearithmetic monthly average return for theasset class. For comparison purposes (seebelow), you should retain the results of the following sets of optimized portfolios from Assignment #2: Equal–weighted (naïve) portfolios and optimal portfolios with 2% p.a. risk free rate, i.e., altogether 6portfolios for three time periods. Please denote the equal–weighted portfolios as EQA, EQ1, and EQ2 for the whole period, sub–period 1 and sub–period 2, respectively. Also, denote the optimal portfolios as OPA, OP1, and OP2 for the (unconstrained) optimal portfolio for the whole period, sub–period 1, and sub–period 2, respectively.The historical arithmetic average monthly returns of the asset classes are weighted and used as the target return for thesesix portfolios.Objective: Mean–Variance Optimization as the Basis for Strategic Asset Allocation Canvas submission due date: 1:30pm, March 9, 2021* This assignment is a twoor three–person team effort. Deliverables include a report(pdf) and an EXCEL workbook(xlx or xlxs) containing all working models of optimization for all cases listed below. (See required formats of these two deliverables are at the end of this Instructions.) Submit the two files to Assignment #3on CANVASby 1:30p.m.on March 9, 2021.** Please note bothdeliverables (the report and the EXCELworkbook) must be turned in on time. Missing any deliverable will be considered as not meeting the requirements for this assignment and automatically you’ll receive a zero score. There will be no make–up of any kind for this assignment. Team peer evaluation will be done after the report is submitted. *** Results you have obtained previously from Assignment #2and the new assignmenttasksenumerated below are to be used in the preparation ofthe report.IMPORTANT: You must use ALL of the results you obtain from the tasks in Assignment #2 and #3 to support your answers to the questions posted in this assignment for full credit.

23.Now, run the optimization for the whole period, sub–period 1, and sub–period 2 with 3% p.a. risk–free rate and thesame target portfolio returns (historical) as before, i.e., same target returns asOPA, OP1 and OP2, respectively. (Hint: you don’t change the expected return, but just change the risk–free rate).Label the results as OPA3, OP13, and OP23, respectively.4.Repeat 3) with a risk–free rate of 4% p.a. for the whole period, sub–period 1, and sub–period 2. Label the results as OPA4, OP14, and OP24, respectively.5.Now, run the optimization for the whole period, sub–period 1, and sub–period 2 with 2% p.a. risk–free rate and the same target portfolio returns (historical) as before, i.e., no need to change the portfolio expected return, but with the following investment limits. [Note: You need to put these as constraintsin the Solver dialog box.]Please denote thesenew portfolios as COPA (constrained optimal for whole period), COP1 (constrained optimal for sub–period 1), and COP2 (constrained optimal for sub–period 2). Asset ClassesMinMaxUS Large Cap stocks (S&P 500)––US Small Cap stocks (Russell 2000)––Equity –Developed Markets ex US (MSCI EAFE)––Equity –Emerging Markets (MSCI Emerging Markets)––Commodities (S&P GSCI)––US Real Estate (FTSE NAREIT–Equity)––Cash (Three–Month US Treasury Bills)2%5%US Treasury Bonds–20%US IG Corporate Bonds–20%US High–YieldCorporate Bonds–5%Global High–Yield Bonds–5%Global IG Corporate BondsGlobal Treasury Bonds6.Run the optimization for the three periods (whole period, subperiod 1, and subperiod2) with an expected target monthly return of 0.75% (i.e.,9% p.a.) with a 2% p.a. risk–free rate. [Note: You need to force the expected return of the optimal portfolio to be 0.75% monthly–You put this as a constraint in the Solver dialog box.See the screen shot below.] Please labeltheseoptimal portfolios as OPA9, OP19, and OP29for the whole period, subperiod 1,and subperiod 2, respectively. 7.Run the optimization for the three periods (whole period, subperiod 1, and subperiod2) with an expected target monthly return of 0.75% (i.e.,9% p.a.) with a 2% p.a. risk–free ratewith investment limits given above.Please denote these portfolios as COPA9(constrained optimal whole period with 0.75% target monthly return), COP19(constrained optimal sub–period 1 with 0.75% target return), and COP29(constrained optimal sub–period 2 with 0.75%target monthly return).

38.Using the optimal weights from the constrainedportfoliosfrom sub–period 1(i.e.,optimal weights from COP1, COP19), computethe portfolio return, portfolio risk, and the reward–to–variability ratiofor a portfolio in sub–period 2.Comparetheseresults to those of COP2, and COP29.(Note: This step is known as the out–of–sample testing. The test tries to find out whether using the optimal weights from a previous (historical) period (i.e. ex post optimal weights from sub–period 1) and applying to a future period (i.e.,sub–period 2) give good results. Evaluate this method by comparing the portfolio results obtained from using previous period’s optimal weights to the ex post optimal portfolio for subperiod 2(i.e.,COP2, and COP29).Important: After you have done the above, record the results in the templates(#1–5),and attachit to the Report as Appendix #1.Now,prepareareportassuming you are submitting it to your boss who is the Chief Investment Officer (CIO)of aUniversity Endowment Fund. Your boss wants you to provide research support (results and analysis) so that she can address the following issues/questions/concernsthat theInvestment Policy Committee (IPC) has asked or would probably ask.Thus,the objective is to help the CIO to prepare answers for likelyquestions from the IPC regarding asset allocation for the Fund. The Endowment Fund pays out 4% a year (the spending rate) to the University for operating expenses. Your report must include “adequate discussion based on the empirical results” you have done for the different tasks to address all of the following likely questions. It is up to your group to determine how to write up the report. If you prefer to answer these questions one by one, your grade would not be at the top of the scale.So, be creative in presenting the discussion and results to the CIO, your boss, so that your boss can confidently respond to the questions fielded by the IPC. a)The CIO has sent some of the results you have done above to the IPC. After the members of the IPCperused the results, some of them asked the CIO to explain why the equal–weighted portfolio underperformed the mean–variance optimal portfolio for the periods studied. Explain to the CIOusing only the whole periodresults.(Hint: You may want to analyze the attributes and performance of various asset classes and try to use them to explain the impact on the equal–weighted and optimal portfolios) b)The IPC has noticed that the optimal allocationsof sub–period 1 and sub–period 2are quitedifferent(based on different scenarios of target returns and investment limits). They asked why. Would you please explainto them?c)Recently, the Fed has held the interest ratesteady after lowering it to a very low level. How would an upwardchange in the risk–free rate affect the portfolio performance? Explain to the CIO using your results.d)The CIO wants to propose investment limitson certain asset classesto the IPC for consideration, but the CIO may not be aware of the likely impact on the performance of the Fund. Since you have run some analysis above based on the proposed limits, present youranalysis,and make a recommendation regarding investment limitsforthe historical arithmetic average (target) return and the 9% p.a. target return.(For your reference, see the paper titled “The Impact of Constraints on Minimum–Variance Portfolios” by Chow, Kose

4and Li in Financial Analysts Journal, Volume 72, Number 2, pp. 52–70. You can download this from the library.) e)The mean–variance optimization is known for having high sensitivity to changing target return. The CIO would like to test the sensitivity of the mean–variance optimization toa change in the portfolio target return. Since you have done some runs using the historical arithmetic average return and 9% p.a. target return, present what you’ve learned from your analysis to the CIO using your results.f)Could weuse the optimal weights from a previous period, say sub–period 1 or sub–period 2 or the whole period, as the recommended asset allocation for the future? Explain your answer with the out–of–sample test resultsyou have done. g)Based on the above analyses, what lessons and implications can be learned from your analysis on the mean–variance portfolio optimizationthat you want to share with your boss and the IPC?(IMPORTANT: You must use ALL of the results from your analysis above to support your answers forthese 7questions(a–g)for full credit.)Notes on the Format of the EXCEL Workbook and ReportEXCEL Workbook•Submit only one workbook containing all Excel spreadsheets, templates(#1–#5), and working models, including supporting materials you use in your report•Format all numbers 6 decimal places, 10–pointfont minimum.•Label tabs (using designated labels such as OPA) and spreadsheets•Present each model (i.e., each result, such as OPA, OP1, OP2) in a separate spreadsheet•Working models are the ones that can be re–run with current or new numbers (e.g., risk–free rate)REPORT•The Report must be organized and presented in a professional format.•The Report must be typed with a minimum 12–point font, 1.5spaced, 10pages maximum for the text. Appendix is not included in this pagelimit.•You can present the results in any way to accompany your discussion and analysis in the report. No matter how you present the results, Templates (#1–#5) must be attached in the Appendix(in the format of given templates, 10–pointfontminimum).•Use designated labels to refer to your results, such as OPA, OP1, etc.•Tables, figures, and charts should be put in the Appendix and referenced in the text clearly. Results must be presented in 6 decimal places. The grading criteria are listed in the instructions for Assignment #1.•Any missing or reformatting of templates (#1–5), and/or violating the formatting requirements will carry a penalty of up to 30 points on the Report.