Description of this paper

Analyzing Historical Risk vs. Return for a Company.

Description

solution


Question

1.Case Instructions;Analyzing Historical Risk vs. Return for a Company.;Choose a company that you are using in the investment challenge and complete the following steps.;Follow the 6 steps below. Submit your answers on these sheets to the Drop Box;Step 1: Gather the most recent 61 monthly stock prices for the S&P500 and your company using http://finance.yahoo.com.;a. Go to the above website and enter "^GSPC" in the Enter Symbol box in upper left-hand home page of Yahoo! Finance.;b. Click on Historical Prices on the left-hand side.;c. Choose a start date 5 years prior to today's date, change to monthly, and click Get Historical Data to get 61 months of data.;(You need 61 observations to calculate 60 returns.);d. Scroll down to the bottom of the page click on Download Spreadsheet Format and copy (DO NOT CUT) the closing prices and dates;into the highlighted areas of this spreadsheet under the tab 2.Calc. Returns below;Step 2: Repeat this process of step 1 for a company of your choosing.;NOTE: The price series is converted into a return series by calculating Return t = (Pt+1-Pt)/Pt + Divt+1/Pt.;When the information is downloaded using adjusted closing prices the Prices are automatically adjusted to include dividend information.;Therefore it is only necessary to calculate the change in Price divided by the beginning monthly Price to calculate returns.;This is automatically done for you in the spreadsheet tab 2.Calc. Returns, by copying the adjusting monthly closing prices to cell D2:D62.;Make sure you have 61 monthly observations with the same starting month for your company and S&P 500 data.;Step 3: Interpretation of Line Graph. (Graph is automatically created hit tab Line Graph at bottom of spreadsheet.);Refer to the Line Graph spreadsheet to answer the following questions. (Note place cursor on a point to get values.);The returns for your company and the S&P 500 are shown in reverse chronological order (most recent is first).;Helpful Hint: Placing the cursor on the point in the line graph will display the actual numerical input values for that point.;a. What is the most recent month in which the company return moves in the opposite dirrection of the S&P 500 return?;(i.e. the monthly return is going up and the market return is going down, or v.v.);b. In what month was the largest positive monthly return for the company in the past five years?;c. What was the largest positive monthly return?;d. In what month was the largest negative monthly return for the company in the past five years?;e. What was the largest negative monthly return?;f. Based on the line Graph 1 would you say that your company returns and S&P 500 returns ________.;1) always move in opposite directions (i.e. when one is positive the other will be negative).;2) seldom move in the same direction.;3) sometimes move in the same direction.;4) often move in the same direction.;5) always move in the same direction.;Step 4: Explanation of Statistical Calculations;Using the Calculating Statistic spreadsheet and your text book write the formulas and describe the calculations for the following cells;Cell I68 is shown as an example.;I68 a. Expected Monthly Return (E(R)) for the company;= C66/60, where C66 is the sum of monthly returns, therefore this is the average monthly return for company;I69 b. Variance for the company;I70 c. Standard Deviation for the company;F68 d. Expected Return (E(R)) for the market index;F69 e. Variance for the market index;F70 f. Standard Deviation for the market index;Step 5: Compare the calculated Beta with the reported Beta on Yahoo! Finance.;Covariance is a statistical measurement that caculates how two series move in relationship to each other.;The calculations for covariance are shown in collumn H of the 5.Calc Beta spreadsheet.;Answer the following questions referring to the Calculate Beta spreadsheet.;a. What is the most recent month for which the company return and market return were less than their expected returns?;(i.e. both had negative amounts in collumn E and collumn G.);b. Was the covariance for this month positive or negative?;c. What is the most recent month for which the company return and market return;were greater than their expected returns?;d. Was the covariance for this month positive or negative?;e. What is the most recent month for which the company return and market return;moved in opposite directions than their expected returns?;f. Was the covariance for this month positive or negative?;g. Look up your company's Beta on Yahoo!Finance by doing the following steps;1. Enter the ticker symbol under get quotes.;2. Click on Key Statistics on the left-hand side;3. Beta is on the right-hand side of the screen, one of the first statistical measures reported.;Don't be surprised if your Beta is different than the beta reported on yahoo finance.;Regression Instructions;Select Data and then Data Analysis from top options of Excel. (See instructions on Regression tab if Data Analysis option is not there).;Select Regression.;Input the Company Returns (collumn C of 6.Regression spreadsheet for Y variable);Input the Market Risk Premium (collumn B of 6.Regression spreadsheet for X var.);Select OK and Regression Output should be created on a new page.;Print out the output that was generated and answer the following questions on the output page;NOTE: There is an Example of MLHR Regression Output on Tab below.;1. On the output page identify and circle the following;a. y-intercept;b. measurement of how well regression model estimates company returns;c. How well does this regression model fit the data?;2. Write the regression equation from the regression output.;After completing the assignment place in dropbox for course.;2.Calc. Returns;month;Date;S&P 500 MLHR Mkt Returns Co. Returns;Input Worksheet;1 6/1/2012 1324.18;17.06 0.0105698564 -0.07683983 In the highlighted collumn D copy a series of closing monthly;2 5/1/2012 1310.33;18.48 -0.062650671 -0.05279344 prices for your company. This price series in collumn D is then;3 4/2/2012 1397.91;19.51 -0.007497497 -0.14914959 converted to a return series in collumn F.;4 3/1/2012 1408.47;22.93 0.0313323765 0.092945663;5 2/1/2012 1365.68;20.98 0.0405894499 -0.00332542 NOTE: You will copy over MLHR data and the S&P 500 series is;6 1/3/2012 1312.41;21.05 0.0435830153 0.143400326 already done for you.;7 12/1/2011;1257.6;18.41 0.008532752 -0.14531105;8 11/1/2011 1246.96;21.54 -0.005058645 0.04664723;9 10/3/2011;1253.3;20.58 0.1077230383 0.156179775;10 9/1/2011 1131.42;17.8 -0.071762013 -0.101010101;11 8/1/2011 1218.89;19.8 -0.056791098 -0.13574858;12 7/1/2011 1292.28;22.91 -0.021474437 -0.15461255;13 6/1/2011 1320.64;27.1 -0.018257508 0.084433774;14 5/2/2011;1345.2;24.99 -0.013500928 -0.03438949;15 4/1/2011 1363.61;25.88 0.0284953576 -0.05340161;16 3/1/2011 1325.83;27.34 -0.001047302 0.020149254;17 2/1/2011 1327.22;26.8 0.0319565826 0.117597998;18 1/3/2011 1286.12;23.98 0.02264559 -0.04614161;19 12/1/2010 1257.64;25.14 0.065300072 0.174217655;20 11/1/2010 1180.55;21.41 -0.002290283 0.121529597;21 10/1/2010 1183.26;19.09 0.0368559411 -0.02302968;22 9/1/2010;1141.2;19.54 0.087551104 0.200983405;23 8/2/2010 1049.33;16.27 -0.047449165 -0.0457478;24 7/1/2010;1101.6;17.05 0.0687778328 -0.08872261;25 6/1/2010 1030.71;18.71 -0.053882377 -0.01887782;26 5/3/2010 1089.41;19.07 -0.081975916 -0.09103908;27 4/1/2010 1186.69;20.98 0.0147593272 0.172722191;28 3/1/2010 1169.43;17.89 0.058796368 -0.00721421;29 2/1/2010 1104.49;18.02 0.0285136935 0.07839617;30 1/4/2010 1073.87;16.71 -0.036974262 0.056257901;31 12/1/2009;1115.1;15.82 0.017770598 0.053262317;32 11/2/2009 1095.63;15.02 0.057363997 -0.01572739;33 10/1/2009 1036.19;15.26 -0.019761986 -0.08622754;34 9/1/2009 1057.08;16.7 0.0357233838 0.042446941;35 8/3/2009 1020.62;16.02 0.0335601734 -0.02197802;36 7/1/2009;987.48;16.38 0.074141757 0.082617317;37 6/1/2009;919.32;15.13 0.0001958352 0.077635328;38 5/1/2009;919.14;14.04 0.0530814267 -0.04098361;39 4/1/2009;872.81;14.64 0.093925076 0.394285714;40 3/2/2009;797.87;10.5 0.0854045083 0.057401813;41 2/2/2009;735.09;9.93 -0.109931225 -0.07455732;42 1/2/2009;825.88;10.73 -0.085657348 -0.15710919;43 12/1/2008;903.25;12.73 0.007821566 -0.11412665;44 11/3/2008;896.24;14.37 -0.074849032 -0.32693208;45 10/1/2008;968.75;21.35 -0.169424534 -0.10067397;46 9/2/2008 1166.36;23.74 -0.090791453 -0.13040293;47 8/1/2008 1282.83;27.3 0.0121905032 0.079905063;48 7/1/2008 1267.38;25.28 -0.009859375 0.050270046;49 6/2/2008;1280;24.07 -0.085962382 0.003334723;50 5/1/2008 1400.38;23.99 0.0106741532 0.067170819;51 4/1/2008 1385.59;22.48 0.0475466848 -0.05067568;52 3/3/2008;1322.7;23.68 -0.005959583 -0.17634783;53 2/1/2008 1330.63;28.75 -0.034761162 -0.05861166;54 1/2/2008 1378.55;30.54 -0.061163475 -0.01895278;55 12/3/2007 1468.36;31.13 -0.008628489 0.180060652;56 11/1/2007 1481.14;26.38 -0.044043424 0.012279355;57 10/1/2007 1549.38;26.06 0.014822335 0.002693344;58 9/4/2007 1526.75;25.99 0.0357940013 -0.0647715;59 8/1/2007 1473.99;27.79 0.0128635923 -0.04632807;60 7/3/2007 1455.27;29.14 -0.031981907 -0.03381963;61 6/3/2007 1503.35;30.16;-0.1;-0.2;-0.3;-0.4;8/1/2007;10/1/2007;12/3/2007;2/1/2008;4/1/2008;6/2/2008;8/1/2008;10/1/2008;12/1/2008;2/2/2009;4/1/2009;6/1/2009;8/3/2009;10/1/2009;12/1/2009;2/1/2010;4/1/2010;6/1/2010;8/2/2010;10/1/2010;12/1/2010;2/1/2011;4/1/2011;6/1/2011;8/1/2011;10/3/2011;12/1/2011;2/1/2012;4/2/2012;6/1/2012;Company vs. S&P 500 over t ime;0.5;0.4;0.3;0.2;0.1;Mkt Returns;Co. Returns;0;4.Calc. Stats.;This spreadsheet calculates the Expected Returns, Variances, and Standard Deviations of 2 Return Series.;Page down to bottom for results;Date;Jun-12;May-12;Apr-12;Mar-12;Feb-12;Jan-12;Dec-11;Nov-11;Oct-11;Sep-11;Aug-11;Jul-11;Jun-11;May-11;Apr-11;Mar-11;Feb-11;Jan-11;Dec-10;Nov-10;Oct-10;Sep-10;Aug-10;Jul-10;Jun-10;May-10;Apr-10;Mar-10;Feb-10;Jan-10;Dec-09;Nov-09;Oct-09;Sep-09;Aug-09;Jul-09;Jun-09;May-09;Apr-09;Mar-09;Feb-09;Jan-09;Dec-08;Nov-08;Oct-08;Sep-08;Aug-08;Jul-08;Jun-08;May-08;Apr-08;Mar-08;Feb-08;Jan-08;Dec-07;Nov-07;Oct-07;Sep-07;Aug-07;Jul-07;Sums;Market;Returns;0.01057;-0.06265;-0.00750;0.03133;0.04059;0.04358;0.00853;-0.00506;0.10772;-0.07176;-0.05679;-0.02147;-0.01826;-0.01350;0.02850;-0.00105;0.03196;0.02265;0.06530;-0.00229;0.03686;0.08755;-0.04745;0.06878;-0.05388;-0.08198;0.01476;0.05880;0.02851;-0.03697;0.01777;0.05736;-0.01976;0.03572;0.03356;0.07414;0.00020;0.05308;0.09393;0.08540;-0.10993;-0.08566;0.00782;-0.07485;-0.16942;-0.09079;0.01219;-0.00986;-0.08596;0.01067;0.04755;-0.00596;-0.03476;-0.06116;-0.00863;-0.04404;0.01482;0.03579;0.01286;-0.03198;-0.03453;E(R);Variance;Standard Deviation;Company;Returns;-0.07684;-0.05279;-0.14915;0.09295;-0.00333;0.14340;-0.14531;0.04665;0.15618;-0.10101;-0.13575;-0.15461;0.08443;-0.03439;-0.05340;0.02015;0.11760;-0.04614;0.17422;0.12153;-0.02303;0.20098;-0.04575;-0.08872;-0.01888;-0.09104;0.17272;-0.00721;0.07840;0.05626;0.05326;-0.01573;-0.08623;0.04245;-0.02198;0.08262;0.07764;-0.04098

 

Paper#28309 | Written in 18-Jul-2015

Price : $42
SiteLock