Number 1

profileHelp4me
Project1ExcelWorkbook_1001214.xlsx
Home>Education homework help>Number 1

Instructions

version 01.10.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 2019–21. You also see partial balance sheet and 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)
2021 2020 2019 2021 2020 2019
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 4000 3358 2976 Long-term debt 457 379 283
Less: Accumulated depreciation 2000 1826 1608 Total liabilities 1014 894 765
Net property, plant, and equipment 2000 1532 1368 Common Stock (98,051,400 shares) 490 487 483
Goodwill and other assets 1280 1280 1280 Additional Paid-in capital 2439 2222 1982
Retained earnings 270 179 98
Total assets 4118 3767 3328 Treasury stock -95 -15 0
Total stockholders’ equity 3104 2873 2563
Total liabilities and equity 4118 3767 3328
# of shares 98051400 97302600 96521200
2021 2020 2019 estimate competitor
Sales (net sales) $2,013 $2,450 $2,733 7546
Cost of goods sold 1400 1689 1765 3838
Gross profit 613 761 968 3708
Selling, general, and administrative expenses 125 98 91 104
Earnings before Interest, taxes, depreciation, and amortization (EBITDA) 488 663 877 3604
Depreciation and amortization 174 218 259 734
Earning before interest and taxes (EBIT) Operating income (loss) 314 445 618 2870
Interest expense 141 137 125 209
Earnings before taxes (EBT) 173 308 493 2661
Taxes (34%) 59 105 168 905
Net earnings (loss)/Net Income 114 203 325 1756

2. Ratio Analysis

2021 2020 2019 Industry Benchmark
Liquidity Ratios
Current ratio 1.50 1.85 1.41 1.93
Quick ratio 0.78 1.08 0.69 1.26
Cash ratio 0.41 0.71 0.35 0.87
Efficiency Ratios
Inventory turnover ratio 3.47 4.24 5.06 5.38
Days' sales in inventory 105.33 86.01 72.17 50.7
Accounts receivable turnover 10.71 13.54 17.41 18.13
Days' sales outstanding 34.09 26.97 20.97 21.6
Total asset turnover (TAT) 0.49 0.65 0.82 0.95
Fixed assets turnover 0.61 0.87 1.03 2.76
Leverage Ratios
Total debt ratio 0.25 0.24 0.23 0.22
Debt to equity ratio 0.33 0.31 0.30 0.28
Equity multiplier (EM) 1.33 1.31 1.30 1.24
Times interest earned 2.23 3.25 4.94 5.55
Cash coverage 3.46 4.84 7.02 9.4
Profitability Ratios
Profit Margin (PM) 0.06 0.08 0.12 0.15
Gross profit margin 0.30 0.31 0.35 0.49
Operating profit margin 0.16 0.18 0.23 0.25
EBIT return on assets (EROA) 0.08 0.12 0.19 0.21
ROA 0.03 0.05 0.10 0.18
ROE 0.04 0.07 0.13 0.18
Market Value ratios (*)
Earning per share (EPS) 0.00000116266 0.00000208628 0.00000336714 n.a.
Price-earnings ratio 55,906,284 32,593,974 21,086,170 n.a.
DuPont Equation
PM 0.06 0.08 0.12 0.15
TAT 0.49 0.65 0.82 0.95
EM 1.33 1.31 1.30 1.24
ROE 0.0367268041 0.0706578489 0.1268045259 0.18
(*) Price per share 65 68 71

3. Common-size Analysis

2021 2020 2019 2021 2020 2019
% 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% 0.00%
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%
Total assets 100.00% 100.00% 100.00% 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 liabilities and equity 100.00% 100.00% 100.00%
2021 2020 2019
% of Sales change % of Sales change % of Sales
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

4. Cash Flow Analysis

2021 2020
Operating Activities
Net income 114 203
Additions (sources of cash)
Depreciation 174 218
Increase in accounts payable 37 12
Subtractions (uses of cash)
Increase in accounts receivable -7 -24
Decrease in accrued income taxes -32 -21
Increase in other current assets -8 -5
Increase in inventories -6 -49
Net cash provided by operating activities 272 334
Long-Term Investing Activities
Increase in property equipment -642 -382
Decrease in goodwill and other assets 0 0
Net cash used in investing activities -642 -382
Financing Activities
Increase in notes payable 37 42
Increase in long-term debt 78 96
Sale of common stock 3 4
Payment of cash dividends 34 88
Purchase of treasury stock 80 15
Net cash provided by financing activities 232 245
(sum of 3 CFs) -138 197
Net increase in cash and marketable securities -138 197
Cash and marketable securities at beginning of year 366 169
Cash and marketable securities at end of year 228 366
all grey cells need to be filled