image has been updated

2 3 4 5 6 7 8 In cell D74, by using cell references, calculate the net profit margin percentage for Procter & Gamble in year 2017. Use the relevant cells from the Given Data section. In cell D75, by using cell references, calculate the inventory turnover for Procter & Gamble in year 2017. Use the relevant cells from the Given Data section. In cell D76, by using cell references, calculate the current ratio for Procter & Gamble in year 2017. Use the relevant cells from the Given Data section. In cell 077, by using cell references, calculate the return on assets percentage for Procter & Gamble in year 2017. Use the relevant cells from the Given Data section. In cell D78, by using cell references, calculate the return on equity percentage for Procter & Gamble in year 2017. Use the relevant cells from the Given Data section. In cell D79, by using cell references, calculate the debt to equity ratio for Procter & Gamble in year 2017. Use the relevant cells from the Given Data section. In cell ranges F74:F79, H74:F79, K74:K79, M74:M79, 074:079, by using cell references, calculate the gross margin, inventory turnover, current ratio, return on assets, return on equity, and debt to equity ratios for Procter & Gamble in year 2016 and 2015, and for Johnson & Johnson in the three years. Copy the contents from cells D74:D79 and paste the contents to cell ranges F74:F79, H74:H79, K74:K79, M74:M79, and 074:079. In cell D80, by using cell references, calculate the earnings per share for Procter & Gamble in year 2017. Use the relevant cells from the Given Data section. In cells F80 and H80, by using absolute and relative cell references, calculate the earnings per share ratios for Procter & Gamble in years 2016 and 2015, respectively. Use the relevant cells from the Given Data section. Copy the content from cell D80 and paste it to cells F80 and H80. In cell K80, by using cell references, calculate the earnings per share for Johnson & Johnson in year 2017. Use the relevant cells from the Given Data section. In cells M80 and 080, by using absolute and relative cell references, calculate the earnings per share ratios for Johnson & Johnson in years 2016 and 2015, respectively. Use the relevant cells from the Given Data section. Copy the content from cell K80 and paste it to cells M80 and 080. 9 10 11 12 Common-size statements: Procter & Gamble and Johnson & Johnson and their ratios. In the spreadsheet below are the income statement data and the balance sheet data for Procter & Gamble and Johnson & Johnson for th past three years. The two companies are compared using common-size statements for each of the three years (see Figures 14.3 and 14.4). Now calculate the financial ratios for Procter & Gamble and Johnson & Johnson, and complete the key ratios in Table 14.7 for the most recent three years. The outstanding shares for each company are given in the data table. For inventory turnover, use the cost of revenues as the cost of goods sold. 4 % 100.00% 52.38% 47.62% Johnson & Johnson 2017 % 2016 % 2015 $ 71,890,000 100.00% $ 70,074,000 100.00% $ 74,331,000 $ 21,685,000 30.16% $ 21,536,000 30.73% $ 22,746,000 $ 50,205,000 69.84% $ 48,538,000 69.27% $ 51,585,000 % 100.00% 30.60% 69.40% 5 6 Given Data 7 8 Shares Outstanding (Procter & Gamble) 2,540,000 9 Shares Outstanding (Johnson & Johnson) 2,690,000 10 11 Procter & Gamble 12 Income Statements 2017 % 2016 % 2015 13 Total Revenue $ 65,058,000 100.00% $ 65,299,000 100.00% $ 70,749,000 14 Cost of Revenue $ 32,535,000 50.01% $ 32,909,000 50.40% $ 37,056,000 15 Gross Profit $ 32,523,000 49.99% $ 32,390,000 49.60% $ 33,693,000 16 Operating Expenses 17 Research Development 18 SG&A $ 18,568,000 28.54% $ 18,949,000 29.02% $ 22,644,000 19 Non Recurring 20 Other 21 Operating Income or Loss $ 13,955,000 21.45% $ 13,441,000 20.58% $ 11,049,000 22 Other Income/Expenses (Net) $ (233,000) -0.36% $ 507,000 0.78% $ 589,000 23 EBIT $ 13,722,000 21.09% $ 13,948,000 21.36% $ 11,638,000 24 Interest Expense $ 465,000 0.71% $ 579,000 0.89% $ 626,000 25 Income Before Taxes $ 13,257,000 20.38% $ 13,369,000 20.47% $ 11,012,000 26 Income Tax Expense $ 3,063,000 4.71% $ 3,342,000 5.12% $ 2,725,000 27 Net Income $ 10,194,000 15.67% $ 10,027,000 15.36% $ 8,287,000 28 29 30 Procter & Gamble 14-2 + 32.01% $ 9,095,000 $ 19,945,000 $ 520,000 12.65% $ 9,046,000 27.74% $ 21,203,000 0.72% $ 733,000 12.91% $ 8,494,000 30.26% $ 21,954,000 1.05% $ 178,000 15.62% 0.83% 16.45% 0.88% 15.56% 3.85% 11.71% $ 20,645,000 S (116,000) $ 20,529,000 $ 726,000 $ 19,803,000 $ 3,263,000 $ 16,540,000 28.72% $ 17,556,000 -0.16% 2,192,000 28.56% $ 19,748,000 1.01% $ 552,000 27.55% $ 19,196,000 4.54% $ 3,787,000 23.01% $ 15,409,000 25.05% $ 20,959,000 3.13% $ 137,000 28.18% $ 21,096,000 0.79% $ 533,000 27.39% $ 20,563,000 5.40% $ 4,240,000 21.99% $ 16,323,000 11.43% 29.54% 0.24% 0.00% 28.20% 0.18% 28.38% 0.72% 27.66% 5.70% 21.96% Johnson & Johnson > B C D E F J 0 P Q G 15.36% $ H 8,287,000 15.67% $ 10,027,000 L M 23.01% $ 15,409,000 Net Income N 21.99% $ 16,323,000 $ 10,194,000 11.71% $ 16,540,000 21.96% 27 28 29 30 Procter & Gamble 2016 % Johnson & Johnson 2016 % 31 2017 % 2015 % 2017 % 2015 % 32 33 34 35 $ 5,569,000 $ 9,568,000 $ 4,594,000 $ 4,624,000 $ 2,139,000 $ 26,494,000 4.63% $ 7,102,000 7.95% $ 6,246,000 3.82% $ 5,880,000 3.84% $ 4,716,000 1.78% $ 9,838,000 22.00% $ 33,782,000 5.59% $ 6,836,000 4.91% $ 4,767,000 4.62% $ 5,924,000 3.71% $ 4,979,000 7.74% $ 7,140,000 26.57% $ 29,646,000 5.28% 3.68% 4.57% 3.84% 5.51% 22.89% $ 18,972,000 $ 22,935,000 $ 11,699,000 $ 8,144,000 $ 3,282,000 $ 65,032,000 13.44% $ 13,732,000 16.24% $ 24,644,000 8.28% $ 10,734,000 5.77% $ 8,053,000 2.32% $ 3,047,000 46.05% $ 60,210,000 10.29% $ 14,523,000 18.47% $ 18,566,000 8.05% $ 10,985,000 6.04% $ 8,184,000 2.28% $ 3,486,000 45.13% $ 55,744,000 11.14% 14.24% 8.43% 6.28% 2.67% 42.76% 36 37 38 Balance Sheet Current Assets Cash and Equivalents Short-Term Investments Net Receivables Inventories Other Current Assets Total Current Assets Long-Term Investments Property, Plant, and Equipment Goodwill Intangible Assets Accumulated Amortization Other Assets Deferred Long-Term Assets Total Assets 39 40 41 $ 19,893,000 $ 44,699,000 $ 24,187,000 16.52% $ 19,385,000 37.12% $ 44,350,000 20.09% $ 24,527,000 15.25% $ 24,859,000 34.88% $ 44,622,000 19.29% $ 25,010,000 19.20% 34.46% 19.31% $ 15,912,000 $ 22,805,000 $ 26,876,000 11.27% $ 15,905,000 16.15% $ 21,629,000 19.03% $ 25,764,000 11.92% $ 16,126,000 16.21% $ 21,832,000 19.31% $ 27,222,000 12.37% 16.75% 20.88% 42 43 44 $ 5,133,000 4.26% $ 5,092,000 4.01% $ 5,358,000 4.14% 45 $ 4,435,000 S 6,148,000 $ 141,208,000 3.14% $ 4,413,000 4.35% $ 5,490,000 100.00% $133,411,000 3.31% $ 3,232,000 4.12% $ 6,202,000 100.00% $130,358,000 2.48% 4.76% 100.00% 46 $ 120,406,000 100.00% $ 127,136,000 100.00% $ 129,495,000 100.00% 47 48 49 $ 16,656,000 $ 13,554,000 $ 21,603,000 $ 4,684,000 15.30% $ 20,743,000 3.32% $ 7,004,000 15.55% $ 21,393,000 5.25% $ 3,638,000 16.41% 2.79% 50 51 52 53 Current Liabilities Accounts Payable Short/Current Debt Other Current Liabilities Total Current Liabilities Long-Term Debt Other Liabilities Deferred Long-Term Liabilities Minority Interests Totallibilities 14-2 13.83% $ 16,774,000 11.26% $ 11,653,000 $ 2,343,000 25.09% $ 30,770,000 14.98% $ 18,945,000 6.86% $ 10,325,000 6.75% $ 9,113,000 0.49% $ 642,000 BA 170/ 60 705 nnn $ 30,210,000 $ 18,038,000 $ 8,254,000 $ 8,126,000 $ 594,000 68 222nnn 13.19% $ 16,229,000 9.17% $ 12,018,000 1.84% $ 1,543,000 24.20% $ 29,790,000 14.90% $ 18,327,000 8.12% $ 9,149,000 7.17% $ 9,179,000 0.50% $ 631,000 BA0/. 67076 nnn 12.53% 9.28% 1.19% 23.00% 14.15% 7.07% 7.09% 0.49% 1 000 $ 26,287,000 $ 22,442,000 $ 19,151,000 $ 2,910,000 18.62% $ 27,747,000 15.89% $ 12,857,000 13.56% $ 19,095,000 2.06% $ 2,562,000 20.80% $ 25,031,000 9.64% $ 15,122,000 14.31% $ 18,006,000 1.92% $ 2,447,000 19.20% 11.60% 13.81% 1.88% 54 55 56 70.700 nnn En 1207. 62 761 non 166707. 60606 non 16 4007. 90% J K L M N O P $ 70,790,000 50.13% $ 62,261,000 46.67% $ 60,606,000 46.49% B C D E F G H 1 56 Minority Interests $ 594,000 0.49% $ 642,000 0.50% $ 631,000 0.49% 57 Total Liabilities $ 65,222,000 54.17% $ 69,795,000 54.90% $ 67,076,000 51.80% 58 Stockholder's Equity 59 Misc. Stocks Options Warrants 60 Redeemmable Preferred Stock 61 Preferred Stock 62 Common Stock $ 4,009,000 3.33% $ 4,009,000 3.15% $ 4,009,000 3.10% 63 Retained Earnings $ 96,124,000 79.83% $ 87,953,000 69.18% $ 84,807,000 65.49% 64 Treasury Stock $ (93,715,000) -77.83% S (82,176,000) -64.64% $ (77,226,000) -59.64% 65 Capital Surplus $ 63,641,000 52.86% $ 63,714,000 50.11% $ 63,852,000 49.31% 66 Other Stockholders' Equity $ (15,881,000) -13.19% $ (17,197,000 -13.53% $ (14,100,000) -10.89% 67 Total Stockholders' Equity $ 55,184,000 45.83% $ 57,341,000 45.10% $ 62,419,000 48.20% 68 Total Lia. & Stockholders' Equity $ 120,406,000 100.00% $127,136,000 100.00% $129,495,000 100.00% 69 70 Solution $ 3,120,000 2.21% $ 3,120,000 2.34% $ 3,120,000 2.39% $110,551,000 78.29% $103,879,000 77.86% $ 97,245,000 74.60% $ (28,352,000) -20.08% S (22,684,000) -17.00% S (19,891,000) -15.26% $ (14,901,000) -10.55% $ (13,165,000) $ 70,418,000 49.87% $ 71,150,000 $ 141,208,000 100.00% $133,411,000 -9.87% $ (10,722,000) -8.23% 53.33% $ 69,752,000 53.51% 100.00% $ 130,358,000 100.00% 71 72 hnson Procter & Gamble 2016 Johnson & 2016 2017 2015 2017 2015 73 74 75 76 77 78 Net Profit Margin Inventory Turnover Current Ratio Return on Assets Return on Equity Debt to Equity Earnings Per Share 79 80 81