Finance Project
You have been assigned a company to research (please check the excel list for the firm you are assigned to). During this quarter you are expected to analyze a firm's financial statements, stock price, WACC, and valuation. Project 1 focuses on the financial statement analysis. The name of company is Colgate Palmolive.
Project 1: Financial Statement Analysis
a) Collect financial statements.
Financial statements are available on the internet through at thehttps://finance.yahoo.com website. At this website you will put the ticker symbol for your company in the symbol box and press "go". At this point you will see a page that has financial information about your company. On the left-hand side of the screen there will be a series of options that you can choose to get more information about your company. Under the heading "financial statements" you will see an option for "income statement" and "balance sheet." You want annual statements. The financial statements for the past 3 years should be available. (Depending on the fiscal year for your company, these might be 2012, 2013, and 2014 or 2013, 2014 and 2015.)
b) Calculate the financial ratio.
Calculate the ratios listed at the end of this document from the financial statements of your company for the most recent 3 years. To do this, you will need to make a spreadsheet, (on Excel or other), that essentially copies certain lines of your various financial statements onto the spreadsheet. Then, make spreadsheet formulas to calculate each financial ratio that is required.
c) The DuPont decomposition Analysis.
Using the ratios you calculate above to conduct the DuPont Decomposition analysis for each of the three years for your company. The DuPont Decomposition is composed of
ROE = NI/Equity = NI/Sales *Sales/Assets * Assets/Equity
Therefore, you should include a total of twelve calculations (four ratios for each of the three years). You can present the ratios in the following format.
|
Year
|
ROE
|
Operation Management
|
Asset Management
|
Leverage Management
|
|
NI/Equity
|
NI/Sales
|
Sales/Assets
|
Assets/Equity
|
|
2012
|
|
|
|
|
|
2013
|
|
|
|
|
|
2014
|
|
|
|
|
d) Check information of M&A located in the company's 10k
Once you have made these calculations, read the MA&D located in the annual report, as well as the footnotes to the various financial statements. These are the pieces of information that management wants you to know, (or is required to tell you!), in addition to "the numbers."
e) Make observations.
Write a 2 page summary in which you describe what you observe about the financial ratios for your company (double spaced). And you can also discuss the most interesting and relevant changes from one year to the next from your calculations. After reading the MD&A and footnotes, you should have a good idea of why certain ratios and figures changed from one year to the next. If certain changes are not explained, try to think of reasons why the figuresmight have changed. For example, did one part of an equation change in greater proportion than another part? (i.e. did a numerator grow much faster than a denominator?) Why might that have occurred?
These observations will vary greatly depending upon the company you are analyzing. Some things you may want to consider are:
Has the company drastically increased or decreased its use of debt?
Has the company's liquidity position changed over the three years?
Has ROE been rising or falling? If so, what has contributed to this change?
What trends do you see developing in the data?
Do you see any major changes in the financial status of the company over the time period?
The goal with this project is to not only give you a chance to calculate the ratios that accountants and analysts frequently use when evaluating a company, but to also make youlook for reasons, (i.e. "drivers"), for those changes and to think of your own reasons if none are given. If you only do the calculations but do not discuss why certain changes took place, then you haven't achieved the goal of the assignment. Therefore, do your best to go past the numbers!
f) Report Submit
In the end, you will hand in: (1) your page with cogs, assets, sales etc.,. that you copied from your financial statements, (2) a sheet of your calculations, labeled so that I know which calculation is which, (3) a print out of your formulas, and (4) your 2-3 page report on those calculations. (You can print out your Excel formulas by choosing "Tools" then "Options" then the "View" tab, then under "Windows options" check the "formulas" box. This might mess up the layout of your page, so don't try to make it look pretty. Just print out the formulas, then uncheck the box to make your spreadsheet go back to normal). Keep it in mind that you are expected to be a business person in the future and your report should look professional.
Required ratio calculations, (in their simplest form), are listed below:
A. Profitability Ratios:
1. Return on Assets (net income/total assets)
2. Return on Sales, (aka profit margin percent) (net income/sales)
3. Assets-to-Equity (total assets/stockholder's equity)
4. Return on Equity (net income/stockholder's equity)
B. Efficiency Ratios:
1. Asset Turnover (sales/total assets)
2. A/R Turnover Rate (sales/accounts receivable)
3. Inventory Turnover Rate (COGS/average inventory)
4. Fixed Asset Turnover (sales/average fixed assets)
C. Leverage Ratios:
1. Debt Ratio (total liabilities/total assets)
2. Debt-to-Equity Ratio (total liabilities/stockholder's equity)
3. Times Interest Earned (earnings before interest and taxes/interest expense)
D. Liquidity Ratios:
1. Current Ratio (current assets/current liabilities)
2. Working Capital (current assets - current liabilities)
These ratios are explained in your textbook, so if you don't understand why a particular calculation is important or relevant, you can look up more information there.