FIN489 Homework 4 - Ratio Analysis 3/10/20 21:32 Spring 2020 Financial Statement Analysis - Ratios, Identifying Issues, the Dupont Equation Company Financial Statements 8 Balance Sheets 2018-2020E 2020E 2019 2018 $ 10 Assets 11 Cash 12 Accounts receivable 13 Inventories 14 Total current assets 15 Net fixed assets 16 Total assets 85,632 878,000 1,716,480 2,680,112 817,040 3,497,152 7,282 $ 632,160 1,287,360 1,926,802 $ 939,790 2,866,592 $ 57,600 351,200 715,200 1,124,000 344,800 1,468,800 $ $ $ $ 17 18 Liabilities and equity 19 Accounts payable 20 Accruals 21 Notes payable 22 Total current liabilities 23 Long-term bonds 24 Common stock (100,000 shares) 25 Retained earnings 26 Total common equity 27 Total liabilities and equity 436,800 $ 408,000 300,000 1,144,800 $ 400,000 1,718,986 233,366 1,952,352 $ 3,497,152 $ 524,160 $ 489,600 636,808 1,650,568 $ 723,432 460,000 32,592 492.592 $ 2,866,592 $ 145,600 136,000 200,000 481,600 323,432 460,000 203,768 663,768 1,468,800 29 Income Statements 2018-2020E 2020 2019 2018 $ $ 31 Sales 32 Cost of goods sold 33 Other expenses 34 Total operating exp. excl. depreciation and amortization 35 EBITDA 36 Depreciation and amortization 37 Earnings before interest and taxes (EBIT) 38 Interest expense 39 Earnings before taxes (EBT) 40 Taxes (25%) 41 Net Income 6,900,600 $ 5,875,992 550,000 6,425,992 $ 474,608 $ 116,960 357,648 $ 70,008 287,640 $ 31,866 255,774 $ 6,126,796 $ 5,528,000 519,988 6.047.988 $ 78,808 $ 116,960 (38,152) $ 122,024 (160,176) $ ***m1111 3,432,000 2,864,000 358,672 3.222.672 209,328 18,900 190,428 43,828 146,600 36,650 109,950 $ $ (160,176) $ 43 Earnings per share (EPS) 44 Dividends per share (DPS) 45 Book value per share (BVPS) 46 Stock price 47 Shares outstanding 48 Tax rate 49 Lease payments 50 Sinking fund payments 1.023 $ 0.220 $ 7.809 12.17 $ 250,000 25.00% 40,000 $ (1.602) $ 0.110 $ 4.926 $ 2.25 $ 100,000 25.00% 40,000 $ 1.100 0.275 6.638 8.50 100,000 25.00% 40,000 52 Ratio Analysis Year over Year - Industry Avg. 2020E 2019 2018 2.3 Industry Average 2.7 0.4 0.8 1.0 4.8 4.8 37.7 37.4 10.0 2.3 54 Current 55 Quick 56 Inventory turnover 57 Days sales outstanding (DSO) 58 Fixed assets turnover 59 Total assets turnover 60 Debt-to-capital ratio 61 Times interest earned (TIE) 62 Operating margin 63 Profit margin 64 Basic earning power (BEP) 65 Return on assets (ROA) 66 Return on equity (ROE) 67 Return on invested capital (ROIC) 68 Pricelearnings (P/E) 69 Market/book (M/B) nel 2.1 73.4% -0.3 -0.6% -2.6% -1.3% -5.6% -32.5% -1.5% -1.4 0.5 44.1% 4.3 5.5% 3.2% 13.0% 7.5% 16.6% 12.0% 6.1 32.0 7.0 2.6 40.0% 6.2 7.3% 4.3% 19.1% 11.2% 18.2% 16.5% 14.2 7.7 1.3 2.4 69 Market/book (M/B) 70 Book value per share (BVPS) 71 EV/EBITDA 0.5 $4.93 20.02 1.3 $6.64 6.29 PART B Calculate the company's 2020 current and quick ratios based on the projected balance sheet and income statement data. CR2020 CA2020 CL2020 CR2020 CR2020 CA2020 Inv2020 ) 1 CL2020 QR2020 QR2020 QR2020 #N/A PARTC Calculate the 2020 inventory turnover, days sales outstanding (DSO), fixed assets turnover, and total assets turnover. Sales2020 In 2020 87 88 Inv TO 2020 Inv TO 2020 Inv TO 2020 #N/A AR2020 (Sales2020 365 92 DSO2020 DSO2020 DSO2020 Sales 2020 NFA2020 FATO2020 FATO2020 FATO 2020 #N/A 99 101 TATO2020 Sales20201 TA2020 TATO2020 TATO2020 #N/A 102 103 PART D 104 Calculate the 2020 debt-to-capital and times-interest-earned ratios. 105 106 Debt-to-capital ratio2020 Total debt2020 Total invested capital2020 107 Debt-to-capital ratio2020 108 Debt-to-capital ratio2020 #N/A 109 110 TIE 2020 EBIT 2020 Int exp2020 111 TIE 2020 112 TIE 2020 #N/A 113 114 PARTE Calculate the 2020 operating margin, profit margin, basic earning power (BEP), return on assets (ROA), return on equity (ROE), and return on invested 115 capital (ROIC). 116 117 OM 2020 EBIT 2020 Sales 2020 118 OM 2020 OM 2020 #N/A NI2020 Sales 2020 PM2020 PM2020 PM2020 #N/A 124 125 EBIT2020 TA2020 126 127 | BEP2020 BEP1920 BEP1920 #N/A 127 TA2020 133 Nl2020 BEP 2020 #N/A 128 129 ROA2020 Nl2020 I 130 ROA2020 131 ROA2020 #N/A 132 ROE 2020 Equity 2020 134 ROE 2020 135 ROE 2020 #N/A 136 137 ROIC 2020 EBIT2020 (1 - T) Total invested capital2020 138 ROIC2020 139 ROIC2020 #N/A 140 141 PARTF 142 Calculate the 2020 pricelearnings ratio,market/book ratio, and EV/EBITDA ratio. 143 144 P/E 2020 Price 2020 NI2020 P/E2020 146 P/E 2020 #N/A 147 BVPS 2020 BV EC2020 # of shares 149 BVPS 2020 BVPS 2020 #N/A # of shares 148 150 152 Price 2020 BVPS 2020 M/B2020 M/B2020 #N/A (MV + MVD + MV Claims - Cash and Equivalents) EBITDA M/B2020 155 156 EV/EBITDA2020 157 EV/EBITDA2020 158 EV/EBITDA2020 159 160 PARTG #N/A 164 Use the DuPont equation to provide a summary and overview of D'Leon's financial condition as projected for 2020. 161 162 163 ROE 2020 = Profit margin2020 Total assets turnover 2020 Equity multiplier 2020 165 ROE 2020 = 166 ROE 2020 = #N/A 167 168 PARTH Use the following simplified 2020 balance sheet to show, in general terms, how an improvement in the DSO would tend to affect the stock price. For example, if the company could improve its collection procedures and thereby lower its DSO from 46.4 days to the 32-day industry average without affecting sales, how would that change "ripple through" the financial statements (shown in thousands below) and influence the stock price? 169 170 171 Accounts receivable 172 Other current assets 173 Net fixed assets 174 Total assets $878 1,802 817 $3,497 Current liabilities Debt Equity Liabilities plus equity $845 700 1,952 $3,497 175 365 #N/A 176 First, we need to calculate the company's's daily sales. 177 178 Daily sales = 179 Daily sales = 180 Daily sales = 181 182 Target A/R = Daily sales 183 Target A/R = 184 Target A/R = 185 Target DSO #N/A 135 130 ROA2020- 131 ROA2020 #N/A 132 133 ROE 2020 = N12020 Equity 2020 134 ROE 2020 ROE 2020 #N/A 136 137 ROIC2020 EBIT2020 (1 -T) Total invested capital2020 138 ROIC 2020 139 ROIC2020 #N/A 140 141 PARTF 142 Calculate the 2020 pricelearnings ratio,market/book ratio, and EV/EBITDA ratio. 143 144 P/E 2020 Price 2020 NI 2020 P/E 2020 P/E2020 #N/A # of shares 145 146 147 BV E22020 # of shares #N/A Price 2020 BVPS 2020 BVPS 2020 BVPS 2020 BVPS 2020 151 152 M/B2020 M/B2020 M/B2020 155 156 EVIEBITDA2020 157 EV/EBITDA2020 158 EV/EBITDA2020 159 160 PART G #N/A (MVE + MVp + MV Claims - Cash and Equivalents) EBITDA #N/A Use the DuPont equation to provide a summary and overview of D'Leon's financial condition as projected for 2020. 161 162 163 164 ROE 2020 = Profit margin2020 Total assets turnover 2020 X Equity multiplier 2020 165 ROE 2020 = 166 ROE 2020 = #N/A 167 168 PARTH Use the following simplified 2020 balance sheet to show, in general terms, how an improvement in the DSO would tend to affect the stock price. For example, if the company could improve its collection procedures and thereby lower its DSO from 46.4 days to the 32-day industry average without affecting sales, how would that change "ripple through" the financial statements (shown in thousands below) and influence the stock price? 169 170 171 Accounts receivable $878 Current liabilities $845 172 Other current assets 1,802 Debt 700 173 Net fixed assets 817 Equity 1,952 174 Total assets $3,497 Liabilities plus equity $3,497 175 176 First, we need to calculate the company's's daily sales. 177 178 Daily sales = Sales 365 179 Daily sales = 180 Daily sales = #N/A 181 182 Target A/R = Daily sales Target DSO Target A/R = 184 Target A/R = #N/A 185 186 Freed-up cash = old A/R new AIR 187 Freed-up cash = 188 Freed-up cash = | #N/A 183 FIN489 Homework 4 - Ratio Analysis 3/10/20 21:32 Spring 2020 Financial Statement Analysis - Ratios, Identifying Issues, the Dupont Equation Company Financial Statements 8 Balance Sheets 2018-2020E 2020E 2019 2018 $ 10 Assets 11 Cash 12 Accounts receivable 13 Inventories 14 Total current assets 15 Net fixed assets 16 Total assets 85,632 878,000 1,716,480 2,680,112 817,040 3,497,152 7,282 $ 632,160 1,287,360 1,926,802 $ 939,790 2,866,592 $ 57,600 351,200 715,200 1,124,000 344,800 1,468,800 $ $ $ $ 17 18 Liabilities and equity 19 Accounts payable 20 Accruals 21 Notes payable 22 Total current liabilities 23 Long-term bonds 24 Common stock (100,000 shares) 25 Retained earnings 26 Total common equity 27 Total liabilities and equity 436,800 $ 408,000 300,000 1,144,800 $ 400,000 1,718,986 233,366 1,952,352 $ 3,497,152 $ 524,160 $ 489,600 636,808 1,650,568 $ 723,432 460,000 32,592 492.592 $ 2,866,592 $ 145,600 136,000 200,000 481,600 323,432 460,000 203,768 663,768 1,468,800 29 Income Statements 2018-2020E 2020 2019 2018 $ $ 31 Sales 32 Cost of goods sold 33 Other expenses 34 Total operating exp. excl. depreciation and amortization 35 EBITDA 36 Depreciation and amortization 37 Earnings before interest and taxes (EBIT) 38 Interest expense 39 Earnings before taxes (EBT) 40 Taxes (25%) 41 Net Income 6,900,600 $ 5,875,992 550,000 6,425,992 $ 474,608 $ 116,960 357,648 $ 70,008 287,640 $ 31,866 255,774 $ 6,126,796 $ 5,528,000 519,988 6.047.988 $ 78,808 $ 116,960 (38,152) $ 122,024 (160,176) $ ***m1111 3,432,000 2,864,000 358,672 3.222.672 209,328 18,900 190,428 43,828 146,600 36,650 109,950 $ $ (160,176) $ 43 Earnings per share (EPS) 44 Dividends per share (DPS) 45 Book value per share (BVPS) 46 Stock price 47 Shares outstanding 48 Tax rate 49 Lease payments 50 Sinking fund payments 1.023 $ 0.220 $ 7.809 12.17 $ 250,000 25.00% 40,000 $ (1.602) $ 0.110 $ 4.926 $ 2.25 $ 100,000 25.00% 40,000 $ 1.100 0.275 6.638 8.50 100,000 25.00% 40,000 52 Ratio Analysis Year over Year - Industry Avg. 2020E 2019 2018 2.3 Industry Average 2.7 0.4 0.8 1.0 4.8 4.8 37.7 37.4 10.0 2.3 54 Current 55 Quick 56 Inventory turnover 57 Days sales outstanding (DSO) 58 Fixed assets turnover 59 Total assets turnover 60 Debt-to-capital ratio 61 Times interest earned (TIE) 62 Operating margin 63 Profit margin 64 Basic earning power (BEP) 65 Return on assets (ROA) 66 Return on equity (ROE) 67 Return on invested capital (ROIC) 68 Pricelearnings (P/E) 69 Market/book (M/B) nel 2.1 73.4% -0.3 -0.6% -2.6% -1.3% -5.6% -32.5% -1.5% -1.4 0.5 44.1% 4.3 5.5% 3.2% 13.0% 7.5% 16.6% 12.0% 6.1 32.0 7.0 2.6 40.0% 6.2 7.3% 4.3% 19.1% 11.2% 18.2% 16.5% 14.2 7.7 1.3 2.4 69 Market/book (M/B) 70 Book value per share (BVPS) 71 EV/EBITDA 0.5 $4.93 20.02 1.3 $6.64 6.29 PART B Calculate the company's 2020 current and quick ratios based on the projected balance sheet and income statement data. CR2020 CA2020 CL2020 CR2020 CR2020 CA2020 Inv2020 ) 1 CL2020 QR2020 QR2020 QR2020 #N/A PARTC Calculate the 2020 inventory turnover, days sales outstanding (DSO), fixed assets turnover, and total assets turnover. Sales2020 In 2020 87 88 Inv TO 2020 Inv TO 2020 Inv TO 2020 #N/A AR2020 (Sales2020 365 92 DSO2020 DSO2020 DSO2020 Sales 2020 NFA2020 FATO2020 FATO2020 FATO 2020 #N/A 99 101 TATO2020 Sales20201 TA2020 TATO2020 TATO2020 #N/A 102 103 PART D 104 Calculate the 2020 debt-to-capital and times-interest-earned ratios. 105 106 Debt-to-capital ratio2020 Total debt2020 Total invested capital2020 107 Debt-to-capital ratio2020 108 Debt-to-capital ratio2020 #N/A 109 110 TIE 2020 EBIT 2020 Int exp2020 111 TIE 2020 112 TIE 2020 #N/A 113 114 PARTE Calculate the 2020 operating margin, profit margin, basic earning power (BEP), return on assets (ROA), return on equity (ROE), and return on invested 115 capital (ROIC). 116 117 OM 2020 EBIT 2020 Sales 2020 118 OM 2020 OM 2020 #N/A NI2020 Sales 2020 PM2020 PM2020 PM2020 #N/A 124 125 EBIT2020 TA2020 126 127 | BEP2020 BEP1920 BEP1920 #N/A 127 TA2020 133 Nl2020 BEP 2020 #N/A 128 129 ROA2020 Nl2020 I 130 ROA2020 131 ROA2020 #N/A 132 ROE 2020 Equity 2020 134 ROE 2020 135 ROE 2020 #N/A 136 137 ROIC 2020 EBIT2020 (1 - T) Total invested capital2020 138 ROIC2020 139 ROIC2020 #N/A 140 141 PARTF 142 Calculate the 2020 pricelearnings ratio,market/book ratio, and EV/EBITDA ratio. 143 144 P/E 2020 Price 2020 NI2020 P/E2020 146 P/E 2020 #N/A 147 BVPS 2020 BV EC2020 # of shares 149 BVPS 2020 BVPS 2020 #N/A # of shares 148 150 152 Price 2020 BVPS 2020 M/B2020 M/B2020 #N/A (MV + MVD + MV Claims - Cash and Equivalents) EBITDA M/B2020 155 156 EV/EBITDA2020 157 EV/EBITDA2020 158 EV/EBITDA2020 159 160 PARTG #N/A 164 Use the DuPont equation to provide a summary and overview of D'Leon's financial condition as projected for 2020. 161 162 163 ROE 2020 = Profit margin2020 Total assets turnover 2020 Equity multiplier 2020 165 ROE 2020 = 166 ROE 2020 = #N/A 167 168 PARTH Use the following simplified 2020 balance sheet to show, in general terms, how an improvement in the DSO would tend to affect the stock price. For example, if the company could improve its collection procedures and thereby lower its DSO from 46.4 days to the 32-day industry average without affecting sales, how would that change "ripple through" the financial statements (shown in thousands below) and influence the stock price? 169 170 171 Accounts receivable 172 Other current assets 173 Net fixed assets 174 Total assets $878 1,802 817 $3,497 Current liabilities Debt Equity Liabilities plus equity $845 700 1,952 $3,497 175 365 #N/A 176 First, we need to calculate the company's's daily sales. 177 178 Daily sales = 179 Daily sales = 180 Daily sales = 181 182 Target A/R = Daily sales 183 Target A/R = 184 Target A/R = 185 Target DSO #N/A 135 130 ROA2020- 131 ROA2020 #N/A 132 133 ROE 2020 = N12020 Equity 2020 134 ROE 2020 ROE 2020 #N/A 136 137 ROIC2020 EBIT2020 (1 -T) Total invested capital2020 138 ROIC 2020 139 ROIC2020 #N/A 140 141 PARTF 142 Calculate the 2020 pricelearnings ratio,market/book ratio, and EV/EBITDA ratio. 143 144 P/E 2020 Price 2020 NI 2020 P/E 2020 P/E2020 #N/A # of shares 145 146 147 BV E22020 # of shares #N/A Price 2020 BVPS 2020 BVPS 2020 BVPS 2020 BVPS 2020 151 152 M/B2020 M/B2020 M/B2020 155 156 EVIEBITDA2020 157 EV/EBITDA2020 158 EV/EBITDA2020 159 160 PART G #N/A (MVE + MVp + MV Claims - Cash and Equivalents) EBITDA #N/A Use the DuPont equation to provide a summary and overview of D'Leon's financial condition as projected for 2020. 161 162 163 164 ROE 2020 = Profit margin2020 Total assets turnover 2020 X Equity multiplier 2020 165 ROE 2020 = 166 ROE 2020 = #N/A 167 168 PARTH Use the following simplified 2020 balance sheet to show, in general terms, how an improvement in the DSO would tend to affect the stock price. For example, if the company could improve its collection procedures and thereby lower its DSO from 46.4 days to the 32-day industry average without affecting sales, how would that change "ripple through" the financial statements (shown in thousands below) and influence the stock price? 169 170 171 Accounts receivable $878 Current liabilities $845 172 Other current assets 1,802 Debt 700 173 Net fixed assets 817 Equity 1,952 174 Total assets $3,497 Liabilities plus equity $3,497 175 176 First, we need to calculate the company's's daily sales. 177 178 Daily sales = Sales 365 179 Daily sales = 180 Daily sales = #N/A 181 182 Target A/R = Daily sales Target DSO Target A/R = 184 Target A/R = #N/A 185 186 Freed-up cash = old A/R new AIR 187 Freed-up cash = 188 Freed-up cash = | #N/A 183