Please read the directions and follow steps.

Attachments are below.

Date 2/28/2017 2/27/2017 2/24/2017 2/23/2017 2/22/2017 2/21/2017 2/17/2017 2/16/2017 2/15/2017 2/14/2017 2/13/2017 2/10/2017 2/9/2017 2/8/2017 2/7/2017 2/6/2017 2/3/2017 2/2/2017 2/1/2017 1/31/2017 1/30/2017 1/27/2017 1/26/2017 1/25/2017 1/24/2017 1/23/2017 1/20/2017 1/19/2017 1/18/2017 1/17/2017 1/13/2017 1/12/2017 1/11/2017 1/10/2017 1/9/2017 1/6/2017 1/5/2017 1/4/2017 1/3/2017 12/30/2016 12/29/2016 12/28/2016 12/27/2016 12/23/2016 12/22/2016 Stock Price S&P 500 Index Stock Returns S&P 500 Returns Risk-Free Return 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0010 0.0010 Stock Excess Returns 12/21/2016 12/20/2016 12/19/2016 12/16/2016 12/15/2016 12/14/2016 12/13/2016 12/12/2016 12/9/2016 12/8/2016 12/7/2016 12/6/2016 12/5/2016 12/2/2016 12/1/2016 11/30/2016 11/29/2016 11/28/2016 11/25/2016 11/23/2016 11/22/2016 11/21/2016 11/18/2016 11/17/2016 11/16/2016 11/15/2016 11/14/2016 11/11/2016 11/10/2016 11/9/2016 11/8/2016 11/7/2016 11/4/2016 11/3/2016 11/2/2016 11/1/2016 10/31/2016 10/28/2016 10/27/2016 10/26/2016 10/25/2016 10/24/2016 10/21/2016 10/20/2016 10/19/2016 10/18/2016 10/17/2016 10/14/2016 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 10/13/2016 10/12/2016 10/11/2016 10/10/2016 10/7/2016 10/6/2016 10/5/2016 10/4/2016 10/3/2016 9/30/2016 9/29/2016 9/28/2016 9/27/2016 9/26/2016 9/23/2016 9/22/2016 9/21/2016 9/20/2016 9/19/2016 9/16/2016 9/15/2016 9/14/2016 9/13/2016 9/12/2016 9/9/2016 9/8/2016 9/7/2016 9/6/2016 9/2/2016 9/1/2016 8/31/2016 8/30/2016 8/29/2016 8/26/2016 8/25/2016 8/24/2016 8/23/2016 8/22/2016 8/19/2016 8/18/2016 8/17/2016 8/16/2016 8/15/2016 8/12/2016 8/11/2016 8/10/2016 8/9/2016 8/8/2016 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 8/5/2016 8/4/2016 8/3/2016 8/2/2016 8/1/2016 7/29/2016 7/28/2016 7/27/2016 7/26/2016 7/25/2016 7/22/2016 7/21/2016 7/20/2016 7/19/2016 7/18/2016 7/15/2016 7/14/2016 7/13/2016 7/12/2016 7/11/2016 7/8/2016 7/7/2016 7/6/2016 7/5/2016 7/1/2016 6/30/2016 6/29/2016 6/28/2016 6/27/2016 6/24/2016 6/23/2016 6/22/2016 6/21/2016 6/20/2016 6/17/2016 6/16/2016 6/15/2016 6/14/2016 6/13/2016 6/10/2016 6/9/2016 6/8/2016 6/7/2016 6/6/2016 6/3/2016 6/2/2016 6/1/2016 5/31/2016 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 5/27/2016 5/26/2016 5/25/2016 5/24/2016 5/23/2016 5/20/2016 5/19/2016 5/18/2016 5/17/2016 5/16/2016 5/13/2016 5/12/2016 5/11/2016 5/10/2016 5/9/2016 5/6/2016 5/5/2016 5/4/2016 5/3/2016 5/2/2016 4/29/2016 4/28/2016 4/27/2016 4/26/2016 4/25/2016 4/22/2016 4/21/2016 4/20/2016 4/19/2016 4/18/2016 4/15/2016 4/14/2016 4/13/2016 4/12/2016 4/11/2016 4/8/2016 4/7/2016 4/6/2016 4/5/2016 4/4/2016 4/1/2016 3/31/2016 3/30/2016 3/29/2016 3/28/2016 3/24/2016 3/23/2016 3/22/2016 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0010 0.0020 0.0020 0.0020 0.0020 0.0020 0.0020 0.0020 0.0020 0.0020 0.0020 0.0020 0.0020 0.0020 0.0020 0.0020 0.0020 0.0020 0.0020 0.0020 0.0020 0.0020 0.0020 0.0020 0.0020 3/21/2016 3/18/2016 3/17/2016 3/16/2016 3/15/2016 3/14/2016 3/11/2016 3/10/2016 3/9/2016 3/8/2016 3/7/2016 3/4/2016 3/3/2016 3/2/2016 3/1/2016 0.0020 0.0020 0.0020 0.0020 0.0020 0.0020 0.0020 0.0020 0.0020 0.0020 0.0020 0.0020 0.0020 0.0020 S&P 500 Excess Returns Beta Extra Credit - FIN 3320 Kevin Green This extra credit assignment can add up to five points to your second midterm grade. All extra credit assignments must be emailed to me no later than Friday, April 14th at 11:59pm. Calculating Beta and WACC For this extra credit assignment you will estimate the equity beta for the public company you submitted to me as a part of the student questionnaire at the start of the semester (Please email me if you need a reminder). Use the extra-credit template excel file uploaded to eLearning to complete Steps 1-5. Step 1: Pull the daily 12 month historical common stock returns for the company you selected from the yahoo finance website (http://finance.yahoo.com/). For example, entering the ticker \"AAPL\" in the search window will pull up the current stock price and a summary of the current trading activity for Apple Inc. Here you can click on the \"Historical Data\" tab. On the historical data tab set the historical date range to (03/01/2016 - 02/28/2017) and the frequency to \"daily\". Then click on the \"Apply\" filter button to refresh the historical data. You can then either click on the \"Download Data\" hyperlink to download the data in a .csv file or copy and past the data directly from the website and into excel. Important: note this data will contain the daily Open, High, Low, Close and Adj. Close prices. Copy the Adj. Close prices into column B of the template file such that the prices match up with the dates in column A. Step 2: Next you will need to complete the exact same process to get the market rate of return. Enter the ticker \"GSPC\" (notice the sign included before GSPC) to pull the historical index levels for the S&P 500. Copy the Adj. Close prices into column C of the template file such that the respective index levels match up with the dates in column A. Step 3: In the template file, calculate the daily historical stock and S&P 500 returns in columns D and E (Note: Returnt=i = (P ricet=i P ricet=i1 )/P ricet=i1 ). You do this easily by entering the return formula in cells D2 and E2 and dragging the formula to the second to last cell in the chart (D252 & E252). Be sure to keep the formulas in the cells so I can see what you are doing. Step 4: Now calculate the daily excess return for your stock and the S&P 500 (i.e. your stock's daily return minus the risk-free rate of return in column F and the S&P 500 daily return minus the risk-free rate of return in column F) for each day in the time period in columns G & H. Step 5: You now have all of the information you need to estimate your chosen stock's (beta)! To estimate the stock's beta, we will need to run a linear regression using the following estimation Rstock = Rmkt , where Rstock is the excess stock return and Rmkt is the excess market return. Note this estimation equation is 1 Extra Credit - FIN 3320 Kevin Green the famous CAPM equation, Ri = Rf + (Rm Rf ), modified by subtracting the risk-free rate of return from both sides. Click on the \"Data\" tab in the excel ribbon. To the far right of the excel data ribbon click on \"Data Solver\"1 . This will bring up the \"Data Analysis\" window. Select \"Regression\" from the list of analysis tools and click \"OK\". This will bring up the \"Regression\" window similar to the one we saw in class. Click on the selection icon for the \"Input Y Range\" and select cells G1:G252. For the \"Input X Range\" select cells H1:H252. Once you have the correct X and Y ranges selected click \"OK\". Excel will now run the regression analysis and include the results in a new tab within the spreadsheet. Using the regression output in the new tab, report the Coefficient estimate for \"X Variable 1\" in cell J2 next to the historical returns chart in the template file. Congratulations...this is the estimated beta for your stock! Save this excel file using the following naming convention: \"UTDnetIDbeta\