+1(978)310-4246 credencewriters@gmail.com
  

Version 10 June 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.
Largo Gobal Balance Sheet as of Dece
2020
2019
2018
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 2020
2020
$
2,013
$
1,400
$
613
Estimate
2019
2018
competitor
$ 2,450 $
2,733 $
7,564
$ 1,689 $
1,765 $
3,883
$
761 $
968 $
3,681
$
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
$
$
$
$
$
$
$
$
$
$
Jan 1, 2020 $
Dec 31, 2020 $
Sub-total (A) $
Average (A/2) $
3,767
4,118
7,885
3,943
ance Sheet as of December 31 (millions)
2020
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
98,051,400
2019
$
$
$
$
324
103
88
515
2018
$
$
$
$
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
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
# of shares
$
2019
2018
Industry
Benchmark
1.50
0.78
0.41
1.85
1.08
0.71
1.41
0.69
0.35
1.92
1.25
0.86
3.47
105.19
11.67
31.28
0.54
0.68
4.24
86.08
14.20
25.70
0.66
0.83
5.06
72.13
15.84
23.04
0.73
0.92
5.37
50.6
18.12
21.5
0.9
2.75
0.25
0.33
1.33
1.23
3.46
0.24
0.31
1.31
2.25
4.84
0.23
0.30
1.30
3.94
7.02
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
14.00%
48.00%
24.00%
19.00%
17.00%
15.00%
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
14.00%
0.9
1.23
15.00%
65.00 $ 68.00 $ 71.00
98051400 97302600 96521200
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
Sales / Ave Total Assets
Sales / Ave Net Fixed Assets
Tot Debt / Tot Assets
Tot Debt / Tot Equity
Tot Assets / Tot SE
EBIT / Int Exp
EBITDA/ Int Exp
NI/Net Sales
GM/Sales
EBIT / Sales
EBIT/Tot Assets
NI / Tot Assets
NI / SE
NI / Outstanding shares
Price/EPS
NI/Net Sales
Sales / Ave Total Assets
Tot Assets / Tot SE
NI / SE
2020
change
% of Assets
2019
% 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
Total 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
2020
% of Sales
change
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
100.00
69.55
30.45
0.00
-0.61
7.35
6.21
-2.21
24.24
2.82
8.64
0.25
15.60
2.56
7.00
8.59
2.93
5.66
-1.41
3.98
1.35
2.62
2019
change
% of Sales
100.00
68.94
37.80
0.00
3.10
-2.39
4.00
-0.67
27.06
8.90
5.03
0.58
18.16
5.59
12.57
4.29
8.29
4.45
-1.02
5.47
1.86
3.61
2018
% of Assets
5.08
4.72
10.49
0.15
20.43
89.42
48.32
41.11
38.46
100.00
2018
% of Sales
100.00
72.04
35.42
3.33
32.09
9.48
22.61
4.57
18.04
6.15
11.89
2020
% 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
2019
% 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
11.10
24.62
1.04
0.89
10.06
23.73
11.90
1.03
12.93
59.23
6.56
2.31
0.24
1.80
1.91
58.99
4.75
0.40
75.38
0.89
76.27
100.00
0.00
100.00
2019
change
2018
% 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
1.81
0.40
59.56
2.94
0.00
0.75
77.01
0.00
100.00
2020
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
2019
114
203
174
37
218
12
7
32
8
6
272
24
21
5
49
334
468
174
-642
164
218
-382
37
78
3
194
-80
232
-138
42
96
4
105
-15
245
197
Net increase in cash and marketable securities
-138
197
Cash and marketable securities at beginning of
year
366
169
Cash and marketable securities at end of year
228
366
all grey cells need to be filled
** Dividends:
2020
Jan 1 Retained Earnings $
add Net Income $
sub-total $
less dividends $
Dec 31 Retained Earnings $
2019
179
114
293
23
270
$
$
$
$
$
98
203
301
122
179
2222
Project 1 Report
Instructions: Answer the five questions below. They focus entirely on the financial health of
Largo Global Inc. (LGI) based on the three years of income statement and balance sheet data
provided in the Excel workbook. Base your analysis only on the financial statements provided in
the Excel workbook. Provide support for your reasoning from the readings in Project 1, Step 1,
and the discussion in Project 1, Step 3. Be sure to cite your sources.
Provide a detailed response below each question. Use 12-point font and double spacing.
Maintain the existing margins in this document. Your final Word document, including the
questions, should not exceed 5 pages. Include a title page in addition to the five pages. Any
tables and graphs you choose to include are also excluded from the five-page limit. Name your
document as follows: P1_Final_lastname_Report_date.
You must address all five questions and make full use of the information on tabs 2–4 as well as
the competitor and industry data in the Excel workbook (ratio, common-size, and cash flow
analysis).
You are strongly encouraged to exceed the requirements by refining your analysis. Consider
other tools and techniques that were discussed in the required and recommended reading for
Project 1. This means adding an in-depth explanation of what happened in the three years for
which data was provided to make precise recommendations to LGI.
011022
Title Page
Name
Course and section number
Faculty name
Submission date
2222
Questions:
1. How would you assess the overall financial health of Largo Global Inc. (LGI)? You will
need to provide a broad view of the main trends that emerge from your analyses of the
information in tabs 2, 3, and 4. Your key findings should be synthesized and highlight a
clear diagnostic of LGI’s financial strength and/or weakness. [HINT: all 5 questions are
interrelated and may sometimes build on each other – it is imperative that you develop a “blueprint” or an outline of what you are answering for each question. Do not answer each question
independently as if they were not connected. You should not be redundant but should make sure
that you are judiciously coordinating these 5 questions. Question 1 and 5 should be providing the
introduction and conclusion of your analysis. Questions 2, 3, and 4 should be providing the
“body” or development of your analysis – with a focus on operations, investing, and financing]
[insert your answer here]
2. How is LGI doing in terms of operating efficiency? How would you assess its
performance compared to its main competitor and the industry index? What are the
principal areas that need to be addressed to strengthen LGI’ s bottom line? Identify and
use key indicators from all 3 analyses that provide insight about LGI’s operations. [HINT:
Focus of this question is the Income Statement and the Net Working Capital (NWC) as it relates
to Current Assets and Current Liabilities]
[insert your answer here]
3. How is LGI doing in terms of using assets efficiently? How would you assess it compared
to its main competitor and the industry index? What are the principal areas that need to be
addressed to strengthen the left-hand side of its balance sheet? Identify and use key
indicators from all 3 analyses that provide insight about LGI’s assets. [HINT: Focus of this
question is the firm’s assets excluding current asset]
[insert your answer here]
4. How is LGI doing in terms of financial leverage? How would you assess it compared to
its main competitor and the industry index? What are the principal areas that need to be
addressed to strengthen the right-hand side of its balance sheet? Identify and use key
indicators from all 3 analyses that provide insight about LGI’s debt and equity mix. [HINT:
Focus of this question is the firm’s liabilities and equity excluding current liabilities]
[insert your answer here]
5. Based on the financial strengths and weaknesses of LGI, how would you prioritize
actions that will ultimately satisfy LGI’ s shareholders? Make specific recommendations
that clearly identify the decisions LGI’ s board and executives need to make. What actions
do they need to take? Set quantifiable targets and objectives for LGI. Your answers must
be supported by all arguments developed in questions 2, 3, and 4. In addition, make sure
you use data not already used in previous questions. [HINT: Be strategic as you will revisit
this question when you reach the last project of this course.]
[insert your answer here]
Version 10 June 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.
Largo Gobal Balance Sheet as of Dece
2020
2019
2018
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 2020
2020
$
2,013
$
1,400
$
613
Estimate
2019
2018
competitor
$ 2,450 $
2,733 $
7,564
$ 1,689 $
1,765 $
3,883
$
761 $
968 $
3,681
$
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
$
$
$
$
$
$
$
$
$
$
Jan 1, 2020
Dec 31, 2020
Sub-total (A)
Average (A/2)
ance Sheet as of December 31 (millions)
2020
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
98,051,400
2019
$
$
$
$
324
103
88
515
2018
$
$
$
$
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
2020
2019
2018
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
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
Sales / Ave Total Assets
Sales / Ave Net Fixed Assets
Tot Debt / Tot Assets
Tot Debt / Tot Equity
Tot Assets / Tot SE
EBIT / Int Exp
EBITDA/ Int Exp
NI/Net Sales
GM/Sales
EBIT / Sales
EBIT/Tot Assets
NI / Tot Assets
NI / SE
NI / Outstanding shares
Price/EPS
NI/Net Sales
Sales / Ave Total Assets
Tot Assets / Tot SE
NI / SE
2020
change
% of Assets
2019
% of Assets
change
2020
% of Sales
change
2019
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
2018
% of Assets
2020
% 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
2018
% of Sales
2019
% of Assets
2019
change
2018
% of Assets
2020
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
2019
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
all grey cells need to be filled
** Dividends:
2020
Jan 1 Retained Earnings
add Net Income
sub-total
less dividends
Dec 31 Retained Earnings
2019

Purchase answer to see full
attachment

  
error: Content is protected !!