- Create an Excel spreadsheet for the financial statements on page 144-146 (Tables IC 4.1, IC 4.2 and IC 4.3). Note for Table IC 4.3 only input the industry averages. The individual year data will be calculated in Step 2.
- Using cell references calculate the ratios for 2017, 2018 and 2019E.
- Answer b,c,d,e and f on page 144/145. Place your answer in a text box on your spreadsheet. Make sure your textbox is easy to read and the user of the data can see the ratios and your interpretations at the same time.
D'LEON INC., PART II 4-26 FINANCIAL STATEMENTS AND TAXES Part I of this case, presented in Chapter 3, discussed the situation of D'Leon Inc., a regional snack foods producer, after an expansion program. D'Leon had increased plant capacity and undertaken a major marketing campaign in an attempt to "go national." Thus far, sales have not been up to the forecasted level, costs have been higher than were projected, and a large loss occurred in 2018 rather than the expected profit. As a result,, its managers, directors, and investors are concerned about the firm's survival. Donna Jamison was brought in as assistant to Fred Campo, D'Leon's chairman, who had the task of getting the company back into a sound financial position. D'Leon's 2017 and 2018 balance sheets and income statements, together with projections for 2019, are given in Tables IC 4.1 and IC 4.2. In addition, Table IC 4.3 gives the company's 2017 and 2018 financial ratios, together with industry average data. The 2019 projected financial statement data represent Jamison's and Campo's best guess for 2019 results, assuming that some new financing is arranged to get the company "over the hump." Jamison examined monthly data for 2018 (not given in the case), and she detected an improving pat- tern during the year. Monthly sales were rising, costs were falling, and large losses in the early months had turned to a small profit by December. Thus, the annual data look somewhat worse than final monthly data. Also, it appears to be taking longer for the advertising program to get the message out, for the new sales offices to generate sales, and for the new manufacturing facilities to operate efficiently. In other words, the lags between spending money and deriving benefits were longer than D'Leon's managers had anticipated. For these reasons, Jamison and Campo see hope for the company-provided it can survive in the short run. analysis of where the company is now, what it must do to regain its financial Jamison must prepare an health, and what actions should be taken. Your assignment is to help her answer the following questions. Provide clear explanations, not yes or no answers. Why b. Calculate D'Leon's 2019 current and quick ratios based on the projected balance sheet and income statement data. What can you say about the company's liquidity positions in 2017, in 2018, and as projected for 2019? We often think of ratios as being useful (1) to managers to help run the business, (2) to bankers for credit analysis, and (3) to stockholders for stock valuation. Would these different types of analysts have an are ratios useful? What are the five major categories of ratios? a. equal interest in the company's liquidity ratios? Explain your answer Calculate the 2019 inventory turnover, days sales utstandine (DSO), fixed assets turmover, and total assets turnover. How does D'Leon's utilization of assets stack up against other firms in the industry? d. Calculate the 2019 debt-to-capital and times-interest-earned ratics. How does D'Leon compare with the industry with respect to financial leverage? What can you conclude from these ratics? Calculate the 2019 operating margin, profit margin. basic earning power (BEP), return on assets (ROA), retum on equity (ROE), and return on invested canital (ROIC), What can you say about these ratios? Calculate the 2019 price/earnings ratio and market /book ratio. Do these ratios indicate that investors are expected to have a high or low opinion of the company? Use the Dulont equation to provide projected for 2019. What are the firm's major strengths and weaknesses? C a summary and overview of DLeon's financial condition as h Use the following simplified 2019 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 collec- tion procedures and thereby lower its DSO from 456 days to the 32-day industry average without affecting sales, how would that change "ripple through" the financial statements (shown in thou- sands below) and influence the stock price? Accounts receiable S 878 S 845 Current liabilities Other current assets Debt 1802 700 Net fved asets 817 1,952 Equity Total assets 53497 Liabilities plus equity 53,497 Does it appear that inventories could be adjusted? If so, how should that adjustment affect DLeon's profitability and stock price? In 2018, the company paid its suppliers much later than the due dates; also, it was not maintaining financial ratios at levels called for in its bank loan agreements. Therefore, suppliers could cut the com- pany off, and its bank could refuse to renew the loan when it comes due in 90 days, On the basis of data. provided, would you, as a credit manager, continue to sell to D'Leon on credit? (You could demand cash on delivery-that is, sell on terms of COD-but that might cause D'Leon to stop buying from your company.) Similarly, if you were the bank loan officer, would you recommend renewing the loan or demanding its repayment? Would your actions be influenced if, in early 2019, DLeon showed you its 2019 projections along with proof that it was going to raise more than $1.2 million of new equity? k. i. In hindsight, what should DLeon have done in 2017? What are some potential problems and limitations of financial ratio analysis? m. What are some qualitative factors that analysts should consider when evaluating a company's likely future financial performance? TABLE IC 4. Balance Sheets 2018 2019 2017 Assets 85,632 7,282 632,160 1,287,360 $1.926,802 57,600 Cash 351,200 Accounts receivable Inventories 878.000 1,716,480 $2.680,112 715.200 S1,124,000 Total current assets 1,202,950 1,197,160 380,120 S 817.040- S3.497,152 491000 Gross fixed assets 263160 146200 Less accumulated depreciation 5939,790 5 344,800 Net foxed assets $1,468.800 $2.866 592 Total assets Liebilities and Equity Accounts payable S 524,160 S 145,600 136,000 $ 436.800 489,600 408,000 Accruals 200.000 636808 300.000 Notes payable S 481,600 323,432 $1,650,568 $1.144,800 Total current liabilities 723432 400000 Long-term debt Common stock 460,000 460.000 1,721.176 231,176 201.768 32.592 Retained earnings S 663.768 5492,5925 $1.952.352 Total equity $1.468 8004 $2.866.592 $3497,152 Total labilities and equity Note:E indicates estimated. The 2019 data are forecasts Income Statements TABLE IC 4.2 2018 2017 2019E 6,034.000 5 3A32000 $7,035,600 Sales 5,528,000 26A000 5875.9925 Cost of goods sold 519,988 358,672 s50,000 Other expenses Total operating costs excluding depreciation and S 6,047,988 $ 3.222,672 $6425,992 amortization 13,988) S 209.328 s 609,608 EBITDA 116,960 18,900 116,960 Deprecistion and amortization ($ 130,948) s 190428 S 492.648. EBIT 136,012 70.008 43828 s 146600 Interest expernse $ 422,640 (S 266.960) EBT (106,784) 169.056 58640 87,960 Taxes (40 %) (S 160,176) S 253.584 Net income (S 1.602) S 1.014 S 0880 EPS S 0.220 50.110 S 0220 DPS $ 4.926 5 6638 S 7.809 Book value per share S 2.25 S 850 S 12.17 Stock price 100,000 100.000 250,000 Shares outstanding 40.00 % 40.00 % 40.00% Tax rate $ 40,000 $40,000 $ 40.000 Lease payments C Sinking fund payments Note: E indicates estimated, The 2019 data are forecasts. The firm had sufficient taxable income in 2016 and 2017 to obtain its full tax refund in 2018. TABLE IC 4.3 Ratio Analysis 2017 2019E 2018 Industry Average 1.2X 23x 27x Current Quick 0.4X 0.8X 1,0x Inventory turnover 4.7X 4.8x 6.1x Days sales outstanding (DSO 38.2 374 32.0 Fixed assets turnover 64x 10.0x 7.0x Total assets turnover 2.1X 2.3x 2.6x Debt-to-capital ratio 73.4% 44.1% 40.0% TIE -1.0x 4.3x 6.2x Operating margin -2.2% 5.5% 7.3% Profit margin -2.7 % 26% 3.5% Basic earning power -4,6% 13.0% 19.1% ROA -5.6% 6.0% 9.1% ROE -32,5 % 13.3% 18.2% ROIC -4.2 % 9.6% 14.5% Price/earnings -1.4x 9.7x 14.2X Market/book 0.5x 1.3x 24x Book value per share $4.93 $6.64 na. Note: E indicates estimated. The 2019 data are forecasts Calculation is based on a 365-day year D'LEON INC., PART II 4-26 FINANCIAL STATEMENTS AND TAXES Part I of this case, presented in Chapter 3, discussed the situation of D'Leon Inc., a regional snack foods producer, after an expansion program. D'Leon had increased plant capacity and undertaken a major marketing campaign in an attempt to "go national." Thus far, sales have not been up to the forecasted level, costs have been higher than were projected, and a large loss occurred in 2018 rather than the expected profit. As a result,, its managers, directors, and investors are concerned about the firm's survival. Donna Jamison was brought in as assistant to Fred Campo, D'Leon's chairman, who had the task of getting the company back into a sound financial position. D'Leon's 2017 and 2018 balance sheets and income statements, together with projections for 2019, are given in Tables IC 4.1 and IC 4.2. In addition, Table IC 4.3 gives the company's 2017 and 2018 financial ratios, together with industry average data. The 2019 projected financial statement data represent Jamison's and Campo's best guess for 2019 results, assuming that some new financing is arranged to get the company "over the hump." Jamison examined monthly data for 2018 (not given in the case), and she detected an improving pat- tern during the year. Monthly sales were rising, costs were falling, and large losses in the early months had turned to a small profit by December. Thus, the annual data look somewhat worse than final monthly data. Also, it appears to be taking longer for the advertising program to get the message out, for the new sales offices to generate sales, and for the new manufacturing facilities to operate efficiently. In other words, the lags between spending money and deriving benefits were longer than D'Leon's managers had anticipated. For these reasons, Jamison and Campo see hope for the company-provided it can survive in the short run. analysis of where the company is now, what it must do to regain its financial Jamison must prepare an health, and what actions should be taken. Your assignment is to help her answer the following questions. Provide clear explanations, not yes or no answers. Why b. Calculate D'Leon's 2019 current and quick ratios based on the projected balance sheet and income statement data. What can you say about the company's liquidity positions in 2017, in 2018, and as projected for 2019? We often think of ratios as being useful (1) to managers to help run the business, (2) to bankers for credit analysis, and (3) to stockholders for stock valuation. Would these different types of analysts have an are ratios useful? What are the five major categories of ratios? a. equal interest in the company's liquidity ratios? Explain your answer Calculate the 2019 inventory turnover, days sales utstandine (DSO), fixed assets turmover, and total assets turnover. How does D'Leon's utilization of assets stack up against other firms in the industry? d. Calculate the 2019 debt-to-capital and times-interest-earned ratics. How does D'Leon compare with the industry with respect to financial leverage? What can you conclude from these ratics? Calculate the 2019 operating margin, profit margin. basic earning power (BEP), return on assets (ROA), retum on equity (ROE), and return on invested canital (ROIC), What can you say about these ratios? Calculate the 2019 price/earnings ratio and market /book ratio. Do these ratios indicate that investors are expected to have a high or low opinion of the company? Use the Dulont equation to provide projected for 2019. What are the firm's major strengths and weaknesses? C a summary and overview of DLeon's financial condition as h Use the following simplified 2019 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 collec- tion procedures and thereby lower its DSO from 456 days to the 32-day industry average without affecting sales, how would that change "ripple through" the financial statements (shown in thou- sands below) and influence the stock price? Accounts receiable S 878 S 845 Current liabilities Other current assets Debt 1802 700 Net fved asets 817 1,952 Equity Total assets 53497 Liabilities plus equity 53,497 Does it appear that inventories could be adjusted? If so, how should that adjustment affect DLeon's profitability and stock price? In 2018, the company paid its suppliers much later than the due dates; also, it was not maintaining financial ratios at levels called for in its bank loan agreements. Therefore, suppliers could cut the com- pany off, and its bank could refuse to renew the loan when it comes due in 90 days, On the basis of data. provided, would you, as a credit manager, continue to sell to D'Leon on credit? (You could demand cash on delivery-that is, sell on terms of COD-but that might cause D'Leon to stop buying from your company.) Similarly, if you were the bank loan officer, would you recommend renewing the loan or demanding its repayment? Would your actions be influenced if, in early 2019, DLeon showed you its 2019 projections along with proof that it was going to raise more than $1.2 million of new equity? k. i. In hindsight, what should DLeon have done in 2017? What are some potential problems and limitations of financial ratio analysis? m. What are some qualitative factors that analysts should consider when evaluating a company's likely future financial performance? TABLE IC 4. Balance Sheets 2018 2019 2017 Assets 85,632 7,282 632,160 1,287,360 $1.926,802 57,600 Cash 351,200 Accounts receivable Inventories 878.000 1,716,480 $2.680,112 715.200 S1,124,000 Total current assets 1,202,950 1,197,160 380,120 S 817.040- S3.497,152 491000 Gross fixed assets 263160 146200 Less accumulated depreciation 5939,790 5 344,800 Net foxed assets $1,468.800 $2.866 592 Total assets Liebilities and Equity Accounts payable S 524,160 S 145,600 136,000 $ 436.800 489,600 408,000 Accruals 200.000 636808 300.000 Notes payable S 481,600 323,432 $1,650,568 $1.144,800 Total current liabilities 723432 400000 Long-term debt Common stock 460,000 460.000 1,721.176 231,176 201.768 32.592 Retained earnings S 663.768 5492,5925 $1.952.352 Total equity $1.468 8004 $2.866.592 $3497,152 Total labilities and equity Note:E indicates estimated. The 2019 data are forecasts Income Statements TABLE IC 4.2 2018 2017 2019E 6,034.000 5 3A32000 $7,035,600 Sales 5,528,000 26A000 5875.9925 Cost of goods sold 519,988 358,672 s50,000 Other expenses Total operating costs excluding depreciation and S 6,047,988 $ 3.222,672 $6425,992 amortization 13,988) S 209.328 s 609,608 EBITDA 116,960 18,900 116,960 Deprecistion and amortization ($ 130,948) s 190428 S 492.648. EBIT 136,012 70.008 43828 s 146600 Interest expernse $ 422,640 (S 266.960) EBT (106,784) 169.056 58640 87,960 Taxes (40 %) (S 160,176) S 253.584 Net income (S 1.602) S 1.014 S 0880 EPS S 0.220 50.110 S 0220 DPS $ 4.926 5 6638 S 7.809 Book value per share S 2.25 S 850 S 12.17 Stock price 100,000 100.000 250,000 Shares outstanding 40.00 % 40.00 % 40.00% Tax rate $ 40,000 $40,000 $ 40.000 Lease payments C Sinking fund payments Note: E indicates estimated, The 2019 data are forecasts. The firm had sufficient taxable income in 2016 and 2017 to obtain its full tax refund in 2018. TABLE IC 4.3 Ratio Analysis 2017 2019E 2018 Industry Average 1.2X 23x 27x Current Quick 0.4X 0.8X 1,0x Inventory turnover 4.7X 4.8x 6.1x Days sales outstanding (DSO 38.2 374 32.0 Fixed assets turnover 64x 10.0x 7.0x Total assets turnover 2.1X 2.3x 2.6x Debt-to-capital ratio 73.4% 44.1% 40.0% TIE -1.0x 4.3x 6.2x Operating margin -2.2% 5.5% 7.3% Profit margin -2.7 % 26% 3.5% Basic earning power -4,6% 13.0% 19.1% ROA -5.6% 6.0% 9.1% ROE -32,5 % 13.3% 18.2% ROIC -4.2 % 9.6% 14.5% Price/earnings -1.4x 9.7x 14.2X Market/book 0.5x 1.3x 24x Book value per share $4.93 $6.64 na. Note: E indicates estimated. The 2019 data are forecasts Calculation is based on a 365-day year
