Need Risk Ratio done on spreadsheet and noth questions answered
Robert Perrin ACC 630 Financial Statement Analysis 6/18/2023 Module 5 Integrative Case 5.1 Pages 295-296 A. Compute the values of each of the ratios in Exhibit 5.26 for Walmart for 2020. Walmart had 2,821 million common shares outstanding at the end of fiscal 2020, and the market price per share was $140.49. For 2019, the comparable shares and price per share were 2,832 million and $114.49, and for 2018, they were 2,878 million and $95.83, respectively. Notes: For days accounts receivable outstanding, use total revenues in your calculations. For computations with interest expense, exclude interest income but include imputed interest expense on average operating lease liabilities (current and long-term); Walmart discloses an implicit interest rate of 6.1% on operating leases. For the interest coverage ratio (cash flow basis), ignore imputed interest for operating leases (as done in the text). For earnings before interest and taxes in the Altman’s Z calculation, include interest income in earnings before interest and taxes. Risk Ratios 2020 2019 2018 Current Ratio 0.79 0.80 Quick Ratio 0.20 0.18 Operating Cash flow to current liabilities ratio 0.33 0.36 Accounts Receivable turnover 83.39 86.48 Days accounts receivable outstanding 4.38 4.22 Inventory turnover 8.90 8.75 Day inventory held 41.02 41.71 Accounts payable turnover 8.90 8.76 Days accounts payable outstanding 41.01 41.65 Net days of working capital financing needed 86.41 87.58 Liabilities to total assets ratio 0.66 0.64 Liabilities to shareholders’ equity ratio 1.90 1.75 Long-Term debt to long-term capital ratio 0.44 0.39 Long-term debt to shareholders’ equity ratio 0.79 0.63 Interest coverage ratio (net income basis) 7.39 5.88 Interest coverage ratio (cash flow basis) 12.72 14.52 Altman’s Z-Score 4.20 4.17 Probability of bankruptcy 0.07% 0.08% Altman’s Z Components: Net working capital/TA -0.07 -0.07 Retained earnings/TA 0.35 0.37 EBIT/TA 0.10 0.06 MVE/BV liabilities 2.09 1.97 Sales/TA 2.22 2.35 4.20 4.17 Shares end of year (millions) 2,821 2,832 2,878 Price per share end of year $140.49 $114.49 $95.83 MV $396,322 $324,236 $275,799 B. Interpret the changes in Walmart’s risk ratios during the three-year period, indicating any areas of concern. Sheet1 Walmart Balance Sheet 201820192020 ASSETS Current Assets:Risk Ratios Cash and cash equivalents$ 7,722$ 9,465$ 17,741202020192018 Receivables, net$ 6,283$ 6,284$ 6,516Current Ratio0.360.790.80Current assets/Current Liabilities Inventories$ 44,269$ 44,435$ 44,949Quick Ratio0.200.18Cash & Cash Equivalents/Current Liabilities Prepaid expenses and other $ 3,623$ 1,622$ 20,861Operating Cash flow to current liabilities ratio0.330.36 Total Current Assets$ 61,897$ 61,806$ 90,067Accounts Receivable turnover83.3986.48 Days accounts receivable outstanding4.384.22 Property and Equipment, net$ 104,317$ 105,208$ 92,201Inventory turnover8.908.75 Operating lease right of use assets, net$ 17,424$ 13,642Day inventory held41.0241.71 Goodwill$ 7,078$ 4,417$ 4,005Accounts payable turnover8.908.76 Other long term assets$ 31,181$ 31,073$ 28,983Days accounts payable outstanding41.0141.65 TOTAL ASSETS$ 14,822$ 16,567$ 23,598Net days of working capital financing needed86.4187.58 $ 157,398$ 174,689$ 162,429Liabilities to total assets ratio0.660.64 $ 219,295$ 236,495$ 252,496Liabilities to shareholders’ equity ratio1.901.75 Long-Term debt to long-term capital ratio0.440.39 LIABILITIES AND EQUITYLong-term debt to shareholders’ equity ratio0.790.63 Current Liabilities: Interest coverage ratio (net income basis)7.395.88 Short term borrowings$ 5,225$ 575$ 224Interest coverage ratio (cash flow basis)12.7214.52 Accounts payable $ 47,060$ 46,973$ 49,141Altman’s Z-Score4.204.17 Accrued liabilities$ 22,159$ 22,296$ 37,966Probability of bankruptcy0.07%0.08% Accured income taxes $ 428$ 280$ 242 Long term debt due within one year $ 1,876$ 5,362$ 3,115 operating lease obligations due within one year $ 1,793$ 1,466Altman’s Z Components: Finance lease obligations due within one year $ 729$ 511$ 491Net working capital/TA-0.07-0.07 Total current liabilities$ 77,477$ 77,790$ 92,645Retained earnings/TA0.350.37 Long-term debt$ 43,520$ 43,714$ 41,194 Long term operating lease obligations $ 16,171$ 12,909 Long term finance lease obligations $ 6,683$ 4,307$ 3,847 Deferred income taxes and other commitments$ 11,981$ 12,961$ 14,370 Equity: $ 62,184$ 77,153$ 72,320 Common Stock $ 288$ 284$ 282 Capital in excess of par value $ 2,965$ 3,247$ 3,646 Retained earnings $ 80,785$ 83,943$ 88,763 Accumulated other comprehensive loss $ (11,542)$ (12,805)$ (11,766) Total Walmart shareholders equity $ 72,496$ 74,669$ 80,925 Noncontrolling interest$ 7,138$ 6,883$ 6,606 Total equity$ 79,634$ 81,552$ 87,531 TOTAL LIABILITIES AND EQUITY$ 219,295$ 236,495$ 252,496 Walmart Income Statements 201820192020 Net Sales$ 510,329$ 519,926$ 555,233 Membership and other income$ 4,076$ 4,038$ 3,918 Total Revenues$ 514,405$ 523,964$ 559,151 Cost of Sales$ 385,301$ 394,605$ 420,315 Operating selling, general, and admin expenses$ 107,147$ 108,791$ 116,288 Operating Income $ 21,957$ 20,568$ 22,548 Interest: debt$ 1,975$ 2,262$ 1,976 Finance, capital lease, and financing obligations$ 371$ 337$ 339 Interest Income $ (217)$ (189)$ (121) Interest, net$ 2,129$ 2,410$ 2,194 Other (gains) and losses$ 8,368$ (1,958)$ (210) Income before Income Taxes$ 11,460$ 20,116$ 20,564 Provision for income taxes $ 4,281$ 4,915$ 6,858 Consolidated net income $ 7,179$ 15,201$ 13,706 Consolidated net income attributable to noncontrolling interest $ (509)$ (320)$ (196) Consolidated net income attributable to Walmart$ 6,670$ 14,881$ 13,510 Net Income per common share: Basic net income per common share attributable to Walmart$ 2.28$ 5.22$ 4.77 Diluted net income per common share attributable to Walmrt$ 2.26$ 5.19$ 4.75 Weighted-average common shares outstanding: Basic292928502831 Diluted294528682847 Dividends declared per common share$ 2.08$ 2.12$ 2.16 Walmart Consolidated Statements of Comprehensive Income 201820192020 Consolidated Net Income $ 7,179$ 15,201$ 13,706 Consolidated Net Income attributable to noncontrolling interest$ (509)$ (320)$ (196) Consolidated net income attributable to Walmart $ 6,670$ 14,881$ 13,510 Other comprehensive income (loss), net of income taxes Currency translation and other $ (226)$ 286$ 842 Net Investment Hedges$ 272$ 122$ (221) Cash Flow hedges$ (290)$ (399)$ 235 Minimum pension liability $ 131$ (1,244)$ (30) Other comprehensive income (loss), net of income taxes$ (113)$ (1,235)$ 826 Other comprehensive (income) loss, attributable to noncontrolling interest$ 188$ (28)$ 213 Other comprehensive income (loss) attributable to Walmart$ 75$ (1,263)$ 1,039 $ 263$ (1,291)$ 1,252 Comprehensive income, net of income taxes$ 7,066$ 13,966$ 14,532 Other comprehensive (income) loss, attributable to noncontrolling interest$ (321)$ (348)$ 17 Comprehensive income attributable to Walmart$ 6,745$ 13,618$ 14,549 Walmarts Statement of Cash Flows 201820192020 Cash flows from operating activities Consolidated net income $ 7,179$ 15,201$ 13,706 Adjustments to reconcile consolidated net income to net cash provided by operating activities Depreciation and amortization$ 10,678$ 10,987$ 11,152 Net unrealized and realized (gains) and losses $ 3,516$ (1,886)$ (8,589) Losses on disposal of business operations$ 4,850$ 15$ 8,401 ASDA pension contribution$ (1,036) Deferred income taxes$ (499)$ 320$ 1,911 Other operating activities$ 1,734$ 1,981$ 1,521 Changes in certain assets and liabilities, net of effects of acquistions and dispositions: Receivables, net $ (368)$ 154$ (1,086) Inventories$ (1,311)$ (300)$ (2,395) Accounts Payable $ 1,831$ (274)$ 6,966 Accrued liabilities $ 183$ 186$ 4,623 Accrued income taxes $ (40)$ (93)$ (136) Net Cash provided by operating activities $ 27,753$ 25,255$ 36,074 Cash flow from investing activities: Payments for property and equipment $ (10,344)$ (10,705)$ (10,264) Proceeds from the disposal of property and equipment $ 519$ 321$ 215 Proceeds from the disposal of certain operations $ 876$ 833$ 56 Payments for business acquisitions, net of cash acquired$ (14,656)$ (56)$ (180) Other investing activities $ (431)$ 479$ 102 Net Cash used in investing activities $ (24,036)$ (9,128)$ (10,071) Cash flows from financing activities: Net Change in short-term borrowings$ (53)$ (4,656)$ (324) Proceeds from issuance of long-term debt $ 15,872$ 5,492 Repayments of long term debt$ (3,784)$ (1,907)$ (5,382) Dividends paid $ (6,102)$ (6,048)$ (6,116) Purchase of company stock $ (7,410)$ (5,717)$ (2,625) Dividends paid to noncontrolling interest$ (431)$ (555)$ (434) Other Financing activities $ (629)$ (908)$ (1,236) Net Cash used in financing activities$ (2,537)$ (14,299)$ (16,117) Effect of exchange rates on cash, cash equivalents, and restricted cash $ (438)$ (69)$ 235 Net increase in cash, cash equivalents, and restricted cash $ 742$ 1,759$ 10,121 cash and cash equivalents reclassified as assets held for sale$ (1,848) Cash, cash equivalents and restricted cash at beginning of year$ 7,014$ 7,756$ 9,515 Cash, cash equivalents and restricted cash at end of year$ 7,756$ 9,515$ 17,788 Supplemental disclosure of cash flow information: Income Taxes paid$ 3,982$ 3,616$ 5,271 Interest paid $ 2,348$ 2,464$ 2,216 Sheet1 Walmart Balance Sheet 201820192020 ASSETS Current Assets:Risk Ratios Cash and cash equivalents$ 7,722$ 9,465$ 17,741202020192018 Receivables, net$ 6,283$ 6,284$ 6,516Current Ratio0.360.790.80Current assets/Current Liabilities Inventories$ 44,269$ 44,435$ 44,949Quick Ratio0.200.18Cash & Cash Equivalents/Current Liabilities Prepaid expenses and other $ 3,623$ 1,622$ 20,861Operating Cash flow to current liabilities ratio0.330.36 Total Current Assets$ 61,897$ 61,806$ 90,067Accounts Receivable turnover83.3986.48 Days accounts receivable outstanding4.384.22 Property and Equipment, net$ 104,317$ 105,208$ 92,201Inventory turnover8.908.75 Operating lease right of use assets, net$ 17,424$ 13,642Day inventory held41.0241.71 Goodwill$ 7,078$ 4,417$ 4,005Accounts payable turnover8.908.76 Other long term assets$ 31,181$ 31,073$ 28,983Days accounts payable outstanding41.0141.65 TOTAL ASSETS$ 14,822$ 16,567$ 23,598Net days of working capital financing needed86.4187.58 $