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. Your report should start with an executive summary. Please only submit one file each group.

I.Fundamental ratio analysis and industry analysis

1.Each group will be assigned a stock.

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.Summarize and compare 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, sales etc.)

II. Valuation analysis

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

4.Collect data for stock price, market price (S&P 500 Index), risk free rate (using 3-month T-bill rate or 10-year T-note rate). Hint: Collect adjusted stock price (Pi) and the S&P 500 Composite Index Price (Pm) from Yahoo Finance (use data more than 10 years), collect three-month T-bill rate (Rf=TB3MS) from Economic data-FRED @ http://research.stlouisfed.org/fred2/categories/116 (TB3MS is an annualized time series) or 10 year T-note rate. Construct stock returns and market returns using stock price and market price.

5.Run the regression of stock 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 R².

6.Estimate the expected return if the stock using CAPM (Please provide details on your estimation. You need to estimate risk free rate and market risk premium. A reasonable risk free rate should be between 2%-4% and reasonable risk premium should be between 4%-10%). Provide analysis showing how to understand the expected return.

7.Estimate the growth rate for the firm using historical dividend information or roe and dividend payout ratio (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).

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

9.Provide sensitivity analysis showing how stock value varying with different discount rates and growth rates.

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