Calculate ratios for 2013,2014, 2015, 2016, 2017show how it was calculated in ex

Question

Calculate ratios for 2013,2014, 2015, 2016, 2017show how it was calculated in excel

Market to book ratio

Quick ratio

Return on assets

Net margin

Debt to equity

Current ratio

Cash ratio

Net working capital total asses

Interval measure

Total debt ratio

Long term debt ratio

Times interest earned ratio

Cash coverage ratio

Receivable turnover

Profit margin

Inventory turnover

Debt to capital

Equity multiplier

Wacc

Cash Flow Statement 9/30/17 9/30/16 9/30/15 9/30/14 9/30/13 Cash Flow From Operations, Investments & Financial Activities Net Income (Loss) 364.48 1,427.93 -134.89 276.48 433.71 Depreciation/Amortization & Depletion 432.33 392.17 249.6 192.73 170.79 Net Change from Assets/Liabilities 467.15 1,028.21 2,769.18 435.73 -137.99 Net Cash from Discontinued Operations 0 0 0 0 145.87 Other Operating Activities 240.17 330.2 1,036.49 558.21 175.75 Net Cash From Operating Activities 1,504.14 3,178.50 3,920.38 1,463.15 788.13 Property & Equipment -466.4 -464.62 -231.59 -264.46 -201.05 Acquisition/ Disposition of Subsidiaries -49.55 -2,731.36 -2,616.25 -9.1 329.98 Investments 14.8 40.71 -86.21 -117.79 0 Other Investing Activities 3.11 -13.92 2.88 7.2 -11.67 Net Cash from Investing Activities -498.04 -3,169.18 -2,931.17 -384.16 117.26 Income Statement 9/30/17 9/30/16 9/30/15 9/30/14 9/30/13 Sales 153,144 146,850 135,962 119,569 87,959 Cost Of Goods 148,598 142,577 132,433 116,587 85,451 Gross Profit 4,546 4,273 3,529 2,982 2,508 Selling & Adminstrative & Depr. & Amort Expenses 3,486 2,699 3,112 2,203 1,609 Income After Depreciation & Amortization 1,060 1,573 417 779 898 Non-Operating Income 3 53 -44 -29 0 Interest Expense 145 140 99 77 74 Pretax Income 918 1,391 274 673 824 Income Taxes 553 -37 409 389 331 Minority Interest 0 0 0 0 0 Investment Gains/Losses 0 0 0 0 0 Other Income/Charges 0 0 0 0 0 Income From Cont. Operations 364 1,428 -135 284 493 Extras & Discontinued Operations 0 0 0 -8 -60 Net Income 364 1,428 -135 276 434 Depreciation Footnote 9/30/17 9/30/16 9/30/15 9/30/14 9/30/13 Income Before Depreciation & Amortization 1,493 1,966 667 972 1,069 Depreciation & Amortization (Cash Flow) 432 392 250 193 171 Income After Depreciation & Amortization 1,060 1,573 417 779 898 Earnings Per Share Data 9/30/17 9/30/16 9/30/15 9/30/14 9/30/13 Average Shares 221.6 225.96 217.79 235.41 235.35 Diluted EPS Before Non-Recurring Items 5.88 5.62 4.96 3.97 3.14 Diluted Net EPS 1.64 6.32 -0.62 1.17 1.84

Explanation / Answer

To calculate the above ratios we must first arrange the financials in the correct manner so that it is easier for us to calculate in excel.

So first we need to columns reaaranged to start from 9/30/2013 to 9/30/2017. This can be achieved by cut & paste commands in excel (to be used on the whole column)

It would look as below:

Please note the first row and column reflects the actual excel row number and column name. To be used when refrencing with the below answers

So after rearranging the table as above the ratios can be calculated now.

Below are the ratios followed by the standard formulae and also the formulaes used in excel to get them. Please use row 1 and column 1 to trace back the formulae.

The formulae refrence shown below is for 2017 only. It can be copy pasted to previous years to get the ratios for them.

