Done in and Explained in Excel
| Hi I need this assignment done, completed and in Excel, also showing step by step of how you got the answers. | ||||||||||||||||||||||
| Balance Sheet | 2012 | 2013 | 2014 | 1. What is the free cash flow for 2014? | ||||||||||||||||||
| Cash | $9,000 | $7,282 | $14,000 | |||||||||||||||||||
| Short-term investments | 48,600 | 20,000 | 71,632 | 2. Suppose Congress changed the tax laws so that Berndt’s depreciation expenses doubled. No changes in operations occurred. What would happen to reported profit and to net cash flow? | ||||||||||||||||||
| Accounts receivable | 351,200 | 632,160 | 878,000 | |||||||||||||||||||
| Inventories | 715,200 | 1,287,360 | 1,716,480 | 3. Calculate the 2014 current and quick ratios based on the projected balance sheet and income statement data. What can you say about the company’s liquidity position in 2013? | ||||||||||||||||||
| Total current assets | $1,124,000 | $1,946,802 | $2,680,112 | |||||||||||||||||||
| Gross fixed assets | 491,000 | 1,202,950 | 1,220,000 | 4. Use the extended DuPont equation to provide a summary and overview of company’s financial condition as projected for 2014. What are the firm’s major strengths and weaknesses? | ||||||||||||||||||
| Less: Accumulated depreciation | 146,200 | 263,160 | 383,160 | |||||||||||||||||||
| Net fixed assets | $344,800 | $939,790 | $836,840 | |||||||||||||||||||
| Total assets | $1,468,800 | $2,886,592 | $3,516,952 | |||||||||||||||||||
| Liabilities and Equity | ||||||||||||||||||||||
| Accounts payable | $145,600 | $324,000 | $359,800 | |||||||||||||||||||
| Notes payable | 200,000 | 720,000 | 300,000 | |||||||||||||||||||
| Accruals | 136,000 | 284,960 | 380,000 | |||||||||||||||||||
| Total current liabilities | $486,600 | $1,328,960 | $1,039,800 | |||||||||||||||||||
| Long-term debt | 323,432 | 1,000,000 | 500,000 | |||||||||||||||||||
| Common stock (100,000 shares) | 460,000 | 460,000 | 1,680,936 | |||||||||||||||||||
| Retained earnings | 203,768 | 97,632 | 296,216 | |||||||||||||||||||
| Total equity | $663,768 | $557,632 | $1,977,152 | |||||||||||||||||||
| Toltal liabilities and equity | $1,468,800 | $2,886,592 | $3,516,952 | |||||||||||||||||||
| Income Statements | ||||||||||||||||||||||
| Sales | $3,432,000 | $5,834,400 | $7,035,600 | |||||||||||||||||||
| Cost of goods sold except depr. | 2,864,000 | 4,980,000 | 5,800,000 | |||||||||||||||||||
| Depreciation and amortization | 18,900 | 116,960 | 120,000 | |||||||||||||||||||
| Other expenses | 340,000 | 720,000 | 612,960 | |||||||||||||||||||
| Total operating costs | $3,222,900 | $5,816,960 | $6,532,960 | |||||||||||||||||||
| EBIT | $209,100 | $17,440 | $502,640 | |||||||||||||||||||
| Interest expense | 62,500 | 176,000 | 80,000 | |||||||||||||||||||
| EBT | $146,600 | ($158,560) | $422,640 | |||||||||||||||||||
| Taxes (40%) | 58,640 | -63,424 | 169,056 | |||||||||||||||||||
| Net Income | $87,960 | ($95,136) | $253,584 | |||||||||||||||||||
| Other Data | 2012 | 2013 | 2014 | |||||||||||||||||||
| Stock price | $8.50 | $6.00 | $12.17 | |||||||||||||||||||
| Shares outstanding | 100,000 | 100,000 | 250,000 | |||||||||||||||||||
| EPS | $0.88 | ($0.95) | $1,104 | |||||||||||||||||||
| DPS | $0.22 | 0.21 | 0.22 | |||||||||||||||||||
| Tax rate | 40% | 40% | 40% | |||||||||||||||||||
| Book value per share | $6.64 | $5.58 | $7.909 | |||||||||||||||||||
| Lease payments | $40,000 | $40,000 | $40,000 | |||||||||||||||||||
| Ratio Analysis | 2012 | 2013 | Industry Average | |||||||||||||||||||
| Current | 2.3 | 1.5 | 2.7 | |||||||||||||||||||
| Quick | 0.8 | 0.5 | 1.0 | |||||||||||||||||||
| Inventory turnover | 4 | 4 | 6.1 | |||||||||||||||||||
| Days sales outstanding | 37.3 | 39.6 | 32.0 | |||||||||||||||||||
| Fixed assets turnover | 10 | 6.2 | 7.0 | |||||||||||||||||||
| Total assets turnover | 2.3 | 2 | 2.5 | |||||||||||||||||||
| Debt ratio | 35.60% | 59.60% | 32.0% | |||||||||||||||||||
| Liabilities - to - assets ratio | 54.80% | 80.70% | 50.0% | |||||||||||||||||||
| TIE | 3.3 | 0.1 | 6.2 | |||||||||||||||||||
| EBITDA coverage | 2.6 | 0.8 | 8.0 | |||||||||||||||||||
| Profit margin | 2.60% | -1.6% | 3.6% | |||||||||||||||||||
| Basic earning power | 14.20% | 0.60% | 17.8% | |||||||||||||||||||
| ROA | 6.00% | -3.3% | 9.0% | |||||||||||||||||||
| ROE | 13.30% | -17.1% | 17.9% | |||||||||||||||||||
| Price / Earnings (P/E) | 9.7 | -6.3 | 16.2 | |||||||||||||||||||
| Price / Cash flow | 8 | 27.5 | 7.6 | |||||||||||||||||||
| Market / Book | 1.3 | 1.1 | 2.9 | |||||||||||||||||||
9 years ago 30
Answer(0)
Bids(0)
other Questions(10)