| Module 01: fill in the numbers-this tab only. Complete PURPLE highlighted areas only. There are no ANALYSIS calculations in week 2, though you may need to calculate some of the line items per the instructions in the labels I have provided for guidance.
Module 08: Complete the Horizontal and Vertical Analysis cells in GREEN, consider completing the OPTIONAL information in the ORANGE cells, and go to the Financial Ratios tab and complete the GREEN cells there. |
| Table I |
| Tootsie Roll Industries | | | | Vertical Analysis 2015 | Vertical Analysis 2014 | Horizontal Analysis |
| Income Statement | 2015 | 2014 | 2013 | | | | Items in place already are CHECK FIGURES for you. |
| Net Revenue (Total revenue) | 540,112,000 | 543,525,000 | 543,383,000 | 100.00% | | | Formulas: |
| Cost of Goods (Product cost of goods sold) | 340,090,000 | 340,933,000 | 350,960,000 | | | -3.10% | 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. |
| Gross Profit (Total gross margin) | 196,602,000 | 201,645,000 | 191,486,000 | 36.40% | | | 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. |
| Total Operating Expenses (Sell, Marketing & Admin Exp) | 108,051,000 | 117,722,000 | 119,133,000 | | 21.66% |
| Earnings from Operations | 91,082,000 | 83,923,000 | 72,353,000 | | | 25.89% | If you double-click on any of the cells I have completed here, you will see the formulas used. Feel free to use these |
| Interest Expense | 0 | 0 | 0 | | 0.00% | | as an example as you write your own formulas for the calculations of these items. |
| Net Earnings | 66,089,000 | 62,860,000 | 60,849,000 |
| Balance Sheet |
| Cash and cash equivalents | 126,145,000 | 100,108,000 | 88,283,000 | | 11.00% |
| Short term investments (Investments) | 42,155,000 | 39,450,000 | 33,572,000 | | | | Notes & Helpers: |
| Net Accounts Receivable (Accounts receivable trade) | 51,010,000 | 43,253,000 | 40,721,000 | | | 25.27% | 1. Since the 100% figures for vertical analysis are always going to be Net Revenue and Total Assets (and thereby Total Liabilities & Equity), they are poor analysis items to choose for your analysis. |
| Inventory (Add: FG and WIP + RM & supplies lines) | 62,263,000 | 70,379,000 | 61,856,000 | 6.85% | | | 2. Horizontal and Vertical analysis measure two COMPLETELY different things, so they cannot be compared to eachother. I should NOT see language in your |
| Current Assets (Total current assets) | 293,806,000 | 264,621,000 | 240,111,000 | | | | analysis paper like "The vertical analysis of X is 40%, but the horizontal analysis of X is -2%, so the vertical is better." NONONO.Nonsensical. :o) |
| Net Fixed Assets (Net property, plant & Equipment) | 184,586,000 | 190,081,000 | 196,916,000 | | | | 3. An analysis statement for vertical analysis might read like this: |
| Total Assets | 908,983,000 | 910,386,000 | 888,409,000 | | 100.00% | | "The stockholder's equity is almost 77% of the total assets, meaning most of the assets are financed through ownership of the |
| Current Liabilities (Total current liabilities) | 72,062,000 | 64,459,000 | 60,121,000 | | | | company’s shares. This means TRI is in a good position to take on additional debt if they find the need to do so, but also means that |
| Long Term Liabilities (Total noncurrent liabilities) | 138,373,000 | 154,791,000 | 147,983,000 | | | | there may be a large number of shareholders who are looking for dividend payments on their shares, so that's a consideration that they need to be aware of." |
| Total liabilities (Add total current liab + total noncurrent) | 210,435,000 | 219,250,000 | 208,104,000 | | | 1.12% | 4. An analysis statement for a horizontal analysis might look like this: |
| Stockholders Equity (Total equity) | 698,548,000 | 691,136,000 | 680,305,000 | 76.85% | | | "Earnings from operations has increased by almost 26%, while other financial measures like Total Liabilities have increased only slightly. This shows that TRI has found |
| Total liabilities + Shareholders Equity | 908,983,000 | 910,386,000 | 888,409,000 | | | | ways to finance operations without taking on additional debt, indicating they are keeping to their goals of having low liabilities and remaining a less risky investment opportunity |
| | | | | | | | for their shareholders." |
| Prepared by your name |
| Select numbers taken from financial statements |
| Tootsie Roll Industries SEC Form 10-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 |
captdmn85
course_project_spreadsheet.xlsx
