Project Instructions Background Congratulations! You’ve just been hired as an analyst at a large investment institution. Your department analyzes and creates portfolios for large institutional investors. Your first big project is to find out whether the company you’ve been assigned is over- or under- priced. Since your fresh out of school, you know exactly what to do! You know that the price of the stock is just the present value of all the expected future cash flows. Therefore, there are two big inputs you need to know when modeling the price of the stock: 1) the expected dividends and 2) the interest rate at which you discount. These two inputs depend on the general business climate, so the first thing you will do is to analyze the macro-economy and the specific industry in which the firm operates. Once you have a feel for the big picture you will then use use CAPM find an estimate of the discount rate and use equity valuation models to estimate the expected future dividends and price of the stock. Objectives The primary goal of this project is to internalize the various models and intuitions developed in the course. There is no substitute for getting your hands dirty when you learn. Additionally, the process of analyzing and creating the final report will help you in your professional life. Formatting Instructions Please submit in your report, including graphs and table, as a Word document and your calculations as an Excel file. Your report should look professional and grammar, format, clarity, flow of ideas, etc. will be graded. Remember, your boss will see it and you’d like to make a good impression! Use 10pt Times New Roman, single space, and one inch margins. For the questions in the first part below do NOT type more than a half page for each question. I will not read more than half of the page (quality NOT quantity!). However, do not give “yes or no answers” with only two lines of explanation. Do not repeat what your resource says, but always explain the information with your own words. I want to see your reasoning skills and communication skills. Use primary resources. DO NOT present any view from a secondary source, or another paper, project, analysis, etc. unless the question asks for it. If you feel stuck using Word or Excel, use “Help”, google, or search YouTube for help. Use other resources a well. Part of the objectives are to help teach you to research things when you’re stuck. Project Instructions Choose a stock to analyze (the first letter of the stock and your name should match) that has paid dividends consistently and traded in the market for more than 5 years. I. 1) 2) 3) 4) 5) Macroeconomic and Industry Analysis (Word file) What is the annual US GDP growth rate over the past 10-year period (2004-2014)? What is the inflation rate over the past 10-year period (2004-2014)? Explain the inflation rate. What is the unemployment rate over the past 10-year period (2004-2014)? Explain the unemployment rate. What years are recession years during past 10 years? What are the economists’ forecast for unemployment, GDP growth, and inflation? a) Visit WSJ Forecasts, scroll down and to the left hand side you should a link that says “download data for August 2015” b) Use a forecast horizon of one year ahead. What do you think about individual forecasts and the average of them? 6) What is your prediction for the above variables? Do you expect more or less than the average forecasts? Explain you reasoning. 7) How do these variables (GDP growth, unemployment rate, and inflation rate) relate to stock market return? (Your own brief opinion only) 8) Summarize the key statistics (values and definitions) for your stock and its industry. a) Choose 8 items you believe informative, such as P/E ratio, market capitalization, dividend yield, ROE, sales, etc. These do not have to be perfect, and can be anything that makes sense, what I’m looking for is your contribution. b) Look at the “Key Statistics” on your stock’s page at finance.yahoo.com. 9) Write the interpretation and conclusion of your Valuation Analysis a) See Steps 7g. and 9 10) a References page so that I know where you got your information. Failure to do so will be taken off points from your project. Project will be checked for plagiarism. Do not fail to report the resources you refer to. II. Valuation Analysis (Excel file) 1) Download stock price information a) Go to finance.yahoo.com and type your company's name in the "get quotes" box. b) From the list, click on "Historical Prices". c) Choose MONTHLY Historical prices for the past 5 years. d) On the bottom of the next page, & click on "Download to spread sheet" to save your data. e) In the downloaded file, USE ONLY the first and last column (date/adj close), delete other columns. 2) Download market (S&P 500) index prices information a) From the finance.yahoo.com page, under the "Investing tab", click on "Market stats". b) From the list on the left, click on US. Then, in the middle of the page, choose S&P500 from the S&P tab. c) Now download the S&P monthly prices by following the same steps in Step 1. 3) Download the risk-free (3-month T-bill) rates a) Go to http://research.stlouisfed.org/fred2/series/TB3MS b) Clicking on, the “monthly” frequency, would give you the TB rates from 1934 to 2014. c) Save this file. d) NOTE: These rates are annualized, therefore you have to divide the series by 12 to generate monthly Tbill rate in percent. Now divide by 100 to get it into decimal form. This is your risk-free rate. 4) Merge the files from the previous steps a) Make sure that the dates in both files match before you merge. Delete any row with missing observations. 5) Calculate the rates of return for the stock and S&P 500 index prices 𝑃 a) Use the following formulas to find the return: 𝑅𝑡 = 𝑡 − 1 or 𝑅𝑡 = ln(𝑃𝑡 ) − ln(𝑃𝑡−1 ) 𝑃𝑡−1 6) Now find the excess returns: (R-Rf) and (Rm-Rf) 7) To find the beta, run a regression of (R-Rf) on (Rm-Rf): 𝑅𝑖 − 𝑅𝑓 = 𝛽(𝑅𝑚 − 𝑅𝑓 ). a) From the excel dropdown menu, click on Tools, then Add-ins. b) Then choose "Analysis Toolpack" and click ok. c) Excel will start installing the "Analysis toolpack" keep your Microsoft office CD ready. d) Click on Tools, "Data Analysis". Then choose, "Regression", and click Ok. e) In the "input Y range", enter the (R-Rf) column, & in the "out X range" enter the (Rm-Rf). f) From the “Options” tab in the regression dialog box, set the intercept/constant value to zero. g) From the summary output, in the 3rd (lowest) table, you will find the beta coefficient under the title coefficient in the second row, next to X-Variable. Report your results in question 9: interpret the coefficients, t-statistics and R2. h) Using the calculated beta, we can now estimate the expected rate of return on the stock using the CAPM: 𝑅𝑖 = 𝑅𝑓 + 𝛽(𝑅𝑚 − 𝑅𝑓 ). 8) Using the beta calculated in Step 7, estimate the discount rate (see pg. 208) a) Use beta you estimated as given b) Download 20 years worth of annual data on the S&P 500 to find the average annual excess return on the market c) Download 20 years worth of data for the 3-month T-bills to calculate the average annual risk-free rate d) Using the average annual risk-free rate (𝑅𝑓 ) and the average annual excess return of the stock (𝑅𝑚 − 𝑅𝑓 ) and plug into the CAPM formula to estimate the discount rate. 9) Find the price of the stock a) Value the stock using the dividend discount model (see Chapter 13) b) Clearly summarize your report and explain your main results (not more than 2 pages). This corresponds to question 9 from I. Macroeconomic and Industry Analysis i) Compare your estimation with the current stock price, show whether the stock is undervalued or overvalued and determine your trading strategy based on your estimation, etc. References: Wall Street Journal @ http://projects.wsj.com/econforecast/ - ind=gdp&r=20 Chapter 1, 12 in Bodie, Kane, and Marcus for Part I Chapter 5, 6, 7, 13, 14 in Bodie, Kane, and Marcus for Part II Yahoo Finance @ http://finance.yahoo.com/ Economic data-FRED ® @ http://research.stlouisfed.org/fred2/ Economic Report of the President @ https://www.whitehouse.gov/administration/eop/cea/economic-report-ofthe-President Federal Reserve Bulletin @ http://www.federalreserve.gov/monetarypolicy/beigebook/beigebook2014.htm Survey of Current Business @ http://www.bea.gov/scb/index.htm Humphrey Hawkins Testimony and Report @ http://www.federalreserve.gov/boarddocs/hh/ NOTE: Late reports without pre-approval from the instructor will receive no credit. This is for illustration, I only used 9 months to make it easier to follow. Part II, CAPM After downloading each price data, match the date and copy&paste the relevant data to the same sheet If you need to sort the T-bill rate, selecet the date and rates and use the SORT under Data Calculate the returns: Click the first entry on Return Ford below and it will give you the formula in the formula b If you double click at the cell, it will show the formula with colors and etc. Risk free rate is annulaied so divide it by 12. For CAPM equation calculate the risk premiums, again double click to any cell and it will tell you what the formu Last row will have: DIV/0! This is because of dividing by zero, since there can bot be any data for the last return. Therefore, number of returns will be one less than number of prices. Don't push the Panic button. Do not alter, delete, change any of the data, EVEN THOUGH YOU WILL NOT USE IT. Your data should be easy to read, clear and should look professional. Now run the regression. Make sure you include LABELS in the options. NO CONSTANT in the regression. Include Date Price SP 500 1/2/2013 1498.11 12/3/2012 1426.19 11/1/2012 1416.18 10/1/2012 1412.16 9/4/2012 1440.67 8/1/2012 1406.58 7/2/2012 1379.32 6/1/2012 1362.16 5/1/2012 1310.33 4/2/2012 1397.91 3/1/2012 1408.47 Price Ford 1.87 1.48 1.21 1.21 1.17 1.14 1.49 1.74 2.14 2.29 2.37 Return SP 500 0.0504 0.0071 0.0028 -0.0198 0.0242 0.0198 0.0126 0.0396 -0.0627 -0.0075 #DIV/0! Return Ford Risk free rate 0.2635 0.07 0.2231 0.07 0.0000 0.09 0.0342 0.1 0.0263 0.11 -0.2349 0.1 -0.1437 0.1 -0.1869 0.09 -0.0655 0.09 -0.0338 0.08 #DIV/0! 0.08 Data explanation: Downloaded Downloaded Calculated return Calculated return Downloaded the same sheet ormula in the formula bar above. tell you what the formula is. data for the last return. Do not delete the rows if you want instead Right click and "Clear contents" the regression. Include plot. Risk free monthly 0.0058 0.0058 0.0075 0.0083 0.0092 0.0083 0.0083 0.0075 0.0075 0.0067 0.0067 Risk premium SP 500 0.0446 0.0012 -0.0047 -0.0281 0.0151 0.0114 0.0043 0.0321 -0.0702 -0.0142 #DIV/0! Risk premium Ford 0.2577 0.2173 -0.0075 0.0259 0.0171 -0.2432 -0.1520 -0.1944 -0.0730 -0.0404 #DIV/0! calculated monthly CAPM independent variable CAPM Dependent variable