Assignment Overview: Financial Statement Analysis
You have been assigned to the CEO of a major investment company. The CEO keeps an eye on potential investment opportunities in several industries. She asks you to do a financial analysis for two firms in an industry the company is considering for investment expansion.
You are to research current year data for two selected firms, and use ratio analysis to compare these two major competitors in the same industry and compare each of them to the most current data for industry ratios. You are to determine whether one or both is a promising investment opportunity—whether for long term or short term investment.
You will create an Excel file with your financial comparison data. See the attached sample that compares Southwest Airlines Company with Jet Blue Airline Company.
Based on the data developed in your Excel file, you will write a report to the CEO that details your analysis and makes investment recommendations based on what your analysis concludes about the financial health of the firms.
- You may obtain financial information and the companies’ latest annual reports on the web directly from the Securities and Exchange Commission for the company website. MSN Money or Yahoo Finance also have information but in some situations it may be incomplete. Current stock price information is not available from the SEC web site.
- Industry financial ratios for your selected industry can be found on Morningstar.com, Yahoo Finance and MSN Money and http://www.bizstats.com/corporations.asp?profType=ratios .
- The 2016 R DMBA Week 3 sample answer file contains a comprehensive analysis of the financial statements of two large airline companies. It illustrates and explains how different companies succeed by focusing on different key ratios like Net Income/Sales, or Sales/Total Assets (asset turnover) or Total Assets/Common Equity (leverage). Also, it further explains how these different strategies are revealed through DuPont Analysis (a powerful technique that many companies use to devise, implement, and monitor their business strategies. It shows the source for every number on every Excel worksheet: just click on any number in any Excel worksheet and you will see where it comes from. Showing the source for every number on every Excel worksheet is very important and helpful to readers, and you should do this on all of your Excel spreadsheets.
All of the data in the example comes from SEC interactive data. Under the company name in interactive data is a small red link that says view excel document. All numbers in this document are easy to use in calculations. Data copied from some other sources may not calculate correctly in Excel.
- **Note on Ratios: Whenever you use balance sheet items to calculate ratios, it is more accurate to average the beginning-of-year and end-of-year amounts to get the average for that year. Dec 31, 2014 and Dec 31, 2015 would be beginning and end of year for 2015. Note: Averaging is NOT required to meet these assignment requirements. The example answer does not use averaging and includes most if not all possible ratios
Instructions for this assignment:
- Pick any two U.S. public companies in the same industry.
- One of the two companies can be the same company you used in Week 2.
- Create an Excel file that follows the format of the example 2016 July DMBA R Sample Week 3 Answer for 2 airlines, using the “Compare” worksheet. This file is also separately posted in this week’s content module.
- Select at least 10 of the most important financial ratios for the two companies and calculate each for the last 2 fiscal years using Excel. All calculations should be shown in the Excel file.
1. Of the 10 required ratios, calculate the 4 ratios of the DuPont Analysis . These ratios are ROE, Profit Margin on Sales, TATO and the Equity Multiplier. Select at least 4 others:
2. Evaluate short-term solvency ratios
3. Evaluate profitability in relation to revenues
4. Evaluate profitability in relation to equity
5. Evaluate long-term solvency
6. Evaluate market value ratios (not just book value ratios)
- Add notes in the Excel file, that explain in your own words how each ratio was calculated and what it means for your analysis.
- Prepare an 8-10 page written report to the CEO, in which you detail:
- What are the strengths and weaknesses of each company?
- Which is the stronger competitor?
- Is one firm the better opportunity?
- Is one or both outperforming or underperforming in the industry?.
- Is long term or short term investment better?
- summarize your findings as detailed in the Excel file,
- explain why these particular ratios were selected for comparison (their importance),
- explain the significance of the comparative analysis.
- Recommend investment actions to the CEO that are well supported by the data and your analysis.
- Cite all sources
- Format requirements for the written report are
- Meaningful report title including your name
- Short Executive summary of no more than one page
- Introduction with report purpose
- Section headings helpful to the reader
- Conclusion with key “take-away” points repeated from report body
- Reference list in APA format
- Page numbers on all pages
- submit both the Excel file and written report into your Assignment folder by the due date..
For a “how to” explanation of Financial Statement Analysis: see the attached Power Point file 2016 R June Financial Statement Analysis with notes and the Word file, “Diagram of Financial Ratios.”
2016 July DMBA R Sample Week 3 Answer file (posted in this week’s Content Module)
“Research Starters: Financial Ratios” posted in this week’s Content Module
“Basic financial accounting objective” posted in this week’s Content Module
Understanding the Cash Flow Statement
Cash Flow Ratios
Recommended Support Materials:
There are many uses for financial statement ratio analysis, especially in business strategy – for an example, see the “Strategic Control Map” developed by McKinsey & Company, the well-known international strategy consulting firm by clicking here: