Refer to Step 3.3(page 3 in the attached file) In the "Unconstrained" or "Short Selling" version of the optimal risky portfolio, what is the weight for XOM?Write your answer as a percentage, with no percentage symbol ("%"), rounded to the nearest tenth percentage point (e.g., you would write "48.1234%" as "48.1", not "0.481234").Hint: You can use the same logic and Solver built from Step 3.2.
Question A) Refer to Step 3.3. In the "Unconstrained" or "Short Selling" version of the optimal risky portfolio, what is the weight for XOM? Write your answer as a percentage, with no percentage symbol ("%"), rounded to the nearest tenth percentage point (e.g., you would write "48.1234%" as "48.1", not "0.481234"). Hint: You can use the same logic and Solver built from Step 3.2. B) Refer to Step 3.3. In the "Constrained" or "Long Only" version of the optimal risky portfolio, what is the weight for GOOG? Write your answer as a percentage, with no percentage symbol ("%"), rounded to the nearest tenth percentage point (e.g., you would write "48.1234%" as "48.1", not "0.481234"). Hint: You can use the same logic and Solver built from Step 3.2. C) Refer to Step 3.3. In the "Unconstrained" or "Short Selling" version of the optimal risky portfolio, what is the portfolio mean? Write your answer as a percentage, with no percentage symbol ("%"), rounded to the nearest hundredth percentage point (e.g., you would write "48.1234%" as "48.12", not "0.481234"). D) Refer to Step 3.3. In the "Constrained" or "Long Only" version of the optimal risky portfolio, what is the portfolio standard deviation? Write your answer as a percentage, with no percentage symbol ("%"), rounded to the nearest hundredth percentage point (e.g., you would write "48.1234%" as "48.12", not "0.481234"). E) Refer to Step 3.3. In the "Unconstrained" or "Short Selling" version of the optimal risky portfolio, what is the portfolio Sharpe Ratio? Write your answer as a number rounded to the nearest thousandth percentage point (e.g., you would write "0.073214" as "0.073"). Description What follows is a 5-Step description of the Project Prompt. Each week, or "Module", you are to complete one or more of the steps before submitting your final project. Note, these Steps cover the entire Capstone. Each of the Steps below has a Quiz in the associated Module that asks about the figures you are calculating and other characteristics of the portfolios at issue. To pass the course, in addition to receiving a passing score on the final project, you must receive a score of 100% on the Quiz in Module 1, 60% on the Quizzes in Module 2 (though you are encouraged to work on this Module until you are confident you have correct figures), and 80% on the Quizzes in Modules 3 and 5; the Quiz in Module 4 is formative, meaning it does not count for your final grade. Step 1. Go to the "Historical Stock Data" link in Module 1 - and download the twelve (12) files there. Those files are: (i) historical daily prices for ten individual stocks (AAPL, MSFT, WFC, DIS, COP, XOM, GOOG, BIDU, TSLA, and TTM) and for the Dow Jones market index, DJI (sample window: June 30th 2010 – June 30th 2016); and (ii) monthly prices for AAPL (sample window: August 2015 - July 2016) If you'd like more background on the Dow Jones index, go here. Step 2. Using the historical data downloaded: 2.1 Calculate the daily return for each security and DJI using both the “Close price” and “Adjusted Close” price. Compare the difference between these two return series and think about what causes the difference (if there’s any). (You will be provided with a sample return to verify whether your calculations are correct.) 2.2 Using the results you get from Step 2.1, calculate the following summary statistics of the return series for the “Adjusted Close” prices: 1. mean 2. standard deviation 3. min max 4. Sharpe Ratio* https://www.djindexes.com/averages/ *The Sharpe Ratio is a widely used tool for measuring the riskiness of investments. For more information on this concept, see the materials presented with Module 2. Assume the “risk free asset” rate = 0. In the assessment for this week, you’ll be asked to verify some of your calculations and answer questions based on a comparison of the summary statistics of the index DJI against the individual stocks. Step 3. Now imagine you are a portfolio manager at an equity quant fund with the option to invest your portfolio in the stocks listed above. Using your knowledge of portfolio variance and the efficient frontier (see Module 3 for more information on these concepts), complete the following: 3.1 If you are only allowed to invest in two stocks, MSFT and WFC, how would you allocate your investment to arrive at a portfolio with the minimum variance (as well as the lowest standard deviation)? You are not allowed to hold cash, so 100% of your portfolio has to be devoted to these two stocks. First, calculate the approximate weights. Hint: Use a 5% increment in weights (e.g. 0%, 5%, …, 95%, 100%) in each stock, calculate the corresponding portfolio return and standard deviation. Plot the efficient frontier using the portfolio characteristics for each weight. How much weight, approximated to the nearest whole percentage, would you assign to MSFT and WFC respectively to achieve the lowest portfolio variance? 3.2 Using the Solver function in Excel, next calculate the exact weight in WFC and MSFT for the minimum variance portfolio, rounded to the nearest tenth decimal point. Verify your answer with the approximation you obtained from Step 3.1 - they should be close. Hint: You can assign your initial portfolio as an equal weighted portfolio before using the Solver function in Excel. Next, using the same technique, please work out the weights for the “optimal risky portfolio” on the efficient frontier consisting of WFC and MSFT. The “optimal risky portfolio” is also known as the “tangent portfolio” because it is where the Capital Market Line meets the efficient frontier (the tangency point). See Module 3 for more information on these concepts. Hint: Your goal now is to find the maximum value of the Sharpe Ratio of the portfolio. For simplicity, always assume the risk free rate = 0. In the assessment for this week, you’ll be asked to compare your "optimal risky portfolio" characteristics to those of the two individual stocks used in the portfolio. 3.3 After your client reviews your proposal from the exercise involving two stocks, she really likes the idea of risk diversification. As a portfolio manager, you are given the right to invest in all 10 of the securities listed in the pool (note, this does not include DJI). https://en.wikipedia.org/wiki/Capital_market_line Can you work out your “optimal risky portfolio” (a.k.a. tangent portfolio) on the efficient frontier using all 10 securities? Hint: You can use the same logic and Solver built from Step 3.2. Calculate your final portfolio weights in each stock and the portfolio characteristics (mean, std dev, and sharpe ratio). You should find that some of your weights come out negative. This means that the optimal risky portfolio contemplates “short selling.” See Module 3 for more information on short selling (if your minimum weight = 0 instead of being negative, please make sure to uncheck the “Make Unconstrained Variables Non-Negative” option in the Solver Parameters window). What if your mandate does not allow you to participate in short selling activity? In other words, the lowest weight you can impose on the stocks in your portfolio is zero. What is your portfolio composition under this ‘constrained’ or ‘long-only’ regime? Hint: You can repeat the same exercise as in the short selling case. You only need to check “Make Unconstrained Variables Non-Negative” this time. tep 4. For this optional, non-credit exercise, you will be working with the Capital Asset Pricing Model (CAPM). See Module 4 for more on this concept. Using Excel, calculate the Alpha and Beta loadings of the 10 securities on the market index DJI. Hint: You can use the regression function in Excel under the “Data Analysis” add-on. In the optional assessment for this Step, you’ll be asked to use the CAPM model results to determine which stock is the riskiest investment and which one is the least risky. Step 5. You have just applied the knowledge of efficient frontier and portfolio optimization in managing a pure equity portfolio in Steps 3 and 4. In real life, many investment strategies utilize other assets or a mix of several, including fixed income, money market funds, commodities, foreign currencies, and even real estates. For instance, among the largest 25 mutual funds, close to half invest in assets other than equity. Using the same set of knowledge on portfolio diversification, you are now required to plan for an investment portfolio of $5 million. Your mandate allows you to invest in the following investment vehicles: Vanguard Total Bond Market Index Fund (ticker: VBTLX) and Vanguard 500 Index (ticker: VFIAX). These two funds represent investment vehicles in fixed income and equity, respectively. Your data set for this part of the project includes the monthly prices for AAPL provided in the "Historical Stock Data" link in Module 1, as well as the historical monthly returns data for VBTLX and VFIAX from January 2012 to July 2016. http://www.marketwatch.com/tools/mutual-fund/top25largest http://www.marketwatch.com/tools/mutual-fund/top25largest You are encouraged to practice retrieving and inputting the VBTLX and VFIAX returns data by: a. Going to MorningStar’s website: http://www.morningstar.com/ b. Searching in the “Quote” box for fund performances using the ticker given, then click on the “Performance” tab to find the monthly total returns at the bottom of the page. c. Copying historical monthly returns data from January 2012 to July 2016 in a spreadsheet (NOTE: The returns are percentages but are presented without a percentage sign, so make sure that when entering them into your spreadsheet they are treated as percentages). If you'd prefer to skip this step, the monthly returns can be downloaded here. 5.1 Your goal is to again design