Calculate Ratios

Excel, Analysis of Financial Statements

I can not upload it

Cambria V 16 ' 10 Paste BIU V V E25 X vfx 50 Tool Kit Chapter 3 12/8/12 Analysis of Pinancial Statements Financial statements are analysed by calculating certain key rates and then comparing them with the ratio of other firms and by examining the trends in raties over time. We can also combine raties to make the analysis more revealing, one below are exceptionally useful for 3-1 Financial Analysis Input Datas 2010 Year-end common stock price 2019 $27.00 $40.00 Year-end shares outstanding in millions 50 Tax rate 50 40N After-tax cost of capital 40% Lase payments 11.0% 10.5% $28 528 Required sinking fund payments $20 $20 Figure - MicroDrive Inc. Balance Sheets and income Statements for Years Ending December 31 Hr Millions of Dollars. Except for Per Share Data) Balance Sheets 2018 2019 Assets Cash and equivalents 3503 60 Short-term investments 40 Mccounts receivable 500 380 entores 1,000 820 i Total current assets 51,550 $ 1,300 et plant and equipment 2,000 1.700 Total bles and Equity Accounts payable S 2005 190 Notes payable 280 130 Accruals 300 280 Total current abilities Long-term bonds 1,000 Total liabilities 31,980 $ 1,600 Preferred stock (400,000 shares) 100 100 Common ock(50,000,000 shares) 500 500 Retained earning 970 Total.commes equity 31/70'S 1300 Total ties and equity $3,550 53.000 2010 2019 Net als $5,000 $4,760 Cots of goods sold except depreciation 3,000 170 other operating 500 550 Karsing before interest and taxes (EBIT) sincere 120 100 Bee 380150 Tus (40%) Net Income before preferred dividende Preferred dividende . Net Income we to come. cheders 203 Other Data Common dividends 350 sa Addition to retained in 3170 Le payments Bundreded in fund payments Common stock price per share 1200 200 releri Calculated Date Operating performance and Cash Flow Export Summary Chapter E25 x fx 50 Calculated Data: Operating Performance and Cash Flows Net operating working capital (NOWC) Total operating capital Net operating profit after taxes (NOPAT) Return on capital (ROIC) Free cash flow (FCF) Net cash flow (Net income . Depreciation) Earnings before interest, taxes, depreciation & amortization Market capitalization (shares x price per share) 2018 $1,050 $3.050 $300 9.8% ($260) 420 $700 $1,350 2019 $790 $2.490 $330 13.3% N/A $432 $720 $2,000 $ Calculated Data Per-share Information 2018 Earnings per share (EPS) Dividends per share (DPS) Book value per share (BVPS) Cash flow per share (CFPS) EDITDA per share Free cash flow per share (FCFPS) $1.00 $29.40 $8.40 $14.00 ($5.20) 2019 $5.24 $0.96 $26.00 $8.64 $14.40 N/A 3-2 liquidity Ratios Industry 2018 2019 Liguration Current Ratio A/CL Quick Ratio (CA- 2.Z 08 3-3 Asset Management Ratios 2018 Industry Average 2019 Asset Management ratios Total Asset Turnover Sales/TA Fixed Asset Tumover Sales/Faxed assets Days Sales Outstanding Accounts receivable/Daily sales Inventory Turnover COGS/inventories 3-4 Debt Management Ratios 1.8 3 30 5 Industry 2018 2019 25.0 3.46 Debt Management rates Debt Ratio - Debt-to-Asset Ratio Total debtTA Debt-to-Equity Rates Total debt Totalcommon equiry Market Delit Ratio - Total debit/Total debte Market Cap to Ants at TLTA Times Interest Eared EBIT/Interest expense EBITDA Coverage Ratio 3-5 Profitability Ratios 20.0 10.0 120 Industry 2019 Badan Power ERITTA 20.2 19 ON Pretoria TOS Price TDA R COMMON SEE ANALYSIS Export Summary Chapter Select destination and press ENTER or choose Paste L Tul E25 x v fx 50 Price to Earnings Ratio PriceNet income Shares) Price-to-Cash Flow Ratio Price-to-EBITDA Ratio Market-to-Book Ratio - Price/(Total common equity/shares) 105 6.3 4.0 10 3-7 Common Size Analysis COMMON SIZE ANALYSIS In common size income statements, all items for a year are divided by the sales for that year. Figure 3-3 MicroDrive Inc. Common Size Income Statements Industry MicroDrive 2018 2018 2019 Net sales 100.0% Costs of goods sold except depreciation 75.5% Depreciation 3.0% Other operating expenses 100% Earnings before interest and taxes (EBIT) 11.5% Less interest 1.2% Pre-tax earnings 10.4% Taxes (40%) 4.1% Net Income before preferred dividends Preferred dividefids 0.0% Net Income available to common stockholders 6.2% In common sheets, all items for a year are divided by the total assets for that year. Figure 3-4 MicroDrive Inca Common Size Balance Sheets Industry Composite MicroDrive 2018 2018 2019 Assets Cash and equivalents 1.8% Short-term investments 0.01 Accounts receivable 14.0% Inventories 26.3% Total current assets 42.1% Net plant and equipment 57.9% Total assets TOON 7.0% 0.0% Accounts payable Notes payable Acers Total current Babies Long-term bonds Total abilities Preferred stock Totalcommodity Total abilities and equy 19.3 0.0 PERCENT CHANGE ANALYSIS la percent change analysis, alitems we divided by the that item's value in the beginning or haraya Pure 3-5 Micro Drive Inc Income Statement Percent Changes Base year 2019 Corts pods soldescer depreciation Export Summary Chapter > Paste BI U v BA E25 fx 50 MicroDrive Inc. Income Statement Percent Change Analysis Base year 2019 Percent 2018 Net sales Costs of goods sold except depreciation Depreciation Other operating expenses Earnings before interest and taxes (EBIT) Less interest Pre-tax earnings Taxes (40%) Net Income before preferred dividends Preferred dividends Net Income available to common stockholders MicroDrive Inc. Rilance Sheet Percent Channe Analysis not in textbooks) Base year 2019 Percent 2018 Assets Cash and equivalents Short-term investments Accounts receivable Inventories Total current assets Net plant and equipment Total sets Labdies and Equity Accounts payable Notes payable Acenas Total current abilities Long-term bonds Total abilities Preferred stock (400,000 shares) Common stock (50,000,000 shares) Rated earning Total.commes equity Total besandegy TA DUPONT ANALYSIS (Section 16) RO Pront MicroDrive 2018 MicroDrive 2019 Industry Average 20.3 670 Suppose Microbrive can improve total verrati Improved TA turover ratio RO Prent 1818 TA JX Market capitalization (shares w price per share) $1,350 $2,000 Calculated Data: Per-share Information Earnings per share (EPS) Dividends per share (DPS) Book value per share (BVPS) Cash flow per share (CFPS) EDITDA per share Free cash flow per share (FCFPS) 2018 $4.40 $1.00 $29.40 $8.40 $14.00 ($5.20) 2019 $5.24 $0.96 $26.00 $8.64 $14.40 N/A 3-2 Uquidity Ratios 2018 Industry Average 2019 Liquidity ratios Current Ratio = CA/CL Quick Ratio = (CA 2.2 0.8 3-3 Asset Management Ratios 2018 Industry Average 2019 Asset Management ratios Total Asset Tumover = Sales/TA Pred Asset Tumover Sales/Fixed assets Days Sales Outstanding - Accounts receivable/Daily sales Inventory Turnier COGS/Inventories 1.8 3 30 5 3-4 Debt Management Ratios 2018 Industry Average 2019 Debt Management ratios Debt Ratio - Debt-to-Assets Ratio Total debt/TA Debt-to-Equity Ratio Total debt Total common equity Market Debt Ratio - Total debt/Total debt Market Cap) Labilities-to-Assets Ratio - TL/TA Times interest Earned - EBIT/Interest expense EBITDA Coverage Ratio 25.0% 0.46 20.0% 45.0 10.0 12.0 3-5 Profitability Ratios Industry 2018 Erfitabaratos Prot Margin = Net Income/Sales Base Earning Power EBIT TA Return on Awets - Net Income TA Return on quity Net Income Total common quay 6.2% 20.2% 11.09 Industry 2018 105 Price to Earnings Ratie Price/Met Income/hares) Price-to-Cash Flow Ratio Price-to-EBITDA Ratio Market-to Bock Ratio e/otal.com/edare) 3.7 Common Sine Analysis 4.0 COMMON SIZE ANALYSIS Flare MicroDicamente come Batement Consolideeep deprecat Depreciation Earnings before interest and COMMON SIZE ANALYSIS In common size income statements, all items for a year are divided by the sales for that year. Figure 3-3 MicroDrive Inc:Common Size Income Statements MicroDrive 2018 2019 Net sales Costs of goods sold except depreciation Depreciation Other operating expenses Earnings before interest and taxes (EBIT) Less interest Pre-tax earnings Taxes (40%) Net Income before preferred dividends Preferred dividends Net Income available to common stockholders Industry 2018 100.0% 75.5% 3.09 10.0% 11.5% 1.2% 10.4% 4.1% 6.2% 0.0% 6.2% In common sheets, all items for a year are divided by the total assets for that year, Figure 3-4 Micro Drive Inc: Common Size Balance Sheets Industry Composite MicroDrive 2010 2018 2019 Assets Cash and equivalent 1.896 Short-term investments 0.09 Accounts receivable 14.0% Inventories 26.39 Total current assets 42.1% Net plant anderen Totales 100.0 Lbs and Equity Accounts payable Notes pie 0.04 Accruals Total current abities Long-term bonds Totide 7.09 25.4N 0.ON 55.3% PERCENT CHANGE ANALYSIS Export sur Chapter Select destination and press ENTER or choose Paste la percent change analysis, all items are divided by the that item's value in the beginning or base, year. Figure 3-5 MicroDrive Inc.: Income Statement Percent Change Analysis Base year 2019 Percent 2018 Net sales Costs of goods sold except deprelation Depreciation Other operating expenses Earnings before interest and taxes (EBIT) Less interest Pre-tax earnings Taxes (40%) Net Income before preferr a dividends Preferred dividends Net income available to commdi stockholders Micro Drive L. Ralance She Percent Change Analysis (not in textbook) Base year = 2019 Percent 2018 Assets Cash and equivalents Short-term investments Accounts receivable Inventories Total current assets Net plant and equipment Total assets Liabilities and Equity Accounts payable Notes payable Accruals Total current abilities Long-term bonds Total liabilities Preferred stock (400,000 shares Common stock (50,000,000 shad Retained earnings Totalcommon equity Total liabilities and equity DUPONT ANALYSIS (Section) ROB Profit TA Equity MicroDrive MicroDrive Industry Average 2018 2019 20.1% 6.20% 1.818 Suppose MicroDrive can improve its total asset turnover ratio Improved TA turover ratio ROP Profit TA Equity Export Summary Chapter 3-2 Select destination and press ENTER or choose Paste Cambria V 16 ' 10 Paste BIU V V E25 X vfx 50 Tool Kit Chapter 3 12/8/12 Analysis of Pinancial Statements Financial statements are analysed by calculating certain key rates and then comparing them with the ratio of other firms and by examining the trends in raties over time. We can also combine raties to make the analysis more revealing, one below are exceptionally useful for 3-1 Financial Analysis Input Datas 2010 Year-end common stock price 2019 $27.00 $40.00 Year-end shares outstanding in millions 50 Tax rate 50 40N After-tax cost of capital 40% Lase payments 11.0% 10.5% $28 528 Required sinking fund payments $20 $20 Figure - MicroDrive Inc. Balance Sheets and income Statements for Years Ending December 31 Hr Millions of Dollars. Except for Per Share Data) Balance Sheets 2018 2019 Assets Cash and equivalents 3503 60 Short-term investments 40 Mccounts receivable 500 380 entores 1,000 820 i Total current assets 51,550 $ 1,300 et plant and equipment 2,000 1.700 Total bles and Equity Accounts payable S 2005 190 Notes payable 280 130 Accruals 300 280 Total current abilities Long-term bonds 1,000 Total liabilities 31,980 $ 1,600 Preferred stock (400,000 shares) 100 100 Common ock(50,000,000 shares) 500 500 Retained earning 970 Total.commes equity 31/70'S 1300 Total ties and equity $3,550 53.000 2010 2019 Net als $5,000 $4,760 Cots of goods sold except depreciation 3,000 170 other operating 500 550 Karsing before interest and taxes (EBIT) sincere 120 100 Bee 380150 Tus (40%) Net Income before preferred dividende Preferred dividende . Net Income we to come. cheders 203 Other Data Common dividends 350 sa Addition to retained in 3170 Le payments Bundreded in fund payments Common stock price per share 1200 200 releri Calculated Date Operating performance and Cash Flow Export Summary Chapter E25 x fx 50 Calculated Data: Operating Performance and Cash Flows Net operating working capital (NOWC) Total operating capital Net operating profit after taxes (NOPAT) Return on capital (ROIC) Free cash flow (FCF) Net cash flow (Net income . Depreciation) Earnings before interest, taxes, depreciation & amortization Market capitalization (shares x price per share) 2018 $1,050 $3.050 $300 9.8% ($260) 420 $700 $1,350 2019 $790 $2.490 $330 13.3% N/A $432 $720 $2,000 $ Calculated Data Per-share Information 2018 Earnings per share (EPS) Dividends per share (DPS) Book value per share (BVPS) Cash flow per share (CFPS) EDITDA per share Free cash flow per share (FCFPS) $1.00 $29.40 $8.40 $14.00 ($5.20) 2019 $5.24 $0.96 $26.00 $8.64 $14.40 N/A 3-2 liquidity Ratios Industry 2018 2019 Liguration Current Ratio A/CL Quick Ratio (CA- 2.Z 08 3-3 Asset Management Ratios 2018 Industry Average 2019 Asset Management ratios Total Asset Turnover Sales/TA Fixed Asset Tumover Sales/Faxed assets Days Sales Outstanding Accounts receivable/Daily sales Inventory Turnover COGS/inventories 3-4 Debt Management Ratios 1.8 3 30 5 Industry 2018 2019 25.0 3.46 Debt Management rates Debt Ratio - Debt-to-Asset Ratio Total debtTA Debt-to-Equity Rates Total debt Totalcommon equiry Market Delit Ratio - Total debit/Total debte Market Cap to Ants at TLTA Times Interest Eared EBIT/Interest expense EBITDA Coverage Ratio 3-5 Profitability Ratios 20.0 10.0 120 Industry 2019 Badan Power ERITTA 20.2 19 ON Pretoria TOS Price TDA R COMMON SEE ANALYSIS Export Summary Chapter Select destination and press ENTER or choose Paste L Tul E25 x v fx 50 Price to Earnings Ratio PriceNet income Shares) Price-to-Cash Flow Ratio Price-to-EBITDA Ratio Market-to-Book Ratio - Price/(Total common equity/shares) 105 6.3 4.0 10 3-7 Common Size Analysis COMMON SIZE ANALYSIS In common size income statements, all items for a year are divided by the sales for that year. Figure 3-3 MicroDrive Inc. Common Size Income Statements Industry MicroDrive 2018 2018 2019 Net sales 100.0% Costs of goods sold except depreciation 75.5% Depreciation 3.0% Other operating expenses 100% Earnings before interest and taxes (EBIT) 11.5% Less interest 1.2% Pre-tax earnings 10.4% Taxes (40%) 4.1% Net Income before preferred dividends Preferred dividefids 0.0% Net Income available to common stockholders 6.2% In common sheets, all items for a year are divided by the total assets for that year. Figure 3-4 MicroDrive Inca Common Size Balance Sheets Industry Composite MicroDrive 2018 2018 2019 Assets Cash and equivalents 1.8% Short-term investments 0.01 Accounts receivable 14.0% Inventories 26.3% Total current assets 42.1% Net plant and equipment 57.9% Total assets TOON 7.0% 0.0% Accounts payable Notes payable Acers Total current Babies Long-term bonds Total abilities Preferred stock Totalcommodity Total abilities and equy 19.3 0.0 PERCENT CHANGE ANALYSIS la percent change analysis, alitems we divided by the that item's value in the beginning or haraya Pure 3-5 Micro Drive Inc Income Statement Percent Changes Base year 2019 Corts pods soldescer depreciation Export Summary Chapter > Paste BI U v BA E25 fx 50 MicroDrive Inc. Income Statement Percent Change Analysis Base year 2019 Percent 2018 Net sales Costs of goods sold except depreciation Depreciation Other operating expenses Earnings before interest and taxes (EBIT) Less interest Pre-tax earnings Taxes (40%) Net Income before preferred dividends Preferred dividends Net Income available to common stockholders MicroDrive Inc. Rilance Sheet Percent Channe Analysis not in textbooks) Base year 2019 Percent 2018 Assets Cash and equivalents Short-term investments Accounts receivable Inventories Total current assets Net plant and equipment Total sets Labdies and Equity Accounts payable Notes payable Acenas Total current abilities Long-term bonds Total abilities Preferred stock (400,000 shares) Common stock (50,000,000 shares) Rated earning Total.commes equity Total besandegy TA DUPONT ANALYSIS (Section 16) RO Pront MicroDrive 2018 MicroDrive 2019 Industry Average 20.3 670 Suppose Microbrive can improve total verrati Improved TA turover ratio RO Prent 1818 TA JX Market capitalization (shares w price per share) $1,350 $2,000 Calculated Data: Per-share Information Earnings per share (EPS) Dividends per share (DPS) Book value per share (BVPS) Cash flow per share (CFPS) EDITDA per share Free cash flow per share (FCFPS) 2018 $4.40 $1.00 $29.40 $8.40 $14.00 ($5.20) 2019 $5.24 $0.96 $26.00 $8.64 $14.40 N/A 3-2 Uquidity Ratios 2018 Industry Average 2019 Liquidity ratios Current Ratio = CA/CL Quick Ratio = (CA 2.2 0.8 3-3 Asset Management Ratios 2018 Industry Average 2019 Asset Management ratios Total Asset Tumover = Sales/TA Pred Asset Tumover Sales/Fixed assets Days Sales Outstanding - Accounts receivable/Daily sales Inventory Turnier COGS/Inventories 1.8 3 30 5 3-4 Debt Management Ratios 2018 Industry Average 2019 Debt Management ratios Debt Ratio - Debt-to-Assets Ratio Total debt/TA Debt-to-Equity Ratio Total debt Total common equity Market Debt Ratio - Total debt/Total debt Market Cap) Labilities-to-Assets Ratio - TL/TA Times interest Earned - EBIT/Interest expense EBITDA Coverage Ratio 25.0% 0.46 20.0% 45.0 10.0 12.0 3-5 Profitability Ratios Industry 2018 Erfitabaratos Prot Margin = Net Income/Sales Base Earning Power EBIT TA Return on Awets - Net Income TA Return on quity Net Income Total common quay 6.2% 20.2% 11.09 Industry 2018 105 Price to Earnings Ratie Price/Met Income/hares) Price-to-Cash Flow Ratio Price-to-EBITDA Ratio Market-to Bock Ratio e/otal.com/edare) 3.7 Common Sine Analysis 4.0 COMMON SIZE ANALYSIS Flare MicroDicamente come Batement Consolideeep deprecat Depreciation Earnings before interest and COMMON SIZE ANALYSIS In common size income statements, all items for a year are divided by the sales for that year. Figure 3-3 MicroDrive Inc:Common Size Income Statements MicroDrive 2018 2019 Net sales Costs of goods sold except depreciation Depreciation Other operating expenses Earnings before interest and taxes (EBIT) Less interest Pre-tax earnings Taxes (40%) Net Income before preferred dividends Preferred dividends Net Income available to common stockholders Industry 2018 100.0% 75.5% 3.09 10.0% 11.5% 1.2% 10.4% 4.1% 6.2% 0.0% 6.2% In common sheets, all items for a year are divided by the total assets for that year, Figure 3-4 Micro Drive Inc: Common Size Balance Sheets Industry Composite MicroDrive 2010 2018 2019 Assets Cash and equivalent 1.896 Short-term investments 0.09 Accounts receivable 14.0% Inventories 26.39 Total current assets 42.1% Net plant anderen Totales 100.0 Lbs and Equity Accounts payable Notes pie 0.04 Accruals Total current abities Long-term bonds Totide 7.09 25.4N 0.ON 55.3% PERCENT CHANGE ANALYSIS Export sur Chapter Select destination and press ENTER or choose Paste la percent change analysis, all items are divided by the that item's value in the beginning or base, year. Figure 3-5 MicroDrive Inc.: Income Statement Percent Change Analysis Base year 2019 Percent 2018 Net sales Costs of goods sold except deprelation Depreciation Other operating expenses Earnings before interest and taxes (EBIT) Less interest Pre-tax earnings Taxes (40%) Net Income before preferr a dividends Preferred dividends Net income available to commdi stockholders Micro Drive L. Ralance She Percent Change Analysis (not in textbook) Base year = 2019 Percent 2018 Assets Cash and equivalents Short-term investments Accounts receivable Inventories Total current assets Net plant and equipment Total assets Liabilities and Equity Accounts payable Notes payable Accruals Total current abilities Long-term bonds Total liabilities Preferred stock (400,000 shares Common stock (50,000,000 shad Retained earnings Totalcommon equity Total liabilities and equity DUPONT ANALYSIS (Section) ROB Profit TA Equity MicroDrive MicroDrive Industry Average 2018 2019 20.1% 6.20% 1.818 Suppose MicroDrive can improve its total asset turnover ratio Improved TA turover ratio ROP Profit TA Equity Export Summary Chapter 3-2 Select destination and press ENTER or choose Paste