Financial Ratios Assignment

Submit Assignment

• DueSundayby11:59pmPoints20Submittinga text entry box or a file upload

This project is not a group project. Your submission should represent your individual effort. In Accordance with Penn State's Academic Integrity Policies, if I suspect that your submission does not represent your individual effort, you may receive a zero on the assignment at the instructor's discretion.

NOTE: this project will likely take you 8-10 hours of effort (possibly a little more). This exercise will get you out of the standard textbook canned examples and into the real world, which can present murky data and real challenges. I have had former students talk about this project in interviews...this is where course content gets real!

You will be downloading financial statement data from the Securities and Exchange Commission (SEC). Navigate towww.sec.gov(Links to an external site.)

and select "Company Filing Search" from the "Filings" dropdown menu. You can find all regulatory filings for any publicly listed company by typing their ticker symbol in the "Fast Search" box. For this project,you will be analyzing three firms: Procter & Gamble (ticker: PG); Colgate Palmolive (ticker: CL); and Clorox (ticker: CLX).Download this Excel templatebefore you get started pulling data from the SEC's website, and leave my color coding in place.

You will repeat the same process for all three firms. Start with Procter & Gamble (ticker: PG). After you type their ticker in the Fast Search box, you will see rows of information. Ultimately, you are looking for the 10-K (the annual report) for 2018, 2016, and 2014. You will need the same reports for all three firms. When you locate the 10-K for the needed year, you will notice a blue button labeled "Interactive Data". This is a very cool, time-saving function. This feature allows you to download the financial statements into Excel. Different companies will record the same item and call it by different names. The SEC's interactive data utilizes XBRL coding that will merge together items into common titled fields. This means a smaller puzzle for the analyst to piece together. Once you have clicked the blue Interactive Data button, you will have the opportunity to select a "View Excel File" link. This will download the 10-K's data into Excel for you. You need to open the downloaded 10-K's and copy and paste the raw data into the appropriate tabs on the template that I gave you at the end of the previous paragraph.

You will be needing information from the Statement of Earnings (the Income Statement), the Consolidated Balance Sheets, and Consolidated Statement of Cash Flows (just harvest the Depreciation from this statement). The Consolidated Balance Sheet from the 2018 10-K, will give you information for 2018 and 2017. However, both the Statement of Earnings (the Income Statement) from the 2018 10-K and the Consolidated Statement of Cash Flows from the 2018 10-K will provide data from 2018, 2017,and2016. This is extremely important... companies periodically restate previously reported numbers. This is done for various reasons, but the point is that you should always use the most recent information. When you open the 10-K for 2016, you will see that the Statement of Earnings (the Income Statement) will present information for 2016, 2015, and 2014.You want to use the 2016 data as reported on the 2018 10-K because it is more recent than the 2016 10-K.Repeat this process until you have all of the necessary data in the spreadsheet for PG, CL, and CLX.

Now comes the first wave of analysisdealing with the financials that you have now aggregated in the spreadsheet template that you downloaded from these instructions. You need to add up the items on the financial statements to make sure that they are in balance (and that you understand what you are seeing). The line items listed below should all be formulas on your financial statement tables.

1. Inventory, Net (this only applies to PG)
2. Total Current Assets
3. Total Assets
4. Total Current Liabilities
5. Total Liabilities
6. Total Equity
7. Total Liabilities plus Total Equity
8. Operating Income
9. EBT
10. Any version of Net Income (some firms have two or three levels of Net Income on the Income Statement).

The goal of this process is to truly understand what comprises each section. Make sure that the output of your formula equals the correct value from the 10-K. I also use Morningstar.com as a secondary audit if something seems off. Check the Balance Sheet Double Check (highlighted in Yellow) at the bottom of each Balance Sheet. If the cell says "No" then your Balance Sheet is not in Balance and you need to audit it before moving on.Your next step on these financials is to calculate the Common Size ratios(see column C on the PG Balance Sheet as an example). Common Size Balance Sheet items are all stated as a percentage of Total Assets. For example, the Total Current Assets should be shown as a percentage of Total Assets. Google this process if you do not understand it (this was taught in FIN 301 as well). Common Size Income Statements are all stated as a percentage of Sales (also possibly called Revenue or Net Sales).