RC C D E F G H 3 Cash Flow Statement 9/30/13 9/30/14 9/30/15 9/30/16 9/30/17 4 Cash Flow From Operations, Investments & Financial 5 Activities 6 Net Income (Loss) 433.71 276.48 -134.89 1,427.93 364.48 7 Depreciation/Amortization & Depletion 170.79 192.73 249.6 392.17 432.33 8 Net Change from Assets/Liabilities -137.99 435.73 2,769.18 1,028.21 467.15 9 Net Cash from Discontinued Operations 145.87 0 0 0 0 10 Other Operating Activities 175.75 558.21 1,036.49 330.2 240.17 11 Net Cash From Operating Activities 788.13 1,463.15 3,920.38 3,178.50 1,504.14 12 Property & Equipment -201.05 -264.46 -231.59 -464.62 -466.4 13 Acquisition/ Disposition of Subsidiaries 329.98 -9.1 -2,616.25 -2,731.36 -49.55 14 Investments 0 -117.79 -86.21 40.71 14.8 15 Other Investing Activities -11.67 7.2 2.88 -13.92 3.11 16 Net Cash from Investing Activities 117.26 -384.16 -2,931.17 -3,169.18 -498.04 17 Income Statement 9/30/13 9/30/14 9/30/15 9/30/16 9/30/17 18 Sales 87,959 119,569 135,962 146,850 153,144 19 Cost Of Goods 85,451 116,587 132,433 142,577 148,598 20 Gross Profit 2,508 2,982 3,529 4,273 4,546 21 Selling & Adminstrative & Depr. & Amort Expenses 1,609 2,203 3,112 2,699 3,486 22 Income After Depreciation & Amortization 898 779 417 1,573 1,060 23 Non-Operating Income 0 -29 -44 53 3 24 Interest Expense 74 77 99 140 145 25 Pretax Income 824 673 274 1,391 918 26 Income Taxes 331 389 409 -37 553 27 Minority Interest 0 0 0 0 0 28 Investment Gains/Losses 0 0 0 0 0 29 Other Income/Charges 0 0 0 0 0 30 Income From Cont. Operations 493 284 -135 1,428 364 31 Extras & Discontinued Operations -60 -8 0 0 0 32 Net Income 434 276 -135 1,428 364 33 Depreciation Footnote 9/30/13 9/30/14 9/30/15 9/30/16 9/30/17 34 Income Before Depreciation & Amortization 1,069 972 667 1,966 1,493 35 Depreciation & Amortization (Cash Flow) 171 193 250 392 432 36 Income After Depreciation & Amortization 898 779 417 1,573 1,060 37 Earnings Per Share Data 9/30/13 9/30/14 9/30/15 9/30/16 9/30/17 38 Average Shares 235.35 235.41 217.79 225.96 221.6 39 Diluted EPS Before Non-Recurring Items 3.14 3.97 4.96 5.62 5.88 40 Diluted Net EPS 1.84 1.17 -0.62 6.32 1.64 41 Balance Sheet 9/30/13 9/30/14 9/30/15 9/30/16 9/30/17 42 Assets 43 Cash & Equivalents 1,231 1,809 2,167 2,742 2,435 44 Receivables 6,052 6,313 8,223 9,176 10,303 45 Notes Receivable 0 0 0 0 0 46 Inventories 6,981 8,594 9,755 10,724 11,461 47 Other Current Assets 129 85 189 210 103 48 Total Current Assets 14,394 16,800 20,334 22,852 24,303 49 Net Property & Equipment 804 900 979 1,531 1,798 50 Investments & Advances 0 0 0 0 0 51 Other Non-Current Assets 0 0 0 0 0 52 Deferred Charges 0 0 0 0 0 53 Intangibles 3,500 3,482 6,124 8,959 8,878 54 Deposits & Other Assets 222 351 298 314 338 55 Total Assets 18,919 21,532 27,736 33,656 35,316 56 Liabilities & Shareholders Equity 9/30/13 9/30/14 9/30/15 9/30/16 9/30/17 57 Notes Payable 0 0 0 0 0 58 Accounts Payable 13,336 15,593 20,886 23,926 25,404 59 Current Portion Long-Term Debt 0 0 0 611 12 60 Current Portion Capital Leases 0 0 0 0 0 61 Accrued Expenses 533 562 679 744 1,402 62 Income Taxes Payable 1,002 1,095 1,135 0 0 63 Other Current Liabilities 0 0 0 0 0 64 Total Current Liabilities 14,871 17,250 22,701 25,281 26,818 65 Mortgages 0 0 0 0 0 66 Deferred Taxes/Income 0 0 819 2,215 2,493 67 Convertible Debt 0 0 0 0 0 68 Long-Term Debt 1,397 1,996 3,493 3,870 3,782 69 Non-Current Capital Leases 0 0 0 0 0 70 Other Non-Current Liabilities 332 329 90 160 160 71 Minority Interest (Liabilities) 0 0 0 0 0 72 Total Liabilities 16,599 19,575 27,103 31,527 33,252 73 Shareholders Equity 9/30/13 9/30/14 9/30/15 9/30/16 9/30/17 74 Preferred Stock 0 0 0 0 0 75 Common Stock (Par) 3 3 3 3 3 76 Capital Surplus 2,361 2,749 3,736 4,333 4,518 77 Retained Earnings 1,508 1,570 1,182 2,304 2,395 78 Other Equity -35 -52 -136 -114 -96 79 Treasury Stock 1,517 2,313 4,151 4,396 4,755 80 Total Shareholder's Equity 2,320 1,957 634 2,129 2,064 81 Total Liabilities & Shareholder's Equity 18,919 21,532 27,736 33,656 35,316 82 Total Common Equity 2,320 1,957 634 2,129 2,064 83 Shares Outstanding 230.9 221.9 206.8 220 217.9 84 Book Value Per Share 10.05 8.82 3.06 9.68 9.47

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.