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.
Paper#23501 | Written in 18-Jul-2015Price : $27