A B C D E F G H K L M N P Q R 1 Revised on 1-29-2024 2 3 Project 1, Step 4: Instructions for This Excel Workbook 4 5 6 1. 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 7 2. Ratio Analysis 8 3. Common-size Analysis 9 10 11 12 13 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. 14 19 20 23 24 1567822222222 Use this information to complete the calculations in tabs 24. 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. E9 1 fx 680 A B 0 D E F G H Largo Gobal Balance Sheet as of December 31 (millions) J K L M N 23 2023 2022 2021 2023 2022 2021 4 Assets: Liabilities and Stockholders' Equity: 567 x 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 8 Other current assets SS $ 404 $ 398 $ 349 Accrued taxes $ 56 $ 88 $ 109 $ 18 $ 10 $ 5 Total current liabilities $ 557 $ 515 $ 482 9 Total current assets $ 838 $ 955 $ 680 17 10 Property, plant, and equipment $ 4,000 $ 3,358 $ 2,976 Long-term debt $ 457 $ 379 $ 283 11 Less: Accumulated depreciation $ 2,000 $ 1,826 $ 1,608 Total liabilities $ 1,014 $ 894 $ 765 12 72 Common Stock (98,051,400 215628222222 13 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 $ Retained earnings $ 270 $ 179 $ 1,982 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 19 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 $ 2,733 S 7,564 Cost of goods sold $ 1,400 $ 1,689 $ 1,765 $ 3,883 24 Gross profit $ 613 $ 761 $ 968 S 3.681 25 Selling, general, and administrative 25 expenses $ 125 $ 98 $ 91 $ 105 Earnings before Interest, taxes, 26 26 depreciation, and amortization (EBITDA) $ 488 $ 27 Depreciation and amortization $ 174 SS 663 $ 877 $ 3,576 218 $ 259 $ 743 Earning before interest and taxes 28 (EBIT) Operating income (loss) $ 314 S 445 $ 618 $ 2,833 29 Interest expense $ 141 S 137 $ 125 $ 207 30 Earnings before taxes (EBT) $ 173 $ 308 $ 493 $ 2,626 31 Taxes (34%) $ 59 $ 105 $ 168 $ 32 Net earnings (loss)/Net Income $ 114 S 203 $ 325 $ 893 1.733 33 34 35 Average Total Assets 2023 36 Jan 1, 2023 Note: Assets on Jan 1, 2023 = Assets on Dec 31, 2022 37 Dec 31, 2023 38 39 Sub-total (A) Average (A/2) 40 41 42 43 038 fx B C D E F G H J K L M N P Q R S 1 2 3 2023 2022 2021 Industry Benchmark Notes 45 4 Liquidity Ratios Current ratio 6 Quick ratio 7 Cash ratio 8 Efficiency Ratios 9 1.92 CA/CL 1.25 CA - INV/CL 0.86 Cash&Cash Equivalents/ CL Inventory turnover ratio 10 Days' sales in inventory 11 Accounts receivable turnover 12 Days' sales outstanding 13 Total asset turnover (TAT) 14 Fixed assets turnover 15 Leverage Ratios 16 Total debt ratio 5.37 CGS/INV 50.6 365/INV turnover 18.12 Sales / Ave AR 21.5 12 month DSO is (Ave AR/Sales)*365 0.9 Net Sales/Ave Total Assets 2.75 Net Sales/Ave Net Fixed Assets 0.21 Tot Debt/Tot Assets 17 Debt to equity ratio 0.27 Tot Debt/Tot Equity 18 Equity multiplier (EM) 1.23 Average Total Assets/Average Shareholders' Equity 19 Times interest earned 5.5 EBIT/Int Exp 20 Cash coverage 21 Profitability Ratios 9.3 EBITDA/ Int Exp 22 Profit Margin (PM) 14.00% NI/Net Sales 23 Gross profit margin 48.00% GM/Sales 24 Operating profit margin 24.00% EBIT/Sales 25 EBIT return on assets (EROA) 19.00% EBIT/Tot Assets 26 ROA 17.00% NI / Tot Assets 27 ROE 15.00% NI / Ave. SE 28 Market Value ratios (*) 29 Earning per share (EPS) 30 Price-earnings ratio 31 DuPont Equation 32 PM 33 TAT** 34 EM 35 ROE 36 37 (*) Price per share 38 n.a. n.a. NI/ Outstanding shares Price/EPS NI/Net Sales Net Sales / Ave Total Assets 14.00% 0.9 1.23 15.00% NI / Ave. SE Average Total Assets/Average Shareholders' Equity $ 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 K33 1234 fx B 5 Assets: 6 Cash and marketable securities 7 Accounts receivable 8 Inventory 9 Other current assts 18 10 Total current assets D E F G H 2023 2022 2021 % of Assets change % of Assets change % of Assets Liabilities and Stockholders' Equity: Accounts payable and 11 Property, plant, and equipment 12 Less: Accumulated depreciation 13 Net property, plant, and equipment 14 Goodwill and other assets 15 165 17 18 Total assets 2022 change 2021 % of Sales 2023 % of Sales change % of Sales 20 2222222 23 Net sales 24 Cost of goods sold 25 Gross profit 26 26 Selling, general, and 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 K 2023 2022 % of Assets change % of Assets 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 M N change 2021 % of Assets 0 A1 fx 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 B C D E F G A H J K 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. L 1234567815 Operating Activities Net income 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 15 Long-Term Investing Activities 16 Increase in property equipment 17 Decrease in goodwill and other assets 18 Net cash used in investing activities Financing Activities 19 20 Increase in notes payable 21 Increase in long-term debt 22 Sale of common stock 23 Payment of cash dividends ** 24 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 2023 114 2022 203 272 334 -642 -382 232 245 30 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