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 |
| ASSETS | | | | | |
| Cash & cash equivalents | $ 334,300 | $ 391,100 | $ 101,300 | $ 192,600 | $ 166,800 |
| | Trade receivables, gross | 536,100 | 554,400 | 505,600 | 386,700 | 440,300 |
| | Allowance for doubtful accounts | 2,400 | 3,000 | 1,800 | 1,100 | 1,600 |
| Trade receivables - net | 533,700 | 551,400 | 503,800 | 385,600 | 438,700 |
| | Finished products | 607,600 | 563,500 | 590,800 | 542,100 | 562,400 |
| | Raw materials | 352,300 | 331,800 | 319,500 | 312,300 | 343,300 |
| Total inventory | 959,900 | 895,300 | 910,300 | 854,400 | 905,700 |
| Other current assets | 113,800 | 134,900 | 109,800 | 122,400 | 130,600 |
| Total current assets | $ 1,941,700 | $ 1,972,700 | $ 1,625,200 | $ 1,555,000 | $ 1,641,800 |
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
