Module 08 Financial Accounting Final Project
ceteiv
module_08_financial_accounting_project_c.xlsxVertical_Horizonal Analysis
| Week 1 fill in the numbers-this tab only. Complete blue highlighted areas only. There are no calculations in week one. | ||||||||
| Table I | ||||||||
| Coca-Cola | Fiscal Year End 12/31/14 | Fiscal Year End 12/31/13 | Fiscal Year End 12/31/12 | Vertical Analysis FY2014 | Vertical Analysis FY2013 | Horizontal Analysis FY2012 to FY2014 | COMPLETE ALL GREEN CELLS FOR COCA-COLA | |
| Income Statement (Consolidated Statements of Operations) | FY2014 | FY2013 | FY2012 | Formulas: | ||||
| Net Revenue (net sales) Roxanne Visser: Roxanne Visser: Coca-Cola: Net Operating Revenues | 45,998,000,000 | 46,854,000,000 | 48,017,000,000 | 100.00% | 100.00% | -4.20% | Vertical Analysis = Item/Net Revenue. This means "Net Revenue" is our 100% number on the income statement, Total Assets is our 100% number on the balance sheet. Everything is relative to those two items. | |
| Cost of Goods Roxanne Visser: Roxanne Visser: Coca-Cola:Cost of Goods Sold | 17,889,000,000 | 18,421,000,000 | 19,053,000,000 | Horizontal Analysis = (Current Year - Base Year)/Base Year. This shows the amount of change in an account from the designated base year to the current year. | ||||
| Gross Profit Roxanne Visser: Roxanne Visser: Coca-Cola:Gross Profit | 28,109,000,000 | 28,433,000,000 | 28,964,000,000 | 60.68% | -2.95% | |||
| Total Operating Expenses Roxanne Visser: Roxanne Visser: Coca-Cola: Add Selling, General and Administrative Expenses and Other Operating Charges | 18,401,000,000 | 18,205,000,000 | 18,185,000,000 | 1.19% | If you double-click on any of the cells I have completed here, you will see the formulas used. Feel free to use these | |||
| Earnings from Operations Roxanne Visser: Roxanne Visser: Coca-Cola:Operating Income | 9,708,000,000 | 10,228,000,000 | 10,779,000,000 | 21.11% | as an example as you write your own formulas for the calculations of these items. | |||
| Interest Expense Roxanne Visser: Roxanne Visser: Coca-Cola:Interest Expense | 483,000,000 | 463,000,000 | 397,000,000 | 0.99% | ||||
| Net Earnings Roxanne Visser: Roxanne Visser: Coca-Cola:Consolidated Net Income | 7,124,000,000 | 8,626,000,000 | 9,086,000,000 | 15.49% | -21.59% | |||
| Balance Sheet (Consolidated Statements of Financial Position) | Fiscal Year End 12/31/14 FY2014 | Fiscal Year End 12/31/13 FY2013 | Fiscal Year End 12/31/12 FY2012* | |||||
| Cash/cash equivalents (cash) Roxanne Visser: Roxanne Visser: Coca-Cola:Cash and cash equivalents | 8,958,000,000 | 10,414,000,000 | 8,442,000,000 | 11.56% | ||||
| Short term investments Roxanne Visser: Roxanne Visser: Coca-Cola:Short-Term Investments | 9,052,000,000 | 6,707,000,000 | 5,017,000,000 | |||||
| Net Accounts Receivable Roxanne Visser: Roxanne Visser: Coca-Cola:Trade Accounts Receivable | 4,466,000,000 | 4,873,000,000 | 4,759,000,000 | 5.41% | ||||
| Inventory Roxanne Visser: Roxanne Visser: Coca-Cola:Inventories | 3,100,000,000 | 3,277,000,000 | 3,624,000,000 | 3.37% | -14.46% | |||
| Current Assets Roxanne Visser: Roxanne Visser: Coca-Cola:Total Current Assets | 32,986,000,000 | 31,304,000,000 | 30,328,000,000 | 34.76% | ||||
| Net Fixed Assets Roxanne Visser: Roxanne Visser: Coca-Cola:Property, Plant and Equipment - net | 14,633,000,000 | 14,967,000,000 | 14,476,000,000 | 1.08% | ||||
| Total Assets Roxanne Visser: Roxanne Visser: Coca-Cola:Total assets | 92,023,000,000 | 90,055,000,000 | 86,174,000,000 | 100.00% | 6.79% | |||
| Current Liabilities Roxanne Visser: Roxanne Visser: Coca-Cola:Total current liabilities | 32,374,000,000 | 27,811,000,000 | 27,821,000,000 | |||||
| Long Term Liabilities Roxanne Visser: Roxanne Visser: Coca-Cola:ADD Long-Term Debt, Other Liabilities and Deferred Income Taxes | 29,088,000,000 | 28,804,000,000 | 25,185,000,000 | 31.98% | ||||
| Total liabilities Roxanne Visser: Roxanne Visser: Coca-Cola:ADD Total Current Liabilities, Long-Term Debt, Other Liabilities, and Deferred Income Taxes | 61,462,000,000 | 56,615,000,000 | 53,006,000,000 | |||||
| Stockholders Equity Roxanne Visser: Roxanne Visser: Coca-Cola:Total Equity | 30,561,000,000 | 33,440,000,000 | 33,168,000,000 | 37.13% | ||||
| Total liabilities + Stockholders Equity Roxanne Visser: Roxanne Visser: Coca-Cola:Total Liabilities and Equity | 92,023,000,000 | 90,055,000,000 | 86,174,000,000 | 100.00% | 6.79% | |||
| Prepared by your name | ||||||||
| Select numbers taken from financial statements | ||||||||
| Include the source here | ||||||||
| Optional Information | ||||||||
| Any Preferred Stock | Items in this area are COMPLETELY OPTIONAL, but as you find them in your research, you may wish | |||||||
| Dividends paid Common shareholder | to enter them for later use. | |||||||
| Dividents paid to Preferred shareholder | ||||||||
| Outstanding Shares Common Stock | ||||||||
| Basic EPS (Earnings Per Share) | ||||||||
| Auditing Firm | ||||||||
Financial Ratios
| Table II | ||||||
| Target Corporation | Company* | Company* | Competition or Industry Ratio** | COMPLETE ALL GREEN CELLS FOR COCA-COLA | ||
| Formula | FY 2014 | FY 2013 | 2014 | COMPLETE ALL BLUE CELLS FOR PEPSI-CO, OTHER COMPETITOR, or INDUSTRY AVERAGES | ||
| Liquidity Ratios | ||||||
| Current Ratio | *Current assets/current liabilities | 1.13 | 1. Use the numbers on the previous page to complete the ratios. | |||
| Acid Test Ratio | *(cash+short term investments +accounts receivable)/current liabilities | 3. One * means you should be completing the calculations | ||||
| Two ** means you should get the information from another source ( remember to cite that source) | ||||||
| Asset Management Ratio | 4. For column E choose a competitor or the Industry averages | |||||
| Inventory Turnover | *Cost of Goods Sold/Average Inventory | 8.82 | You do not need to calculate the competitor ratios, you can use pre-calculated ratios | |||
| (remember, Avg Inv is begging inv + ending inv, result divided by 2.) | just remember to cite your source. (like a database) | |||||
| Solvency Ratios | ||||||
| Debt ratio | *Total Liabilities/Total Assets | 62.87% | Formulas that result in "number of times" answers include: Current Ratio, Acid Test Ratio, Inventory Turnover, and Times Interest Earned Ratio. | |||
| Times Interest Earned Ratio | *Operating Income/Interest Expense | Formulas that result in "percentage" answer include: Debt Ratio, Gross Profit Percentage, and Return on Net Sales. | ||||
| Earnings per share results in a dollar amount answer and is located on the income statement. | ||||||
| Profitability Ratios | ||||||
| Gross Profit Percent | Gross profit/net revenue | 61.11% | ||||
| Return on Net Sales | Net Income/Net Sales | 18.41% | ||||
| EPS** | Locate in research | |||||
| Market Analysis | ||||||
| Price Earning Ratio** | Locate in research | 21.81* | Market Analysis Ratios Hint-Check the following source for information on these two items: https://ycharts.com/companies/TGT/pe_ratio | |||
| Dividend Yield** | Locate in research | 2.77%** | You should be able to use the slider-bar on the dates to find an answer close to the fiscal year end for each of these two market analysis items. | |||
| *Calculated by Author | *http://www.nasdaq.com/symbol/ko/pe-ratio | |||||
| ** Information from (give sources below OR create Reference tab) | **https://ycharts.com/companies/KO/dividend_yield | |||||
| Prepared by |
2012 Balance Sheet Info
| Source: http://www.coca-colacompany.com/content/dam/journey/us/en/private/fileassets/pdf/2014/02/2013-annual-report-on-form-10-k.pdf |
