Spreadsheet Exercise: Chapter 3 #NAME? The income statement and balance sheet are the primary reports that a firm constructs for use by management and for distribution to stockholders, regulatory bodies, and the general public. They are the primary sources of historical financial information about the firm. Dayton Products, Inc., is a moderate-sized manufacturer. The companys management has asked you to perform a detailed financial statement analysis of the firm. The income statements for the years ending December 31, 2022 and 2021, respectively, are presented in the following table. Important: Do not use cell references to cells in rows 8 to 37. For all required answers, use cell references to values starting in row 58) Annual Income Statements (Values in $ Thousands) 2022 2021 Sales $178,909 $187,510 Cost of goods sold 109,701 111,631 Selling, general and administrative expenses 12,356 12,900 Other tax expense 33,572 33,377 Depreciation and amortization 12,103 7,944 Other income (add to EBIT to arrive at EBT) 3,147 3,323 Interest expense 398 293 Income tax rate 21% 21% Dividends paid per share $1.15 $0.91 Basic EPS from total operations $1.64 $2.87 You also have the following balance sheet information as of December 31, 2022 and 2021, respectively. Annual Balance Sheets (Values in $ Thousands) 2022 2021 Cash $9,090 $6,547 Receivables 21,163 19,549 Inventories 8,068 7,904 Other current assets 1,831 1,681 Property, plant and equipment, gross 204,960 187,519 Accumulated depreciation and depletion 110,020 97,917 Other noncurrent assets 19,413 17,891 Accounts payable 13,792 22,862 Short-term debt payable 4,093 3,703 Other current liabilities 15,290 3,549 Long-term debt payable 6,655 7,099 Deferred income taxes 16,484 16,359 Other noncurrent liabilities 21,733 16,441 Total Stockholders' Equity 76,458 73,161 Total common shares outstanding (million) 6.7 6.8 To Do a. Create a spreadsheet similar to Table 3.1 to model the following: (1) A multiple-step comparative income statement for Dayton, Inc., for the periods ending December 31, 2022 and 2021. You must calculate the cost of goods sold for the year 2022. (2) A common size income statement for Dayton, Inc., covering the years 2022 and 2021. b. Create a spreadsheet similar to Table 3.2 to model the following: (1) A detailed, comparative balance sheet for Dayton, Inc., for the years ended December 31, 2022 and 2021. (2) A common size balance sheet for Dayton, Inc., covering the years 2022 and 2021. c. Create a spreadsheet similar to Table 3.5 to perform the following analysis: (1) Create a table that reflects both 2022 and 2021 operating ratios for Dayton, Inc., segmented into (a) liquidity, (b) activity, (c) debt, (d) profitability, and (e) market. Assume that the current market price for the stock is $90. (2) Compare the 2022 ratios to the 2021 ratios. Indicate whether the results outperformed the prior year or underperformed relative to the prior year. Solution a. Create a spreadsheet similar to Table 3.1 to model the following: (1) A multiple-step comparative income statement for Dayton, Inc., for the periods ending December 31, 2022 and 2021. You must calculate the cost of goods sold for the year 2022. (2) A common size income statement for Dayton, Inc., covering the years 2022 and 2021. Annual Income Statement (Values in $ Thousands) Common Size 2022 2021 2022 2021 Sales $178,909 $187,510 Cost of goods sold 109,701 111,631 Gross operating profit $69,208 Selling, general and administrative expenses 12,356 12,900 Other taxes 33,572 33,377 EBITDA $45,928 Depreciation and amortization 12,103 7,944 EBIT $33,825 Other income 3,147 3,323 Earnings before interest and taxes $14,324 Interest expense 398 293 Earnings before taxes $13,926 Income taxes 21% 9,007 Net income available to common shareholders $6,700 EPS $1.64 $2.87 Dividends per share $1.15 $0.91 b. Create a spreadsheet similar to Table 3.2 to model the following: (1) A detailed, comparative balance sheet for Dayton, Inc., for the years ended December 31, 2022 and 2021. (2) A common size balance sheet for Dayton, Inc., covering the years 2022 and 2021. Annual Balance Sheet (Values in $ Thousands) Common Size 2022 2021 2022 2021 Assets Current assets Cash $9,090 $6,547 Receivables 21,163 19,549 Inventories 8,068 7,904 Other current assets 1,831 1,681 Total current assets Noncurrent Assets Property, plant and equipment, gross $204,960 $187,519 Accumulated depreciation and depletion 110,020 97,917 Property, plant and equipment, net Other noncurrent assets 19,413 17,891 Total noncurrent assets Total Assets Liabilities and Stock Equity Current liabilities Accounts payable $13,792 $22,862 Short-term debt payable 4,093 3,703 Other current liabilities 15,290 3,549 Total current liabilities Noncurrent liabilities Long-term debt payable $6,655 $7,099 Deferred income taxes 16,484 16,359 Other noncurrent liabilities 21,733 16,441 Total noncurrent liabilities Total Liabilities Total Stockholders' Equity $76,458 $73,161 Total Liabilities and Stock Equity Total common shares outstanding (million) 6.7 6.8 c. Create a spreadsheet similar to Table 3.5 to perform the following analysis: (1) Create a table that reflects both 2022 and 2021 operating ratios for Dayton, Inc., segmented into (a) liquidity, (b) activity, (c) debt, (d) profitability, and (e) market. Assume that the current market price for the stock is $90. (2) Compare the 2022 ratios to the 2021 ratios. Indicate whether the results outperformed the prior year or underperformed relative to the prior year. 2022 2021 Analysis Liquidity Ratios Current ratio Quick ratio Activity Ratios Inventory turnover Average days of inventory Accounts receivable turnover Average collection period Fixed asset turnover Total asset turnover Debt Ratios Debt-to-asset ratio Times interest earned Profitability Ratios Gross profit margin Operating profit margin Net profit margin Return on assets Return on equity Market Ratios Earnings per share $1.64 $2.87 Price per share $90 NA P/E ratio NA Points 1 In cells G60 and H60, by using cell references to the given data, calculate the gross operating profit for years 2022 and 2021, respectively. 2 2 In cells G63 and H63, by using cell references to the given data, calculate the EBITDA for years 2022 and 2021, respectively. 2 3 In cells G65 and H65, by using cell references to the given data, calculate the EBIT for years 2022 and 2021, respectively. 2 4 In cells G67 and H67, by using cell references to the given data, calculate the earnings before interest and taxes for years 2022 and 2021, respectively. 2 5 In cells G69 and H69, by using cell references to the given data, calculate the earnings before taxes for years 2022 and 2021, respectively. 2 In cells G70 and H70, by using cell references to the given data, calculate the income tax for years 2022 and 2021, respectively. 2 6 In cells G71 and H71, by using cell references to the given data, calculate the net income available to common shareholders for years 2022 and 2021, respectively. 2 7 In cell range I58:I71, by using cell references to the given data, calculate the common size income statement for year 2022. Begin in cell I58 with an absolute reference to cell G58 and copy/paste down the column. 14 8 In cell range J58:J71, by using cell references to the given data, calculate the common size income statement for year 2021. Begin in cell J58 with an absolute reference to cell H58 and copy/paste down the column. 14 9 In cells G88 and H88, by using cell references to the given data, calculate the total current assets for years 2022 and 2021, respectively. 2 10 In cells G92 and H92, by using cell references to the given data, calculate the property, plant and equipment, net for years 2022 and 2021, respectively. 2 11 In cells G94 and H94, by using cell references to the given data, calculate the total noncurrent assets for years 2022 and 2021, respectively. 2 12 In cells G95 and H95, by using cell references to the given data, calculate the total assets for years 2022 and 2021, respectively. 2 13 In cells G101 and H101, by using cell references to the given data, calculate the total current liabilities for years 2022 and 2021, respectively. 2 14 In cells G106 and H106, by using cell references to the given data, calculate the total noncurrent liabilities for years 2022 and 2021, respectively. 2 15 In cells G107 and H107, by using cell references to the given data, calculate the total liabilities for years 2022 and 2021, respectively. 2 16 In cells G110 and H110, by using cell references to the given data, calculate the total liabilities and stock equity for years 2022 and 2021, respectively. 2 17 In cell ranges I84:I88, I90:I95, I98:I101 and I103:I110, by using absolute cell references to the given data, calculate the common size balance sheet for year 2022. (Although the values are equivalent, be sure to reference the Total Asset value rather than the Total Liabilities and Stock Equity.) 23 18 In cell ranges J84:J88, J90:J95, J98:J101 and J103:J110, by using absolute cell references to the given data, calculate the common size balance sheet for year 2021. (Although the values are equivalent, be sure to reference the Total Asset value rather than the Total Liabilities and Stock Equity.) 23 19 In cells F120 and G120, by using cell references to the given data, calculate the current ratio for years 2022 and 2021, respectively. 2 In cell I120, type either Better or Worse depending on whether the current ratio improved or worsened from year 2021 to year 2022. 1 20 In cells F121 and G121, by using cell references to the given data, calculate the quick ratio for years 2022 and 2021, respectively. 2 In cell I121, type either Better or Worse depending on whether the quick ratio improved or worsened from year 2021 to year 2022. 1 21 In cells F123 and G123, by using cell references to the given data, calculate the inventory turnover for years 2022 and 2021, respectively. Use a 365-day year. 2 In cell I123, type either Better or Worse depending on whether the inventory turnover improved or worsened from year 2021 to year 2022. 1 22 In cells F124 and G124, by using cell references to the given data, calculate the average days of inventory for years 2022 and 2021, respectively. 2 In cell I124, type either Better or Worse depending on whether the average days of inventory improved or worsened from year 2021 to year 2022. 1 23 In cells F125 and G125, by using cell references to the given data, calculate the accounts receivable turnover for years 2022 and 2021, respectively. 2 In cell I125, type either Better or Worse depending on whether accounts receivable turnover improved or worsened from year 2021 to year 2022. 1 24 In cells F126 and G126, by using cell references to the given data, calculate the average collection period for years 2022 and 2021, respectively. Use a 365-day year. 2 In cell I126, type either Better or Worse depending on whether the average collection period improved or worsened from year 2021 to year 2022. 1 25 In cells F127 and G127, by using cell references to the given data, calculate the fixed asset turnover for years 2022 and 2021, respectively. 2 In cell I127, type either Better or Worse depending on whether the fixed asset turnover improved or worsened from year 2021 to year 2022. 1 26 In cells F128 and G128, by using cell references to the given data, calculate the total asset turnover for years 2022 and 2021, respectively. 2 In cell I128, type either Better or Worse depending on whether the total asset turnover improved or worsened from year 2021 to year 2022. 1 27 In cells F130 and G130, by using cell references to the given data, calculate the debt-to-asset ratio for years 2022 and 2021, respectively. (Although the values are equivalent, be sure to reference the Total Asset value rather than the Total Liabilities and Stock Equity.) 2 28 In cells F131 and G131, by using cell references to the given data, calculate the times interest earned for years 2022 and 2021, respectively. 2 In cell I131, type either Better or Worse depending on whether the times interest earned improved or worsened from year 2021 to year 2022. 1 29 In cells F133 and G133, by using cell references to the given data, calculate the gross profit margin for years 2022 and 2021, respectively. 2 In cell I133, type either Better or Worse depending on whether the gross profit margin improved or worsened from year 2021 to year 2022. 1 30 In cells F134 and G134, by using cell references to the given data, calculate the operating profit (EBIT) margin for years 2022 and 2021, respectively. 2 In cell I134, type either Better or Worse depending on whether the operating profit margin improved or worsened from year 2021 to year 2022. 1 31 In cells F135 and G135, by using cell references to the given data, calculate the net profit margin for years 2022 and 2021, respectively. 2 In cell I135, type either Better or Worse depending on whether the net profit margin improved or worsened from year 2021 to year 2022. 1 32 In cells F136 and G136, by using cell references to the given data, calculate the return on assets for years 2022 and 2021, respectively. 2 In cell I136, type either Better or Worse depending on whether the return on assets improved or worsened from year 2021 to year 2022. 1 33 In cells F137 and G137, by using cell references to the given data, calculate the return on equity for years 2022 and 2021, respectively. 2 In cell I137, type either Better or Worse depending on whether the return on equity improved or worsened from year 2021 to year 2022. 1 In cell I139, type either Better or Worse depending on whether the earnings per share improved or worsened from year 2021 to year 2022. 1 34 In cell F141, by using cell references to the given data, calculate the P/E ratio for year 2022.