Details of this Paper

Finance Class Assignment

Description

solution


Question

Question;1.Case InstructionsAnalyzing 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 BoxStep 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 datesinto the highlighted areas of this spreadsheet under the tab 2.Calc. Returns belowStep 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 CalculationsUsing 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 companyI69 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 returnwere 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 returnmoved 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 side3. 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 InstructionsSelect 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 pageNOTE: There is an Example of MLHR Regression Output on Tab below.1. On the output page identify and circle the followinga. y-interceptb. measurement of how well regression model estimates company returnsc. 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. ReturnsmonthDateS&P 500 MLHR Mkt Returns Co. ReturnsInput Worksheet1 6/1/2012 1324.1817.06 0.0105698564 -0.07683983 In the highlighted collumn D copy a series of closing monthly2 5/1/2012 1310.3318.48 -0.062650671 -0.05279344 prices for your company. This price series in collumn D is then3 4/2/2012 1397.9119.51 -0.007497497 -0.14914959 converted to a return series in collumn F.4 3/1/2012 1408.4722.93 0.0313323765 0.0929456635 2/1/2012 1365.6820.98 0.0405894499 -0.00332542 NOTE: You will copy over MLHR data and the S&P 500 series is6 1/3/2012 1312.4121.05 0.0435830153 0.143400326 already done for you.7 12/1/20111257.618.41 0.008532752 -0.145311058 11/1/2011 1246.9621.54 -0.005058645 0.046647239 10/3/20111253.320.58 0.1077230383 0.15617977510 9/1/2011 1131.4217.8 -0.071762013 -0.10101010111 8/1/2011 1218.8919.8 -0.056791098 -0.1357485812 7/1/2011 1292.2822.91 -0.021474437 -0.1546125513 6/1/2011 1320.6427.1 -0.018257508 0.08443377414 5/2/20111345.224.99 -0.013500928 -0.0343894915 4/1/2011 1363.6125.88 0.0284953576 -0.0534016116 3/1/2011 1325.8327.34 -0.001047302 0.02014925417 2/1/2011 1327.2226.8 0.0319565826 0.11759799818 1/3/2011 1286.1223.98 0.02264559 -0.0461416119 12/1/2010 1257.6425.14 0.065300072 0.17421765520 11/1/2010 1180.5521.41 -0.002290283 0.12152959721 10/1/2010 1183.2619.09 0.0368559411 -0.0230296822 9/1/20101141.219.54 0.087551104 0.20098340523 8/2/2010 1049.3316.27 -0.047449165 -0.045747824 7/1/20101101.617.05 0.0687778328 -0.0887226125 6/1/2010 1030.7118.71 -0.053882377 -0.0188778226 5/3/2010 1089.4119.07 -0.081975916 -0.0910390827 4/1/2010 1186.6920.98 0.0147593272 0.17272219128 3/1/2010 1169.4317.89 0.058796368 -0.0072142129 2/1/2010 1104.4918.02 0.0285136935 0.0783961730 1/4/2010 1073.8716.71 -0.036974262 0.05625790131 12/1/20091115.115.82 0.017770598 0.05326231732 11/2/2009 1095.6315.02 0.057363997 -0.0157273933 10/1/2009 1036.1915.26 -0.019761986 -0.0862275434 9/1/2009 1057.0816.7 0.0357233838 0.04244694135 8/3/2009 1020.6216.02 0.0335601734 -0.0219780236 7/1/2009987.4816.38 0.074141757 0.08261731737 6/1/2009919.3215.13 0.0001958352 0.07763532838 5/1/2009919.1414.04 0.0530814267 -0.0409836139 4/1/2009872.8114.64 0.093925076 0.39428571440 3/2/2009797.8710.5 0.0854045083 0.05740181341 2/2/2009735.099.93 -0.109931225 -0.0745573242 1/2/2009825.8810.73 -0.085657348 -0.1571091943 12/1/2008903.2512.73 0.007821566 -0.1141266544 11/3/2008896.2414.37 -0.074849032 -0.3269320845 10/1/2008968.7521.35 -0.169424534 -0.1006739746 9/2/2008 1166.3623.74 -0.090791453 -0.1304029347 8/1/2008 1282.8327.3 0.0121905032 0.07990506348 7/1/2008 1267.3825.28 -0.009859375 0.05027004649 6/2/2008128024.07 -0.085962382 0.00333472350 5/1/2008 1400.3823.99 0.0106741532 0.06717081951 4/1/2008 1385.5922.48 0.0475466848 -0.0506756852 3/3/20081322.723.68 -0.005959583 -0.1763478353 2/1/2008 1330.6328.75 -0.034761162 -0.0586116654 1/2/2008 1378.5530.54 -0.061163475 -0.0189527855 12/3/2007 1468.3631.13 -0.008628489 0.18006065256 11/1/2007 1481.1426.38 -0.044043424 0.01227935557 10/1/2007 1549.3826.06 0.014822335 0.00269334458 9/4/2007 1526.7525.99 0.0357940013 -0.064771559 8/1/2007 1473.9927.79 0.0128635923 -0.0463280760 7/3/2007 1455.2729.14 -0.031981907 -0.0338196361 6/3/2007 1503.3530.16-0.1-0.2-0.3-0.48/1/200710/1/200712/3/20072/1/20084/1/20086/2/20088/1/200810/1/200812/1/20082/2/20094/1/20096/1/20098/3/200910/1/200912/1/20092/1/20104/1/20106/1/20108/2/201010/1/201012/1/20102/1/20114/1/20116/1/20118/1/201110/3/201112/1/20112/1/20124/2/20126/1/2012Company vs. S&P 500 over t ime0.50.40.30.20.1Mkt ReturnsCo. Returns04.Calc. Stats.This spreadsheet calculates the Expected Returns, Variances, and Standard Deviations of 2 Return Series.Page down to bottom for resultsDateJun-12May-12Apr-12Mar-12Feb-12Jan-12Dec-11Nov-11Oct-11Sep-11Aug-11Jul-11Jun-11May-11Apr-11Mar-11Feb-11Jan-11Dec-10Nov-10Oct-10Sep-10Aug-10Jul-10Jun-10May-10Apr-10Mar-10Feb-10Jan-10Dec-09Nov-09Oct-09Sep-09Aug-09Jul-09Jun-09May-09Apr-09Mar-09Feb-09Jan-09Dec-08Nov-08Oct-08Sep-08Aug-08Jul-08Jun-08May-08Apr-08Mar-08Feb-08Jan-08Dec-07Nov-07Oct-07Sep-07Aug-07Jul-07Sums:MarketReturns0.01057-0.06265-0.007500.031330.040590.043580.00853-0.005060.10772-0.07176-0.05679-0.02147-0.01826-0.013500.02850-0.001050.031960.022650.06530-0.002290.036860.08755-0.047450.06878-0.05388-0.081980.014760.058800.02851-0.036970.017770.05736-0.019760.035720.033560.074140.000200.053080.093930.08540-0.10993-0.085660.00782-0.07485-0.16942-0.090790.01219-0.00986-0.085960.010670.04755-0.00596-0.03476-0.06116-0.00863-0.044040.014820.035790.01286-0.03198-0.03453E(R):Variance:Standard Deviation:CompanyReturns-0.07684-0.05279-0.149150.09295-0.003330.14340-0.145310.046650.15618-0.10101-0.13575-0.154610.08443-0.03439-0.053400.020150.11760-0.046140.174220.12153-0.023030.20098-0.04575-0.08872-0.01888-0.091040.17272-0.007210.078400.056260.05326-0.01573-0.086230.04245-0.021980.082620.07764-0.040980.394290.05740-0.07456-0.15711-0.11413-0.32693-0.10067-0.130400.079910.050270.003330.06717-0.05068-0.17635-0.05861-0.018950.180060.012280.00269-0.06477-0.04633-0.03382-0.17676Distancefrom Mean Variance(M)(RM - E(RM)) (RM - E(RM))20.011150.00012-0.062080.00385-0.006920.000050.031910.001020.041160.001690.044160.001950.009110.00008-0.004480.000020.108300.01173-0.071190.00507-0.056220.00316-0.020900.00044-0.017680.00031-0.012930.000170.029070.00085-0.000470.000000.032530.001060.023220.000540.065880.00434-0.001710.000000.037430.001400.088130.00777-0.046870.002200.069350.00481-0.053310.00284-0.081400.006630.015330.000240.059370.003530.029090.00085-0.036400.001320.018350.000340.057940.00336-0.019190.000370.036300.001320.034140.001170.074720.005580.000770.000000.053660.002880.094500.008930.085980.00739-0.109360.01196-0.085080.007240.008400.00007-0.074270.00552-0.168850.02851-0.090220.008140.012770.00016-0.009280.00009-0.085390.007290.011250.000130.048120.00232-0.005380.00003-0.034190.00117-0.060590.00367-0.008050.00006-0.043470.001890.015400.000240.036370.001320.013440.00018-0.031410.000990.18032Market-0.000580.003010.05482Variance(C)(RC - E(RC)) (RC - E(RC))2-0.073890.00546-0.049850.00248-0.146200.021380.095890.00920-0.000380.000000.146350.02142-0.142370.020270.049590.002460.159130.02532-0.098060.00962-0.132800.01764-0.151670.023000.087380.00764-0.031440.00099-0.050460.002550.023100.000530.120540.01453-0.043200.001870.177160.031390.124480.01549-0.020080.000400.203930.04159-0.042800.00183-0.085780.00736-0.015930.00025-0.088090.007760.175670.03086-0.004270.000020.081340.006620.059200.003510.056210.00316-0.012780.00016-0.083280.006940.045390.00206-0.019030.000360.085560.007320.080580.00649-0.038040.001450.397230.157790.060350.00364-0.071610.00513-0.154160.02377-0.111180.01236-0.323990.10497-0.097730.00955-0.127460.016250.082850.006860.053220.002830.006280.000040.070120.00492-0.047730.00228-0.173400.03007-0.055670.00310-0.016010.000260.183010.033490.015230.000230.005640.00003-0.061830.00382-0.043380.00188-0.030870.000950.78557Company-0.002950.013090.114425.Calc. BetaThis spreadsheet calculates the Covariance and Beta of a Company Return Series with a Market Series.Page down to bottom for resultsDateJun-12May-12Apr-12Mar-12Feb-12Jan-12Dec-11Nov-11Oct-11Sep-11Aug-11Jul-11Jun-11May-11Apr-11Mar-11Feb-11Jan-11Dec-10Nov-10Oct-10Sep-10Aug-10Jul-10Jun-10May-10Apr-10Mar-10Feb-10Jan-10Dec-09Nov-09Oct-09Sep-09Aug-09Jul-09Jun-09May-09Apr-09Mar-09Feb-09Jan-09Dec-08Nov-08Oct-08Sep-08Aug-08Jul-08Jun-08May-08Apr-08Mar-08Feb-08Jan-08Dec-07Nov-07Oct-07Sep-07Aug-07Jul-07Sums:MarketReturns0.01057-0.06265-0.007500.031330.040590.043580.00853-0.005060.10772-0.07176-0.05679-0.02147-0.01826-0.013500.02850-0.001050.031960.022650.06530-0.002290.036860.08755-0.047450.06878-0.05388-0.081980.014760.058800.02851-0.036970.017770.05736-0.019760.035720.033560.074140.000200.053080.093930.08540-0.10993-0.085660.00782-0.07485-0.16942-0.090790.01219-0.00986-0.085960.010670.04755-0.00596-0.03476-0.06116-0.00863-0.044040.014820.035790.01286-0.03198-0.03453CompanyReturns-0.07684-0.05279-0.149150.09295-0.003330.14340-0.145310.046650.15618-0.10101-0.13575-0.154610.08443-0.03439-0.053400.020150.11760-0.046140.174220.12153-0.023030.20098-0.04575-0.08872-0.01888-0.091040.17272-0.007210.078400.056260.05326-0.01573-0.086230.04245-0.021980.082620.07764-0.040980.394290.05740-0.07456-0.15711-0.11413-0.32693-0.10067-0.130400.079910.050270.003330.06717-0.05068-0.17635-0.05861-0.018950.180060.012280.00269-0.06477-0.04633-0.03382-0.17676E(R):Variance and Covariance:Standard Deviation:Distancefrom Mean(RM - E(RM))0.01115-0.06208-0.006920.031910.041160.044160.00911-0.004480.10830-0.07119-0.05622-0.02090-0.01768-0.012930.02907-0.000470.032530.023220.06588-0.001710.037430.08813-0.046870.06935-0.05331-0.081400.015330.059370.02909-0.036400.018350.05794-0.019190.036300.034140.074720.000770.053660.094500.08598-0.10936-0.085080.00840-0.07427-0.16885-0.090220.01277-0.00928-0.085390.011250.04812-0.00538-0.03419-0.06059-0.00805-0.043470.015400.036370.01344-0.03141Distancefrom Mean(RC - E(RC))-0.07389-0.04985-0.146200.09589-0.000380.14635-0.142370.049590.15913-0.09806-0.13280-0.151670.08738-0.03144-0.050460.023100.12054-0.043200.177160.12448-0.020080.20393-0.04280-0.08578-0.01593-0.088090.17567-0.004270.081340.059200.05621-0.01278-0.083280.04539-0.019030.085560.08058-0.038040.397230.06035-0.07161-0.15416-0.11118-0.32399-0.09773-0.127460.082850.053220.006280.07012-0.04773-0.17340-0.05567-0.016010.183010.015230.00564-0.06183-0.04338-0.03087Market-0.000580.003010.05482Company-0.002950.013090.11442Calculating Beta: Beta = Cov(C,M)/Var(M) =Covariance(M,C)(RM - E(RM))((RC - E(RC))-0.000820.003090.001010.00306-0.000020.00646-0.00130-0.000220.017230.006980.007470.00317-0.001550.00041-0.00147-0.000010.00392-0.001000.01167-0.00021-0.000750.017970.00201-0.005950.000850.007170.00269-0.000250.00237-0.002150.00103-0.000740.001600.00165-0.000650.006390.00006-0.002040.037540.005190.007830.01312-0.000930.024060.016500.011500.00106-0.00049-0.000540.00079-0.002300.000930.001900.00097-0.00147-0.000660.00009-0.00225-0.000580.000970.20235Market & Company0.003371.12Note: Beta measures the comovement of the company returns with the market.Mkt Returns Mkt Premium Co. Returns0.0105698564 0.0068998564 -0.076839827-0.062650671 -0.066320671 -0.052793439-0.007497497 -0.0111674973 -0.14914958570.0313323765 0.0276623765 0.09294566250.0405894499 0.0369194499 -0.0033254160.0435830153 0.0399130153 0.14340032590.0085327517 0.0048627517 -0.1453110492-0.005058645 -0.008728645 0.04664723030.1077230383 0.1040530383 0.1561797753-0.071762013 -0.075432013 -0.101010101-0.056791098 -0.060461098 -0.1357485814-0.021474437 -0.025144437 -0.1546125461-0.018257508 -0.021927508 0.0844337735-0.013500928 -0.017170928 -0.034389490.0284953576 0.0248253576 -0.0534016094-0.0010473019 -0.0047173019 0.02014925370.0319565826 0.0282865826 0.11759799830.0226455902 0.0189755902 -0.0461416070.065300072 0.061630072 0.1742176553-0.002290283 -0.005960283 0.12152959660.0368559411 0.0331859411 -0.0230296830.087551104 0.083881104 0.200983405-0.047449165 -0.0511191649 -0.0457478010.0687778328 0.0651078328 -0.088722608-0.053882377 -0.057552377 -0.018877819-0.081975916 -0.085645916 -0.09103908480.0147593272 0.0110893272 0.17272219120.0587963676 0.0551263676 -0.0072142060.0285136935 0.02484369350.07839617-0.036974262 -0.040644262 0.05625790140.0177705977 0.0141005977 0.05326231690.057363997 0.053693997 -0.015727392-0.0197619858 -0.0234319858 -0.0862275450.0357233838 0.0320533838 0.04244694130.0335601734 0.0298901734 -0.0219780220.074141757 0.070471757 0.08261731660.0001958352 -0.003474165 0.07763532760.0530814267 0.0494114267 -0.0409836070.0939250755 0.0902550755 0.39428571430.0854045083 0.0817345083 0.0574018127-0.1099312249 -0.1136012249 -0.074557316-0.085657348 -0.089327348 -0.15710919090.0078215657 0.0041515657 -0.114126653-0.074849032 -0.078519032 -0.326932084-0.169424534 -0.1730945344 -0.100673968-0.0907914533 -0.0944614533 -0.13040293040.0121905032 0.0085205032 0.0799050633-0.009859375 -0.013529375 0.0502700457-0.085962382 -0.089632382 0.00333472280.0106741532 0.0070041532 0.06717081850.0475466848 0.0438766848 -0.050675676-0.0059595831 -0.009629583 -0.176347826-0.034761162 -0.0384311621 -0.058611657-0.061163475 -0.064833475 -0.018952779-0.008628489 -0.012298489 0.180060652-0.044043424 -0.047713424 0.01227935530.014822335 0.011152335 0.00269334360.0357940013 0.0321240013 -0.0647715010.0128635923 0.0091935923 -0.046328071-0.0319819071 -0.0356519071 -0.033819629Assumes: 4.5% Annual Risk-free rate &.367% monthly Risk-free rateRegression InstructionsSelect Data & then Data Analysis from top Excel options (If Data Analysis option is not there see instructions below).Select Regression.Input the Company Returns (collumn C of this spreadsheet for Y variable)Input the Market Risk Premium (collumn B of this 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 followingquestions on the output page.1. On the output page identify and circle the followinga. y-interceptb. measurement of how well regression model estimates company returnsc. How does this regression model fit the data?2. Write the regression equation from the regression output.Instructions for adding Regression option on Excel1. Click the Microsoft Office Button, and then click Excel Options.2. Click Add-ins, and then in the Manage box, select Excel Add-ins.3. Click Go.4. In the Add-Ins available box, select the Analysis ToolPak check box, and then click OK.Tip If Analysis ToolPak is not listed in the Add-Ins available box, click Browse to locate it.If you are prompted that the Analysis ToolPak is not currently installed on your computer, click Yes to install it.This page is an example of the regression output generated for the MLHR data.SUMMARY OUTPUTRegression StatisticsMultiple R0.29438966R Square0.08666527Adjusted R Square0.07091812 Goodness of Fit MeasurementStandard Error0.08009739Observations60ANOVAdfRegressionResidualTotalInterceptX Variable 1Regression Equation:15859Coefficients0.005801320.86293692SSMSFSignificance F0.0353085446 0.0353085 5.5035526 0.02241761710.372104303 0.00641560.4074128476Standard Errort StatP-valueLower 95% Upper 95% Lower 95.0% Upper 95.0%0.010340535 0.5610267 0.5769405 -0.014897513 0.02650015 -0.0148975134 0.02650014560.3678387622 2.3459652 0.0224176 0.1266276467 1.5992462 0.1266276467 1.5992461982Y = 0.0058013 + 0.8629X

 

Paper#48404 | Written in 18-Jul-2015

Price : $42
SiteLock