FIIN
whbyry
finacial_ratios.docxYou will be working with one website for this project (www.sec.gov (Links to an external site.)). Navigate to this website and under the “Filings” dropdown menu, select “Company Filings Search.” You will begin by typing the ticker symbol for a stock into the box that says “ticker” on the right side of the screen underneath the heading “Fast Search.” The first company that you will work with is Coca Cola (ticker symbol = KO). Type “KO” into the box and click “search.” You should now see all of Coca Cola’s regulatory filings with the SEC using the online database known as EDGAR. You are looking for the 10-K report from early 2016 (2015 data). The 10-K is the annual financial statements. Scroll down until you find it (HINT: it should be in the month of February for Coca Cola). Click on the blue link that says “Interactive Data.” This will take you to a very special place. If you click on “Financial Statements” on the left navigation pane, then you will have access to Excel downloadable financial statements, which are organized by XBRL taxonomy. Before your eyes glaze over, let me explain why XBRL is so helpful. It is a common coding language that all companies are required to deploy on their financial statements. When you use financial statements coded using XBRL, you can compare “Cash and Cash Equivalents” on the Balance Sheet between different companies and know that the same items are included or excluded from this category across all companies. XBRL is a process of standardizing what data goes where…it also is helpful for algorithmic traders because it can be downloaded.
You will be dealing with the “Consolidated Statements of Income”, and the “Consolidated Balance Sheets.” Once you have one of these financial statements selected, click on the red link towards the top of the page that says “View Excel Document.” This step with download the most recent 3 years of financial statements for Coca Cola.
Use your browser’s back button to navigate back to the list of available reports (where you first chose the 10-K by clicking the blue interactive data link). On the bottom of the page, you will find a link for “Next 40.” You will need to find the 10-K for the last year where you had data from the previous step. For example, if you chose the 2015 Balance Sheet, then you will have data for 2015, 2014, and 2013. You will need the 10-Ks from February 2016, 2014, and 2012 to capture all of the required 5 year’s worth of data for this assignment. Ultimately, you will have the Income Statement and the Balance Sheet downloaded for Coca Cola for the most recent 5 years . To copy the data, I strongly recommend highlighted the needed information in the cells on your downloaded spreadsheet and copying them. Then paste them into a master spreadsheet that will hold all of your data for KO (and PEP and DPS that you will read about next). When you paste, do not simply use the “CTRL + V” shortcut…you want to right click on the new Excel cell and select “Values” under the paste options. Ultimately, you do not want any “$” or “,” in your raw data or you will have problems. The “$” display in Excel is very useful for visual display, but there should not be a “$” physically typed in any cell.
You will need to turn in an Excel file with the Income Statement and the Balance Sheet for the last 5 years. Once you have this data organized neatly in your spreadsheet, compute all of the ratios listed below for KO. Then, repeat the same process for PEP (Pepsi) and for DPS (Dr. Pepper Snapple).
Ratios to compute :
1. Current Ratio
2. Quick Ratio
3. Interest Coverage (TIE Ratio)
4. Days Sales in Inventory (do not use average inventory)
5. Days Sales in Receivables (do not use average receivables)
6. Return on Assets
7. Return on Equity using the DuPont Breakdown (show each sub-component of the DuPont Breakdown)
For each ratio you must compute the value for the last 5 years for each company. Tell me what each ratio communicates about the specific company’s health on a stand-alone basis and relative to its 5-year trend. Then comment on how Coca Cola's ratios compare to those of Pepsi and Dr. Pepper Snapple. There should be 3-4 sentences in a readable MS Word Document about each ratio. Your comments should be focused on the numbers for KO, PEP, and DPS and not a general comment on the definition of a given ratio. You will receive 1.5 points for each ratio (numbers 1-6 from the numbered list above) that is calculated properly and discussed in detail from the perspective of meaning, trend, and peer comparison. You will receive 2 points for the DuPont ROE ratio (number 7 from the numbered list above) if it is calculated properly and discussed in detail from the perspective of meaning, trend, and peer comparison. These discussions should be in a small paragraph labeled for the given ratio. I want you to show me that you know what the number means to KO, PEP, and DPS. Theremaining 4 points will be for your overall conclusion of Coca Cola relative to its peers.
Do not compute ratios using average receivables or average inventory. Simply use the accounts receivables or inventory as reported for each year. Treat “Operating Income” as EBIT. Net Income should be “Net Income Attributed to Shareholders.” Be sure to use "Interest Expense" as interest expense NOT interest income.
Required Formatting:
You need to write a 1-2 page, single-spaced paper detailing what you have discovered. Your document should be in Times New Roman, 12 point font with 1-inch margins. Type your name and the assignment’s name in the Header (“Insert” tab, then select “Header” in MS Word). Do not include a heading in the body of your text to fill space…I will remove it when determining the length requirement. You should include at the top of your paper an image of the ratio summaries for all three stocks from 2015 only (google “snipping tool” if you do not know how to use it), but it needs to be no larger than 3 inches by 3 inches.
