accounting project
conasn
module_01financial_accounting_project_spreadsheet.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 | ||||||||
| Wal-Mart (thousands) | Vertical Analysis 2014 | Vertical Analysis 2013 | Horizontal Analysis | |||||
| Income Statement | 2014 | 2013 | 2012 | |||||
| Net Revenue (net sales) | 473,076,000 | 466,114,000 | 443,854,000 | 100% | 100% | 1% | ||
| Cost of Goods | 358,069,000 | 352,488,000 | 335,127,000 | 76% | 76% | 2% | ||
| Gross Profit | 118,225,000 | 116,674,000 | 111,823,000 | 25% | 25% | 1% | ||
| Total Operating Expenses | 91,353,000 | 88,873,000 | 85,265,000 | 19% | 19% | 3% | ||
| Earnings from Operations | 26,872,000 | 27,801,000 | 26,558,000 | 6% | 6% | -3% | ||
| Interest Expense | 2,216,000 | 2,064,000 | 2,160,000 | 0% | 0% | 7% | ||
| Net Earnings | 16,695,000 | 17,756,000 | 16,387,000 | 4% | 4% | -6% | ||
| Balance Sheet | ||||||||
| Cash/cash equivalents (cash) | 7,281,000 | 7,781,000 | 6,550,000 | 3% | 3% | -6% | 243,035,000 | |
| Short term investments | 7,670,000 | 6,805,000 | 4,047,000 | 3% | 3% | 13% | ||
| Net Accounts Receivable | 6,677,000 | 6,768,000 | 5,937,000 | 3% | 3% | -1% | ||
| Inventory | 44,858,000 | 43,803,000 | 40,714,000 | 18% | 18% | 2% | ||
| Current Assets | 61,185,000 | 59,940,000 | 54,975,000 | 25% | 25% | 2% | ||
| Net Fixed Assets | 115,364,000 | 113,929,000 | 109,603,000 | 47% | 48% | 1% | ||
| Total Assets | 243,035,000 | 239,026,000 | 193,406,000 | 100% | 100% | 2% | ||
| Current Liabilities | 69,345,000 | 71,818,000 | 62,300,000 | 34% | 35% | -3% | ||
| Long Term Liabilities | 45,874,000 | 43,981,000 | 45,862,000 | 22% | 22% | 4% | ||
| Total liabilities | 128,496,000 | 126,762,000 | 122,091,000 | 63% | 62% | 1% | ||
| Stockholders Equity | 76,255,000 | 76,343,000 | 71,315,000 | 37% | 38% | -0% | ||
| Total liabilities + Stockholders Equity | 204,751,000 | 203,105,000 | 193,406,000 | 100% | 100% | 1% | ||
| Prepared by your name | ||||||||
| Select numbers taken from financial statements | ||||||||
| Include the source here | ||||||||
| Optional Information | ||||||||
| Any Preferred Stock | ||||||||
| Dividends paid Common shareholder | ||||||||
| Dividents paid to Preferred shareholder | ||||||||
| Outstanding Shares Common Stock | ||||||||
| Basic EPS (Earnings Per Share) | 5.07 | |||||||
| Auditing Firm | Ernst & Young LLP | |||||||
Financial Ratios
| Table II | ||||||
| Wal-Mart | Company* | Company* | Publix | |||
| Formula | 2014 | 2013 | 2014 | |||
| Liquidity Ratios | 1. Complete the formula column. See example for gross profit percent. | |||||
| Current Ratio | Current Assets/Current Liabilities | 88% | 83% | 138% | 2. Use the numbers on the previous page to complete the ratios-Use Excel formulas. | |
| Acid Test Ratio | (Cash + AR + Short Term Investments) / Current Liabilities | 31% | 30% | 73% | 3. 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. | |||||
| Inventory Turnover | Cost of Goods Sold / Average Inventory | 8% | 8% | 19% | You do not need to calculate the competitor ratios, you can use pre-calculated ratios | |
| just remember to cite your source. (like a database) | ||||||
| Solvency Ratios | ||||||
| Debt ratio | Total Liabilities / Total Assets | 53% | 53% | 43% | ||
| Times Interest Earned Ratio | (Income before Interest and Taxes) / Interest Expense | 7% | 8% | 6% | ||
| Profitability Ratios | ||||||
| Gross Profit Percent | Gross profit/net revenue | 25% | 25% | 27% | ||
| Return on Net Sales | [Net Income (Before Interest and Tax)] / Sales | 3% | 3% | 5% | ||
| EPS** | nasdaq.com/wamart | $4.88 | $5.02 | |||
| Market Analysis | ||||||
| Price Earning Ratio** | ||||||
| Dividend Yield** | ||||||
| 2.21% | 2.40% | |||||
| *Calculated by Author | ||||||
| ** Information from give souce | ||||||
| http://www.bloomberg.com/research/stocks/financials/ratios.asp?ticker=PUSH | ||||||
| Prepared by |
