Description
To help you analyze LGI’s financial situation so that you can make recommendations for improvement, you receive an Excel workbook containing the company’s key financial statements. After performing the ratio, common-size, and cash flow analyses, submit your initial findings.Complete the analysis calculation for the project:Download the Project 1 Excel Workbook , click the Instructions tab, and read the instructions.Perform ratio, common-size, and cash flow analyses using the Income Statement and Balance Sheet worksheet in the Excel workbook.If you would like instructor feedback on this step, follow the instructions below to submit your Excel file to the Assignments folder as a milestone by the end of Week 1. This is optional. If you choose to submit the milestone, you will receive instructor feedback. To distinguish the milestone submission from the file you will submit in Step 5, label your file as follows: P1_milestone_lastname_Calculation_date
Unformatted Attachment Preview
Revised on 6-15-2023
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 2020–22. 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 2020–22 ratios.
• Calculate the ratios for each year.
Open tab 3.
• Complete a common-size analysis.
Open tab 4.
• Complete a cash flow analysis.
Largo Gobal Balance Sheet as of Dece
2022
2021
2020
Assets:
Cash and marketable securities
Accounts receivable
Inventory
Other current assets
Total current assets
Property, plant, and equipment
Less: Accumulated depreciation
$
$
$
$
$
$
$
228
188
404
18
838
4,000
2,000
$
$
$
$
$
$
$
366
181
398
10
955
3,358
1,826
$
$
$
$
$
$
$
169
157
349
5
680
2,976
1,608
Net property, plant, and equipment
Goodwill and other assets
$
$
2,000 $
1,280 $
1,532
1,280
$
$
1,368
1,280
Total assets
$
4,118 $
3,767
$
3,328
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 (34%)
Net earnings (loss)/Net Income
Average Total Assets 2022
Jan 1, 2022
Estimate
2021
2020
competitor
$ 2,450 $
2,733 $
7,564
$ 1,689 $
1,765 $
3,883
$
761 $
968 $
3,681
$
$
$
2022
2,013
1,400
613
$
125
$
98 $
91 $
105
$
$
488
174
$
$
663 $
218 $
877 $
259 $
3,576
743
$
$
$
$
$
314
141
173
59
114
$
$
$
$
$
445
137
308
105
203
618
125
493
168
325
2,833
207
2,626
893
1,733
$
$
$
$
$
$
$
$
$
$
Note: Assets on Jan 1, 2022 = Assets on Dec 31, 2021
Dec 31, 2022
Sub-total (A)
Average (A/2)
ance Sheet as of December 31 (millions)
2022
Liabilities and Stockholders’
Equity:
Accounts payable and accruals
Notes payable
Accrued taxes
Total current liabilities
$
$
$
$
361
140
56
557
Long-term debt
Total liabilities
Common Stock (98,051,400
shares)
Additional Paid-in capital
Retained earnings
Treasury stock
Total stockholders’ equity
Total liabilities and equity
$
$
Number of shares (actual)
98,051,400
ets on Dec 31, 2021
2021
$
$
$
$
324
103
88
515
2020
$
$
$
$
312
61
109
482
457 $
1,014 $
379 $
894 $
283
765
$
$
$
$
$
490 $
2,439 $
270 $
(95) $
3,104 $
487 $
2,222 $
179 $
(15) $
2,873 $
483
1,982
98
2,563
$
4,118 $
3,767 $
3,328
97,302,600
96,521,200
2022
2021
2020
Liquidity Ratios
Current ratio
Quick ratio
Cash ratio
Efficiency Ratios
Inventory turnover ratio
Days’ sales in inventory
Accounts receivable turnover
Days’ sales outstanding
Total asset turnover (TAT)
Fixed assets turnover
Leverage Ratios
Total debt ratio
Debt to equity ratio
Equity multiplier (EM)
Times interest earned
Cash coverage
Profitability Ratios
Profit Margin (PM)
Gross profit margin
Operating profit margin
EBIT return on assets (EROA)
ROA
ROE
Market Value ratios (*)
Earning per share (EPS)
Price-earnings ratio
DuPont Equation
PM
TAT**
EM
ROE
(*) Price per share
Industry
Benchmark
1.92
1.25
0.86
5.37
50.6
18.12
21.5
0.9
2.75
0.21
0.27
1.23
5.5
9.3
14.00%
48.00%
24.00%
19.00%
17.00%
15.00%
n.a.
n.a.
14.00%
0.9
1.23
15.00%
$
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
amount from an annual income statement, while the denominator is a balance sheet amount. Since a balance sheet amount
instant or moment, there is an inconsistency between the numerator and the denominator. For example, the numerator in th
cost of goods sold for the 365-day year, while the denominator reflects the cost of inventory for a just one moment at the en
year. To overcome this shortcoming, the denominator needs to be representative of all of the moments during the year. Whe
year’s balance sheet and the amount on this year’s balance sheet are the only amounts available, it is common to use the ave
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
Notes
CA/CL
CA – INV/CL
Cash&Cash Equivalents/ CL
CGS/INV
365/INV turnover
Sales / Ave AR
12 month DSO is (Ave AR/Sales)*365
Net Sales / Ave Total Assets
Net Sales / Ave Net Fixed Assets
Tot Debt / Tot Assets
Tot Debt / Tot Equity
Average Total Assets/Average Shareholders’ Equity
EBIT / Int Exp
EBITDA/ Int Exp
NI/Net Sales
GM/Sales
EBIT / Sales
EBIT/Tot Assets
NI / Tot Assets
NI / Ave. SE
NI / Outstanding shares
Price/EPS
NI/Net Sales
Net Sales / Ave Total Assets
Average Total Assets/Average Shareholders’ Equity
NI / Ave. SE
g the turnover ratios? In the calculation of a turnover ratio, the numerator is an
eet amount. Since a balance sheet amount is a snapshot and reflects only an
ominator. For example, the numerator in the inventory turnover ratio is the
f inventory for a just one moment at the end of the last day of the accounting
of all of the moments during the year. When the inventory amount on last
ounts available, it is common to use the average of these two balance sheet
alance sheet amounts in the trend analysis.
ers’ Equity
ch is correct but is not recommended for the trend analysis for this project.
2022
change
% of Assets
2021
% of Assets
change
2022
% of Sales
change
2021
change
% of Sales
Assets:
Cash and marketable securities
Accounts receivable
Inventory
Other current assts
Total current assets
Property, plant, and equipment
Less: Accumulated depreciation
Net property, plant, and equipment
Goodwill and other assets
Total assets
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
2020
% of Assets
2022
% of Assets
change
Liabilities and
Stockholders’ Equity:
Accounts payable and
accruals
Notes payable
Accrued taxes
Total current
liabilities
Long-term debt
Total liabilities
Common Stock
(98,051,400 shares)
Additional Paid-in
capital
Retained earnings
Treasury stock
Total stockholders’
equity
Total liabilities and
equity
2020
% of Sales
2021
% of Assets
2021
change
2020
% of Assets
Instructions: All grey cells need to be filled. Numbers in the yellow colour cells are the sums of each activities. The sum of n
2022
Operating Activities
Net income
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
Long-Term Investing Activities
Increase in property equipment
Decrease in goodwill and other assets
Net cash used in investing activities
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
2021
114
203
272
334
-642
-382
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:
2022
Jan 1 Retained Earnings
add Net Income
sub-total
less dividends
Dec 31 Retained Earnings
2021
ch activities. The sum of numbers in grey cells in each category must equal the number in the yellow colour cell.
Purchase answer to see full
attachment
