Finance

Instructions
Revised on 11-25-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.
Tab 1_BalanceSheet & Inc.State
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 Note: Assets on Jan 1, 2020 = Assets on Dec 31, 2019
Dec 31, 2020
Sub-total (A)
Average (A/2)
Tab 2_Ratio Analysis
2020 2019 2018 Industry Benchmark Notes
Liquidity Ratios
Current ratio 1.92 CA/CL
Quick ratio 1.25 CA – INV/CL
Cash ratio 0.86 Cash&Cash Equivalents/ CL
Efficiency Ratios
Inventory turnover ratio 5.37 CGS/INV
Days’ sales in inventory 50.6 365/INV turnover
Accounts receivable turnover 18.12 Sales / Ave AR
Days’ sales outstanding 21.5 12 month DSO is (Ave AR/Sales)*365
Total asset turnover (TAT) 0.9 Net Sales / Ave Total Assets
Fixed assets turnover 2.75 Net Sales / Ave Net Fixed Assets
Leverage Ratios
Total debt ratio 0.21 Tot Debt / Tot Assets
Debt to equity ratio 0.27 Tot Debt / Tot Equity
Equity multiplier (EM) 1.23 Average Total Assets/Average Shareholders’ Equity
Times interest earned 5.5 EBIT / Int Exp
Cash coverage 9.3 EBITDA/ Int Exp
Profitability Ratios
Profit Margin (PM) 14.00% NI/Net Sales
Gross profit margin 48.00% GM/Sales
Operating profit margin 24.00% EBIT / Sales
EBIT return on assets (EROA) 19.00% EBIT/Tot Assets
ROA 17.00% NI / Tot Assets
ROE 15.00% NI / Ave. SE
Market Value ratios (*)
Earning per share (EPS) n.a. NI / Outstanding shares
Price-earnings ratio n.a. Price/EPS
DuPont Equation
PM 14.00% NI/Net Sales
TAT** 0.9 Net Sales / Ave Total Assets
EM 1.23 Average Total Assets/Average Shareholders’ Equity
ROE 15.00% NI / Ave. SE
(*) Price per share $ 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 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.
Tab 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
Treasury stock
Total stockholders’ equity
Total assets 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
Tab 4_Cash Flow Analysis
Instructions: All grey cells need to be filled. Numbers in the yellow colour cells are the sums of each activities. The sum of numbers in grey cells in each category must equal the number in the yellow colour cell.
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
Net increase in cash and marketable securities
Cash and marketable securities at beginning of year
Cash and marketable securities at end of year
** Dividends:
2020 2019
Jan 1 Retained Earnings
add Net Income
sub-total
less dividends
Dec 31 Retained Earnings