mba
bechem34
P1-Final_OseiTwumasi_Calculation_Date.xlsxInstructions
| Version 10 June 2022 | |
| 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 2018–20. 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 2018–20 ratios. | |
| · Calculate the ratios for each year. | |
| Open tab 3. | |
| · Complete a common-size analysis. | |
| Open tab 4. | |
| · Complete a cash flow analysis. |
1. Bal. Sheet & Inc. Statement
| Largo Gobal Balance Sheet as of December 31 (millions) | ||||||||||
| 2020 | 2019 | 2018 | 2020 | 2019 | 2018 | |||||
| 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 | |||
| 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 | 98,051,400 | 97,302,600 | 96,521,200 | |||||||
| 2020 | 2019 | 2018 | Estimate 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 expenses | $ 125 | $ 98 | $ 91 | $ 105 | ||||||
| Earnings before Interest, taxes, depreciation, and amortization (EBITDA) | $ 488 | $ 663 | $ 877 | $ 3,576 | ||||||
| Depreciation and amortization | $ 174 | $ 218 | $ 259 | $ 743 | ||||||
| Earning before interest and taxes (EBIT) Operating income (loss) | $ 314 | $ 445 | $ 618 | $ 2,833 | ||||||
| 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 2020 | ||||||||||
| Jan 1, 2020 | $ 3,767 | |||||||||
| Dec 31, 2020 | $ 4,118 | |||||||||
| Sub-total (A) | $ 7,885 | |||||||||
| Average (A/2) | $ 3,943 | |||||||||
2. Ratio Analysis
| 2020 | 2019 | 2018 | Industry Benchmark | Notes | ||
| Liquidity Ratios | ||||||
| Current ratio | 1.5044883303 | 1.854368932 | 1.4107883817 | 1.92 | CA/CL | |
| Quick ratio | 0.7791741472 | 1.0815533981 | 0.6867219917 | 1.25 | CA - INV/CL | |
| Cash ratio | 0.7791741472 | 1.0815533981 | 0.6867219917 | 0.86 | Cash&Cash Equivalents/ CL | |
| Efficiency Ratios | ||||||
| Inventory turnover ratio | 3.4653465347 | 4.243718593 | 5.0573065903 | 5.37 | CGS/INV | |
| Days' sales in inventory | 105.3285714286 | 86.009473061 | 72.1728045326 | 50.6 | 365/INV turnover | |
| Accounts receivable turnover | 10.7074468085 | 13.5359116022 | 17.4076433121 | 18.12 | Sales / Ave AR | |
| Days' sales outstanding | 34.088425236 | 26.9653061224 | 20.9678009513 | 21.5 | 12 month DSO is (Ave AR/Sales)*365 | |
| Total asset turnover (TAT) | 0.4888295289 | 0.6503849217 | 0.8212139423 | 0.9 | Sales / Ave Total Assets | |
| Fixed assets turnover | 0.50325 | 0.7296009529 | 0.9183467742 | 2.75 | Sales / Ave Net Fixed Assets | |
| Leverage Ratios | ||||||
| Total debt ratio | 0.2462360369 | 0.2373241306 | 0.2298677885 | 0.21 | Tot Debt / Tot Assets | |
| Debt to equity ratio | 0.3266752577 | 0.3111729899 | 0.2984783457 | 0.27 | Tot Debt / Tot Equity | |
| Equity multiplier (EM) | 1.3266752577 | 1.3111729899 | 1.2984783457 | 1.23 | Tot Assets / Tot SE | |
| Times interest earned | 2.2269503546 | 3.2481751825 | 4.944 | 5.5 | EBIT / Int Exp | |
| Cash coverage | 3.4609929078 | 4.8394160584 | 17.2753623188 | 9.3 | EBITDA/ Int Exp | |
| Profitability Ratios | ||||||
| Profit Margin (PM) | 5.66% | 8% | 12% | 14.00% | NI/Net Sales | |
| Gross profit margin | 30.45% | 31% | 35% | 48.00% | GM/Sales | |
| Operating profit margin | 16% | 18% | 23% | 24.00% | EBIT / Sales | |
| EBIT return on assets (EROA) | 8% | 12% | 19% | 19.00% | EBIT/Tot Assets | |
| ROA | 3% | 5% | 10% | 17.00% | NI / Tot Assets | |
| ROE | 4% | 7% | 13% | 15.00% | NI / SE | |
| Market Value ratios (*) | ||||||
| Earning per share (EPS) | 1.16265551 | 2.0703427 | 3.31458806 | n.a. | NI / Outstanding shares | |
| Price-earnings ratio | $ 2,669.75 | $ 1,387.69 | $ 773.25 | n.a. | Price/EPS | |
| DuPont Equation | ||||||
| PM | 0.06% | 0.27% | 0.12% | 14.00% | NI/Net Sales | |
| TAT | 0.4888295289 | 0.6503849217 | 0.8212139423 | 0.9 | Sales / Ave Total Assets | |
| EM | 1.3266752577 | 1.3111729899 | 1.2984783457 | 1.23 | Tot Assets / Tot SE | |
| ROE | 0.04% | 0.07% | 0.13% | 15.00% | NI / SE | |
| (*) Price per share | $ 65.00 | $ 68.00 | $ 71.00 |
3. Common-size Analysis
| 2020 | 2019 | 2018 | 2020 | 2019 | 2018 | ||||||||
| % of Assets | change | % of Assets | change | % of Assets | % of Assets | change | % of Assets | change | % of Assets | ||||
| Assets: | Liabilities and Stockholders' Equity: | ||||||||||||
| Cash and marketable securities | 5.54% | -4.18% | 9.72% | 4.64% | 5.08% | Accounts payable and accruals | 8.77% | 0.17% | 8.60% | -0.77% | 9.38% | ||
| Accounts receivable | 4.57% | -0.24% | 4.80% | 0.09% | 4.72% | Notes payable | 3.40% | 0.67% | 2.73% | 0.90% | 1.83% | ||
| Inventory | 9.81% | -0.75% | 10.57% | 0.08% | 10.49% | Accrued taxes | 1.36% | -0.98% | 2.34% | -0.94% | 3.28% | ||
| Other current assts | 0.44% | 0.17% | 0.27% | 0.12% | 0.15% | Total current liabilities | 13.53% | -0.15% | 13.67% | -0.81% | 14.48% | ||
| Total current assets | 20.35% | -5.00% | 25.35% | 4.92% | 20.43% | ||||||||
| Property, plant, and equipment | 97.13% | 7.99% | 89.14% | -0.28% | 89.42% | Long-term debt | 11.10% | 1.04% | 10.06% | 1.56% | 8.50% | ||
| Less: Accumulated depreciation | 48.57% | 0.09% | 48.47% | 0.16% | 48.32% | Total liabilities | 24.62% | 0.89% | 23.73% | 0.75% | 22.99% | ||
| Net property, plant, and equipment | 48.57% | 7.90% | 40.67% | -0.44% | 41.11% | Common Stock (98,051,400 shares) | 11.90% | -1.03% | 12.93% | -1.59% | 14.51% | ||
| Goodwill and other assets | 31.08% | -2.90% | 33.98% | -4.48% | 38.46% | Additional Paid-in capital | 59.23% | 0.24% | 58.99% | -0.57% | 59.56% | ||
| Retained earnings | 6.56% | 1.80% | 4.75% | 1.81% | 2.94% | ||||||||
| Treasury stock | -2.31% | -1.91% | -0.40% | -0.40% | 0.00% | ||||||||
| Total stockholders’ equity | 75.38% | -0.89% | 76.27% | -0.75% | 77.01% | ||||||||
| Total assets | 100.00% | 0.00% | 100.00% | 0.00% | 100.00% | Total liabilities and equity | 100.00% | 0.00% | 100.00% | 0.00% | 100.00% | ||
| 2020 | 2019 | 2018 | |||||||||||
| % of Sales | change | % of Sales | change | % of Sales | |||||||||
| Net sales | |||||||||||||
| Cost of goods sold | |||||||||||||
| Gross profit | |||||||||||||
| Selling, general, and administrative expenses | 3.04% | 0.43% | 2.60% | -0.13% | 2.73% | ||||||||
| Earnings before Interest, taxes, depreciation, and amortization (EBITDA) | |||||||||||||
| Depreciation and amortization | 4.23% | -1.56% | 5.79% | -2.00% | 7.78% | ||||||||
| Earning before interest and taxes (EBIT) Operating income (loss) | |||||||||||||
| Interest expense | 3.42% | -0.21% | 3.64% | -0.12% | 3.76% | ||||||||
| Earnings before taxes (EBT) | |||||||||||||
| Taxes | 1.43% | -1.35% | 2.79% | -2.26% | 5.05% | ||||||||
| Net earnings (loss)/Net Income | 2.77% | -2.62% | 5.39% | -4.38% | 9.77% | ||||||||
| 0 |
4. Cash Flow Analysis
| 2020 | 2019 | |
| Operating Activities | ||
| Net income | 114 | 203 |
| Additions (sources of cash) | ||
| Depreciation | $ 174 | $ 218 |
| Increase in accounts payable | $ 361 | $ 324 |
| Subtractions (uses of cash) | ||
| Increase in accounts receivable | $ 188 | $ 181 |
| Decrease in accrued income taxes | $ 56 | $ 88 |
| Increase in other current assets | $ 18 | $ 10 |
| Increase in inventories | $ 404 | $ 398 |
| Net cash provided by operating activities | 272 | 334 |
| Long-Term Investing Activities | ||
| Increase in property equipment | $ 4,000 | $ 3,358 |
| Decrease in goodwill and other assets | $ 1,280 | $ 1,280 |
| Net cash used in investing activities | -642 | -382 |
| Financing Activities | ||
| Increase in notes payable | $ 140 | $ 103 |
| Increase in long-term debt | $ 457 | $ 379 |
| Sale of common stock | $ 490 | $ 487 |
| Payment of cash dividends ** | $ (23) | $ 122 |
| Purchase of treasury stock | $ (95) | $ (15) |
| Net cash provided by financing activities | 232 | 245 |
| Net increase in cash and marketable securities | $ 228 | $ 366 |
| Cash and marketable securities at beginning of year | ||
| Cash and marketable securities at end of year | ||
| all grey cells need to be filled | ||
| ** Dividends: | ||
| 2020 | 2019 | |
| Jan 1 Retained Earnings | $ 179 | $ 98 |
| add Net Income | $ 114 | $ 203 |
| sub-total | $ 293 | $ 301 |
| less dividends | $ (23) | $ 122 |
| Dec 31 Retained Earnings | $ 270 | $ 179 |
