6:10 LTE Tab 1_Balance Sheet & Inc.State Tab 2_Ratio Analysis Tab 3_Common-size Analysis Ta Instructions 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: 1. Bal. Sheet & Inc. Statement 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. Complete a common-size analysis. Open tab 4. Complete a cash flow analysis. AA leocontent.umgc.edu 6:10 LTE Instructions Tab 1 Balance Sheet & Inc.State Tab 2_Ratio Analysis Tab 3 Common-size Analysis Ta Largo Gobal Balance Sheet as of December 31 (millions) 2023 2022 2021 2023 2022 2021 Inventory Assets: Cash and marketable securities Accounts receivable Other current assets Liabilities and Stockholders' Equity: 228 366 169 Accounts payable and accruals 361 324 312 188 181 157 Notes payable 140 103 61 404 398 349 Accrued taxes 56 88 109 18 10 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 Net property, plant, and equipment 2,000 1,532 1,368 Common Stock (98,051,400 shares) 490 487 483 Goodwill and other assets 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 3,104 2,873 2,563 Total assets 4,118 3,767 3,328 Total liabilities and equity 4,118 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 2,733 7,564 Cost of goods sold 1,400 1,689 1,765 3,883 Gross profit 613 761 968 3,681 Selling, general, and administrative 125 98 91 105 expenses Earnings before Interest, taxes, 488 663 663 877 3,576 depreciation, and amortization (EBITDA) Depreciation and amortization 174 218 259 743 Earning before interest and taxes (EBIT) 314 445 618 2,833 Operating income (loss) Interest expense 141 137 125 207 Earnings before taxes (EBT) 173 308 493 2,626 Taxes (34%) 59 105 168 893 Net earnings (loss)/Net Income 114 203 325 1,733 Average Total Assets 2023 Jan 1, 2023 Note: Assets on Jan 1, 2023 Assets on Dec 31, 2022 AA Dec 31, 2023 Sub-total (A) Average (A/2) leocontent.umgc.edu 6:10 LTE Instructions Tab 1_Balance Sheet & Inc.State Tab 2 Ratio Analysis Tab 3_Common-size Analysis Ta 2023 2022 2021 Industry Benchmark Notes Liquidity Ratios Current ratio Quick ratio Cash ratio 1.92 CA/CL 1.25 CA-INV/CL 0.86 Cash&Cash Equivalents/ CL Efficiency Ratios Inventory turnover ratio Days' sales in inventory Accounts receivable turnover Days' sales outstanding Total asset turnover (TAT) Fixed assets turnover Leverage Ratios 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 Debt to equity ratio 0.27 Tot Debt/Tot Equity Equity multiplier (EM) Times interest earned Cash coverage Profitability Ratios 1.23 Average Total Assets/Average Shareholders' Equity 5.5 EBIT/Int Exp 9.3 EBITDA/ Int Exp Profit Margin (PM) 14.00% NI/Net Sales Gross profit margin 48.00% Operating profit margin 24.00% EBIT return on assets (EROA) 19.00% GM/Sales EBIT/Sales EBIT/Tot Assets ROA ROE 17.00% 15.00% NI/Tot Assets Market Value ratios (*) Earning per share (EPS) Price-earnings ratio DuPont Equation n.a. n.a. PM TAT** NI / Ave. SE NI / Outstanding shares Price/EPS NI/Net Sales EM 14.00% 0.9 1.23 15.00% Net Sales Ave Total Assets Average Total Assets/Average Shareholders' Equity NI / Ave. SE ROE (*) Price per share 65 68 71 (**) 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 It is worth noting that the textbook focuses on the single year's ratio analysis which is correct but is not recommended for the trend analysis for this project. AA leocontent.umgc.edu 6:10 LTE Instructions Tab 1_Balance Sheet & Inc.State Tab 2_Ratio Analysis Tab 3 Common-size Analysis Ta Assets: Cash and marketable securities Accounts receivable Inventory Other current assts Total current assets Property, plant, and equipment Less: Accumulated depreciation Net property, plant, and equipment Goodwill and other assets Total assets. Net sales Cost of goods sold Gross profit Selling, general, and administrative expenses Earnings before Interest, taxes, depreciation, and amortization (EBITDA) Depreciation and amortization Earning before interest and taxes (EBIT) Operating income (loss) Interest expense Earnings before taxes (EBT) Taxes Net earnings (loss)/Net Income 2023 2022 % of Assets change % of Assets change 2021 % of Assets 2023 2022 % of Sales change % of Sales change 2021 % of Sales Liabilities and Stockholders' Equity: Accounts payable and 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 equity Total stockholders' Total liabilities and equity AA leocontent.umgc.edu 2023 % of Assets change % of A: 6:10 LTE Instructions Tab 1_BalanceSheet & Inc.State Tab 2_Ratio Analysis Tab 3_Common-size Analysis Ta 2023 % of Assets 2022 change % of Assets change 2021 % of Assets 2023 2022 2021 % of Assets change % of Assets change % of Assets Liabilities and Stockholders' Equity: Accounts payable and accruals Notes payable % of Sales 2023 change % of Sales 2022 change 2021 % of Sales 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 AA leocontent.umgc.edu 6:10 LTE Instructions Tab 1_BalanceSheet & Inc.State Tab 2_Ratio Analysis Tab 3_Common-size Analysis 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 Additions (sources of cash) Depreciation Increase in accounts payable Subtractions (uses of cash) 2023 114 2022 203 Increase in accounts receivable Decrease in accrued income taxes Increase in other current assets Increase in inventories Net cash provided by operating activities 272 334 Long-Term Investing Activities Increase in property equipment Decrease in goodwill and other assets Net cash used in investing activities. Financing Activities Increase in notes payable Increase in long-term debt Sale of common stock Payment of cash dividends ** Purchase of treasury stock Net cash provided by financing activities Net increase in cash and marketable securities Cash and marketable securities at beginning of year Cash and marketable securities at end of year -642 -382 232 245 **Dividends: 2023 2022 Jan 1 Retained Earnings add Net Income sub-total less dividends Dec 31 Retained Earnings AA leocontent.umgc.edu Ta