How to Download and Analyze Company 10-Ks for Financial Data

Step 1: Download Excel Template

Step 1: Download this Excel Download Exceltemplate.

Step 2: Navigate to www.sec.gov (Links to an external site.). Select the "Filings" dropdown menu and the option for "Company Filing Search". Step 3: Using the search box in the middle of the page, type in the ticker symbol for the company in question. Your first company is Proctor & Gamble and the ticker symbol is PG. After you type in the ticker, select your company from the drop-down list that appears. (Do not type PG and then press enter. Wait for the dropdown list to load.) When the search result loads a page that says "EDGAR: Company Search Results", then you need to select "Classic Version" in the upper right corner. At this point, you should see a series of rows with company filings listed with the most recent filings at the top. You will see in Row 1 on the template, which annual reports (10-Ks) you need to find. Locate your first 10-K and select the blue oval that says "Interactive Data". Once the new page loads, just below the company's name in the upper left corner, you will see a link that says "View Excel Document". Click this link and you will download the full 10-K, which includes the Balance Sheet, the Income Statement, and the Statement of Cash Flows. At this point, you should just be gathering data. Download all spreadsheets needed first. See Row 1 on the Excel template. Wait to use the data until the next section on these instructions.

Step 4: Repeat step 3 to gather all needed information for Proctor & Gamble. In Row 1 on the provided template, you will see all the 10-Ks that you need to gather.

Step 5: Repeat step 3 to gather all needed information on your second company, which is Colgate Palmolive (ticker: CL). Your third company under analysis is Clorox (ticker: CLX). I have provided you with the information that you need for CLX so that you can see how this process is done. Using the Downloaded Data: Step 1: It is extremely important to understand that companies in the real world will periodically adjust their financial results. The 2018 results that were originally reported on the 2018 10-K may be changed/adjusted on the 2020 10-K. For this reason, you must use all information needed from the most recent 10-K. Then go to the next most recent 10-K and gather any information not already provided in the most recent 10-K. For example, use the 2018 Sales disclosed on the most recent (i.e., 2020 10-K), not on the original 2018 10-K. Step 2: On the template, you will see certain rows that are highlighted in blue (i.e., Operating Income, Net Income, Total Assets, etc.). You need to type a formula into each of these rows. The formulas are left in for you on CLX to help you understand the logic. The purpose of this step is so that when you adjust the statements, the major line items (i.e., Operating Income, Net Income, Total Assets, etc.) will automatically adjust too. The items listed below should all use a formula, which means this number should not be copied over in step 3. Verify that each of these lines matches the 10-K before you make adjustments. You need to use formulas in: Inventory, Net (this only applies to PG) Total Current Assets Total Assets Total Current Liabilities Total Liabilities Total Equity Total Liabilities plus Total Equity Operating Income EBT Any version of Net Income (some firms have two or three levels of Net Income on the Income Statement). Step 3: Now that you understand the process, you need to begin copying your information from the downloaded spreadsheets to the template. Watch the video below to see how easy this process can be. Check the row at the bottom of the Balance Sheet. It should say "YES" if your Balance Sheet is in balance. If it does not say "YES" then something is wrong with your spreadsheet. Special Note: You may find that PG (or another company) chooses to not list cash as a line item on the Balance Sheet. If you find this, then you need to look at the bottom row (ending cash balance) on the Statement of Cash Flows. This is an annoyance, but you are dealing with real-world data and it is sometimes a little messy and not consistent. You may also find that a company reports Interest Expense as a negative number (i.e., in parenthesis). This is just a display issue. If interest expense were negative then someone is paying the company to take out a loan. Enter the absolute value of Interest Expense. Adjustments: An analyst will make adjustments to reported financial statements. The easiest adjustment is for one-time items that are not expected to keep happening (i.e., disposition expenses, retirement plan charges, goodwill impairment, asset writeoffs, etc.). Check Column O on the Income Statement for instructions on some items that need to be made zero if they exist in a given year. Since you used formulas in step 2 of the last section, your main items of interest will automatically adjust.