if you could include the excel formulas also it would be appreciated
Financial Ratios Exersise ( no not round any calculations)
2011 MPUT DATA NEGRO Year Shangminal Tax Francia Ratios Exercise Ow Ayat 1471 10 24 Quick INCOME STATEMENTS 2 20 514 Savena Rose Debate 14 1010 1341 10 22.44 DIT 17.30 13 2011 L LE CArtes Cesto On pol as Canaries Deprestando On Operating in Or Caming for za Town e Tarente 154 76 764 17 nyum Day Sain 18 2. 221 3333 700 244 354 8623 BER EST 7337 1614 02 1353 - Haasan Total Amat Tum Dj Pro BALANCE SHEETS 2010 2010 Annet Can and hort Taman Actie 337 2.11 2335 rem mory Marbet Value 150 2.150 3109 21 522 10 1.335 BA 1,778 1734 1230 30 650 ON O AS Toulouse 20 Pro bool v ka 11,12 OLA Tolong- Total Assets 2.101 19.1 3.730 2014 10.14 1 20 12. Fang 1644 Labdul ACCORD 5355 5481 UNE L Formatting Tables Fort H9 c 730 2552 3.10 1.711 622 2.325 3425 ende Short Terminants Accounts movable Inventores Other Current Assets Total current asset Net plant and equipment Other Lang.com Assets 29 Totai Long Ansels 30 Total Assets Market Value Ratio 2,115 2.936 1818 600 8205 8.07 2.101 19,178 10,113 100 1,230 301 2.023 800 3,509 10.172 2,18 12,560 640 10,979 9,110 2.213 11,325 22,398 Eumiga Porshan Preaming Porte Book Valper Share Manok 6788 2054 TO, 847 19,52 Eatinga math for Period How Growth food 444 5,356 2.000 2010 2017 2010 2010 1,122 9377 2.700 900 3.69 13. OTO 5.418 2.559 1,190 1,245 2500 1,500 4.100 13.345 5,600 3.034 1,218 9.00 2434 1773 4,202 14.000 5.530 283 1.381 7205 2770 1142 2,820 Opening Cash Flow Net Capital Expenses Change in Woning Captat Free Cash Flow 2,002 FOR Growth for Peso Estimated 2020 FCF Liabilities and Equity 41 Accounts payable Short-term Deot 43 A and Other current libis 44 Total currentes 45 Bonds payable M Other Long-term Les 47 Total Long-term Lab 4 Total Owner ty Common Stock & APIC 51 Reindeaming 52 Total con equity 33 Total liable and wity 14 55 Statement of Retained laminge se Beginning wance S7 Add: Net Income 50 Less Dividend paid s Ending Balance 60 2.000 2.507 5307 18,183 2.745 3.432 0737 19,582 2.745 543 2220 22,308 2.730 2.394 11,124 22.12 Identity and come to one category of os for the 5 above) that foc good improving performance 2017 2015 2,832 1.824 2,140 2,50 3492 4.577 2.500 5.483 3.253 2.265 3,492 2010 5483 5.723 2812 31 Idently and comment on one category erasos of the above that reflect bad or deteriorating performance B D E F 1 2 Financial Ratios E> Do not round any calcu 3 A) 5 INPUT DATA SECTION: 6 Year-end stock price 7 Shares outstanding in millions) 8 Tax rate 2016 $10.55 1,800 28% 2017 $19.64 1,770 24% 2018 $25.22 1,770 24% 2019 $31.47 1,750 22% U C B) D TI ) c) 2016 31,509 13,91 17,592 13,509 1,329 154 2,600 160 2,760 227 2,533 709 1,824 2017 33,394 14.093 19,301 13,411 1,374 156 4,360 164 4,524 244 4,280 1,027 3,253 2018 35.470 14.559 20,911 13,252 1,418 162 6,079 178 6,257 234 6,023 1,446 4,577 2019 37,541 15.074 22,467 13,413 1,477 171 7,406 182 7,588 251 7,337 1,614 5,723 In DE RE De D) P 10 INCOME STATEMENTS (Millions) 11 12 Net sales 13 Cost of Goods Sold 14 Gross profit 15 Sales, General and Administrative expenses 16 Depreciation and amortization 17 Other Misc expenses 18 Operating Income 19 Other Income 20 Eamings Before Interest and Taxes (EBIT) 21 Interest Expense 22 Taxable income 23 Income Tax Expense 24 Net income 25 26 BALANCE SHEETS (Millions) 27 28 Assets 29 Cash and equivalents 30 Short Term Investments 31 Accounts receivable 32 Inventories 33 Other Current Assets 34 Total current assets Net plant and equipment 36 Other Long-term Assets 37 Total Long-term Assets 38 Total Assets 39 40 Liabilities and Equity 41 Accounts payable student name main Pro 2016 2017 2018 2019 Re Re: E) Mi 937 2,115 2.935 1,618 600 8,205 8,077 2,101 10,178 18,383 750 2,552 3.105 1.711 622 8,740 8.788 2,054 10,842 19,582 1,802 3,325 3,425 1.778 649 10,979 9.116 2,213 11,329 22,308 1,734 1,230 3,912 2,023 690 9.589 10.172 2,388 12,560 22,149 Eami Price! Book Marke 35 Ear Reve 5,355 5,488 5,626 5,536 + . 5,355 2,900 1,122 9,377 2,700 999 3,699 13,076 5,488 2,559 1,198 9,245 2,600 1,500 4,100 13,345 5,626 3,034 1,218 9,878 2,429 1,773 4,202 14,080 5,536 288 1,381 7,205 2,678 1,142 3,820 11,025 Ne Ch Fre 39 40 Liabilities and Equity 41 Accounts payable 42 Short-term Debt 43 Accruals and Other Current Liabilities 44 Total current liabilities 45 Bonds payable 46 Other Long-term Liabilities 47 Total Long-term Liabilities 48 Total liabilities 49 Owner's Equity 50 Common Stock & APIC 51 Retained earings 52 Total common equity 53 Total liabilities and equity 54 55 Statement of Retained Earnings 56 Beginning Balance 57 Add: Net Income 58 Less: Dividends paid 59 Ending Balance 60 61 62 Id 2,800 2,507 5,307 18,383 2,745 3,492 6,237 19,582 2,745 5,483 8,228 22,308 2,730 8,394 11.124 22,149 2016 2,832 1,824 2,149 2,507 2017 2,507 3,253 2,268 3,492 2018 3,492 4.577 2,586 5,483 Idd 2019 5,483 5,723 2,812 8,394 TRUE cial Ratios Exercise ot round any calculations 2019 A) Liquidity Ratios: $31.47 1,750 Current Ratio: 22% Quick Ratio: Cash Ratio: 2016 2017 2018 2019 4-year Avg 0.9 0.9 1.1 1.3 1.1 B) Solvency / Leverage Ratios Debt Ratio: Times Interest Eamed: Cash Coverage Ratio: C) Asset Management Ratios 2019 37,541 15,074 22,467 13,413 1,477 171 7,406 182 7,588 251 7,337 1,614 5,723 Inventory Tumover. Days' Sales In Inventory: Receivables Tumover. Days' Sales in Receivables: Total Asset Tumover D) Profitability Ratios 2019 Profit Margin: Return on Assets: Retum on Equity E) Market Value Ratios 1,734 1,230 3,912 2,023 690 9,589 10,172 2,388 12,560 22.149 Eamings Per Share: Price/Eamings Ratio: Book Value por Share: MarketBook Value 2.9 3.5 4.6 6.4 use as su 46.4% Eamings Growth for Period: Revenue Growth for Period: 2018 2019. (Use formula for solving for Y from Module 3 2016-2019 (une formula for solving for Y from Module 3) 5,536 Merge & Center $ % 9 od 2 Conditional Form Formatting as Ta H M N Eamings Per Share: Price/Eamings Ratio: Book Value per Share: Market/Book Value 2.9 3.5 4.6 6.4 o as subcalculation 46.4% Eamings Growth for Period: Revenue Growth for Period: 2016-2019 (une formula for solving for from Module 3 2016-2019 (uste formula for solving for from Module 3) 2016 2017 2018 2019 Operating Cash Flow Net Capital Expenditure: Change in Working Capital Free Cash Flow: 2,002 or "Cash Flow from Assets FCF Growth for Period: Estimated 2020 FCF Identity and comment on one category of ratios (of the 5 above) that reflect good or improving performance Identify and comment on one category of ratios (of the 5 above) that reflect bad or deteriorating performance 3 7 & 2011 MPUT DATA NEGRO Year Shangminal Tax Francia Ratios Exercise Ow Ayat 1471 10 24 Quick INCOME STATEMENTS 2 20 514 Savena Rose Debate 14 1010 1341 10 22.44 DIT 17.30 13 2011 L LE CArtes Cesto On pol as Canaries Deprestando On Operating in Or Caming for za Town e Tarente 154 76 764 17 nyum Day Sain 18 2. 221 3333 700 244 354 8623 BER EST 7337 1614 02 1353 - Haasan Total Amat Tum Dj Pro BALANCE SHEETS 2010 2010 Annet Can and hort Taman Actie 337 2.11 2335 rem mory Marbet Value 150 2.150 3109 21 522 10 1.335 BA 1,778 1734 1230 30 650 ON O AS Toulouse 20 Pro bool v ka 11,12 OLA Tolong- Total Assets 2.101 19.1 3.730 2014 10.14 1 20 12. Fang 1644 Labdul ACCORD 5355 5481 UNE L Formatting Tables Fort H9 c 730 2552 3.10 1.711 622 2.325 3425 ende Short Terminants Accounts movable Inventores Other Current Assets Total current asset Net plant and equipment Other Lang.com Assets 29 Totai Long Ansels 30 Total Assets Market Value Ratio 2,115 2.936 1818 600 8205 8.07 2.101 19,178 10,113 100 1,230 301 2.023 800 3,509 10.172 2,18 12,560 640 10,979 9,110 2.213 11,325 22,398 Eumiga Porshan Preaming Porte Book Valper Share Manok 6788 2054 TO, 847 19,52 Eatinga math for Period How Growth food 444 5,356 2.000 2010 2017 2010 2010 1,122 9377 2.700 900 3.69 13. OTO 5.418 2.559 1,190 1,245 2500 1,500 4.100 13.345 5,600 3.034 1,218 9.00 2434 1773 4,202 14.000 5.530 283 1.381 7205 2770 1142 2,820 Opening Cash Flow Net Capital Expenses Change in Woning Captat Free Cash Flow 2,002 FOR Growth for Peso Estimated 2020 FCF Liabilities and Equity 41 Accounts payable Short-term Deot 43 A and Other current libis 44 Total currentes 45 Bonds payable M Other Long-term Les 47 Total Long-term Lab 4 Total Owner ty Common Stock & APIC 51 Reindeaming 52 Total con equity 33 Total liable and wity 14 55 Statement of Retained laminge se Beginning wance S7 Add: Net Income 50 Less Dividend paid s Ending Balance 60 2.000 2.507 5307 18,183 2.745 3.432 0737 19,582 2.745 543 2220 22,308 2.730 2.394 11,124 22.12 Identity and come to one category of os for the 5 above) that foc good improving performance 2017 2015 2,832 1.824 2,140 2,50 3492 4.577 2.500 5.483 3.253 2.265 3,492 2010 5483 5.723 2812 31 Idently and comment on one category erasos of the above that reflect bad or deteriorating performance B D E F 1 2 Financial Ratios E> Do not round any calcu 3 A) 5 INPUT DATA SECTION: 6 Year-end stock price 7 Shares outstanding in millions) 8 Tax rate 2016 $10.55 1,800 28% 2017 $19.64 1,770 24% 2018 $25.22 1,770 24% 2019 $31.47 1,750 22% U C B) D TI ) c) 2016 31,509 13,91 17,592 13,509 1,329 154 2,600 160 2,760 227 2,533 709 1,824 2017 33,394 14.093 19,301 13,411 1,374 156 4,360 164 4,524 244 4,280 1,027 3,253 2018 35.470 14.559 20,911 13,252 1,418 162 6,079 178 6,257 234 6,023 1,446 4,577 2019 37,541 15.074 22,467 13,413 1,477 171 7,406 182 7,588 251 7,337 1,614 5,723 In DE RE De D) P 10 INCOME STATEMENTS (Millions) 11 12 Net sales 13 Cost of Goods Sold 14 Gross profit 15 Sales, General and Administrative expenses 16 Depreciation and amortization 17 Other Misc expenses 18 Operating Income 19 Other Income 20 Eamings Before Interest and Taxes (EBIT) 21 Interest Expense 22 Taxable income 23 Income Tax Expense 24 Net income 25 26 BALANCE SHEETS (Millions) 27 28 Assets 29 Cash and equivalents 30 Short Term Investments 31 Accounts receivable 32 Inventories 33 Other Current Assets 34 Total current assets Net plant and equipment 36 Other Long-term Assets 37 Total Long-term Assets 38 Total Assets 39 40 Liabilities and Equity 41 Accounts payable student name main Pro 2016 2017 2018 2019 Re Re: E) Mi 937 2,115 2.935 1,618 600 8,205 8,077 2,101 10,178 18,383 750 2,552 3.105 1.711 622 8,740 8.788 2,054 10,842 19,582 1,802 3,325 3,425 1.778 649 10,979 9.116 2,213 11,329 22,308 1,734 1,230 3,912 2,023 690 9.589 10.172 2,388 12,560 22,149 Eami Price! Book Marke 35 Ear Reve 5,355 5,488 5,626 5,536 + . 5,355 2,900 1,122 9,377 2,700 999 3,699 13,076 5,488 2,559 1,198 9,245 2,600 1,500 4,100 13,345 5,626 3,034 1,218 9,878 2,429 1,773 4,202 14,080 5,536 288 1,381 7,205 2,678 1,142 3,820 11,025 Ne Ch Fre 39 40 Liabilities and Equity 41 Accounts payable 42 Short-term Debt 43 Accruals and Other Current Liabilities 44 Total current liabilities 45 Bonds payable 46 Other Long-term Liabilities 47 Total Long-term Liabilities 48 Total liabilities 49 Owner's Equity 50 Common Stock & APIC 51 Retained earings 52 Total common equity 53 Total liabilities and equity 54 55 Statement of Retained Earnings 56 Beginning Balance 57 Add: Net Income 58 Less: Dividends paid 59 Ending Balance 60 61 62 Id 2,800 2,507 5,307 18,383 2,745 3,492 6,237 19,582 2,745 5,483 8,228 22,308 2,730 8,394 11.124 22,149 2016 2,832 1,824 2,149 2,507 2017 2,507 3,253 2,268 3,492 2018 3,492 4.577 2,586 5,483 Idd 2019 5,483 5,723 2,812 8,394 TRUE cial Ratios Exercise ot round any calculations 2019 A) Liquidity Ratios: $31.47 1,750 Current Ratio: 22% Quick Ratio: Cash Ratio: 2016 2017 2018 2019 4-year Avg 0.9 0.9 1.1 1.3 1.1 B) Solvency / Leverage Ratios Debt Ratio: Times Interest Eamed: Cash Coverage Ratio: C) Asset Management Ratios 2019 37,541 15,074 22,467 13,413 1,477 171 7,406 182 7,588 251 7,337 1,614 5,723 Inventory Tumover. Days' Sales In Inventory: Receivables Tumover. Days' Sales in Receivables: Total Asset Tumover D) Profitability Ratios 2019 Profit Margin: Return on Assets: Retum on Equity E) Market Value Ratios 1,734 1,230 3,912 2,023 690 9,589 10,172 2,388 12,560 22.149 Eamings Per Share: Price/Eamings Ratio: Book Value por Share: MarketBook Value 2.9 3.5 4.6 6.4 use as su 46.4% Eamings Growth for Period: Revenue Growth for Period: 2018 2019. (Use formula for solving for Y from Module 3 2016-2019 (une formula for solving for Y from Module 3) 5,536 Merge & Center $ % 9 od 2 Conditional Form Formatting as Ta H M N Eamings Per Share: Price/Eamings Ratio: Book Value per Share: Market/Book Value 2.9 3.5 4.6 6.4 o as subcalculation 46.4% Eamings Growth for Period: Revenue Growth for Period: 2016-2019 (une formula for solving for from Module 3 2016-2019 (uste formula for solving for from Module 3) 2016 2017 2018 2019 Operating Cash Flow Net Capital Expenditure: Change in Working Capital Free Cash Flow: 2,002 or "Cash Flow from Assets FCF Growth for Period: Estimated 2020 FCF Identity and comment on one category of ratios (of the 5 above) that reflect good or improving performance Identify and comment on one category of ratios (of the 5 above) that reflect bad or deteriorating performance 3 7 &
