Finance

see attached

Instructions

–20. You also see estimated average income statement data for LGI’s main competitors.

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

·         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

2018

2020
2019
2018

8

payable

3

6

$ 18
$ 10
$ 5

$ 2,000

shares)

$ 1,280
$ 1,280

$ 4,118
$ 3,767
$ 3,328

98,051,400

2020
2019
2018

$ 98

$ 125

(34%)

$ 105

Largo Gobal Balance Sheet as of December 31 (millions)
								2020									2019
	Assets:		Liabilities and Stockholders’ Equity:
	Cash and marketable securities	$ 228	$ 366	$ 169		Accounts payable and accruals	$ 361	$ 324	$ 312
	Accounts receivable		$ 18	$ 181	$ 157		Notes	$ 140		$ 10	$ 61
	Inventory	$ 404	$ 398	$ 349		Accrued taxes		$ 5	$ 88	$ 109
Other current assets		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	$ 1,532	$ 1,368	Common Stock (	98,051,400	$ 490	$ 487	$ 483
	Goodwill and other assets			$ 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
Number of shares	97,302,600	96,521,200
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	$ 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	$ 207
	Earnings before taxes (EBT)	$ 173	$ 308	$ 493	$ 2,626
	Taxes	$ 59	$ 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

Notes

)

)

)

ROA

n.a.

PM

14.00%

NI/Net Sales

0.9

Net Sales / Ave Total Assets

EM

1.23

Average Total Assets/Average Shareholders’ Equity

ROE

15.00%

NI / Ave. SE

Industry Benchmark
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 (E	ROA	19.00%	EBIT/Tot Assets
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	Price/EPS
DuPont Equation
TAT**
(*) 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

% of Assets
change
% of Assets
change
% of Assets

Assets:

Liabilities and Stockholders’ Equity:

Cash and marketable securities

Accounts payable and accruals

Accounts receivable

Inventory

Accrued taxes

Total current liabilities

Total current assets
Property, plant, and equipment

Long-term debt

Less: Accumulated depreciation

Total liabilities

Net property, plant, and equipment

Goodwill and other assets

Additional Paid-in capital

Retained earnings

Treasury stock

Total stockholders’ equity

Total assets

Total liabilities and equity

2020

2019

2018

change
% of Sales
change
% of Sales

Cost of goods sold
Gross profit
Selling, general, and administrative expenses
Earnings before Interest, taxes, depreciation, and amortization (EBITDA)

and amortization

Earning before interest and taxes (EBIT) Operating income (loss)
Interest expense
Earnings before taxes (EBT)
Taxes
Net earnings (loss)/Net Income

					% of Assets						change
Notes payable
Other current assts
Common Stock (98,051,400 shares)
		% of Sales
Net sales
Depreciation

Tab 4_Cash Flow Analysis

2020
2019

114
203

Depreciation

2020
2019

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.
Operating Activities
Net income
Additions (sources of cash)
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:
Jan 1 Retained Earnings
add Net Income
sub-total
less dividends
Dec 31 Retained Earnings