Accounting
Canberk
Ratioanalysis4.xlsInput
| [Company Name] | |||||||
| Ratio Analysis | |||||||
| Input Worksheet | |||||||
| [Date] | |||||||
| Gray cells will be calculated for you. You do not need to enter anything into them. | |||||||
| Line Item | Year 1 | Year 2 | Year 3 | ||||
| Current assets | 45,000 | 46,000 | 46,500 | ||||
| Fixed assets | 80,000 | 80,000 | 80,000 | ||||
| Total assets | 125,000 | 126,000 | 126,500 | ||||
| Average total assets | 125,000 | 125,500 | 126,250 | ||||
| Cash and cash equivalents | 15,000 | 18,000 | 16,500 | ||||
| Inventory | 15,000 | 18,000 | 16,500 | ||||
| Average inventory | 15,000 | 16,500 | 17,250 | ||||
| Current liabilities | 23,000 | 25,000 | 22,500 | ||||
| Total liabilities | 125,000 | 125,000 | 125,000 | ||||
| Owners' equity | 28,000 | 30,900 | 32,000 | ||||
| Number of common shares | 25,000 | 25,000 | 25,000 | ||||
| Average number of common shares | 25,000 | 25,000 | 25,000 | ||||
| Average owners' equity | 28,000 | 29,450 | 31,450 | ||||
| Market price per share | 10.00 | 10.00 | 10.00 | ||||
| Cash flow | 175,000 | 186,000 | 169,000 | ||||
| Cash flow per share | 7.00 | 7.44 | 6.76 | ||||
| Dividends paid | 5,000 | 5,000 | 5,000 | ||||
| Total sales | 145,000 | 156,000 | 135,600 | ||||
| Operating expenses | 68,000 | 68,000 | 68,000 | ||||
| Operating income | 77,000 | 88,000 | 67,600 | ||||
| Advertising expense | 18,000 | 18,000 | 18,000 | ||||
| Marketing expense | 11,000 | 11,000 | 11,000 | ||||
| Earnings before interest and taxes | 132,000 | 127,000 | 114,500 | ||||
| Interest expense | 24,000 | 24,000 | 24,000 | ||||
| Net income | 89,000 | 87,000 | 95,000 | ||||
| Total loan | 25,000 | 24,000 | 23,000 | ||||
| Value of collateral or property | 65,000 | 65,000 | 65,000 |
Liquidity
| [Company Name] | |||||||||||||||||||||||
| Ratio Analysis | |||||||||||||||||||||||
| Liquidity Ratios | Year 1 | Year 2 | Q2 | Year 3 | |||||||||||||||||||
| Definition: | |||||||||||||||||||||||
| Current Ratio | = | Current Assets | 1.96 | = | $45,000 | 1.84 | = | $46,000 | 2.07 | = | $46,500 | ||||||||||||
| Current Liabilities | $23,000 | $25,000 | $22,500 | ||||||||||||||||||||
| Industry Average | 2.00 | 2.00 | 2.00 | ||||||||||||||||||||
| Variance | (0.04) | (0.16) | 0.07 | ||||||||||||||||||||
| Quarterly Growth/Decline | (0.12) | 0.23 | |||||||||||||||||||||
| Quick Ratio | = | Current Assets – Inventory | 1.30 | = | $45,000 | - | $15,000 | 1.12 | = | $46,000 | - | $18,000 | 1.33 | = | $46,500 | - | $16,500 | ||||||
| Current Liabilities | $23,000 | $25,000 | $22,500 | ||||||||||||||||||||
| Industry Average | 7.00 | 7.00 | 7.30 | ||||||||||||||||||||
| Variance | (5.70) | (5.88) | (5.97) | ||||||||||||||||||||
| Quarterly Growth/Decline | (0.18) | 0.21 | |||||||||||||||||||||
| Net Working Capital Ratio | = | Current Assets – Current Liabilities | 0.18 | = | $45,000 | - | $23,000 | 0.17 | = | $46,000 | - | $25,000 | 0.19 | = | $46,500 | - | $22,500 | ||||||
| Total Assets | $125,000 | $126,000 | $126,500 | ||||||||||||||||||||
| Industry Average | 2.00 | 2.00 | 2.00 | ||||||||||||||||||||
| Variance | (1.82) | (1.83) | (1.81) | ||||||||||||||||||||
| Quarterly Growth/Decline | (0.01) | 0.02 | |||||||||||||||||||||
| Current Liabilities to Inventory Ratio | = | Current Liabilities | 1.53 | = | $23,000 | 1.39 | = | $25,000 | 1.36 | = | $22,500 | ||||||||||||
| Inventory | $15,000 | $18,000 | $16,500 | ||||||||||||||||||||
| Industry Average | 2.00 | 2.00 | 2.00 | ||||||||||||||||||||
| Variance | (0.47) | (0.61) | (0.64) | ||||||||||||||||||||
| Quarterly Growth/Decline | (0.14) | (0.03) | |||||||||||||||||||||
| Cash Ratio | = | Cash and Cash Equivalents | 0.65 | = | $15,000 | 0.72 | = | $18,000 | 0.73 | = | $16,500 | ||||||||||||
| Current Liabilities | $23,000 | $25,000 | $22,500 | ||||||||||||||||||||
| Industry Average | 2.00 | 2.00 | 2.00 | ||||||||||||||||||||
| Variance | (1.35) | (1.28) | (1.27) | ||||||||||||||||||||
| Quarterly Growth/Decline | 0.07 | 0.01 | |||||||||||||||||||||
| Operating Ratio | = | Operating Expenses | 0.88 | = | $68,000 | 0.77 | = | $68,000 | 1.01 | = | $68,000 | ||||||||||||
| Operating Income | $77,000 | $88,000 | $67,600 | ||||||||||||||||||||
| Industry Average | 2.00 | 2.00 | 2.00 | ||||||||||||||||||||
| Variance | (1.12) | (1.23) | (0.99) | ||||||||||||||||||||
| Quarterly Growth/Decline | (0.11) | 0.23 | |||||||||||||||||||||
| The following calculations can be used for any expense line item or grouping of expense line items: | |||||||||||||||||||||||
| Advertising Expense to Sales Ratio | = | Advertising Expense | 0.12 | = | $18,000 | 0.12 | = | $18,000 | 0.13 | = | $18,000 | ||||||||||||
| Total Sales | $145,000 | $156,000 | $135,600 | ||||||||||||||||||||
| Industry Average | 2.00 | 2.00 | 2.00 | ||||||||||||||||||||
| Variance | (1.88) | (1.88) | (1.87) | ||||||||||||||||||||
| Quarterly Growth/Decline | (0.01) | 0.02 | |||||||||||||||||||||
| Marketing Expense | = | Marketing Expense | 0.08 | = | $11,000 | 0.07 | = | $11,000 | 0.08 | = | $11,000 | ||||||||||||
| to Sales Ratio | Total Sales | $145,000 | $156,000 | $135,600 | |||||||||||||||||||
| Industry Average | 2.00 | 2.00 | 2.00 | ||||||||||||||||||||
| Variance | (1.92) | (1.93) | (1.92) | ||||||||||||||||||||
| Quarterly Growth/Decline | (0.01) | 0.01 |
Assets
| [Company Name] | |||||||||||||||||
| Ratio Analysis | |||||||||||||||||
| Asset Ratios | Year 1 | Year 2 | Year 3 | ||||||||||||||
| Definition: | |||||||||||||||||
| Inventory Turnover Ratio | = | Total Sales | 9.67 | = | $145,000 | 9.45 | = | $156,000 | 7.86 | = | $135,600 | ||||||
| Average Inventory | $15,000 | $16,500 | $17,250 | ||||||||||||||
| Industry Average | 6.00 | 6.00 | 6.00 | ||||||||||||||
| Variance | 3.67 | 3.45 | 1.86 | ||||||||||||||
| Quarter Growth/Decline | (0.21) | (1.59) | |||||||||||||||
| Fixed Assets Turnover Ratio | = | Total Sales | 1.81 | = | $145,000 | 1.95 | = | $156,000 | 1.70 | = | $135,600 | ||||||
| Fixed Assets | $80,000 | $80,000 | $80,000 | ||||||||||||||
| Industry Average | 2.00 | 2.00 | 2.00 | ||||||||||||||
| Variance | (0.19) | (0.05) | (0.31) | ||||||||||||||
| Quarter Growth/Decline | 0.14 | (0.26) | |||||||||||||||
| Total Assets Ratio | = | Total Sales | 1.16 | = | $145,000 | 1.24 | = | $156,000 | 1.07 | = | $135,600 | ||||||
| Total Assets | $125,000 | $126,000 | $126,500 | ||||||||||||||
| Industry Average | 2.00 | 2.00 | 2.00 | ||||||||||||||
| Variance | (0.84) | (0.76) | (0.93) | ||||||||||||||
| Quarter Growth/Decline | 0.08 | (0.17) | |||||||||||||||
| Asset to Equity Ratio | = | Total Assets | 4.46 | = | $125,000 | 4.08 | = | $126,000 | 3.95 | = | $126,500 | ||||||
| Owners' Equity | $28,000 | $30,900 | $32,000 | ||||||||||||||
| Industry Average | 2.00 | 2.00 | 2.00 | ||||||||||||||
| Variance | 2.46 | 2.08 | 1.95 | ||||||||||||||
| Quarter Growth/Decline | (0.39) | (0.12) |
Profitability
| [Company Name] | |||||||||||||||||
| Ratio Analysis | |||||||||||||||||
| Profitability Ratios | Year 1 | Year 2 | Year 3 | ||||||||||||||
| Definition: | |||||||||||||||||
| Return on Assets Ratio | = | Net Income | 0.71 | = | $89,000 | 0.69 | = | $87,000 | 0.75 | = | $95,000 | ||||||
| Average Total Assets | $125,000 | $125,500 | $126,250 | ||||||||||||||
| Industry Average | 2.00 | 2.00 | 2.00 | ||||||||||||||
| Variance | (1.29) | (1.31) | (1.25) | ||||||||||||||
| Quarter Growth/Decline | (0.02) | 0.06 | |||||||||||||||
| Return on Equity Ratio | = | Net Income | 3.18 | = | $89,000 | 2.95 | = | $87,000 | 3.02 | = | $95,000 | ||||||
| Average Owners' Equity | $28,000 | $29,450 | $31,450 | ||||||||||||||
| Industry Average | 2.00 | 2.00 | 2.00 | ||||||||||||||
| Variance | 1.18 | 0.95 | 1.02 | ||||||||||||||
| Quarter Growth/Decline | (0.22) | 0.07 | |||||||||||||||
| Profit Margin Ratio | = | Net Income | 0.61 | = | $89,000 | 0.56 | = | $87,000 | 0.70 | = | $95,000 | ||||||
| Total Sales | $145,000 | $156,000 | $135,600 | ||||||||||||||
| Industry Average | 2.00 | 2.00 | 2.00 | ||||||||||||||
| Variance | (1.39) | (1.44) | (1.30) | ||||||||||||||
| Quarter Growth/Decline | (0.06) | 0.14 | |||||||||||||||
| Basic Earnings Power Ratio | = | Earnings Before Interest and Taxes | 1.06 | = | $132,000 | 1.01 | = | $127,000 | 0.91 | = | $114,500 | ||||||
| Total Assets | $125,000 | $126,000 | $126,500 | ||||||||||||||
| Industry Average | 2.00 | 2.00 | 2.00 | ||||||||||||||
| Variance | (0.94) | (0.99) | (1.09) | ||||||||||||||
| Quarter Growth/Decline | (0.05) | (0.10) | |||||||||||||||
| Earnings per Share Ratio | = | Net Income | 3.56 | = | $89,000 | 3.48 | = | $87,000 | 3.80 | = | $95,000 | ||||||
| Average Number of Common Shares | $25,000 | $25,000 | $25,000 | ||||||||||||||
| Industry Average | 2.00 | 2.00 | 2.00 | ||||||||||||||
| Variance | 1.56 | 1.48 | 1.80 | ||||||||||||||
| Quarter Growth/Decline | (0.08) | 0.32 |
Debt
| [Company Name] | |||||||||||||||||
| Ratio Analysis | |||||||||||||||||
| Debt Ratios | Year 1 | Year 2 | Year 3 | ||||||||||||||
| Definition: | |||||||||||||||||
| Total Debt Ratio | = | Total Liabilities | 1.00 | = | $125,000 | 0.99 | = | $125,000 | 0.99 | = | $125,000 | ||||||
| Total Assets | $125,000 | $126,000 | $126,500 | ||||||||||||||
| Industry Average | 2.00 | 2.00 | 2.00 | ||||||||||||||
| Variance | (1.00) | (1.01) | (1.01) | ||||||||||||||
| Quarter Growth/Decline | (0.01) | (0.00) | |||||||||||||||
| Interest Coverage Ratio | = | Earnings Before Interest and Taxes | 5.50 | = | $132,000 | 5.29 | = | $127,000 | 4.77 | = | $114,500 | ||||||
| Interest Expense | $24,000 | $24,000 | $24,000 | ||||||||||||||
| Industry Average | 2.00 | 2.00 | 2.00 | ||||||||||||||
| Variance | 3.50 | 3.29 | 2.77 | ||||||||||||||
| Quarter Growth/Decline | (0.21) | (0.52) | |||||||||||||||
| Debt/Equity Ratio | = | Total Liabilities | 4.46 | = | $125,000 | 4.05 | = | $125,000 | 3.91 | = | $125,000 | ||||||
| Owners' Equity | $28,000 | $30,900 | $32,000 | ||||||||||||||
| Industry Average | 2.00 | 2.00 | 2.00 | ||||||||||||||
| Variance | 2.46 | 2.05 | 1.91 | ||||||||||||||
| Quarter Growth/Decline | (0.42) | (0.14) | |||||||||||||||
| Loan to Value Ratio | = | Total Loan | 0.38 | = | $25,000 | 0.37 | = | $24,000 | 0.35 | = | $23,000 | ||||||
| Value of Collateral or Property | $65,000 | $65,000 | $65,000 | ||||||||||||||
| Industry Average | 2.00 | 2.00 | 2.00 | ||||||||||||||
| Variance | (1.62) | (1.63) | (1.65) | ||||||||||||||
| Quarter Growth/Decline | (0.02) | (0.02) |
Market
| [Company Name] | |||||||||||||||||
| Ratio Analysis | |||||||||||||||||
| Market Ratios | Year 1 | Year 2 | Year 3 | ||||||||||||||
| Definition: | |||||||||||||||||
| Earnings per Share | = | Net Income | $3.56 | = | $89,000 | $3.48 | = | $87,000 | $3.80 | = | $95,000 | ||||||
| (EPS) Ratio | Average Number of Common Shares | 25,000 | 25,000 | 25,000 | |||||||||||||
| Industry Average | 2.00 | 2.00 | 2.00 | ||||||||||||||
| Variance | 1.56 | 1.48 | 1.80 | ||||||||||||||
| Quarter Growth/Decline | (0.08) | 0.32 | |||||||||||||||
| Price to Earnings Ratio | = | Market Price per Share | 2.81 | = | $10.00 | 2.87 | = | $10.00 | 2.63 | = | $10.00 | ||||||
| Earnings per Share | $3.56 | $3.48 | $3.80 | ||||||||||||||
| Industry Average | 2.00 | 2.00 | 2.00 | ||||||||||||||
| Variance | 0.81 | 0.87 | 0.63 | ||||||||||||||
| Quarter Growth/Decline | 0.06 | (0.24) | |||||||||||||||
| Price to Cash Flow Ratio | = | Market Price per Share | 1.43 | = | $10.00 | 1.34 | = | $10.00 | 1.48 | = | $10.00 | ||||||
| Cash Flow per Share | $7.00 | $7.44 | $6.76 | ||||||||||||||
| Industry Average | 2.00 | 2.00 | 2.00 | ||||||||||||||
| Variance | (0.57) | (0.66) | (0.52) | ||||||||||||||
| Quarter Growth/Decline | (0.08) | 0.14 | |||||||||||||||
| Payout Ratio | = | Dividends Paid | 0.06 | = | $5,000 | 0.06 | = | $5,000 | 0.05 | = | $5,000 | ||||||
| Net Income | $89,000 | $87,000 | $95,000 | ||||||||||||||
| Industry Average | 2.00 | 2.00 | 2.00 | ||||||||||||||
| Variance | (1.94) | (1.94) | (1.95) | ||||||||||||||
| Quarter Growth/Decline | 0.00 | (0.00) |
