Description
This probably seems like you have done most of the work with all these calculations, but your work is just beginning. Now you have to figure out what each tab tells you about the company and how to put it all together for a comprehensive picture. Include graphs in your written analysis.
Today you will meet with your team to discuss the reasons for LGI’s reduced operational efficiency. The reduced efficiency has raised questions about the company’s income statement, balance sheet, and statement of cash flows. Answer the questions in the Project 1 Questions – Report Template document. Prepare your analysis report including recommendations for how the company can improve its financial situation.
To complete the analysis report for the project:
Download the Project 1 Questions – Report Template
Read the instructions.
Answer all the questions.
Include your recommendations.
Submit the business report (Word document) and calculation (Excel file) to the Assignments folder as your final deliverable at the end of Week 2. Label your files as follows:
P1_Final_lastname_Report_date.docx
P1_Final_lastname_Calculation_date.xlsx
Check Your Evaluation Criteria
Before you submit your assignment, review the competencies below, which your instructor will use to evaluate your work. A good practice would be to use each competency as a self-check to confirm you have incorporated all of them. To view the complete grading rubric, click My Tools, select Assignments from the drop-down menu, and then click the project title.
3.2: Employ mathematical or statistical operations and data analysis techniques to arrive at a correct or optimal solution.
3.4: Employ software applications and analytic tools to analyze, visualize, and present data to inform decision-making.
6.2: Evaluate strategic implications for domestic and international markets of an organization’s industry.
10.2: Analyze financial statements to evaluate and optimize organizational performance.
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
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
Average Total Assets 2022
Jan 1, 2022 $
$
$
$
$
$
$
$
$
$
$
3,767 Note: Assets on Jan 1, 2022 = Assets on Dec 31, 2021
Dec 31, 2022 $
4,118
Sub-total (A) $
7,885
Average (A/2)
3,942.50
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
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.41
0.69
0.35
1.92
1.25
0.86
3.47
105.33
10.91
33.45
0.51
1.14
4.24
86.01
14.50
25.18
0.69
1.69
5.06
72.17
17.41
20.97
0.82
2.00
5.37
50.6
18.12
21.5
0.9
2.75
0.25
0.33
1.32
2.23
3.46
0.24
0.31
1.31
3.25
4.84
0.23
0.30
1.30
4.94
7.02
0.21
0.27
1.23
5.5
9.3
5.66%
30.45%
15.60%
7.63%
2.77%
3.81%
8.29%
31.06%
18.16%
11.81%
5.39%
7.47%
11.89%
35.42%
22.61%
18.57%
9.77%
12.68%
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.
5.66%
0.51
1.32
3.81%
8.29%
0.69
1.31
7.47%
11.89%
0.82
1.30
25.36%
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
moment, there is an inconsistency between the numerator and the denominator. For example, the numerator in the invento
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
shortcoming, the denominator needs to be representative of all of the moments during the year. When the inventory amoun
amount on this year’s balance sheet are the only amounts available, it is common to use the average of these two balance sh
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
urnover ratios? In the calculation of a turnover ratio, the numerator is an
ount. Since a balance sheet amount is a snapshot and reflects only an instant or
ample, the numerator in the inventory turnover ratio is the cost of goods sold
moment at the end of the last day of the accounting year. To overcome this
he year. When the inventory amount on last year’s balance sheet and the
the average of these two balance sheet amounts in the denominator. It is also
uity
rrect but is not recommended for the trend analysis for this project.
2022
change
% of Assets
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
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
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%
2022
% of Sales
change
100.00%
0.00%
69.55%
0.61%
30.45%
-0.61%
2021
change
% of Sales
100.00%
0.00%
68.94%
4.36%
31.06%
-4.36%
6.21%
2.21%
4.00%
0.67%
24.24%
-2.82%
27.06%
-5.03%
8.64%
-0.25%
8.90%
-0.58%
15.60%
-2.56%
18.16%
-4.45%
7.00%
8.59%
2.93%
5.66%
1.41%
-3.98%
-1.35%
-2.62%
5.59%
12.57%
4.29%
8.29%
1.02%
-5.47%
-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%
100.00%
2020
% of Sales
100.00%
64.58%
35.42%
3.33%
32.09%
9.48%
22.61%
4.57%
18.04%
6.15%
11.89%
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
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%
11.10%
24.62%
1.04%
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%
2021
change
2020
% of Assets
-0.77%
0.90%
-0.94%
9.38%
1.83%
3.28%
-0.81%
14.48%
1.56%
8.50%
-1.59%
14.51%
-0.57%
1.81%
-0.40%
59.56%
2.94%
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
2021
114
203
174
37
218
12
(7)
(32)
(8)
(6)
272
(24)
(21)
(5)
(49)
334
(642)
0
-642
(382)
0
-382
37
78
220
(23)
(80)
232
42
96
244
(122)
(15)
245
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
** Dividends:
2022
Jan 1 Retained Earnings $
add Net Income $
sub-total $
less dividends $
Dec 31 Retained Earnings $
2021
179
114
293
23
270
$
$
$
$
$
98
203
301
122
179
ch activities. The sum of numbers in grey cells in each category must equal the number in the yellow colour cell.
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]
Purchase answer to see full
attachment
