FOR ACCOUNTING GENIUS ONLY
iropandtravel88
module_04_course_project_spreadsheet.xlsxVertical_Horizonal Analysis
| Module 02: fill in the numbers-this tab only. Complete PURPLE highlighted areas only. There are no ANALYSIS calculations in week two, though you may need to calculate some of the line items per the instructions in the labels I have provided for guidance. | |||||||
| Table I | |||||||
| Tootsie Roll Industries | Vertical Analysis 2014 | Vertical Analysis 2013 | Horizontal Analysis | ||||
| Income Statement | 2014 | 2013 | 2012 | Formulas: | |||
| Net Revenue (Total revenue) | 543,525,000 | 543,383,000 | 549,870,000 | 100.00% | -1.15% | 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 (Product cost of goods sold) | 340,933,000 | 350,960,000 | 365,573,000 | 62.73% | 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 (Total gross margin) | 201,645,000 | 191,486,000 | 183,321,000 | ||||
| Total Operating Expenses (Sell, Marketing & Admin Exp) | 117,722,000 | 119,133,000 | 113,842,000 | 21.92% | 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 | 83,923,000 | 72,353,000 | 69,479,000 | 20.79% | as an example as you write your own formulas for the calculations of these items. | ||
| Interest Expense | 0 | 0 | 0 | ||||
| Net Earnings | 62,860,000 | 60,849,000 | 52,004,000 | 11.20% | |||
| Balance Sheet | |||||||
| Cash and cash equivalents | 100,108,000 | 88,283,000 | 63,862,000 | 56.76% | |||
| Short term investments (Investments) | 39,450,000 | 33,572,000 | 18,746,000 | 4.33% | |||
| Net Accounts Receivable (Accounts receivable trade) | 43,253,000 | 40,721,000 | 42,108,000 | ||||
| Inventory (Add: FG and WIP + RM & supplies lines) | 70,379,000 | 61,856,000 | 62,383,000 | ||||
| Current Assets (Total current assets) | 264,621,000 | 240,111,000 | 197,241,000 | 27.03% | |||
| Net Fixed Assets (Net property, plant & Equipment) | 190,081,000 | 196,916,000 | 201,290,000 | ||||
| Total Assets | 910,386,000 | 888,409,000 | 846,737,000 | 100.00% | |||
| Current Liabilities (Total current liabilities) | 64,459,000 | 60,121,000 | 60,765,000 | 7.08% | |||
| Long Term Liabilities (Total noncurrent liabilities) | 154,791,000 | 147,983,000 | 136,157,000 | ||||
| Total liabilities (Add total current liab + total noncurrent) | 219,250,000 | 208,104,000 | 196,922,000 | 11.34% | |||
| Stockholders Equity (Total equity) | 691,136,000 | 680,305,000 | 649,815,000 | ||||
| Total liabilities + Shareholders Equity | 910,386,000 | 888,409,000 | 846,737,000 | ||||
| Prepared by your name | |||||||
| Select numbers taken from financial statements | |||||||
| Tootsie Roll Industries SEC Form 10-K; http://www.tootsie.com/search/?q=SEC%20Form%2010-k | |||||||
| Optional Information | |||||||
| Any Preferred Stock | |||||||
| Dividends paid Common shareholder | |||||||
| Dividents paid to Preferred shareholder | |||||||
| Outstanding Shares Common Stock | |||||||
| Basic EPS (Earnings Per Share) | |||||||
| Auditing Firm | |||||||
Financial Ratios
| Table II | ||||||
| Company | Company* | Company* | Competition or Industry Ratio** | You can use a competitor here, Hershey, Rocky Mountain, or another, or industry standards. | ||
| Formula | 2014 | 2013 | 2014 | |||
| Liquidity Ratios | 1. Formula column is completed for you. | |||||
| Current Ratio | *Current assets/current liabilities | 3.99 | ||||
| Acid Test Ratio | *(cash+short term investments +accounts receivable)/current liabilities | 2. One * means you did the calculations | ||||
| Two ** means you got the information from another source (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 | 5.16 | 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 | |||||
| Times Interest Earned Ratio | *Operating Income/Interest Expense | 0.00 | 0.00 | |||
| Formulas that result in "number of times" answers include: Current Ratio, Acid Test Ratio, Inventory Turnover, and Times Interest Earned Ratio. | ||||||
| Profitability Ratios | Formulas that result in "percentage" answer include: Debt Ratio, Gross Profit Percentage, and Return on Net Sales. | |||||
| Gross Profit Percent | Gross profit/net revenue | 35.24% | Earnings per share results in a dollar amount answer and is located on the income statement. | |||
| Return on Net Sales | Net Income/Net Sales | |||||
| EPS** | Locate in income statement, its called "Net earnings attributable to Tootsie Roll Industries, Inc. per share" | |||||
| Market Analysis | ||||||
| Price Earning Ratio** | Locate in research | 31.78 | You can locate the market analysis ratios in the Mergent database or by searching online at a site such as | |||
| Dividend Yield** | Locate in research | 1.03% | ycharts, stocks-on-the-net, or other reputable resources. Remember to cite your sources! | |||
| What you locate might differ slightly from the ones I've given here, which is completely fine. Be sure to | ||||||
| *Calculated by Author | cite your sources, and as long as your references are credible, you will be considered correct for the market analysis figures. | |||||
| ** Information from give souce | ||||||
| Prepared by |
2012 Balance Sheet Info
| Source: | https://www.sec.gov/Archives/edgar/data/98677/000110465914014392/a13-25823_2ex13.htm |
