21 Jun Case instructions and grading scheme. Perform the vert
Case instructions and grading scheme.
• Perform the vertical and horizontal analysis and calculate the ratios as instructed (33.1 points-33.1 of your grade). Fill the grey cells by using Excel formulas or cells referencing NOT hardcoding. You will not earn credit for hardcoding (entering the figures manually). • You are a financial analyst. Based on your analysis of Sherwin-Williams Co (SW) financial statements, provide a report to guide investors whether SW is a buy (keep) or sell stock. Your report should include the following (66.9points-61.3% of your grade):
a. An interpretation of trends illustrated by supportive ratios, horizontal and vertical analysis and focus on the main ratios that “tell the story”. (a minimum of 2 pages-not to exceed 3 pages, single space, 12 font size). i. horizontal and vertical analysis-10 pointsii. Liquidity analysis-10 pointsiii. Profitability analysis-10 pointsiv. Solvency analysis –10 pointsb. How did SW perform compared to the industry? (1 paragraph)- 7 pointsc. What additional information do you suggest could influence the investment decision? Do some research about the company. (1 paragraph) 10 pointsd. Your informed recommendation (buy or sell) about SWwith justifications. What is your general impression about SW? Support your conclusion (1-2 paragraph) 10 points
Instructions:
1. Show good understanding of what each analysis and each ratio is measuring.2. Make sure that every claim is justified by an analysis of the company’s financials.3. Ensure that the report tells a coherent story about the company.
Notes
• Groups will receive zero credit for plagiarism• Vertical analysis: expresses the items of each statement for the period relative to a base figure. For B/S, you express each item of the B/S as a percentage of total assets whereas for I/S, each item of the I/S is expressed as a percentage of total sales.Hint for EBIT calculation: net income + income tax+ interest expense• Horizontal analysis: evaluates the trends (rate of change) over a period of time. For example, the rate of change of each item of the B/S from 2020 to 2021 is calculated as follows: (Amount in 2021-Amount in 2020)/amount in 2020.
Cover Page
| NAME | ||||
| TOTAL POINTS | 0 | out of | 33.1 | |
| GRADE | 0 | out of | 10 |
Balance Sheet
| Sherwin-Williams Co (The) (NYS: SHW) | ||||||||||||
| Report Date | Y3 | Y2 | Y1 | Horizontal Analysis (2016-2015) | Horizontal Analysis (2016-2015) | Vertical Analysis Y3 | Vertical Analysis Y3 | Vertical Analysis Y2 | Vertical Analysis Y2 | |||
| Scale | Thousands | Thousands | Thousands | |||||||||
| Cash & cash equivalents | 889,793 | 205,744 | 40,732 | 332.5% | 0 | 13.2% | 0 | 3.6% | 0 | |||
| Accounts receivable, gross | 1,271,437 | 1,163,695 | 1,184,335 | 9.3% | 0 | 18.8% | 0 | 20.1% | 0 | |||
| Less: allowance for doubtful accounts | 40,450 | 49,420 | 53,770 | -18.2% | 0 | 0.6% | 0 | 0.9% | 0 | |||
| Accounts receivable, less allowance | 1,230,987 | 1,114,275 | 1,130,565 | 10.5% | 0 | 18.2% | 0 | 19.2% | 0 | |||
| Finished goods | 898,627 | 840,603 | 841,784 | 6.9% | 0 | 13.3% | 0 | 14.5% | 0 | |||
| Work in process & raw materials | 169,699 | 177,927 | 191,743 | -4.6% | 0 | 2.5% | 0 | 3.1% | 0 | |||
| Inventories | 1,068,326 | 1,018,530 | 1,033,527 | 4.9% | 0 | 15.8% | 0 | 17.6% | 0 | |||
| Deferred income taxes | 57,162 | 87,883 | 109,087 | -35.0% | 0 | 0.8% | 0 | 1.5% | 0 | |||
| Other current assets | 381,030 | 232,442 | 252,869 | 63.9% | 0 | 5.6% | 0 | 4.0% | 0 | |||
| Total current assets | 3,627,298 | 2,658,874 | 2,566,780 | 36.4% | 0 | 53.7% | 0 | 45.9% | 0 | |||
| Goodwill | 1,126,892 | 1,143,333 | 1,158,346 | -1.4% | 0 | 16.7% | 0 | 19.7% | 0 | |||
| Intangible assets | 255,010 | 255,371 | 289,127 | -0.1% | 0 | 3.8% | 0 | 4.4% | 0 | |||
| Deferred pension assets | 225,529 | 244,882 | 250,144 | -7.9% | 0 | 3.3% | 0 | 4.2% | 0 | |||
| Other assets | 421,904 | 447,533 | 420,625 | -5.7% | 0 | 6.2% | 0 | 7.7% | 0 | |||
| Land | 115,555 | 119,530 | 125,691 | -3.3% | 0 | 1.7% | 0 | 2.1% | 0 | |||
| Buildings | 714,815 | 696,202 | 698,202 | 2.7% | 0 | 10.6% | 0 | 12.0% | 0 | |||
| Machinery & equipment | 2,153,437 | 2,026,617 | 1,952,037 | 6.3% | 0 | 31.9% | 0 | 35.0% | 0 | |||
| Construction in progress | 117,126 | 81,082 | 59,330 | 44.5% | 0 | 1.7% | 0 | 1.4% | 0 | |||
| Property, plant & equipment, gross | 3,100,933 | 2,923,431 | 2,835,260 | 6.1% | 0 | 45.9% | 0 | 50.5% | 0 | |||
| Less: allowances for depreciation | 2,005,045 | 1,881,569 | 1,814,230 | 6.6% | 0 | 29.7% | 0 | 32.5% | 0 | |||
| Property, plant & equipment, net | 1,095,888 | 1,041,862 | 1,021,030 | 5.2% | 0 | 16.2% | 0 | 18.0% | 0 | |||
| Total assets | 6,752,521 | 5,791,855 | 5,706,052 | 16.6% | 0 | 100.0% | 0 | 100.0% | 0 | |||
| Short-term borrowings | 40,739 | 39,462 | 679,436 | 3.2% | 0 | 0.6% | 0 | 0.7% | 0 | |||
| Accounts payable | 1,034,608 | 1,157,561 | 1,042,182 | -10.6% | 0 | 15.3% | 0 | 20.0% | 0 | |||
| Compensation & taxes withheld | 398,045 | 338,256 | 360,458 | 17.7% | 0 | 5.9% | 0 | 5.8% | 0 | |||
| Accrued taxes | 76,765 | 81,146 | 86,744 | -5.4% | 0 | 1.1% | 0 | 1.4% | 0 | |||
| Current portion of long-term debt | 700,475 | 3,154 | 3,265 | 22109.1% | 0 | 10.4% | 0 | 0.1% | 0 | |||
| Other accruals | 578,547 | 522,280 | 508,581 | 10.8% | 0 | 8.6% | 0 | 9.0% | 0 | |||
| Total current liabilities | 2,829,179 | 2,141,859 | 2,680,666 | 32.1% | 0 | 41.9% | 0 | 37.0% | 0 | |||
| Senior notes | 1,086,473 | 1,795,696 | 998,055 | -39.5% | 0 | 16.1% | 0 | 31.0% | 0 | |||
| Debentures | 122,436 | 122,872 | 122,869 | -0.4% | 0 | 1.8% | 0 | 2.1% | 0 | |||
| Promissory notes | 2,417 | 1,628 | 1,791 | 48.5% | 0 | 0.0% | 0 | 0.0% | 0 | |||
| Long-term debt | 1,211,326 | 1,920,196 | 1,122,715 | -36.9% | 0 | 17.9% | 0 | 33.2% | 0 | |||
| Postretirement benefits other than pensions | 250,397 | 248,523 | 277,892 | 0.8% | 0 | 3.7% | 0 | 4.3% | 0 | |||
| Other long-term liabilities | 583,178 | 613,367 | 628,309 | -4.9% | 0 | 8.6% | 0 | 10.6% | 0 | |||
| Total Liabilities | 4,874,080 | 4,923,945 | 4,709,582 | -1.0% | 0 | 72.2% | 0 | 85.0% | 0 | |||
| Common stock | 116,563 | 115,761 | 114,525 | 0.7% | 0 | 1.7% | 0 | 2.0% | 0 | |||
| Convertible preferred stock | – | – | – | |||||||||
| Unearned ESOP compensation | – | – | – | |||||||||
| Other capital | 2,488,564 | 2,330,426 | 2,079,639 | 6.8% | 0 | 36.9% | 0 | 40.2% | 0 | |||
| Retained earnings | 4,049,497 | 3,228,876 | 2,424,674 | 25.4% | 0 | 60.0% | 0 | 55.7% | 0 | |||
| Treasury stock, at cost | (4,235,832) | (4,220,058) | (3,150,410) | 0.4% | 0 | -62.7% | 0 | -72.9% | 0 | |||
| Foreign currency translation adjustment | – | (482,629) | (354,384) | -8.3% | 0 | |||||||
| Net actuarial gains (losses) related to pension & other postretirement benefit plans | – | (104,346) | (118,167) | -1.8% | 0 | |||||||
| Unrealized net gains on marketable equity securities | – | (120) | 593 | -0.0% | 0 | |||||||
| Cumulative other comprehensive income (loss) | (540,351) | (587,095) | (471,958) | -8.0% | 0 | -8.0% | 0 | -10.1% | 0 | |||
| Total shareholders' equity | 1,878,441 | 867,910 | 996,470 | 116.4% | 0 | 27.8% | 0 | 15.0% | 0 | |||
| 0 | 0 | 0 | ||||||||||
| 4.2 | 4.2 | 4.5 |
Income Statement
| Sherwin-Williams Co (The) (NYS: SHW) | ||||||||||||
| Report Date | Y3 | Y2 | Y1 | Horizontal Analysis (2016-2015) | Horizontal Analysis (2016-2015) | Vertical Analysis Y3 | Vertical Analysis Y3 | Vertical Analysis Y2 | Vertical Analysis Y2 | |||
| Scale | Thousands | Thousands | Thousands | |||||||||
| Net sales | 11,855,602 | 11,339,304 | 11,129,533 | 4.6% | 0 | 100.0% | 0 | 100.0% | 0 | |||
| Cost of goods sold | 5,933,337 | 5,780,078 | 5,965,049 | 2.7% | 0 | 50.0% | 0 | 51.0% | 0 | |||
| Gross profit | 5,922,265 | 5,559,226 | 5,164,484 | 6.5% | 0 | 50.0% | 0 | 49.0% | 0 | |||
| Selling, general & administrative expenses | 4,159,435 | 3,913,518 | 3,822,966 | 6.3% | 0 | 35.1% | 0 | 34.5% | 0 | |||
| Provisions for environmental matters, net | 42,932 | 31,071 | 36,046 | 38.2% | 0 | 0.4% | 0 | 0.3% | 0 | |||
| Gain (loss) on disposition of assets | 30,564 | 803 | (1,436) | 3706.2% | 0 | 0.3% | 0 | 0.0% | 0 | |||
| Total other general expense – net | 12,368 | 30,268 | 37,482 | -59.1% | 0 | 0.1% | 0 | 0.3% | 0 | |||
| Impairment of trademarks & goodwill | 10,688 | – 0 | – 0 | 0.1% | 0 | |||||||
| Operating Income | 1,739,774 | 1,615,440 | 1,304,036 | 7.7% | 0 | 14.7% | 0 | 14.2% | 0 | |||
| Interest expense | 154,088 | 61,791 | 64,205 | 149.4% | 0 | 1.3% | 0 | 0.5% | 0 | |||
| Interest & net investment income | 4,960 | 1,399 | 2,995 | 254.5% | 0 | 0.0% | 0 | 0.0% | 0 | |||
| Dividend & royalty income | 4,573 | 3,668 | 4,864 | 24.7% | 0 | 0.0% | 0 | 0.0% | 0 | |||
| Net income (expense) of financing activities | (8,667) | (11,091) | (11,367) | -21.9% | 0 | -0.1% | 0 | -0.1% | 0 | |||
| Foreign currency related transaction gains (losses) | (7,335) | (9,503) | (3,603) | -22.8% | 0 | -0.1% | 0 | -0.1% | 0 | |||
| Other income | 25,279 | 23,880 | 37,524 | 5.9% | 0 | 0.2% | 0 | 0.2% | 0 | |||
| Other expense | 9,263 | 13,036 | 12,018 | -28.9% | 0 | 0.1% | 0 | 0.1% | 0 | |||
| Total other income (expense), net | 4,587 | (6,082) | 15,400 | -175.4% | 0 | 0.0% | 0 | -0.1% | 0 | |||
| Income (loss) before income taxes – domestic | 1,504,990 | 1,440,511 | 1,113,527 | 4.5% | 0 | 12.7% | 0 | 12.7% | 0 | |||
| Income (loss) before income taxes – foreign | 90,243 | 108,455 | 144,698 | -16.8% | 0 | 0.8% | 0 | 1.0% | 0 | |||
| Income (loss) before income taxes | 1,595,233 | 1,548,966 | 1,258,226 | 3.0% | 0 | 13.5% | 0 | 13.7% | 0 | |||
| Current provision for (benefit from) income taxes – federal | 438,244 | 399,677 | 308,283 | 9.6% | 0 | 3.7% | 0 | 3.5% | 0 | |||
| Current provision for (benefit from) income taxes – foreign | 31,125 | 30,145 | 53,045 | 3.3% | 0 | 0.3% | 0 | 0.3% | 0 | |||
| Current provision for (benefit from) income taxes – state & local | 61,402 | 60,319 | 50,049 | 1.8% | 0 | 0.5% | 0 | 0.5% | 0 | |||
| Total current provision for (benefit from) income taxes | 530,771 | 490,141 | 411,377 | 8.3% | 0 | 4.5% | 0 | 4.3% | 0 | |||
| Deferred provision for (benefit from) income taxes – federal | (56,891) | 13,505 | (14,974) | -521.3% | 0 | -0.5% | 0 | 0.1% | 0 | |||
| Deferred provision for (benefit from) income taxes – foreign | (2,121) | (10,752) | (7,361) | -80.3% | 0 | -0.0% | 0 | -0.1% | 0 | |||
| Deferred provision for (benefit from) income taxes – state & local | (9,229) | 2,223 | 3,297 | -515.2% | 0 | -0.1% | 0 | 0.0% | 0 | |||
| Total deferred provision for (benefit from) income taxes | (68,241) | 4,976 | (19,038) | -1471.4% | 0 | -0.6% | 0 | 0.0% | 0 | |||
| Income taxes | 462,530 | 495,117 | 392,339 | -6.6% | 0 | 3.9% | 0 | 4.4% | 0 | |||
| Net income (loss) | 1,132,703 | 1,053,849 | 865,887 | 7.5% | 0 | 9.6% | 0 | 9.3% | 0 | |||
| 0 | 0 | 0 | ||||||||||
| 2.7 | 2.8 | 2.7 | ||||||||||
Cash Flow Statement
| Sherwin-Williams Co (The) (NYS: SHW) | |||
| Report Date | Y3 | Y2 | Y1 |
| Scale | Thousands | Thousands | Thousands |
| Net income (loss) | 1,132,703 | 1,053,849 | 865,887 |
| Depreciation | 172,074 | 170,323 | 169,087 |
| Amortization of intangible assets | 25,637 | 28,239 | 29,858 |
| Impairment of trademarks & goodwill | 10,688 | – | – |
| Amortization of credit facility & debt issuance costs | 63,759 | – | – |
| Provisions for environmental-related matters | 42,932 | 31,071 | 36,046 |
| Provisions for (net credit from) qualified exit costs | 3,038 | 9,761 | 13,578 |
| Deferred income taxes | (68,241) | 4,976 | (19,038) |
| Defined benefit pension plans net cost (credit) | 14,851 | 6,491 | 990 |
| Stock-based compensation expense | 72,109 | 72,342 | 64,735 |
| Net increase (decrease) in postretirement liability | (12,373) | (6,645) | (718) |
| Increase (decrease) in non-traded investments | 64,689 | 65,144 | 63,365 |
| Loss (gain) on disposition of assets | (30,564) | (803) | 1,436 |
| Other net income adjustments | 5,101 | 6,711 | 203 |
| Accounts receivable | (113,855) | (56,873) | (80,252) |
| Inventories | (52,577) | (40,733) | (101,112) |
| Accounts payable | (118,893) | 160,111 | 78,603 |
| Accrued taxes | (2,159) | 4,606 | 13,187 |
| Accrued compensation & taxes withheld | 60,632 | (13,128) | 29,513 |
| Refundable income taxes | (1,343) | 19,230 | (36,601) |
| DOL settlement accrual | – | – | – |
| Other working capital accounts | 56,215 | (955) | (20,029) |
| Costs incurred for environmental-related matters | (15,178) | (11,995) | (9,676) |
| Costs incurred for qualified exit costs | (6,267) | (11,200) | (10,882) |
| Other cash flows from operating activities | 5,594 | (43,059) | (6,652) |
| Net cash flows from operating activities | 1,308,572 | 1,447,463 | 1,081,528 |
| Capital expenditures | (239,026) | (234,340) | (200,545) |
| Acquisition of businesses, net of cash acquired | – | – | – |
| Proceeds from sale of assets | 38,434 | 11,300 | 1,516 |
| Increase (decrease) in other investments | (103,182) | (65,593) | (111,021) |
| Net cash flows from investing activities | (303,774) | (288,633) | (310,050) |
| Net increase (decrease) in short-term borrowings | (899) | (630,226) | 591,423 |
| Proceeds from long-term debt | 500 | 797,514 | 1,474 |
| Payments of long term debt | (1,111) | – | (500,661) |
| Payment for credit facility & debt issuance costs | (65,119) | – | – |
| Payments of cash dividends | (312,082) | (249,647) | (215,263) |
| Proceeds from stock options exercised | 86,831 | 89,990 | 100,069 |
| Income tax effect of stock-based compensation exercises & vesting | – | 89,691 | 68,657 |
| Treasury stock purchased | – | (1,035,291) | (1,488,663) |
| Other financing activities | (15,473) | (42,384) | (24,111) |
| Net cash flows from financing activities | (307,353) | (980,353) | (1,467,075) |
| Effect of exchange rate changes on cash | (13,396) | (13,465) | (8,560) |
| Net increase (decrease) in cash & cash equivalents | 684,049 | 165,012 | (704,157) |
| Cash & cash equivalents at beginning of year | 205,744 | 40,732 | 744,889 |
| Cash & cash equivalents at end of year | 889,793 | 205,744 | 40,732 |
Ratios
| RATIO ANALYSIS | ||||||
| Y3 | Y3 | Y2 | Y2 | |||
| LIQUIDITY | ||||||
| Working Capital | 798119 | 0 | 517015 | 0 | ||
| Current Ratio | 1.28 | 0 | 1.24 | 0 | ||
| ASSET MANAGEMENT & PROFITABILITY | ||||||
| Inventory Turnover | 5.55 | 0 | 5.67 | 0 | ||
| Total Asset Turnover | 1.76 | 0 | 1.96 | 0 | ||
| Days Sales Outstanding | 37.90 | 0 | 35.87 | 0 | ||
| Return on total Assets | 0.17 | 0 | 0.18 | 0 | ||
| Profit margin | 0.10 | 0 | 0.09 | 0 | ||
| Earnings per Share (EPS) | 12.33 | 0 | 11.43 | 0 | ||
| DEBT MANAGEMENT | ||||||
| Debt to Assets Ratio | 2.59 | 0 | 5.67 | 0 | ||
| Times Interest Earned | 11.35 | 0 | 26.07 | 0 | ||
| MARKET VALUE | ||||||
| Market /Book | 13.14 | 0 | 27.58 | 0 | ||
| Price/Earning | 21.79 | 0 | 22.71 | 0 | ||
| ADDITIONAL INFORMATION | ||||||
| Weighted average shares outstanding – basic | 91,839 | 92,197 | ||||
| Market Price per Share at Year End | 268.74 | 259.6 | ||||
| 0 | 0 | |||||
| 6 | 6 |
