Description
Attached is an excel on analyzing financial data. Majority of excel is completed and only need corrections made.
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
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 o
Open tab 1. You see the balance sheet and income statement for LGI for 2020–22. You
• 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
• Calculate the ratios for each year.
Open tab 3.
• Complete a common-size analysis.
Open tab 4.
• Complete a cash flow analysis.
company, Largo Global Inc. (LGI), using this Excel workbook, which includes the following tabs:
ou can receive feedback on the accuracy of your calculations before submitting your final
nt for LGI for 2020–22. You also see estimated average income statement data for LGI’s
s in tabs 2–4.
in the Excel workbook.
n industry benchmark you can use for comparison with LGI’s 2020–22 ratios.
Largo Gobal Balance Sheet as of Decemb
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
366
181
398
10
169
157
349
5
838
4000
2000
955
3358
1826
680
2976
1608
Net property, plant, and equipment
Goodwill and other assets
2000
1280
1532
1280
1368
1280
Total assets
4118
3767
3328
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
2022
$2,013
1400
613
2021
$2,450
1689
761
2020
$2,733
1765
968
Estimate
competitor
7564
3883
3681
125
98
91
105
488
174
663
218
877
259
3576
743
314
141
173
59
114
445
137
308
105
203
618
125
493
168
325
2833
207
2626
893
1733
Note: Assets on Jan 1, 2022 = Assets on Dec 31, 2021
Dec 31, 2022
Sub-total (A)
Average (A/2)
nce Sheet as of December 31 (millions)
2022
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
Number of shares (actual)
ts on Dec 31, 2021
2021
2020
361
140
56
557
324
103
88
515
312
61
109
482
457
1014
379
894
283
765
490
2439
270
-95
487
2222
179
-15
483
1982
98
0
3104
4118
2873
3767
2563
3328
98,051,400
97,302,600
96,521,200
2022
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
2021
Industry
Benchmark
2020
1.50
0.78
0.41
1.85
1.08
0.71
1.411
0.69
0.35
1.92
1.25
0.86
3.47
105.3
10.71
34.09
0.49
1.01
4.24
86.0
13.54
26.97
0.65
1.60
5.06
72.2
17.41
20.97
0.82
2.03
5.37
50.6
18.12
21.5
0.9
2.75
0.25
0.33
1.33
2.23
3.46
0.24
0.31
1.31
3.25
4.84
0.23
0.30
1.30
4.94
0.70
0.21
0.27
1.23
5.5
9.3
0.06
0.30
0.16
0.08
0.03
0.04
0.08
0.31
0.18
0.12
0.05
0.07
0.12
0.35
0.23
0.19
0.10
0.13
0.14
0.48
0.24
0.19
0.17
0.15
1.16
55.91
2.09
32.59
3.37
21.09
n.a.
n.a.
0.06
0.49
1.33
0.04
0.08
0.65
1.31
0.07
0.12
0.82
1.30
0.13
0.14
0.9
1.23
0.15
65
68
71
(**) Instructor’s Note: Why are average balance sheet amounts used in calculating the turnover ratios? In the calculation of a
inconsistency between the numerator and the denominator. For example, the numerator in the inventory turnover ratio is th
be representative of all of the moments during the year. When the inventory amount on last year’s balance sheet and the am
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
he turnover ratios? In the calculation of a turnover ratio, the numerator is an amount from an annual income statement, while the denom
rator in the inventory turnover ratio is the cost of goods sold for the 365-day year, while the denominator reflects the cost of inventory f
nt 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 averag
s’ Equity
is correct but is not recommended for the trend analysis for this project.
ement, while the denominator is a balance sheet amount. Since a balance sheet amount is a snapshot and reflects only an instant or mom
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 denom
mmon to use the average of these two balance sheet amounts in the denominator. It is also common to use the average of these two bala
lects only an instant or moment, there is an
his shortcoming, the denominator needs to
he average of these two balance sheet
2022
% of Assets
change
2021
% of Assets
change
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
5.54%
4.57%
9.81%
-4.18%
-0.24%
-0.75%
9.72%
4.80%
10.57%
4.64%
0.09%
0.08%
0.44%
20.35%
97.13%
48.57%
0.17%
-5.00%
7.99%
0.09%
0.27%
25.35%
89.14%
48.47%
0.12%
4.92%
-0.28%
0.16%
48.57%
7.90%
40.67%
-0.44%
31.08%
-2.90%
33.98%
-4.48%
100.00%
0.00%
100.00%
0.00%
100.00%
69.55%
30.45%
0.00%
0.61%
-0.61%
100.00%
68.94%
31.06%
0.00%
4.36%
-4.36%
6.21%
2.21%
4.00%
0.67%
24.24%
-2.82%
27.06%
8.90%
-5.03%
-0.58%
18.16%
5.59%
12.57%
-4.45%
1.02%
-5.47%
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)
8.64%
-0.25%
15.60%
-2.56%
7.00%
8.59%
1.41%
-3.98%
Taxes
Net earnings (loss)/Net Income
2.93%
5.66%
-1.35%
-2.62%
4.29%
8.29%
-1.86%
-3.61%
2020
% of Assets
5.08%
4.72%
10.49%
0.15%
20.43%
89.42%
48.32%
41.11%
38.46%
2022
% of Assets
Liabilities and
Stockholders’ Equity:
Accounts payable and
accruals
Notes payable
Accrued taxes
Total current
liabilities
change
2021
% of Assets
8.77%
3.40%
1.36%
0.17%
0.67%
-0.98%
8.60%
2.73%
2.34%
13.53%
-0.15%
13.67%
Long-term debt
Total liabilities
Common Stock
(98,051,400 shares)
Additional Paid-in
capital
11.10%
24.62%
1.04%
0.89%
10.06%
23.73%
11.90%
-1.03%
12.93%
59.23%
0.24%
58.99%
Retained earnings
6.56%
1.80%
4.75%
-2.31%
-1.91%
-0.40%
75.38%
-0.89%
76.27%
100.00%
0.00%
100.00%
Treasury stock
100.00%
Total stockholders’
equity
Total liabilities and
equity
100.00%
64.58%
35.42%
3.33%
32.09%
9.48%
22.61%
4.57%
18.04%
6.15%
11.89%
change
2020
% of Assets
-0.77%
0.90%
-0.94%
9.38%
1.83%
3.28%
-0.81%
14.48%
1.56%
0.75%
8.50%
22.99%
-1.59%
14.51%
-0.57%
59.56%
1.81%
2.94%
-0.40%
0.00%
-0.75%
77.01%
0.00%
100.00%
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
$
(sum of 3 CFs) $
Net increase in cash and marketable securities
$
Cash and marketable securities at beginning of year
Cash and marketable securities at end of year
$
$
2021
114.00 $
203.00
174.00 $
37.00 $
218.00
12.00
7.00
32.00
8.00
6.00
272.00
$
$
$
$
$
24.00
21.00
5.00
49.00
334.00
642.00 $
– $
(642.00) $
382.00
(382.00)
37.00 $
78.00 $
490.00 $
(522.00) $
(95.00) $
232.00 $
(138.00) $
(138.00) $
42.00
96.00
487.00
(680.00)
(15.00)
245.00
197.00
197.00
366.00 $
228.00 $
169.00
366.00
** Dividends:
2022
Jan 1 Retained Earnings $
add Net Income $
sub-total $
less dividends $
Dec 31 Retained Earnings $
2021
270
114
384
270
114
$
$
$
$
$
179
203
382
179
203
h 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
