Financial Statement Analysis Template - NEW
xlsx
keyboard_arrow_up
School
Concordia University *
*We aren’t endorsed by this school
Course
MANAGERIAL
Subject
Finance
Date
Feb 20, 2024
Type
xlsx
Pages
8
Uploaded by dogsuga
Company Name:
Apple
Balance Sheet
Income Statement
Data Needed
2022
2021
2020
2019
Cash
$ 18,546,000 $ 17,305,000 $ 17,773,000 $ 12,204,000 Short-Term Investments
$ 24,658,000 $ 27,699,000 $ 52,927,000 $ 51,713,000 2022 Avg. A/R
2021 Avg. A/R
2020 Avg. A/R
Accounts Receivable
$ 28,184,000 $ 26,278,000 $ 16,120,000 $ 22,992,600 $ 27,231,000 $ 21,199,000 $ 19,556,300 2022 Avg. Inv.
2021 Avg. Inv.
2020 Avg. Inv.
Inventory
$ 4,946,000 $ 6,580,000 $ 4,061,000 $ 4,106,000 Use cell formulas to calculate the averages here.
$ 5,763,000 $ 5,320,500 $ 4,083,500 Current Assets
$ 135,405,000 $ 134,836,000 $ 143,713,000 $ 162,819,000 2022 Avg. Total Assets
2021 Avg. Total Assets
2020 Avg. Total Assets
Use cell formulas to calculate the averages here.
Total Assets
$ 352,755,000 $ 351,002,000 $ 323,888,000 $ 323,888,000 $ 351,878,500 $ 337,445,000 $ 323,888,000 Current Liabilities
$ 153,982,000 $ 125,481,000 $ 105,392,000 $ 105,718,000 Use cell formulas to calculate the averages here.
Total Liabilities
$ 302,083,000 $ 287,912,000 $ 258,549,000 $ 248,028,000 2022 Avg. Total Equity
2021 Avg. Total Equity
2020 Avg. Total Equity
Total Stockholders' Equity
$ 50,672,000 $ 63,090,000 $ 65,339,000 $ 90,488,000 $ 56,881,000 $ 64,214,500 $ 77,913,500 Preferred Dividends
Use cell formulas to calculate the averages here.
Net Sales
$ 394,328,000 $ 365,817,000 $ 274,515,000 $ 260,174,000 Cost of Goods Sold
$ 223,546,000 $ 212,981,000 $ 169,559,000 $ 161,782,000 Operating Income
$ 119,437,000 $ 108,949,000 $ 66,288,000 $ 63,930,000 Interest Expense
$ 2,931,000 $ 2,654,000 $ 2,873,000 $ 3,576,000 Income Taxes
$ 19,300,000 $ 14,527,000 $ 9,680,000 $ 10,481,000 Net Income
$ 99,803,000 $ 94,680,000 $ 57,411,000 $ 55,256,000 Weighted-Average # Common Shares Outstanding
$ 16,215,963 $ 16,701,272 $ 17,352,119 (18,577,000)
Market Price per Share
$ 170.65 Annual Dividend per Share
$ 0.94 14.4808490323528
$ 223,546,000 *Change these year labels to reflect the four years of data provided. The oldest
year should be in column B.
Company Name:
Apple Inc. (AAPL)
upward
downward
fluctuating
steady
Ticker Symbol:
AAPL
Stock Price (52-week range):
$198.23/$124.17 H/L
Ratio Analysis
Ratio Analysis
2019
2020
2021
2022
Trend
Profitability
Gross Profit Percentage
37.8%
38.2%
41.8%
43.3% upward
21.2%
20.9%
25.9%
25.3% fluctuating
Asset Turnover
0.85 1.08 1.12 upward
Return on Assets (ROA)
17.7%
28.1%
28.4% upward
Return on Equity (ROE)
73.7%
147.4%
175.5% upward
Liquidity
Working Capital
$ 57,101,000 $ 38,321,000 $ 9,355,000 $ (18,577,000) fluctuating
Current Ratio
1.5
1.4
1.1
0.9 downward
Quick Ratio
0.8
0.8
0.6
0.5 downward
Accounts Receivable Turnover
14.04
17.26
14.48 fluctuating
Average Collection Period
26
21
25 fluctuating
Inventory Turnover
41.52
40.03
38.79 downward
Days' Sales in Inventory
9
9 9.4096740715558 steady
Long-Term Solvency
Debt-to-Equity Ratio
2.7
4.0
4.6
6.0 upward
Times-Interest-Earned Ratio
17.9
23.1
41.1
40.7 fluctuating
Ratios for Common Stockholders
Earnings per Share
$ (2.97) $ 3.31 $ 0.87 $ 6.15 fluctuating
Price-Earnings Ratio
27.7 Dividend Yield
$ 0.01 Dividend Payout Ratio
15%
Market Price per Share
$ 170.65 Annual Dividend
$ 0.94 Use the income statement and balance sheet information from Yahoo Finance
, to analyze your company. Record the ratios below for each of the four years shown. Your company must have a minimum of four years of data available. If it doesn't, you need to choose a different company. When calculating ratios that require an "average" of something, use the averages calculated on the Balance Sheet worksheet.
*Change the year labels to reflect the four years of data provided. The oldest year of data should be shown in column B
. Do not enter any data in the gray-shaded cells.
Note: You must use Excel formulas for all ratios calculated in this section. Do not simply type your answers into the cells. If you do not use cell formulas, you will not get credit for this section and that will have a significant negative impact on your grade.
Return on Sales
To calculate the
price-earnings ratio,
you need to know the
market price per share
. You can obtain the market price per share from Yahoo Finance. It’s the first number you see when searching your company and it appears directly below the name of the company. This market price can change during the day and from day to day as the stock is actively traded on the market. Use whatever stock price is shown when you retrieve the data. For the purposes of this analysis, you do not need to look at historical market price data.
To calculate the
dividend yield
and the
dividend payout ratio
, you need the
annual dividend
. You can obtain this information from the Historical Data
tab on Yahoo Finance. Change the date at the top of the page to reflect January 1st of your most recent year to December 31st of your most recent year. Next to the date range, choose show Dividends Only. Add up the total dividends paid during the year. Use that for your annual dividend number.
Key Indicator Charts
Insert your key indicator information (taken from the Overview & Ratios worksheet) in the cells below and then
Year
2019
2020
2021
2022
Total Revenue $
$ 260,174,000 $ 274,515,000 $ 365,817,000 $ 394,328,000 Year
2019
2020
2021
2022
Gross Profit Percentage
38%
38%
42%
43%
Year
2019
2020
2021
2022
Return on Sales
21.2%
20.9%
25.9%
25.3%
Year
2019
2020
2021
2022
Working Capital
$ 57,101,000 $ 38,321,000 $ 9,355,000 $ (18,577,000)
Year
2019
2020
2021
2022
Current Ratio
1.54
1.36
1.07
0.88
Year
2019
2020
2021
2022
Quick Ratio
0.82
0.82
0.57
0.46
Year
2019
2020
2021
2022
Debt-to-Equity
2.74
3.96
4.56
5.96
Year
2019
2020
2021
2022
Earnings per Share
$ (2.97) $ 3.31 $ 0.87 $ 6.15 Note: You must use Excel cell references for all data contained in this section. Do not simply type your answers into the cells. Use a cell reference here to refer back to the ratio where you already calculated it on the previous worksheet. If you do not use cell references, you will not get credit for this section and that will have a significant negative impact on your grade.
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
n use that information to create charts showing each indicator's trend over time. Total Revenue
Gross Profit Percentage
Return on Sales
Working Capital
2019
2020
2021
2022
$- $50,000,000 $100,000,000 $150,000,000 $200,000,000 $250,000,000 $300,000,000 $350,000,000 $400,000,000 $450,000,000 'Total Revenue $'
Year
Total Revenue $
2019
2020
2021
2022
0%
5%
10%
15%
20%
25%
30%
35%
40%
45%
50%
'Gross Profit Percentage'
Year
Gross Profit Percentage
2019
2020
2021
2022
0.0%
5.0%
10.0%
15.0%
20.0%
25.0%
30.0%
'Return on Sales'
Year
Return on Sales
2019
2020
2021
2022
$(30,000,000)
$(20,000,000)
$(10,000,000)
$- $10,000,000 $20,000,000 $30,000,000 $40,000,000 $50,000,000 $60,000,000 $70,000,000 'Working Capital'
Year
Working Capital
Current Ratio
Quick Ratio
Debt to Equity Ratio
Earnings per Share
Year
2019
2020
2021
2022
0.00
0.20
0.40
0.60
0.80
1.00
1.20
1.40
1.60
1.80
'Current Ratio'
Year
Current Ratio
2019
2020
2021
2022
0.00
0.10
0.20
0.30
0.40
0.50
0.60
0.70
0.80
0.90
'Quick Ratio'
Year
Quick Ratio
2019
2020
2021
2022
0.00
1.00
2.00
3.00
4.00
5.00
6.00
7.00
'Debt-to-Equity'
Year
Debt-to-Equity
2019
2020
2021
2022
$(4.00)
$(2.00)
$- $2.00 $4.00 $6.00 $8.00 'Earnings per Share'
Year
Earnings per Share
Competitor Analysis
Competitor's Name
Samsung
Which five ratios have you chosen to analyze the competition? Fill out the Indicate the year of comparison.
Ratio Name
*Use cell references to the Overview & Ratios worksheet. Do not type you
**Do not type your answer directly into these cells. Use a cell formula to c
Your Company's Ratio for Most Recent Year
*
Competitor's Ratio for the Matching Year
**
Who's performing better based solely on the this ratio?
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
information below.
Formulas
ur answer directly into the cells.
complete each calculation. Enter the formula information and data where indicated.
My Company
Competitor
Competitor's Data Used to Calculate Ratio
Amounts
Related Documents
Related Questions
How do I calculate:
Sales per day
Accounts receivable new policy
Freed-up cash
arrow_forward
QWC
2016
$000
2017
$000
2018
$000
2019
$000
2020
$000
2021
$000
Current ratio
0.66
0.58
0.96
0.92
1.06
0.83
Quick ratio
0.43
0.37
0.58
0.53
0.49
0.57
Operating cash flow ratio
0.10
0.01
0.01
0.00
0.02
0.07
MMC
Current ratio
2.37
2.23
2.07
1.89
2.74
2.16
Quick ratio
0.93
0.95
0.81
0.80
1.36
1.01
Operating cash flow ratio
1.02
0.72
0.96
0.47
0.32
0.52
Please interpret the ratio for both the companies. Not required to explain the theory just interpret the values for the following:
1) Current Ratio
2) Quick Ratio
3) Operating cash flow ratio
arrow_forward
WHY THE FORECAST INCOME HSVE DEACLINE IN THE 2022 AND 2023 OF APPLE COMPANY
arrow_forward
Calculate the Horizontal (Trend) Analysis and horizontal analysis % change for the year 2020, 2019, 2020
Please use excel to calculate the formula
Thank you!
Toyota Motor Corporation
Consolidated Balance Sheet
2020
2019
2018
Assets
Current Assets
Cash and Cash equivalents
4190518
3574704
3,052,269
Time Deposits
828220
1126352
901244
Marketable securities
678731
1127160
1768360
Trade accounts and notes recievables
2094894
2372734
2219562
less A.F.D.A.
Finance receivables, net
6614171
6647771
6438306
Other recievables
564854
568156
489338
Inventories
2434918
2656396
2539789
Prepaid expenses and other current assets
1236225
805964
833788
Total current assets
18642531
18879237
18,242,656
Noncurrent finance recievables, net
10423858
10281118
9481618
Investments and other assets:
Marketable…
arrow_forward
Balance Sheet for Bearcat Hathaway, 2022
2021
2022
Cash
Accounts.
Receivable
$5,268,485 $10,268,485
Inventory
$529,062 $696,685
Current Assets $8,371,777 $13,279,842
Less
$2,574,230 $2,314,672
Gross Fixed Assets $16,251,665 $20,567,330
Accum.Depreciation
Total Assets
$7,460,897 $10,117,819
Accounts
Payable
Notes.
Payable
Current
Liabilities
Calculate the net working capital in 2022.
Long Termi
Debt
Total
Liabilities,
None of these options are correct
$10,572,740
$5,664,675
$8,854,338
$3,946,273
Total
$17,162,545 $23,729,353 Liabilities
and Equity
Capital
Surplus
Retained.
Earnings
2021
2022
$1,673,992 $2,438,271
Common
Net Fixed Assets $8,790,768 $10,449,511 Stock ($0.50 $1,300,000 $1,600,000
par)
$1,033,110 $1,987,233
$2,707,102 $4,425,504
$9,242,830 $11,468,302
$11,949,932 $15,893,806
$1,148,120 $1,800,969
$2,764,493 $4,434,578
$17,162,545 $23,729,353
arrow_forward
Assume the following sales data for a company:
2023
$966000
2022
871000
2021
696800
If 2021 is the base year, what is the percentage increase in sales from 2021 to 2022?
А. 25%
В. 139%
С. 125%
D. 39%
arrow_forward
Maples group
Comparative Balance Sheet
December 31, 2020 and 2019
2020
2019
Increase/(Decrease
Assets
Cash and cash equivalent
64,990
61,895
?
Accounts receivable
95,100
88,500
?
Inventories
72,500
79,855
?
Fixed Assets, net
?
?
?
Total Assets
442,590
395,800
46,790
Liabilities
Accounts payable
45,000
58,350
?
Accrued liabilities
?
?
?
Long –term notes payable
99,500
128,550
?
Stockholder’ Equity:
Common Stock
143,050
105,110
37,940
Retained earnings
43,540
24,290
19,250
Total liabilities and stockholders’ equity
442, 590
395, 800…
arrow_forward
Pls stepwise with explanation and correct only.
arrow_forward
A7
arrow_forward
sa
arrow_forward
Year ended December 31,
2023
2022
2021
Revenues
$4,578,041
$3,864,324
$3,003,610
Costs and expenses:
Cost of goods sold
$2,227,189
$2,089,089
$2,005,691
Selling and administrative
922,261
836,212
664,061
Interest
29,744
32,966
30,472
Other expenses (income)
1,475
2,141
(43)
Total costs and expenses
$3,180,669
$2,960,408
$2,700,181
Income before income taxes
$1,397,372
$903,916
$303,429
Income taxes
229,500
192,600
174,700
Net income
$1,167,872
$711,316
$128,729
Venus IndustriesConsolidated Balance Sheets (in thousands)
December 31,
ASSETS
2023
2022
Current assets:
Cash and equivalents
$291,284
$260,050
Accounts receivable, less allowance for doubtful accounts of $19,447 and $20,046
826,977
616,064
Inventories
592,986
512,917
Deferred income taxes
26,378
28,355
Prepaid expenses
40,663
32,977
Total current assets
$1,778,288
$1,450,363
Property, plant, and equipment
$571,032
$497,795
Less accumulated depreciation
(193,037)…
arrow_forward
Kindly help me with this question general Accounting
arrow_forward
Feeney, Inc., reported the following sales and net income amounts
(Click on the icon to view the data.)
Show Feeney's trend percentages for sales and net income. Use 2018 as the base year. (Do not enter the % sign if
any of the input fields.)
Sales
Net income
2021
%
%
2020
1%
%
2019
%
28 29
%
2018
%
%
arrow_forward
ccounting
Assume the following sales data for a company:
2026
$734000
2025
655500
2024
570000
If 2024 is the base year, what is the percentage increase in sales from 2024 to 2025?
129%
29%
115%
15%
arrow_forward
Please help me
arrow_forward
wbwne not use ai
arrow_forward
Maple Group Ltd
Comparative Balance Sheet
December 31, 2020 and 2019
2020
2019
Increase/(Decrease)
Assets
Cash and cash equivalent
64,990
61,895
?
Accounts Receivable
95,100
88,500
?
Inventories
72,500
79,855
?
Fixed Assets, net
?
?
?
Total Assets
442,590
395,800
46,790
Liabilities
Accounts payable
45,000
58,350
?
Accrued liabilities
?
?
?
Long-term notes payable
99,500
128,550
?
Stockholders' Equity:
Common Stock
143,050
105,110
37,940
Retained earnings
43,540
24,290
19,250
Total liabilities and stockholders' equity
442,590
395,800…
arrow_forward
Hi expert please give me answer general accounting question
arrow_forward
plzz answer it properly
arrow_forward
Perform horizontal analysis for the two quotas
arrow_forward
SEE MORE QUESTIONS
Recommended textbooks for you
Essentials Of Investments
Finance
ISBN:9781260013924
Author:Bodie, Zvi, Kane, Alex, MARCUS, Alan J.
Publisher:Mcgraw-hill Education,
Foundations Of Finance
Finance
ISBN:9780134897264
Author:KEOWN, Arthur J., Martin, John D., PETTY, J. William
Publisher:Pearson,
Fundamentals of Financial Management (MindTap Cou...
Finance
ISBN:9781337395250
Author:Eugene F. Brigham, Joel F. Houston
Publisher:Cengage Learning
Corporate Finance (The Mcgraw-hill/Irwin Series i...
Finance
ISBN:9780077861759
Author:Stephen A. Ross Franco Modigliani Professor of Financial Economics Professor, Randolph W Westerfield Robert R. Dockson Deans Chair in Bus. Admin., Jeffrey Jaffe, Bradford D Jordan Professor
Publisher:McGraw-Hill Education
Related Questions
- How do I calculate: Sales per day Accounts receivable new policy Freed-up casharrow_forwardQWC 2016 $000 2017 $000 2018 $000 2019 $000 2020 $000 2021 $000 Current ratio 0.66 0.58 0.96 0.92 1.06 0.83 Quick ratio 0.43 0.37 0.58 0.53 0.49 0.57 Operating cash flow ratio 0.10 0.01 0.01 0.00 0.02 0.07 MMC Current ratio 2.37 2.23 2.07 1.89 2.74 2.16 Quick ratio 0.93 0.95 0.81 0.80 1.36 1.01 Operating cash flow ratio 1.02 0.72 0.96 0.47 0.32 0.52 Please interpret the ratio for both the companies. Not required to explain the theory just interpret the values for the following: 1) Current Ratio 2) Quick Ratio 3) Operating cash flow ratioarrow_forwardWHY THE FORECAST INCOME HSVE DEACLINE IN THE 2022 AND 2023 OF APPLE COMPANYarrow_forward
- Calculate the Horizontal (Trend) Analysis and horizontal analysis % change for the year 2020, 2019, 2020 Please use excel to calculate the formula Thank you! Toyota Motor Corporation Consolidated Balance Sheet 2020 2019 2018 Assets Current Assets Cash and Cash equivalents 4190518 3574704 3,052,269 Time Deposits 828220 1126352 901244 Marketable securities 678731 1127160 1768360 Trade accounts and notes recievables 2094894 2372734 2219562 less A.F.D.A. Finance receivables, net 6614171 6647771 6438306 Other recievables 564854 568156 489338 Inventories 2434918 2656396 2539789 Prepaid expenses and other current assets 1236225 805964 833788 Total current assets 18642531 18879237 18,242,656 Noncurrent finance recievables, net 10423858 10281118 9481618 Investments and other assets: Marketable…arrow_forwardBalance Sheet for Bearcat Hathaway, 2022 2021 2022 Cash Accounts. Receivable $5,268,485 $10,268,485 Inventory $529,062 $696,685 Current Assets $8,371,777 $13,279,842 Less $2,574,230 $2,314,672 Gross Fixed Assets $16,251,665 $20,567,330 Accum.Depreciation Total Assets $7,460,897 $10,117,819 Accounts Payable Notes. Payable Current Liabilities Calculate the net working capital in 2022. Long Termi Debt Total Liabilities, None of these options are correct $10,572,740 $5,664,675 $8,854,338 $3,946,273 Total $17,162,545 $23,729,353 Liabilities and Equity Capital Surplus Retained. Earnings 2021 2022 $1,673,992 $2,438,271 Common Net Fixed Assets $8,790,768 $10,449,511 Stock ($0.50 $1,300,000 $1,600,000 par) $1,033,110 $1,987,233 $2,707,102 $4,425,504 $9,242,830 $11,468,302 $11,949,932 $15,893,806 $1,148,120 $1,800,969 $2,764,493 $4,434,578 $17,162,545 $23,729,353arrow_forwardAssume the following sales data for a company: 2023 $966000 2022 871000 2021 696800 If 2021 is the base year, what is the percentage increase in sales from 2021 to 2022? А. 25% В. 139% С. 125% D. 39%arrow_forward
- Maples group Comparative Balance Sheet December 31, 2020 and 2019 2020 2019 Increase/(Decrease Assets Cash and cash equivalent 64,990 61,895 ? Accounts receivable 95,100 88,500 ? Inventories 72,500 79,855 ? Fixed Assets, net ? ? ? Total Assets 442,590 395,800 46,790 Liabilities Accounts payable 45,000 58,350 ? Accrued liabilities ? ? ? Long –term notes payable 99,500 128,550 ? Stockholder’ Equity: Common Stock 143,050 105,110 37,940 Retained earnings 43,540 24,290 19,250 Total liabilities and stockholders’ equity 442, 590 395, 800…arrow_forwardPls stepwise with explanation and correct only.arrow_forwardA7arrow_forward
- saarrow_forwardYear ended December 31, 2023 2022 2021 Revenues $4,578,041 $3,864,324 $3,003,610 Costs and expenses: Cost of goods sold $2,227,189 $2,089,089 $2,005,691 Selling and administrative 922,261 836,212 664,061 Interest 29,744 32,966 30,472 Other expenses (income) 1,475 2,141 (43) Total costs and expenses $3,180,669 $2,960,408 $2,700,181 Income before income taxes $1,397,372 $903,916 $303,429 Income taxes 229,500 192,600 174,700 Net income $1,167,872 $711,316 $128,729 Venus IndustriesConsolidated Balance Sheets (in thousands) December 31, ASSETS 2023 2022 Current assets: Cash and equivalents $291,284 $260,050 Accounts receivable, less allowance for doubtful accounts of $19,447 and $20,046 826,977 616,064 Inventories 592,986 512,917 Deferred income taxes 26,378 28,355 Prepaid expenses 40,663 32,977 Total current assets $1,778,288 $1,450,363 Property, plant, and equipment $571,032 $497,795 Less accumulated depreciation (193,037)…arrow_forwardKindly help me with this question general Accountingarrow_forward
arrow_back_ios
SEE MORE QUESTIONS
arrow_forward_ios
Recommended textbooks for you
- Essentials Of InvestmentsFinanceISBN:9781260013924Author:Bodie, Zvi, Kane, Alex, MARCUS, Alan J.Publisher:Mcgraw-hill Education,
- Foundations Of FinanceFinanceISBN:9780134897264Author:KEOWN, Arthur J., Martin, John D., PETTY, J. WilliamPublisher:Pearson,Fundamentals of Financial Management (MindTap Cou...FinanceISBN:9781337395250Author:Eugene F. Brigham, Joel F. HoustonPublisher:Cengage LearningCorporate Finance (The Mcgraw-hill/Irwin Series i...FinanceISBN:9780077861759Author:Stephen A. Ross Franco Modigliani Professor of Financial Economics Professor, Randolph W Westerfield Robert R. Dockson Deans Chair in Bus. Admin., Jeffrey Jaffe, Bradford D Jordan ProfessorPublisher:McGraw-Hill Education
Essentials Of Investments
Finance
ISBN:9781260013924
Author:Bodie, Zvi, Kane, Alex, MARCUS, Alan J.
Publisher:Mcgraw-hill Education,
Foundations Of Finance
Finance
ISBN:9780134897264
Author:KEOWN, Arthur J., Martin, John D., PETTY, J. William
Publisher:Pearson,
Fundamentals of Financial Management (MindTap Cou...
Finance
ISBN:9781337395250
Author:Eugene F. Brigham, Joel F. Houston
Publisher:Cengage Learning
Corporate Finance (The Mcgraw-hill/Irwin Series i...
Finance
ISBN:9780077861759
Author:Stephen A. Ross Franco Modigliani Professor of Financial Economics Professor, Randolph W Westerfield Robert R. Dockson Deans Chair in Bus. Admin., Jeffrey Jaffe, Bradford D Jordan Professor
Publisher:McGraw-Hill Education