Open and download the excel spreadsheet,M5 Calc Ret Var Beta Regr.xls Please follow the instructions on the first worksheet of the Excel Spreadsheet below. Feel free to choose any company you wish,...

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.
    Print these instructions and follow the 6 steps below. Turn in your answers on these sheets and printouts listed at the bottom of the page
    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
        Highlight the dates and adjusted closing stock prices. Then Click Data and Sort by Date to show Most Recent Monthly Data at Top.
    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 Returnt = (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.
        Make Sure that most recent month is at top. (See last instruction in Step 1 to sort)
    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?______                                                Oc 2016
        (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?_______                                        Nov-16
        c. What was the largest positive monthly return?________                        0.28
        d. In what month was the largest negative monthly return for the company in the past five years?_______                                        Jan-16
        e. What was the largest negative monthly return?________                        -0.1575
        f. Based on the line Graph 1 would you say that your company returns and S&P 500 returns ________.                                        often move in the same direction
            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:
            Variance for the company = +I66/60 where I66 is the sum of variance of each monthly return
        I70    c. Standard Deviation for the company:
            Std. deviation for the company = Sqrt (+I69) where, I69 is the total variance of the monthly returns
        F68    d. Expected Return (E(R)) for the market index:
            Expected monthly return for the market = B66/60, where B66 is the sum of monthly returns
        F69    e. Variance for the market index:
            Variance for the market Index = +F66/60 where F66 is the sum of variance of each monthly return
        F70    f. Standard Deviation for the market index:
            Std. deviation for the market Index = Sqrt (+F69) where, F69 is the total variance of the monthly returns
    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?________                                                    May-17
        (i.e. both had negative amounts in collumn E and collumn G.)
        b. Was the covariance for this month positive or negative?________                            Positive
        c. What is the most recent month for which the company return and market return
        were greater than their expected returns?________                        Feb-17
        d. Was the covariance for this month positive or negative?________                            Positive
        e. What is the most recent month for which the company return and market return
        moved in opposite directions than their expected returns?________                            Oct-16
        f. Was the covariance for this month positive or negative?_________                            Negative
        g. Look up your company's Beta on Yahoo!Finance by doing the following steps:                                1.54
            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.
&A
Page &P of &N
2.Calc. Returns
    month    Date    S&P 500    BAC    Mkt Returns    Co. Returns        Input Worksheet
    1    5/1/17    2381.72998    20.69    -0.0010359748    -0.0398457956        In the highlighted collumn D copy a series of closing monthly
    2    4/1/17    2384.199951    21.55    0.0090912085    -0.0075818097        prices for your company. This price series in collumn D is then
    3    3/1/17    2362.719971    21.71    -0.0003891972    -0.0441653512        converted to a return series in collumn F.
    4    2/1/17    2363.639893    22.72    0.0371981603    0.090106128
    5    1/1/17    2278.870117    20.84    0.0178843582    0.0244340806        NOTE: You will copy over MLHR data and the S&P 500 series is
    6    12/1/16    2238.830078    20.34    0.0182007622    0.050283867        already done for...