Security Analysis

Guidelines for the Security Analysis Project

Your report, graphs, tables, and calculations must be processed on MS Excel. You should have a cover page including your group name, names of group members, stock symbol, and firm name, etc. You may address each of the following questions in one Sheet. Please only submit one Excel file each group via Canvas. Don’t submit any other file type.

I. Fundamental ratio analysis and industry analysis

1. Provide an executive summary on the firm you are assigned.

2. Study the industry of the stock you are assigned. Which industry is your firm engaged (provide the different business lines of your firm)?

3. Explain, compare, and analyze the key statistics for the stock and the industry (choose at least 5 items you believe informative, such as P/E ratio, market cap, dividend yield, ROE, etc.).

II. Valuation analysis

Valuate the stock using dividend discount model and multi period dividend discount model.

4. Collect data for stock (you are assigned) price, market price (S&P 500 Index), risk-free rate (using 3-month T-bill rate). Hint: Collect adjusted stock price (Pi) and the S&P 500 Composite Index Price (Pm) from Yahoo Finance (use monthly frequency data series more than 10 years), collect three-month T-bill rate (Rf=TB3MS) from Economic data-FRED ® @ http://research.stlouisfed.org/fred2/categories/116 (Attention: TB3MS is an annualized time series). Construct stock returns and market returns using stock price and market price. Provide and discuss the descriptive statistics for the risk-free rate, stock returns and market returns (including mean, median, standard deviation, maximum, and minimum).

5. Run the regression of the stock (you are assigned) return on market return in Excel (Hint: click on Tools in the menu bar, choose Data Analysis and then double lick regression. If it does not show Data Analysis in the drop-down list, click Add-ins on the drop-down list. When the Add-Ins dialog box pops up, click the box in front of Analysis ToolPak, and then click the OK button. The Data Analysis add-in will be installed. Report your results: interpret the coefficients, t-statistics and R2. Discuss the estimated beta.

6. Estimate the expected return of the stock you are assigned using CAPM (Please provide details on your estimation. You need to estimate average annualized risk free rate and market risk premium. A reasonable risk-free rate should be between 1%-3% and reasonable market risk premium should be between 5%-9%). Discuss your rationale on the estimated risk-free rate, market risk premium, and expected return.

7. Estimate the short run growth rate and the long run grow rate for the firm using historical dividend information, ROE and dividend payout ratio, and GDP growth rate (Provide details on your calculation. Common practice is to estimate industry growth; for multi period DDM, you have to estimate the short run growth for the future five years and the long run growth from year 6 to infinity). Discuss the difference between the estimated short run growth rate and long run growth rate.

8. Valuate your stock using constant dividend growth model (provide details on your calculation) and multi period dividend discount model. Discuss the results.

9. Provide sensitivity analysis on constant dividend growth model showing how stock value varies with different discount rates and growth rates.

10. Comparing your estimation with the stock price in March 31, 2021, show whether the stock is undervalued or overvalued. Determine your trading strategy based on your estimation.

Sources:

Wall Street Journal

Chapter 12, 20, 21 in Bodie, Kane, and Marcus for Part I

Chapter 5, 6, 7, 13, 14, 18 in Bodie, Kane, and Marcus for Part II

Chapter 14 in Bodie, Kane, and Marcus for Part III

Yahoo Finance @ http://finance.yahoo.com/

Economic data-FRED ® @ http://research.stlouisfed.org/fred2/

Economic Report of the President @ http://www.gpoaccess.gov/eop/index.html

Federal Reserve Bulletin @ http://www.federalreserve.gov/FOMC/BeigeBook/2007/

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.