A B C D E F G H J K L M N P R Revised on 1-29-2024 Project 1, Step 4: Instructions for This Excel Workbook View the balance sheet and income statement for the client company, Largo Global Inc. (LGI), using this Excel workbook, which includes the following tabs: Bal. Sheet & Inc. Statement 1. 2. Ratio Analysis 3. Common-size Analysis 4. Cash Flow Analysis You may submit this workbook as a milestone in Step 4, so you can receive feedback on the accuracy of your calculations before submitting your final project in Step 5 at the end of Week 2. Open tab 1. You see the balance sheet and income statement for LGI for 2021-23. You also see estimated average income statement data for LGI's main competitors. Use this information to complete the calculations in tabs 2-4. Perform your calculations using the formula bar in the Excel workbook. Open tab 2. You see six groups of financial ratios as well as an industry benchmark you can use for comparison with LGI's 2021-2023 ratios. Calculate the ratios for each year. Open tab 3. Open tab 4. Complete a common-size analysis. Complete a cash flow analysis. Largo Gobal Balance Sheet as of December 31 (millions) 2023 2022 2021 2023 2022 2021 Assets: Liabilities and Stockholders' Equity: Cash and marketable securities $ 228 $ 366 $ 169 Accounts payable and accruals $ 361 $ 324 $ 312 Accounts receivable $ 188 $ 181 $ 157 Notes payable $ 140 $ 103 $ 61 Inventory $ 404 $ 398 $ 349 Accrued taxes $ 56 $ 88 $ 109 Other current assets $ 18 $ 10 $ 5 Total current liabilities $ 557 $ 515 $ 482 Total current assets $ 838 $ 955 $ 680 Property, plant, and equipment $ 4,000 $ 3,358 $ 2,976 Long-term debt $ 457 $ 379 $ 283 Less: Accumulated depreciation $ 2,000 $ 1,826 $ 1,608 Total liabilities $ 1,014 $ 894 $ 765 Common Stock (98,051,400 Net property, plant, and equipment Goodwill and other assets $ 2,000 $ 1,532 $ 1,368 shares) $ 490 $ 487 $ 483 $ 1,280 $ 1,280 $ 1,280 Additional Paid-in capital $ 2,439 $ 2,222 $ 1,982 Retained earnings $ 270 $ 179 $ 98 Treasury stock $ (95) $ (15) $ Total stockholders' equity $ Total assets $ 4,118 $ 3,767 $ 3,328 Total liabilities and equity $ 3,104 $ 4,118 $ 2,873 $ 2,563 3,767 $ 3,328 Number of shares (actual) 98,051,400 97,302,600 96,521,200 Estimate 2023 2022 2021 competitor Sales (net sales) $ 2,013 $ 2,450 S 2,733 $ 7,564 Cost of goods sold $ 1,400 S 1,689 $ 1,765 $ 3.883 Gross profit $ 613 $ 761 $ 968 $ 3,681 Selling, general, and administrative EA 125 S 98 $ 91 $ 105 expenses Earnings before Interest, taxes, depreciation, and amortization (EBITDA) Depreciation and amortization SASA 488 $ 174 S 663 $ 218 SS 877 $ 3,576 259 $ 743 Earning before interest and taxes (EBIT) Operating income (loss) $ 314 $ 445 $ 618 $ 2,833 Interest expense $ 141 S 137 $ 125 $ 207 Earnings before taxes (EBT) Taxes (34%) $ 173 S 308 $ 493 $ 2,626 $ 59 S 105 S 168 $ Net earnings (loss)/Net Income $ 114 $ 203 $ 325 $ 893 1,733 Average Total Assets 2023 Jan 1, 2023 Dec 31, 2023 Sub-total (A)| Average (A/2) Note: Assets on Jan 1, 2023 = Assets on Dec 31, 2022 A B D E F G H J K L M N 1 2 Industry 3 2023 2022 2021 Benchmark Notes 4 Liquidity Ratios 5 Current ratio 6 Quick ratio 7 Cash ratio 1.586 1.92 CA/CL 0.868 1.25 CA - INV/CL 0.351 0.86 Cash&Cash Equivalents/CL 8 Efficiency Ratios 9 Inventory turnover ratio 4.78% 5.37 CGS/INV 10 Days' sales in inventory 77.23% 50.6 365/INV turnover 11 Accounts receivable turnover 8.83% 18.12 Sales / Ave AR 12 Days' sales outstanding 41.33% 21.5 12 month DSO is (Ave AR/Sales)*365 13 Total asset turnover (TAT) 0.82% 0.9 Net Sales/Ave Total Assets 14 Fixed assets turnover 15 Leverage Ratios 1.99% 2.75 Net Sales/Ave Net Fixed Assets 16 Total debt ratio 28.86 0.21 Tot Debt/Tot Assets 17 Debt to equity ratio 0.34 0.27 Tot Debt/Tot Equity 18 Equity multiplier (EM) 1.21 1.23 Average Total Assets/Average Shareholders' Equity 19 Times interest earned 3.64 5.5 EBIT/Int Exp 20 Cash coverage 9.3 EBITDA/ Int Exp 21 Profitability Ratios 22 Profit Margin (PM) 11.89 14.00% NI/Net Sales 23 Gross profit margin 35.42 48.00% GM/Sales 24 Operating profit margin 18.95 24.00% EBIT/Sales 25 EBIT return on assets (EROA) 0.18 19.00% EBIT/Tot Assets 26 ROA 9.86 17.00% NI/Tot Assets 27 ROE 11.96 15.00% NI / Ave. SE 28 Market Value ratios (*) 29 Earning per share (EPS) 30 Price-earnings ratio 31 DuPont Equation 32 PM B3 TAT** 34 EM 35 ROE 36 37 (*) Price per share 38 n.a. n.a. 14.00% 0.9 1.23 15.00% NI/Outstanding shares Price/EPS NI/Net Sales Net Sales / Ave Total Assets Average Total Assets/Average Shareholders' Equity NI/ Ave. SE $ 65.00 $68.00 $ 71.00 (**) Instructor's Note: Why are average balance sheet amounts used in calculating the turnover ratios? In the calculation of a turnover ratio, the numerator is an amount from an annual income statement, while the denominator is a balance sheet amount. Since a balance sheet amount is a snapshot and reflects only an instant or moment, there is an inconsistency between the numerator and the denominator. For example, the numerator in the inventory turnover ratio is the cost of goods sold for the 365-day year, while the denominator reflects the cost of inventory for a just one moment at the end of the last day of the accounting year. To overcome this shortcoming, the denominator needs to be representative of all of the moments during the year. When the inventory amount on last year's balance sheet and the amount on this year's balance sheet are the only amounts available, it is common to use the average of these two balance sheet amounts in the denominator. It is also common to use the average of these two balance sheet amounts in the trend analysis. With the same reason, Dupont ratio can be expressed as: DuPont Analysis = Net Profit Margin TAT EM where: Net Profit Margin= Net Income/Sales TAT=Asset turnover Total Asset Turnover-Sales/Average Total Asset EM-Equity multiplier Equity Multiplier-Average Total Assets/Average Shareholders' Equity 39 10 P Q R S T A B D E F G H 1 2 3 4 5 Assets: 2023 2022 2021 % of Assets change % of Assets change % of Assets Liabilities and Stockholders' Equity: Accounts payable and 6 Cash and marketable securities 7 Accounts receivable 8 Inventory 9 Other current assts 10 Total current assets 567 16 17 11 Property, plant, and equipment 12 Less: Accumulated depreciation 13 Net property, plant, and equipment 14 Goodwill and other assets 15 accruals Notes payable Accrued taxes Total current liabilities Long-term debt Total liabilities Common Stock (98,051,400 shares) Additional Paid-in capital Retained earnings Treasury stock Total stockholders' equity Total liabilities and equity 18 Total assets 19 20 21 22 23 Net sales 24 Cost of goods sold 25 Gross profit Selling, general, and 26 administrative expenses Earnings before Interest, taxes, 27 depreciation, and amortization (EBITDA) 28 Depreciation and amortization 29 Earning before interest and taxes (EBIT) Operating income (loss) 30 Interest expense 31 Earnings before taxes (EBT) 32 Taxes 33 Net earnings (loss)/Net Income 34 35 36 37 38 39 40 41 42 12 2022 change 2021 % of Sales 2023 % of Sales change % of Sales K M N 2023 2022 2021 % of Assets change % of Assets change % of Assets A B D E F G H K -2345678 Instructions: All grey cells need to be filled. Numbers in the yellow color cells are the sums of each activities. The sum of numbers in grey cells in each category must equal the number in the yellow color cell. Operating Activities Net income 2023 114 2022 203 Additions (sources of cash) Depreciation Increase in accounts payable 9 Subtractions (uses of cash) 10 Increase in accounts receivable 11 Decrease in accrued income taxes 12 Increase in other current assets 13 Increase in inventories 14 Net cash provided by operating activities 272 334 15 Long-Term Investing Activities 16 Increase in property equipment 17 Decrease in goodwill and other assets 18 Net cash used in investing activities 19 Financing Activities 20 Increase in notes payable 21 22222222 23 24 Increase in long-term debt Sale of common stock Payment of cash dividends ** Purchase of treasury stock 25 Net cash provided by financing activities 26 27 Net increase in cash and marketable securities 28 Cash and marketable securities at beginning of year 29 Cash and marketable securities at end of year 30 -642 -382 232 245 31 32 33 ** Dividends: 34 2023 2022 35 Jan 1 Retained Earnings 36 37 38 39 add Net Income sub-total less dividends Dec 31 Retained Earnings 40 41 42 43 44 45 46 47 48 49 50