Question;EXERCISE;4 Creating a hyperbola;One;of the salient features of Modern Portfolio Theory (MPT) is the phenomenon of;putting two stocks together such that the resulting portfolio has a lower;standard deviation (lower risk) than either of the component stocks. This is the magic of diversification and this;exercise illustrates the phenomenon graphically.;1) Overview: Given the pps data of two;firms* from your portfolio file of last week you will use Excel to create the;hyperbola that would result from plotting the risk/return profile of a set of;five portfolios, each comprised of the two stocks as the dollar mix goes from;100% of the dollars in stock A and 0% in stock B, to 0% in Stock A and 100% in;stock B.;*Note: For Spring;2014, stock A= GE & stock B=GOOG.;2) You will begin by generating data for these;five portfolios. The data will be risk;and return, where risk is the standard deviation (population) of the daily;returns of the portfolio and returns is the total annual (or 12 month) return.;3) The dollar;mix of the five portfolios that you plot will be;Portfolio;1: 100% stock A and 0% stock B;Portfolio;2: 75% stock A and 25% stock B;Portfolio;3: 50% stock A and 50% stock B;Portfolio;4: 25% stock A and 75% stock B;Portfolio;5: 0% stock A and 100% stock B;4);Start by creating;5 separate portfolios in a new sheet (or ?tab?) [Rename the tab ?hyperbola?] in;your portfolio file with the two firms mentioned above (stockA and;stockB). Each of the five new portfolios;will have the three tiers (pps, mkt value, daily returns) and each portfolio;will have three columns (the total portfolio, stockA, stockB). There will be a total of 15 new columns. See also the screenshot called ?hyperbola;snapshot? available on the Moodle webpage.;5);Populate tier1 of stock A and stock B with the original pps data found in;sheet1. Do this five times, that is;once for each of the five portfolios.;6) Calculate the ?normalized number of shares?;for stockA and stockB using =10000/ average pps. Enter ?number of share? data for both stocks;in each of the five portfolios such that portfolio1 has 100% of the normalized;number of shares for stockA and 0% of the normalized number of shares for;stockB. Portfolio2 has a 75% and 25%;mix, and so on for the other three portfolios.;7) Using the same logic and formulas as in the;main sheet, calculate the Market Values (Tier2) and Daily Returns (Tier3) for;all 5 portfolios in the hyperbola tab.;8) Calculate the Standard Deviation (of the;population using =STDEVP) of the Daily Returns of each of the 5;portfolios. Put these in a row below;tier 3.;9) Calculate the Total Return (top to bottom of;Tier2) for each of the 5 portfolios using (last day-first day)/first day. Put these in a row below the STDEVP.;10) Build and new little block of data (see again;?hyperbola snapshot?) in which you display the std deviations and total returns;of the five portfolios. For each of the;cells you can simply reference the appropriate cell above. For example, use ?=B766? [I?m don?t the;actual row number] to display ?.01857? as calculated in a previous cell.;11) Chart the stdevp and totalK of the 5;portfolios. Use ?scatter with data points connected by smooth lines?.;12) Optional;Modify the two axis so as to exaggerate the curve. Chart Tools>Layout;Axis. That is, by moving the borders;closer to the actual curve, the curve will become more pronounced.
Paper#50174 | Written in 18-Jul-2015Price : $27