3 mins readPortfolio Management and Optimization AssessmentThis is a sample portfolio management and assessment exam used for the Executive MBA version of the course. Enrolled participants Students were given three hours to solve this assessment exam. \u00a0All question are to be solved used the exam data set shared earlier by your class coordinator. You should have a copy of the processed exam data set on your laptop and have a version of MS Excel professional with Solver installed on your computer. \u00a0If you don\u2019t have Solver working correctly on your laptop, you will not be able to complete this exam.Please pay exceedingly careful attention to the definition of the construction period and the evaluation period in each question. The construction period defines the data set used to create and construct your portfolio recommendations using solver.The evaluation period is different from the construction period and is used to independently validate the success or failure of the investment strategy by using a separate dataset from a different period.\u00a0 You may be asked to calculate holding periods return for both data sets and compare them.Question No. 1 Calculating Beta and Alpha with respect to an IndexYou have been given five market based indexes in the dataset.The indexes relate to the Bond market (Bond index), Currency markets (Currency index), Commodities market (commodity index) and NASDAQ and NYSE. Calculate value of Alpha and Beta for all five index using only the dataset from the period starting 1st January 2012 to 31st December 2015.Question No. 2 Calculating holding period returns for a portfolioUsing Excel Solver create two portfolio that meets the following conditions for the bond index. The holding period for the investment portfolio is the same as Question number 1 above.Portfolio 1Full investment \u2013 sum of all portfolio allocations equals 100%.Beta with respect to the Bond Index of the portfolio is as high as possible.Portfolio 2In addition to conditions a) and b) above, ensure that Alpha with respect to the Bond Index of the portfolio is higher than Beta return of the portfolio. Beta return is calculated as the Beta of a given security multiplied by the return of the index in question. Beta return is the value we use in calculating Alpha.Calculate the holding period return for the two portfolios for the investing holding period. Also calculate the holding period return of the portfolio over the evaluation period beginning 1st of January 2016 \u2013 31st October 2016.Compare the holding period returns of the two portfolio and briefly comment on the results?How is the composition of the two portfolios different?Questions No. 3 Alternative investment strategies \u2013 Option AYour private wealth management client would like to create indirect exposure to gold as a commodity. \u00a0However he doesn\u2019t want to invest more than 25% of his portfolio directly in gold given his outlook on gold.An investment advisor has suggested an alternate strategy where he can meet his goal by investing 25% of his portfolio in Gold and the remaining amount in securities that have a high Beta with respect to Gold.i) Create a portfolio using Solver that is invested 25% in Gold and the rest in high Gold Beta securities.ii) Calculate the holding period returns for this portfolio and compare them with the returns for gold as a commodity for both the portfolio construction period (start date \u2013 1st Jan 2010 \u2013 31st December 2014) and the evaluation period (1st Jan 2015 \u2013 30th June 2016).iii) Briefly comment on the results.Questions No. 3 Alternative investment strategies \u2013 Option BYour private wealth management client would like to create indirect exposure to oil (specifically the WTI crude oil blend) as a commodity.\u00a0 However he doesn\u2019t want to invest more than 35% of his portfolio directly in oil.An investment advisor has suggested an alternate strategy where he can meet his goal by investing 35% of his portfolio in oil and the remaining amount in securities that have a high Beta with respect to oil.i) Create a portfolio using Solver that is invested 35% in oil and the rest in high oil Beta securities.ii) Calculate the holding period returns for this portfolio and compare them with the returns for oil as a commodity for both the portfolio construction period (start date \u2013 1st Jan 2010 \u2013 31st December 2014) and the evaluation period (1st March 2015 \u2013 30th September 2016).iii) Briefly comment on the results.