This course requires you to complete an Annual Report Project. The primary purpose of this project is to analyze a company’s financial statements using trend and ratio analysis. The secondary purpose of the project is to demonstrate your ability to follow directions, use Excel and Word, and write a professional report. For the project you may choose either a United States based manufacturing company or a merchandising company. You will be working on this project throughout the semester as it is worth 20% of your course grade.
Note: in Part 3 students are require to calculate ratios for three years. Since some ratios require a beginning and ending number that means that you will need four years’ of data to calculate all ratios for three years. To get four years of data you will need to use the financial statements from three years of 10-K filings. This is due to that fact that each financial statement contains more than one year’s worth of data. The Income Statement and Statement of Cash Flows contain three years of data and the Balance Sheet contains two years of data.
The project is divided into four parts:
Select a company you would like to analyze.
The company must be public (you cannot get the financial statements for private companies).
The company must be based in the United States.
The company must be either a manufacturing or a merchandising company. Note: it is okay to submit the name of your company to your instructor for approval at this point if you have any reservation about whether or not the company you wish to use will be approved for use in this project.
Using the Security and Exchange Commission’s Electronic Data Gathering and Retrieval (EDGAR) database obtain the company’s SEC 10-K filings for the past three years.
First, search for your company using either the Google Finance or Yahoo Finance search engine.
Once you have found your company note the company’s stock symbol.
Using the EDGAR (http://www.sec.gov/edgar/searchedgar/companysearch.html) Fast Search, enter your company’s stock (ticker) symbol and find your company.
After finding your company enter “10-K” in the Filing Type box and search for the company’s annual 10-K filings. These will be displayed in chronological order from the most recent to the oldest.
Locate the company’s most recent 10-K and click on the “Documents” tab. You will be presented with the company’s complete 10-K listing for the year.
Select the Document whose description is Form 10-K and open it by double clicking on the red file link. This will open the company’s 10-K report.
Search the document for the company’s Consolidated Income Statement (Note: it may not be titled exactly that). If you can’t find this in the document it means you’ve got the wrong document. Go back to 2f and look instead for a document whose type is “EX-13”. Open that document and you should find the company’s Consolidated Income Statement
Save the company’s 10-K report (and the EX-13 document if necessary) to a pdf file by choosing to print it in PDF format using the file name COMPANY_Annual Report_YEAR (and COMPANY_ Financial Statements_YEAR if necessary). This will require that a pdf printer be installed on your computer. If you do not have a pdf printer installed on your computer see the instructions for saving, combining, and printing pdf files (Choosing and Installing a pdf Printer) in the Resources Section. Pay particular attention to making sure you correctly identify the year. It’s easy to mistake the year if the company’s fiscal year ends in January or February. For example, if the information on the front page of the company’s 10-K says “for Fiscal Year ending January 31, 2014” that’s the company’s 2013 year.
Repeat the above procedure for the company’s two previous 10-K filings (and EX-13 documents if necessary). Note that the information needed for your project will come from three different 10-K filings, the most current one and the previous two years.
Using your pdf print software save the required financial statements.
Income Statement
You will need to print out the Consolidated Income Statement from the company’s current and previous 10-K filings. Print the most recent year’s Income Statement first, then print the company’s previous year’s Income Statement to the same pdf file. Name the file COMPANY_ Financial Statements_YOUR LAST NAME-YOUR FIRST NAME.pdf.
Balance Sheet
From the same files you found the Consolidated Income Statement reports in, you will need to print the Consolidated Balance Sheet. These should be printed to the same pdf file as you used for the Income Statements. You will print three Consolidated Balance Sheets, first the most recent year, then the previous year, and then the year prior to that.
Statement of Cash Flows
Repeat the procedure you followed in step 3b but print the company’s Consolidated Statements of Cash Flows. Note that you only need to print the Statement of Cash Flows for the most recent and prior year.
Format of Financial Statement pdf File
When you finish with step 3 you should have one pdf file called COMPANY_ Financial Statements_YOUR LAST NAME-YOUR FIRST NAME.pdf with the following seven financial statements printed in the following order:
Most Recent Income Statement
Prior Year’s Income Statement
Most Recent Balance Sheet
Prior Year’s Balance Sheet
Previous Year’s Balance Sheet
Most Recent Cash Flow Statement
Prior Year’s Cash Flow Statement
Note: Do not proceed with Part 2 until the instructor has approved the company you choose for use in this project. Failure to wait for approval may result in having to redo your work if your company is not approved.
In Part 1 you selected your company and gathered the necessary financial statements for your analysis. In Part 2 you will perform a horizontal (trend) analysis of both the company’s Income Statements and Balance Sheets. You will submit Part 2 as an Excel file using the following naming convention: Annual Report Project_YOUR LAST NAME_YOUR FIRST NAME.xlsx Note that the file should be in Excel 2010/2013 format. If you don’t have either Excel 2010 or Excel 2013 you can download the latest version of Microsoft Office free at www.iuware.iu.edu.
The workbook should be set up with the following tabs:
Income Statement
Trend AnalysisIS
Balance Sheet
Trend AnalysisBS
Each of the four worksheets needs to be able to be printed on one page (portrait orientation) with no horizontal scaling (scaling is the process of automatically printing a spreadsheet so that it fits of one page). All percentages should be presented with one decimal place and all dollar amounts should be rounded to the nearest whole dollar. Income Statement Tab
In the first worksheet of your workbook you will duplicate the company’s Consolidated Statements of Income as closely as possible. Note: Only include the part of the company’s Income Statement above the EPS calculations. When finished with this section you should have four years of information on one worksheet. All summing on the worksheet must be done using Excel’s SUM formula.
Trend Analysis-IS Tab
In the second worksheet you will perform a horizontal (trend) analysis on the company’s Income Statement using the oldest (fourth) year as the base year. The trend analysis must be done using Excel cell references to get the appropriate information from the Income Statement entered in the first worksheet.
Balance Sheet Tab
In the third worksheet of your workbook you will duplicate the company’s Consolidated Balance Sheets as closely as possible. When finished with this section you should have four years of information on one worksheet. All summing on the worksheet must be done using Excel’s SUM formula.
Trend Analysis-BS Tab
In the fourth worksheet you will perform a horizontal (trend) analysis on the company’s Balance Sheet using the oldest (fourth) year as the base year. The trend analysis must be done using Excel cell references to get the appropriate information from the Balance Sheet entered in the third worksheet.
Part 3 – 50 points
In Part 3 you will perform a ratio analysis using the following ratios:
This analysis will be done in a Word document using the following file designation: Annual Report Project_YOUR LAST NAME_YOUR FIRST NAME.docx Note that the file should be in Word 2010/2013 format. If you don’t have either Word 2010 or Word 2013 you can download the latest version of Microsoft Office free at www.iuware.iu.edu.
Ratio Calculation
Each of the above ratios need to be calculated for the most recent and the two prior years.
You must first show the general equation of the ratio using Word’s equation editor.
You must next show the numbers you used to calculate the ratio using Word’s equation editor.
Finally you must show the ratio. All ratios must be displayed to two decimal places. All profitability ratios and the debt ratio should be expressed as percentages to two decimal places.
Here is an example of exactly how the ratio calculations should be formatted:
Note that all eleven ratios must be calculated.
Ratio Analysis
After calculating each ratio you must write a paragraph explaining what the ratio is used generally is used for and then exactly what the ratios you calculated mean. Here is an example based on the above current ratio calculation:
The current ratio measures the firm’s ability to pay current liabilities with current assets. It is calculated by dividing the total current assets by the total current liabilities. The higher the current ratio the easier it is for the firm to pay its bills in a timely manner. However, if the ratio is too high it indicates that the firm is not correctly utilizing its current assets and short term financing. In the absence of an industry standard a good rule of thumb is to maintain a ratio of approximately 2. The company’s current ratio has fluctuated over the past three years but they have been able to maintain a ratio greater than 2 for the last 2 years indicating the company can easily pay its short-term debts with its current liabilities.
Part 4 – 100 points
In Part 1 you selected a company and gathered the required financial statements. In Part 2 you performed a trend analysis on the company’s Income Statements and Balance Sheets. In Part 3 you calculated and explained several ratios that are used to analyze the financial performance and health of a company. In Part 4 you will put everything together and write a professional report detailing the company and your assessment of its financial health. Your final submission must include the two trend analysis and the seven financial statements you gathered in Part 1 as an appendix to the report.
The final submission must be a pdf file using the following name:
Annual Report Project_YOUR LAST NAME_YOUR FIRST NAME_FINAL.pdf
The report should consist of 8 to 15 about double-spaced pages (not including the Appendix) using APA citations and organized according to the following outline:
