FINANCE ANALYSIS PROJECT
Guideline for the Group Project
Each group chooses a public company at the beginning of the term. Each group is required to evaluate the company as an investment holding. Each group will write a paper on their respective company. The paper should include a brief description of the company, an evaluation of the financial statements, and a convincing argument to either buy or sell the stock.
DUE DATE – March 3. Absolutely no paper will be accepted after the due date. The work should be equitably split up among the group members – I will not mediate. Each member will receive the same mark as the overall group. The report should not read as if it is a ‘cut and paste’ job from various documents. While grading the report, I will place a lot of emphasis on whether the final product looks professional, reads seamlessly from one section to the other, the quality and depth of the analysis, clarity, persuasiveness and critical thought. Group presentations will begin on March 3. Likewise, the presentation should focus on concisely conveying the most important information within your allotted time.
1. Brief description of the company a. Corporate history, products, competition, etc. b. Discuss all major company specific, economic or market news that may have an
impact on your company (Attach the news article you collected). 2. Brief bios of top executives and Board of Directors 3. Important events or transactions within the last five years (2015 – 2019) 4. Evaluation of the financial statements
a. The company’s performance over the past five years (2015 – 2019): i. Discuss current trends and prospects for revenue and profit growth or
decline. b. Calculate the following ratios for the past three years
i. Current ratio, Total asset turnover, Debt ratio, Debt-to-equity ratio, Profit margin, Return on assets, Return on equity, Payout ratio, P/E ratio, Dividend yield, Market-to-book ratio
c. Select three ratios from the above with which you can explain some of the major changes that occurred during the past three years. You should show actual calculations and plot the ratios in a line graph format. You should then explain the changes in these ratios using the information you may gather from published sources. The three ratios must be selected from different categories. Compare your firm’s ratios with those of competitors (or industry) and include the trend analysis for the past three years.
5. Stock analysis and evaluation a. Graph daily stock price movements from the beginning to ending date of your
collection period (October 1 to December 31, 2019) using Excel. i. Go to the Yahoo! Finance web page. http://finance.yahoo.com ii. Enter your stock’s ticker. iii. Choose "Historical Data” from the menu. iv. Use October 1, 2019 as the start date and December 31, 2019 as the ending
date and obtain daily stock prices. After you click the “Apply” button, right click the “Download Data” link. Make sure all the dates match for the stock and index.
v. Save the data as an Excel file.
vi. Do the same with the S&P 500 using the index ticker ^GSPC. vii. Paste the S&P 500 Index prices into the company spreadsheet. viii. Use the “Adj. Close” value for all calculations.
b. How has the stock performed over the past year, and past 5/10 years? i. Discuss the company’s chart, and all-time high and low prices.
c. Relative performances of your stock and the S&P 500 index i. Calculate performance evaluation tools for your stock and the S&P 500 during
your collection period (October 1 to December 31, 2019): 1. Calculate the quarterly holding period return for the company and the
S&P500 Index such as [(December 31, 2019 price + Quarterly dividends – October 1, 2019 price)/ October 1, 2019 price]. Don’t forget to adjust for stock splits if applicable. (Ignore quarterly dividends for S&P 500 index.)
2. Annual holding period return (January 2, 2019 to December 31, 2019 price price) [(December 31, 2019 price + Annual dividends – January 2, 2019 price)/ January 2, 2019)]. Don’t forget to adjust for stock splits if applicable. (Ignore annual dividends for S&P 500 index.)
ii. Create a spreadsheet containing the historical monthly rates of returns of your group’s stock, and the S&P 500 over the past five-year period (i.e., 1/02/2015 – 12/31/2019). Obtain the monthly stock prices to compute the following performance evaluation tools. You need to convert the prices into monthly rates by differencing the monthly prices. [i.e., Rate of return (t) = [price (t) – price (t-1)] / price (t-1)]. Answer the following questions using the monthly rates of
return. Print out the Excel spreadsheet containing the data, and outputs. 1. What are the average monthly rates of return and standard
deviations of your stock and the S & P 500? Use Excel's AVERAGE and STDEV functions
2. What are the average annual rates of return and standard deviations of your stock and the S & P 500? Annual return = (1 + monthly return) 12 – 1 Annual standard deviation = monthly standard deviation*[(12) 0.5 ]
3. What is the correlation coefficient of their returns? Use Excel's CORREL Function or Correlation command from Data Analysis tool.
d. Compute the intrinsic price using the constant growth model.
i. 𝑃0 = 𝐷1
1. You must provide the estimate of dividend growth rate (g). 2. You may find (g) by looking at the percent change in dividend over
the past 5 years (2015 – 2019). Do not forget the past g may not represent the future g. Remember that g is the future constant dividend growth rate in the long run. Be sure to defend your choice of g.
3. D1 is the expected annual dividends for next year. ii. Compare your calculated intrinsic stock price with the current market
price. iii. For calculation of rs, use the Capital Asset Pricing Model (CAPM).
1. You can get the values of CAPM variables from the following sources:
a. Risk-free interest rate: The proxy for risk-free return is the yield of long-term bonds issued by the Federal Government. You can get this information from the "Federal Reserve Bulletin." You may use the 10-year Treasury rate for rrf. https://www.federalreserve.gov/releases/h15/
b. Beta: Beta of the stock can be obtained from Reuters, or Yahoo Finance.
c. Market return: Use 12.1 %, which is the S&P 500 annual total returns from 1926 to 2015.
6. Conclusion: Evaluate the stock and report your recommendation on the stock (buy, sell, or hold) based on your comprehensive analyses of financial statements, company, industry, and stock valuation models.
IMPORTANT POINT – THERE IS NO RIGHT OR WRONG ANSWERS!!!!
The final report must look professional, read seamlessly from one section to the other, and show the quality and depth of the analysis. It should show clarity, persuasiveness and critical thought. Report summaries of all calculations and Excel work in the main body and place all the detailed calculations and Excel outputs in the Appendix.
a. Business Databases: Library i. Lexis-Nexis Academic
1. Full-text articles, company, industry, and financial information. Also covers legal information including full text of court cases.
ii. ABI/INFORM Complete 1. Scholarly articles in management, industry news, and company
information. iii. Business Source Complete
1. Full text coverage for over 1,120 business publications. iv. ProQuest Research Library
1. All purpose database providing full text articles on a broad range of subjects. Science, Humanities, Social Sciences, etc.
b. Federal Reserve Bulletin (Interest rates) i. Online: www.federalreserve.gov/releases/h15/data.htm ii. http://purl.access.gpo.gov/GPO/LPS23404 iii. Printed copy: Library (HG2401.A5)
1. It contains information on interest rates on Treasury securities and corporate bonds. You may find the latest Treasury rates from Wall Street Journal, Business Week and so on.
c. Russell i. Market return: For market return you can use the returns of Russell 1000
index as a proxy. Ten year average returns of this index can be obtained from the following website:
1. http://www.russell.com/Indexes/performance/calculator/calculator.a sp
d. Valueline Investment Survey i. Current issue: www.valueline.com ii. Printed copy: Library (Ref. HG 4501. V26)
1. It contains the information on credit ratings, beta, corporate tax rates, dividends, capital structures and detailed analysis of the firm and industry, etc.
e. Industry Ratios i. Morningstar.com, Reuters.com, Yahoo Finance, and so on.
f. Financial Websites i. Yahoo Finance, CNN Money, Google Finance, Zachs.com, Hoovers.com,
Marketwatch.com, Morningstar.com, and so on. g. Past Financial Statements
i. Edgar Company Search 1. http://www.sec.gov/edgar/searchedgar/companysearch.html
ii. Company’s Website
Note: You must submit the following materials in a binder for full credit.
1. Project Paper (that answers all the questions on this guideline)
2. Copy of PowerPoint presentation
3. Copy of past three years’ balance sheets and income statements
4. Printout of Excel spreadsheet containing the data and outputs (Place Excel spreadsheet and calculations in Appendix).
6. Copies of the important resources for the project (Please highlight the information to support your analysis for the paper.)
7. Email the group project report, PowerPoint, and the Excel spreadsheet work the night before the due date