Answered step by step
Verified Expert Solution
Question
1 Approved Answer
Problem 3-29 - Spreading Financial Statements (Common sizing) Balance Sheet Just use Excel formulas to show your work. 2015 Common-Size 2015 2014 Common-Size 2014 Current
Problem 3-29 - Spreading Financial Statements ("Common sizing") | |||||
Balance Sheet | Just use Excel formulas to show your work. | ||||
2015 | Common-Size 2015 | 2014 | Common-Size 2014 | ||
Current Assets: | |||||
Cash and marketable securities | $ 75.00 | 8.24% | $ 65.00 | 8.28% | |
Accounts receivable | $ 115.00 | 12.64% | $ 110.00 | 14.01% | |
Inventory | $ 200.00 | 21.98% | $ 190.00 | 24.20% | |
Total Current Assets | $ 390.00 | 42.86% | $ 365.00 | 46.50% | |
Fixed assets | |||||
Net plant and equipment | $ 470.00 | 51.65% | $ 371.00 | 47.26% | |
Other long term assets | $ 50.00 | 5.49% | $ 49.00 | 6.24% | |
Total Fixed Assets | $ 520.00 | 57.14% | $ 420.00 | 53.50% | |
Total assets: | $ 910.00 | 100.00% | $ 785.00 | 100.00% | |
Liabilities and Owner's Equity | |||||
Current Liabilities | |||||
Accrued wages and taxes | $ 40.00 | 4.40% | $ 43.00 | 9.09% | |
Accounts payable | $ 90.00 | 9.89% | $ 80.00 | 16.91% | |
Notes payable | $ 80.00 | 8.79% | $ 70.00 | 14.80% | |
Total Current Liabilities | $ 210.00 | 23.08% | $ 193.00 | 40.80% | |
Long term debt: | $ 300.00 | 32.97% | $ 280.00 | 59.20% | |
Total Liabilities | $ 510.00 | 56.04% | $ 473.00 | 100.00% | |
Stockholder's equity: | |||||
Preferred stock | $ 5.00 | 0.55% | $ 5.00 | 0.64% | |
Common stock and paid in surplus | $ 65.00 | 7.14% | $ 65.00 | 8.28% | |
Retained earnings | $ 330.00 | 36.26% | $ 242.00 | 30.83% | |
Total Equity | $ 400.00 | 43.96% | $ 312.00 | 39.75% | |
Total liabilities and equity | $ 910.00 | 100.00% | $ 785.00 | 100.00% | |
Income Statement | |||||
2015 | 2015 Common Sized | 2014 | 2014 Common Sized | ||
Net sales | $ 515.00 | 100.00% | $ 432.00 | 100.00% | |
Less: Cost of goods sold | $ 230.00 | 44.66% | $ 175.00 | 40.51% | |
Gross Profits | $ 285.00 | 55.34% | $ 257.00 | 59.49% | |
Less:other operating expenses | $ 30.00 | 5.83% | $ 25.00 | 5.79% | |
EBITDA | $ 255.00 | 49.51% | $ 232.00 | 53.70% | |
Less: Depreciation | $ 22.00 | 4.27% | $ 20.00 | 4.63% | |
EBIT | $ 233.00 | 45.24% | $ 212.00 | 49.07% | |
Less: Interest | $ 33.00 | 6.41% | $ 30.00 | 6.94% | |
Earnings before taxes | $ 200.00 | 38.83% | $ 182.00 | 42.13% | |
Less: Taxes | $ 57.00 | 11.07% | $ 55.00 | 12.73% | |
Net Income | $ 143.00 | 27.77% | $ 127.00 | 29.40% | |
Less: Preferred stock dividends | $ 5.00 | 0.009709 | |||
Net income available to common stockholders | $ 138.00 | 0.267961 | |||
Less: common stock dividends | $ 65.00 | 0.126214 | |||
Addition to retained earnings | $ 73.00 | 0.141748 | |||
Problem 3-30 Ratios | Just use Excel formulas to show your work. | ||||
Current ratio | 1.86 | ||||
Quick ratio | 75.55 | ||||
Cash ratio | 274.05 | ||||
Inventory turnover | 1.15 | ||||
Days' sales in inventory | 317.39 | ||||
Average collection period | |||||
Average payment period | 81.50 | ||||
Fixed asset turnover | |||||
Sales to working capital | |||||
Total asset turnover | |||||
Capital intensity | |||||
Debt ratio | |||||
Debt-to-equity | |||||
Equity multiplier | |||||
Times interest earned | |||||
Cash coverage | |||||
Profit margin | |||||
Gross profit margin | |||||
Operating profit margin | |||||
Basic earnings power | |||||
ROA | |||||
ROE | |||||
Dividend payout | |||||
Market-to-book ratio | |||||
PE ratio
| |||||
I need assistance with the ratios as i have to show the work in excel..... Please show the formulas to arrive at the correct ratios |
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started