Ticker Symbol is MAT.

TBill rates 2013-2018

--------------------------

This project is designed to illustrate the process of estimating the CAPM market risk factor, beta. The technique uses a simple regression model of firm excess returns vs. Market index excess returns to estimate beta. The technique and the timing can make a big difference in the beta estimate. We want to compare the betas from the monthly return regression with the beta currently published in Yahoo Finance. The project is also intended to familiarize you with accessing financial data in Yahoo Finance. To download the data for your monthly regression, follow the steps below. Do not use a statistics package other than Excel for the regression.

1. Use the ticker assigned to you for the financial ratio project. All of the firms assigned are in the S&P500, so the price series should be available. However, if you cant get a complete price series for your firm, email me and I will give you another ticker, do not select a new firm on your own. (I expect you to check for the data well before the due date, so requests made for a new firm after 5 pm on the day before the project due date will not be responded to and you will receive no credit for the project.)

2. Download the monthly prices for the firm from Yahoo Finance.

A. Access the Yahoo Finance website.

B. Enter the ticker symbol for your firm.

C. Click on Historical Data

D. Under Time Period, Enter the start and end dates for the period: 10/01/2013 - 10/01/2018.

E. Under Frequency, select monthly prices.

F. Click on Apply

G. Click on Download Data.

H. A small icon will appear in the lower left corner of the screen that shows yourticker.csv. Click on the icon to open the file in Excel.

I. Only the date and the adjusted close need to be preserved, the other columns can be deleted. So the date will be in column A and the adjusted close in column B after deleting the other columns.

J. Label blank column C at the right Firm Rtrn.

K. Go to the cell in column 3 that is on the same row as the second price in the series (C4 in my example) and enter the formula: =(b3/b2 -1)*100. The monthly percent return will be calculated and displayed. Drag this formula down to the bottom of the column to create the daily return series. Save the file as an Excel file.

3. Return to Yahoo Finance and using exactly the same range of months, download monthly prices for the S&P500.

4. The S&P500 historical prices can be accessed by clicking on the current S&P500 at the top of the Yahoo Finance main page.

5. As in steps 2C through K above, download the index series, and create a monthly return series for the S&P500. After the worksheet with the S&P price and return series is created, copy it to the worksheet with the firm price series (use one of the Paste - Special commands to copy it over with the dates intact). Put the S&P date, price, and return series in columns D through F. The only reason to copy the date is just to make sure that the dates for your firm and the S&P align exactly in time.

6. I am providing you with an Excel template containing the treasury bill rates for each of the months of your data that I obtained from the St. Louis Fed website. You will use the T bill rate as a proxy for the risk free rate, Rf , needed to calculate the excess return for each month. The excess return is just the monthly return minus the T bill return for that month. Copy the date and TB3MS from the template I furnished into columns G and H in your template. Again, the date column is needed only to make sure that you have the data aligned correctly in time.

7. Calculate the firm excess returns in column I and the S&P excess returns in column J. For firm excess returns, on my template in cell I4, I entered = C4 - H4 and then dragged the equation down the column to fill in the excess returns. Repeat for S&P excess returns in column J.

8. You may have to install the Analysis ToolPak in Excel in order to access the regression module. Click on the Data tab in the toolbar. If there is no Data Analysis tab underneath, you will need to install the ToolPak. Click on File / Options / Add-Ins and highlight Analysis ToolPak. Click Go and this should install the Analysis ToolPak. You should now find the Data Analysis tab under the Data tab. Scroll down to and click on Regression.

9. The data input box should appear.

10. Enter the series of firm excess returns in the Input Y box by either giving the cell references for the beginning and ending cells of the series (e.g. $I$4:$I$63) or by highlighting the series of cells containing the excess returns.

11. Enter the series of S&P500 excess returns from column J in the Input X box in the same manner.

12. Click on O.K.

13. The Summary Output should appear shortly.

14. The coefficient of the X-Variable is the estimated beta.

15. The t-statistic for the coefficient of the X-Variable should be >1.96 in order for there to be a significant beta relationship between firm excess returns and S&)500 excess returns. (If you dont get a significant relationship, for this project thats ok, just proceed with the rest of the project).

16. In Yahoo Finance, the beta for your firm is on the same page that you accessed for historical prices. You will need the Yahoo Finance beta for the comparison below.

17. Identify your firm by name and ticker in your comments. Compare your monthly return beta, and the Yahoo -Finance beta. How closely do they agree? It wont be unusual for them to be quite different because betas tend to be unstable over time. Make some written comments about how closely they agree. Based on your calculated beta, how risky is your firm relative to the market portfolio that has a beta of one, more risky, less risky? You can make your comments about the beta differences and level on the Excel regression output like in my example.

18. Submit your Excel spreadsheet with the worksheet of prices and returns for your firm and the S&P500, and the worksheet of the regression analysis, using the assignment tool.

Example

I am including an example that covers a different time period than your project as a guideline for you, but you are to use the monthly data series from 10/01/2013 through 10/01/2018 as stated above. The series of 61 prices will yield 60 monthly returns from 11/01/2013 through 11/01/2018.

Economic Research DIMSIon 6 Federal Reserve Bank of St. Louis 8 TB3MS 3-Month Treasury Bill: Secondary Market Rate, Percent, Monthly, Not Seasonally Adjusted 10 Frequency: Monthly 11 lobservation date 12 13 TB3MS 2013-10-01 2013-11-01 2013-12-01 2014-01-01 2014-02-01 2014-03-01 2014-04-01 2014-05-01 2014-06-01 2014-07-01 2014-08-01 2014-09-01 2014-10-01 2014-11-01 2014-12-01 2015-01-01 2015-02-01 2015-03-01 2015-04-01 2015-05-01 2015-06-01 0.05 0.07 0.07 0.04 0.05 0.05 0.03 0.03 0.04 0.03 0.03 0.02 0.02 0.02 0.03 0.03 0.02 0.03 0.02 0.02 0.02 15 16 17 18 19 20 21 23 24 25 26 27 28 29 30 31 32