Now that you have all of the data populated in the financial statements, the sub items added up with formulas, and your common size ratios built... you are ready to move forward with actually calculating the ratios.Proceed to the Ratio Summary Tab and begin to build all of the ratios using only the formulas that I have given you in the class. EBITDA is simply EBIT plus Depreciation and Amortization. This is a non-GAAP proxy for cash flow and you want to see a steady upward trend for a really healthy company. An equity analyst would go through a process to adjust EBITDA for one-time items like goodwill impairment, restructuring charges, stock-based compensation charges, and retirement asset gains or losses. We are not adjusting EBITDA in this class (take a deep breath). Make sure that you use averaging as shown in the textbook where needed. You gathered six year's worth of financial data so that you can provide five year's worth of ratios using averaging as needed. In columns J and V you will be displaying the 5-year average for the ratios in the preceding cells.Use Net Income from Continuing Operations for all applicable ratios (if the firm has that as a line item). In Columns K and W, you need to provide a sparkline for the trend of the ratio (not including the 5-year average). Google "sparklines" if this is new to you.

You also need to Google the "Annual Report" for each company. You will find a document on the company's Investor Relations page that includes all of the financials that you already downloaded, a ton of legal disclosures and explanations, and the Manager's Discussion and Analysis (MD&A). You need to read the MD&A for each company (they are not very long). Pay attention to strategic goals, any mention of "organic" sales growth, any mentions to risks or opportunities that they are facing. Also, pay attention to any comments about Research & Development (R&D). It is not a good sign for a company to have weak organic sales growth and/or declining R&D spending.

Now you are ready to write one single-spaced paper, which should be professional in appearance and clearly labeled with headings for each topic on the rubric. You need to interpret the meaning of all ratios and comment on any thoughts that you have relative to:

1. MD&A discussion on the Annual Reports
2. Compare the current ratio for all three firms and comment on strengths and reasons for any shortfalls.
3. Compare the quick ratio for all three firms and comment on strengths and reasons for any shortfalls.
4. Discuss the trend in Working Capital for all three firms and any concerns that you may have.
5. Discuss the trend in EBITDA for all three firms and any concerns that you may have.
6. Compare the Inventory DOH for all three firms and comment on strengths and reasons for any shortfalls.
7. Compare the Receivables DOH for all three firms and comment on strengths and reasons for any shortfalls.
8. Discuss the trend in Total Asset Turnover for all three firms and any concerns that you may have.
9. Discuss the trend in Debt-to-Equity for all three firms and any concerns that you may have.
10. Compare the Interest Coverage for all three firms and comment on strengths and reasons for any shortfalls.
11. Discuss any differences between the Revenue CAGR and the Earnings CAGR. Google "CAGR" if you are unsure of what this is. If revenue and earnings are growing at margins that always change, then something is going wrong with the firm's operations. They should not be at the same percentage, but there should be a somewhat similar margin over time.
12. Compare the Profit Margin for all three firms and comment on strengths and reasons for any shortfalls. This one is HUGE.
13. Compare the Return on Assets for all three firms and comment on strengths and reasons for any shortfalls. This one is also HUGE.
14. Discuss the trend in EVA for all three firms and any concerns that you may have.
15. Discuss the trend in the DuPont ROE for all three firms and any concerns that you may have. Focus on why the firm has the ROE that it has... understand it deeply.
16. The icing on the cake: you need to make a conclusion. If you had $50,000 to invest in some combination of these firms, then which one(s) would you pick and why? This should be an in-depth discussion showing you thinking deeply about this process. A shallow response will garnish low points on this metric.

Ultimately, you need to turn in a completed version of the template that I gave to youanda Word document with the required discussions above. Make sure that you use cell referenced formulas in all cells on the spreadsheet (where applicable). If there are no formulas, then I cannot give you feedback if you were wrong somewhere. Make sure that you audit your financial statements against the 10-K and Morningstar.com as needed to make sure that you have the right value. You need all financial data (do not just copy the few items that you think that you need).