Chapter 3, Problem 1SE

Spreadsheet Exercise

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 company’s management has asked you to perform a detailed financial statement analysis of the firm.

The income statements for the years ending December 31, 2019 and 2018, respectively, are presented in the following table.

Annual Income Statements (Values in Millions)

	For the year ended
	December 31, 2019	December 31, 2018
Sales	$178,909	$187,510
Cost of goods sold	109,701	11 1,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 (average)	35.324%	37.945%
Dividends paid per share	$1.13	$0.91
Basic EPS from total operations	$1.34	$2.25

You also have the following balance sheet information as of December 31, 2019 and 2018, respectively.

Annual Balance Sheets (Values in Millions)

	December 31, 2019	December 31, 2018
Cash	$ 7.229	$ 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
Retained earnings	74,597	73,161
Total common shares outstanding	6.7 billion	6.8 billion

To Do

1. a. Create a spreadsheet similar to Table 3.1 to model the following:
   1. 1. A multiple-step comparative income statement for Dayton Inc. for the periods ending December 31, 2019 and 2018. You must calculate the cost of goods sold for the year 2019.
   2. 2. A common-size income statement for Dayton Inc. covering the years 2019 and 2018.
2. b. Create a spreadsheet similar to Table 3.2 to model the following:
   1. 1. A detailed, comparative balance sheet for Dayton Inc. for the years ended December 31, 2019 and 2018.
   2. 2. A common-size balance sheet for Dayton Inc. covering the years 2019 and 2018.
3. c. Create a spreadsheet similar to Table 3.10 to perform the following analysis:
   1. 1. Create a table that reflects both 2019 and 2018 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. 2. Compare the 2019 ratios to the 2018 ratios. Indicate whether the results “outperformed the prior year” or “underperformed relative to the prior year.”