Number 1

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

Instructions

version 10.01.2021
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 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)
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 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
2020 2019 2018 estimate competitor
Sales (net sales) $2,013 $2,450 $2,733 7564
Cost of goods sold 1400 1689 1765 3883
Gross profit 613 761 968 3681
Selling, general, and administrative expenses 125 98 91 105
Earnings before Interest, taxes, depreciation, and amortization (EBITDA) 488 663 877 3576
Depreciation and amortization 174 218 259 743
Earning before interest and taxes (EBIT) Operating income (loss) 314 445 618 2833
Interest expense 141 137 125 207
Earnings before taxes (EBT) 173 308 493 2626
Taxes (34%) 59 105 168 893
Net earnings (loss)/Net Income 114 203 325 1733

2. Ratio Analysis

2020 2019 2018 Industry Benchmark
Liquidity Ratios
Current ratio 1.92
Quick ratio 1.25
Cash ratio 0.86
Efficiency Ratios
Inventory turnover ratio 5.37
Days' sales in inventory 50.6
Accounts receivable turnover 18.12
Days' sales outstanding 21.5
Total asset turnover (TAT) 0.9
Fixed assets turnover 2.75
Leverage Ratios
Total debt ratio 0.21
Debt to equity ratio 0.27
Equity multiplier (EM) 1.23
Times interest earned 5.5
Cash coverage 9.3
Profitability Ratios
Profit Margin (PM) 0.14
Gross profit margin 0.48
Operating profit margin 0.24
EBIT return on assets (EROA) 0.19
ROA 0.17
ROE 0.15
Market Value ratios (*)
Earning per share (EPS) n.a.
Price-earnings ratio n.a.
DuPont Equation
PM 0.14
TAT 0.9
EM 1.23
ROE 0.15
(*) Price per share 65 68 71

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 Accounts payable and accruals
Accounts receivable Notes payable
Inventory Accrued taxes
Other current assts Total current liabilities
Total current assets
Property, plant, and equipment Long-term debt
Less: Accumulated depreciation Total liabilities
Net property, plant, and equipment Common Stock (98,051,400 shares)
Goodwill and other assets Additional Paid-in capital
Retained earnings
Total assets Treasury stock
Total stockholders’ equity
Total liabilities and equity
2020 2019 2018
% 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

2020 2019
Operating Activities
Net income 114 203
Additions (sources of cash)
Depreciation
Increase in accounts payable
Subtractions (uses of cash)
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 -642 -382
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 232 245
(sum of 3 CFs) -138 197
Net increase in cash and marketable securities
Cash and marketable securities at beginning of year
Cash and marketable securities at end of year
all grey cells need to be filled