Answered step by step
Verified Expert Solution
Question
1 Approved Answer
Please recreate excel sheet with all formulas in place. Formulas are below. As Reported Annual Balance Sheet Report Date 04/30/2021 04/30/2020 04/30/2019 04/30/2018 04/30/2017 Scale
Please recreate excel sheet with all formulas in place. Formulas are below.
As Reported Annual Balance Sheet | ||||||
Report Date | 04/30/2021 | 04/30/2020 | 04/30/2019 | 04/30/2018 | 04/30/2017 | |
Scale | Thousands | Thousands | Thousands | Thousands | Thousands | |
Land & land improvements | 124,300 | 129,500 | 122,100 | 120,100 | 115,600 | |
Buildings & fixtures | 967,000 | 977,900 | 903,200 | 812,600 | 766,200 | |
Machinery & equipment | 2,469,700 | 2,398,300 | 2,185,000 | 2,111,500 | 1,983,000 | |
Construction in progress | 282,300 | 232,600 | 321,800 | 212,100 | 116,900 | |
Gross property, plant & equipment | 3,843,300 | 3,738,300 | 3,532,100 | 3,256,300 | 2,981,700 | |
Accumulated depreciation | 1,841,800 | 1,768,900 | 1,619,700 | 1,527,200 | 1,364,200 | |
Total property, plant & equipment | 2,001,500 | 1,969,400 | 1,912,400 | 1,729,100 | 1,617,500 | |
Operating lease right-of-use assets | 142,000 | 148,400 | 0 | 0 | 0 | |
Goodwill | 6,023,600 | 6,304,500 | 6,310,900 | 5,942,200 | 6,077,100 | |
Other intangible assets - net | 6,041,200 | 6,429,000 | 6,718,800 | 5,916,500 | 6,149,900 | |
Other noncurrent assets | 134,200 | 146,400 | 144,000 | 158,400 | 153,400 | |
Total assets | $ 16,284,200 | $ 16,970,400 | $ 16,711,300 | $ 15,301,200 | $ 15,639,700 |
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
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started