Please recreate excel spreadsheet with all formulas in place. Formulas are below.
| As Reported Annual Cash Flow | | | | | |
| Report Date | 04/30/2021 | 04/30/2020 | 04/30/2019 | 04/30/2018 | 04/30/2017 |
| Scale | Thousands | Thousands | Thousands | Thousands | Thousands |
| Net income (loss) | $ 876,300 | $ 779,500 | $ 514,400 | $ 1,338,600 | $ 592,300 |
| Depreciation | 219,500 | 210,200 | 206,000 | 206,300 | 211,700 |
| Amortization | 233,000 | 236,300 | 240,300 | 206,800 | 207,300 |
| Goodwill impairment charge | 0 | 0 | 97,900 | 145,000 | 0 |
| Other intangible assets impairment charges | 3,800 | 52,400 | 107,200 | 31,900 | 133,200 |
| Pension settlement loss (gain) | 35,500 | 0 | 0 | 0 | 0 |
| Share-based compensation expense | 28,700 | 26,800 | 20,700 | 15,400 | 22,000 |
| Loss (gain) on divestitures - net | (25,300) | 0 | (27,700) | 0 | 0 |
| Deferred income tax expense (benefit) | (13,900) | 7,600 | (93,500) | (803,400) | (79,400) |
| Loss (gain) on disposal of assets - net | 7,100 | 13,000 | 4,500 | 6,600 | 0 |
| Other noncash adjustments - net | 11,800 | 8,100 | 1,200 | 3,700 | 4,800 |
| Settlement of interest rate contracts | 0 | (239,800) | 0 | 0 | 0 |
| Defined benefit pension contributions | (13,100) | (5,100) | (29,300) | (39,600) | (28,700) |
| Trade receivables | 22,000 | (49,100) | (53,000) | 54,700 | 8,900 |
| Inventories | (110,400) | 12,600 | (5,300) | 54,000 | (10,400) |
| Other current assets | (34,000) | (15,700) | 13,300 | (5,300) | 8,900 |
| Accounts payable | 260,900 | 181,600 | 43,700 | 19,000 | 2,100 |
| Accrued liabilities | 56,000 | 48,000 | 66,700 | 20,500 | (39,800) |
| Income & other taxes | (17,600) | 6,500 | 51,800 | (28,700) | 7,900 |
| Other changes in assets & liabilities - net | 24,700 | (18,100) | (17,700) | (7,500) | 18,200 |
Metric Description Profitability Return on shareholders' equity (ROE), pgs. 4-3 to 4-4 Return on common equity (ROCE), pg. 4-5 Net income/Average Shareholders' equity = (Net income - preferred dividends) (Average Common Shareholders' equity - Average Preferred Equity) = Net Income /Average Total Assets = (Net Income + [Net Interest Expense x (1-Statutory Tax Rate)] Net Income/Sales Revenue (Sales - Cost of sales)/Sales Revenue OR Gross Profit/Sales Revenue = = Return on Assets (ROA), Pgs. 4-4 to 4-6 Adjusted ROA pgs. 4-6 Profit Margin (PM), pgs. 4-6 & 4-7 Gross profit margin, pg. 4-8 Productivity and Efficiency Assets turnover (AT), pg. 4-6. 4-7 Accounts Receivable Turnover (ART), pg. 4-7, 5-21 Days Sales Outstanding (DSO), pg. 5-21 Inventory Turnover (ITO), pgs. 4-7, 6-13 Average Days Inventory Outstanding (ADIO), Pg. 6-13 Accounts Payable Turnover (APTO), pg. 6-15 Days' Payable Outstanding (DPO), pg. 6-15 Cash Conversion Cycle, pg. 6-16 PP&E turnover (PPET), pgs. 4-7 & 6-25 Percent Used Up, pg. 6-27 Liquidity Current Ratio, pg. 4-36 Quick (Acid) Ratio, pg.4-36 = Sales/Average Total Assets Sales Revenue/Average Accounts receivable = 365 days/Accounts Receivable Turnover = Cost of Goods Sold/Average Inventory 365 days/ Inventory Turnover = Cost of Goods Sold/Average Accounts payable 365/Accounts Payable Turnover DSO + ADIO - DPO - Sales Revenue/Average PP&E = Accumulated Depreciation/Cost of Depreciable Assets = - = Current assets/Current liabilities (Cash & Equivalents + Short-term securities + Accounts receivable)/Current liabilities Operating Cash Flow/Average current liabilities Operating Cash Flow/Capital Expenditure Operating Cash Flow to Current Liabilities, pg. 11-27 Operating cash flow to capital expenditure, pg. 11-27 Metric Description Solvency Liabilities to equity ratio, pg. 4-37 Financial leverage (FL), pg. 4-4, 4-5 Times interest earned, pg. 4-38 Altman Z-Score Market/Investor Metrics = Total Liabilities/Total Shareholders' equity = Average Total Assets/Average Shareholders' Equity = Earnings before interest and taxes/Interest expense, gross Class Notes = Basic earnings per common share, pg. 8-24 Price-to-earnings Book value per share, pg. 8-4 = (Net Income - Preferred dividends)/Weighted Avg. Number of Common Shares Outstanding = Market price per common share/Earnings per share (basic) (Shareholders' equity - Preferred equity)/ Number of Common Shares Outstanding Market price per common share/Book value per common share = Common Stock Dividend per Share/Current Share Price Common Stock Dividend per Share/ Basic Earnings per Share ROEX (1-Dividend Payout Ratio) = ROE x Retention Ratio - Market-to-book, pg. 8-4 Dividend yield, pg. 8-17 Dividend Payout Ratio, pg. 8-17 Sustainable Growth Rate - - Metric Description Profitability Return on shareholders' equity (ROE), pgs. 4-3 to 4-4 Return on common equity (ROCE), pg. 4-5 Net income/Average Shareholders' equity = (Net income - preferred dividends) (Average Common Shareholders' equity - Average Preferred Equity) = Net Income /Average Total Assets = (Net Income + [Net Interest Expense x (1-Statutory Tax Rate)] Net Income/Sales Revenue (Sales - Cost of sales)/Sales Revenue OR Gross Profit/Sales Revenue = = Return on Assets (ROA), Pgs. 4-4 to 4-6 Adjusted ROA pgs. 4-6 Profit Margin (PM), pgs. 4-6 & 4-7 Gross profit margin, pg. 4-8 Productivity and Efficiency Assets turnover (AT), pg. 4-6. 4-7 Accounts Receivable Turnover (ART), pg. 4-7, 5-21 Days Sales Outstanding (DSO), pg. 5-21 Inventory Turnover (ITO), pgs. 4-7, 6-13 Average Days Inventory Outstanding (ADIO), Pg. 6-13 Accounts Payable Turnover (APTO), pg. 6-15 Days' Payable Outstanding (DPO), pg. 6-15 Cash Conversion Cycle, pg. 6-16 PP&E turnover (PPET), pgs. 4-7 & 6-25 Percent Used Up, pg. 6-27 Liquidity Current Ratio, pg. 4-36 Quick (Acid) Ratio, pg.4-36 = Sales/Average Total Assets Sales Revenue/Average Accounts receivable = 365 days/Accounts Receivable Turnover = Cost of Goods Sold/Average Inventory 365 days/ Inventory Turnover = Cost of Goods Sold/Average Accounts payable 365/Accounts Payable Turnover DSO + ADIO - DPO - Sales Revenue/Average PP&E = Accumulated Depreciation/Cost of Depreciable Assets = - = Current assets/Current liabilities (Cash & Equivalents + Short-term securities + Accounts receivable)/Current liabilities Operating Cash Flow/Average current liabilities Operating Cash Flow/Capital Expenditure Operating Cash Flow to Current Liabilities, pg. 11-27 Operating cash flow to capital expenditure, pg. 11-27 Metric Description Solvency Liabilities to equity ratio, pg. 4-37 Financial leverage (FL), pg. 4-4, 4-5 Times interest earned, pg. 4-38 Altman Z-Score Market/Investor Metrics = Total Liabilities/Total Shareholders' equity = Average Total Assets/Average Shareholders' Equity = Earnings before interest and taxes/Interest expense, gross Class Notes = Basic earnings per common share, pg. 8-24 Price-to-earnings Book value per share, pg. 8-4 = (Net Income - Preferred dividends)/Weighted Avg. Number of Common Shares Outstanding = Market price per common share/Earnings per share (basic) (Shareholders' equity - Preferred equity)/ Number of Common Shares Outstanding Market price per common share/Book value per common share = Common Stock Dividend per Share/Current Share Price Common Stock Dividend per Share/ Basic Earnings per Share ROEX (1-Dividend Payout Ratio) = ROE x Retention Ratio - Market-to-book, pg. 8-4 Dividend yield, pg. 8-17 Dividend Payout Ratio, pg. 8-17 Sustainable Growth Rate
