Excel Project: Projecting Financial Statements Assignment

profilepacova223
acct370pt2.xlsx
Home>Business & Finance homework help>Accounting homework help>Excel Project: Projecting Financial Statements Assignment

Company Information

ACCT 370 Excel Project
Apple Inc.
One Apple Park Way Cupertino, CA 95014 United States
Jessica Lynch
Company Name Apple Inc
Ticker Symbol AAPL
Industry Technology
Products Consumer Electronics
Major Competitors Samsung, Sony, Microsoft

Historical Income Statements.

Apple Inc.
Historical Income Statement
As of September 25, 2021, September 26, 2020, September 28, 2019
$'s In thousands
9/25/21 9/26/20 9/28/19
Net sales:
   Products $ 297,392.00 $ 220,747.00 $ 213,883.00
   Services $ 68,425.00 $ 53,768.00 $ 46,291.00
Total net sales $ 365,817.00 $ 274,515.00 $ 260,174.00
Cost of sales:
   Products $ 192,266.00 $ 151,286.00 $ 144,996.00
   Services $ 20,715.00 $ 18,273.00 $ 16,786.00
Total cost of sales $ 212,981.00 $ 169,559.00 $ 161,782.00
Gross margin $ 152,836.00 $ 104,956.00 $ 98,392.00
Operating expenses:
Research and development $ 21,914.00 $ 18,752.00 $ 16,217.00
Selling, general and administrative $ 21,973.00 $ 19,916.00 $ 18,245.00
Total operating expenses $ 43,887.00 $ 38,668.00 $ 34,462.00
Operating income $ 108,949.00 $ 66,288.00 $ 63,930.00
Other income/(expense), net $ 258.00 $ 803.00 $ 1,807.00
Income before provision for income taxes $ 109,207.00 $ 67,091.00 $ 65,737.00
Provision for income taxes $ 14,527.00 $ 9,680.00 $ 10,481.00
Net income $ 94,680.00 $ 57,411.00 $ 55,256.00
Earnings per share:
Basic $ 5.67 $ 3.31 $ 2.99
Diluted $ 5.61 $ 3.28 $ 2.97
Shares used in computing earnings per share:
Basic 16,701,272  17,352,119  18,471,336 
Diluted 16,864,919  17,528,214  18,595,651 

Historical Balance Sheet

Apple Inc.
Historical Balance Sheet
As of September 25, 2021, September 26, 2020, September 28, 2019
$'s in millions, execpt number of shares which are reflected in thousands and par value
9/25/21 9/26/20 9/28/19
Current Assets
Cash and cash equivalents $ 34,940.00 $ 38,016.00 $ 48,844.00
Marketable securities $ 27,699.00 $ 52,927.00 $ 51,713.00
Accounts receivable, net $ 26,278.00 $ 16,120.00 $ 22,926.00
Inventories $ 6,580.00 $ 4,061.00 $ 4,106.00
Vendor non-trade receivables $ 25,228.00 $ 21,325.00 $ 22,878.00
Other current assets $ 14,111.00 $ 11,325.00 $ 12,352.00
Total current assets $ 134,836.00 $ 143,774.00 $ 162,819.00
Non-current assets:
Marketable securities $ 127,877.00 $ 100,887.00 $ 105,341.00
Property, plant and equipment, net $ 39,440.00 $ 36,766.00 $ 37,378.00
Other non-current assets $ 48,849.00 $ 42,522.00 $ 32,978.00
Total non-current assets $ 216,166.00 $ 180,175.00 $ 175,697.00
Total assets $ 351,002.00 $ 323,949.00 $ 338,516.00
LIABILITIES AND SHAREHOLDERS’ EQUITY:
Current liabilities:
Accounts payable $ 54,763.00 $ 42,296.00 $ 46,236.00
Other current liabilities $ 47,493.00 $ 42,684.00 $ 37,720.00
Deferred revenue $ 7,612.00 $ 6,643.00 $ 5,522.00
Commercial paper $ 6,000.00 $ 4,996.00 $ 5,980.00
Term debt $ 9,613.00 $ 8,773.00 $ 10,260.00
Total current liabilities $ 125,481.00 $ 105,392.00 $ 105,718.00
Non-current liabilities:
Term debt $ 109,106.00 $ 98,667.00 $ 91,807.00
Other non-current liabilities $ 53,325.00 $ 54,490.00 $ 50,503.00
Total non-current liabilities $ 162,431.00 $ 153,157.00 $ 142,310.00
Total liabilities $ 287,912.00 $ 258,549.00 $ 248,028.00
Commitments and contingencies
Shareholders’ equity:
Common stock and additional paid-in capital, $0.00001 par value: 50,400,000 shares authorized; 16,426,786 and 16,976,763 shares issued and outstanding, respectively $ 57,365.00 $ 50,779.00 $ 45,174.00
Retained earnings $ 5,562.00 $ 14,966.00 $ 45,898.00
Accumulated other comprehensive income/(loss) $ 163.00 $ (406.00) $ (584.00)
Total shareholders’ equity $ 63,090.00 $ 65,339.00 $ 90,488.00
Total liabilities and shareholders’ equity $ 351,002.00 $ 323,888.00 $ 338,516.00

Historical Statements of Cash F

Apple Inc.
Historical Statement Of Cash Flow
As of September 25, 2021, September 26, 2020, September 28, 2019
$'s in millions
9/25/21 9/26/20 9/28/19
Cash, cash equivalents and restricted cash, beginning balances $ 39,789.00 $ 50,224.00 $ 25,913.00
Operating activities:
Net income $ 94,680.00 $ 57,411.00 $ 55,256.00
Adjustments to reconcile net income to cash generated by operating activities:
Depreciation and amortization $ 11,284.00 $ 11,056.00 $ 12,547.00
Share-based compensation expense $ 7,906.00 $ 6,829.00 $ 6,068.00
Deferred income tax benefit $ (4,774.00) $ (215.00) $ (340.00)
Other $ (147.00) $ (97.00) $ (652.00)
Changes in operating assets and liabilities:
Accounts receivable, net $ 10,125.00 $ 6,917.00 $ 245.00
Inventories $ 2,642.00 $ (127.00) $ (289.00)
Vendor non-trade receivables $ (3,903.00) $ 1,553.00 $ 2,931.00
Other current and non-current assets $ (8,042.00) $ (9,588.00) $ 873.00
Accounts payable $ 12,326.00 $ (4,062.00) $ (1,923.00)
Deferred revenue $ 1,676.00 $ 2,081.00 $ (625.00)
Other current and non-current liabilities $ 5,799.00 $ 8,916.00 $ (4,700.00)
Cash generated by operating activities $ 104,038.00 $ 80,674.00 $ 69,391.00
Investing activities:
Purchases of marketable securities $ (109,558.00) $ (114,938.00) $ (39,630.00)
Proceeds from maturities of marketable securities $ 59,023.00 $ 69,918.00 $ 40,102.00
Proceeds from sales of marketable securities $ 47,460.00 $ 50,473.00 $ 56,988.00
Payments for acquisition of property, plant and equipment $ (11,085.00) $ (7,309.00) $ (10,495.00)
Payments made in connection with business acquisitions, net $ (33.00) $ (1,524.00) $ (624.00)
Purchases of non-marketable securities $ (131.00) $ (210.00) $ (1,001.00)
Proceeds from non-marketable securities $ 387.00 $ 92.00 $ 1,634.00
Other $ (608.00) $ (791.00) $ (1,078.00)
Cash generated by/(used in) investing activities $ (14,545.00) $ (4,289.00) $ 45,896.00
Financing activities:
Proceeds from issuance of common stock $ 1,105.00 $ 880.00 $ 781.00
Payments for taxes related to net share settlement of equity awards $ (6,556.00) $ (3,634.00) $ (2,817.00)
Payments for dividends and dividend equivalents $ (14,467.00) $ (14,081.00) $ (14,119.00)
Repurchases of common stock $ (85,971.00) $ (72,358.00) $ (66,897.00)
Proceeds from issuance of term debt, net $ 20,393.00 $ 16,091.00 $ 6,963.00
Repayments of term debt $ (8,750.00) $ (12,629.00) $ (8,805.00)
Proceeds from/(Repayments of) commercial paper, net $ 1,022.00 $ (963.00) $ (5,977.00)
Other $ (129.00) $ (126.00) $ (105.00)
Cash used in financing activities $ (93,353.00) $ (86,820.00) $ (90,976.00)
Increase/(Decrease) in cash, cash equivalents and restricted cash $ (3,860.00) $ (10,435.00) $ (24,311.00)
Cash, cash equivalents and restricted cash, ending balances $ 35,929.00 $ 39,789.00 $ 50,224.00
Supplemental cash flow disclosure:
Cash paid for income taxes, net $ 25,385.00 $ 9,501.00 $ 15,263.00
Cash paid for interest $ 2,687.00 $ 3,002.00 $ 3,423.00

Samsung Income Statement

Samsung Electronics Co., Ltd
Historical Income Statement
2020, 2019, 2018
In millions of Korean won, in thousands of US dollars
2020 2019 2018
Rvenue 200,606,179 195,179,376 209,163,262
Cost of Sales 122,400,294 124,730,961 113,598,417
Gross Profit 78,205,885 70,448,415 95,564,845
Selling and administrative expenses 47,714,412 46,924,893 45,038,298
Operating Profit 30,491,473 23,523,522 50,526,547
Other non-operating income 1,172,485 1,506,760 1,274,207
Other non-operating Expenses 2,108,422 1,198,440 979,886
Share of net profit of associates and joint ventures 429,096 349,831 463,203
Financial income 10,392,246 8,608,218 8,579,720
Financial expenses 9,587,858 7,100,090 7,386,694
Profit before income tax 30,789,020 26,111,740 52,477,097
Income tax expense 8,418,167 7,459,135 14,427,866
Profit for the year 22,370,853 18,415,633 38,049,231
Profit attributable to - - -
Owners of the company 22,102,325 18,217,565 37,659,703
Non-controlling interests 268,528 198,068 389,528
Earnings per share - - -
Basic 3.25 2.68 5.54
Diluted 3.25 2.68 5.54
Profit for the year 22,370,853 18,415,633 38,049,231
Other comprehensive income (loss) - - -
Items that will not be reclassified subsequently to profit or loss - - -
Gain on valuation of financial assets at fair value 2,120,139 971,318 -202,380
Share of other comprehensice income (loss) of associates and joint ventures 4,736 -14,313 -9,122
Remeasurement of net defined benefit assets, net of tax -609,560 -1,000,009 -351,922
Items that may be reclassified subsequently to profit or loss - - -
Share of other comprehensice income (loss) of associates and joint ventures -41,414 41,212 5,739
Gain (Loss) on overseas business translation net of tax -4,557,874 2,555,365 506,786
Gain (loss) on valuation of cash flow hedge derivitives -28,300 1,534 40,395
Other comprehensive income (loss) for the year, net of tax -3,112,273 2,555,107 -10,504
Total comprehensive income for the year 19,258,580 20,970,740 38,038,727
Comprehensive income attributable to - - -
Owners of the company 18,954,012 20,726,704 37,652,492
Non-controlling interests 304,568 244,036 386,235

Samsung Balance Sheet

Samsung Electronics Co., Ltd
Balance Sheet
2020, 2019, 2018
In millions of Korean won, in thousands of US dollars
2020 2019 2018 2018
Cash and short Term Investments 124,652,843 108,779,703 100,939,943
Cash Only 29,382,578 26,885,999 30,340,505
Short-Term Investments 95,270,265 81,893,704 70,599,438
Cash and short Term Investments Growth 14.59% 7.77% 21.35%
Cash and short Term Investments Total Assets 32.96% 30.85% 29.74%
Total Accounts Receivable 34,569,597 39,310,463 36,948,466
Accounts Receivable, Net 30,965,058 35,131,343 33,867,733
Accounts Receivable, Gross 31,343,276 35,530,419 34,470,342
Bad Debt/ Doubtful Accounts -378,218 -399,076 -602,609
Other Receivables 3,604,539 4,179,120 3,080,733
Accounts Receivable, Gross -12.06% 6.39% 16.17%
Accounts Receivable Turnover 6.85% 5.86% 6.60%
Inventories 32,043,145 26,766,464 28,984,704
Finished Goods 10,434,289 9,132,720 9,870,054
Work in Progress 11,818,090 9,886,634 11,066,511
Raw Materials 9,790,766 7,747,110 8,048,139
Other Current Assets 9,789,024 9,645,317 10,594,527
Prepaid Expenses 2,266,100 3,833,053 5,497,974
Miscellaneous Current Assets 7,522,924 5,821,264 5,096,553
Total Current Assets 201,054,609 184,510,947 177,416,724
Net Property, Plant and Equipment 128,952,892 119,825,474 115,416,724
Poperty, Plant and Equipment - Gross 328,606,313 304,075,769 279,158,166
Buildings 55,026,369 48,839,439 45,033,843
Land and Improvements 9,850,942 9,828,309 9,346,285
Machinery and Equipment 233,056,501 211,416,021 206,407,913
Construction in Progress 20,175,917 23,930,019 9,705,056
Other Property, Plant and Equipment 10,496,584 10,061,981 8,665,069
Accumilated Depreciation 199,653,421 184,250,295 163,741,442
Buildings 20,474,365 18,369,819 15,687,933
Land and Improvements 78,786 53,755 -
Machinery and Equipment 172,062,371 159,266,085 142,591,682
Other Property, Plant and Equipment 7,037,899 6,560,636 5,461,827
Total Investments and Advances 21,854,964 17,561,328 15,628,293
LT Investment - Affiliate Companies 8,076,779 7,591,612 7,313,206
Other Long-Term Investments 13,778,185 9,969,716 8,315,087
Intangible Assets 18,468,502 20,703,504 14,891,598
Net Goodwill 5,673,642 6,250,439 5,833,678
Net Other Intangibles 12,794,860 14,453,065 9,057,920
Other Assets 6,468,781 8,583,882 13,255,203
Deferred Charges 1,355,503 589,882 5,572,035
Tangible Other Assets 5,113,279 7,994,050 7,683,168
Total Assets 378,235,718 352,564,497 339,357,244
ST Debt and Current Portion Lt Debt 17,269,528 15,239,558 13,620,046
Short Term Debt 16,553,429 14,393,468 13,586,660
Current Portion of Long Term Debt 716,099 846,090 33,386
Accounts Payable 9,739,222 8,718,222 8,479,916
Accounts Payable Growth 11.71% 2.81% -6.65%
Income Tax Payable 4,430,272 1,387,773 8,720,050
Other Current Liabilities 44,165,329 38,437,211 38,261,498
Miscellaneous Current Liabilites 44,165,329 38,437,211 38,261,498
Total Current Liabilities 75,604,351 63,782,764 69,081,510
Long-Term Debt 2,947,853 3,172,479 1,047,057
Long-Term Debt EXL Capitablized Leases 948,137 975,298 996,935
Non-Convertible Debts 948,137 975,298 996,935
Deferred Taxes 17,374,875 15,674,808 12,464,737
Deferred Taxes - Credit 18,810,845 17,053,808 15,162,523
Deffered Taxes Debit 1,435,970 1,379,362 2,697,786
Other Liabilities 3,408,767 4,593,145 5,145,294
Other Liablities EXCL Deffered Income 3,408,767 4,593,145 5,145,294
Total Liabilities 102,287,702 89,684,076 91,604,067
Total Liabilities / Total Assets 27.04% 25.44% 26,99%
Common Equity 267,670,331 254,915,472 240,068,993
Common Stock Par/ Carry Value 897,514 897,514 897,514
Additional Paid-in Capital 4,403,893 4,403,893 4,403,893
Retained Earnings 96,328,646 86,260,026 91,179,305
Cumulative Translation -10,989,715 -5,645,769 -8,612,742
Unrealized Gain/Losss Marketable Securities 4,975,756 2,573,530 1,462,266
Other Appropriated Reserves -2,712,054 -1,957,019 -840,775
Unappropriated Reserves 174,766,291 168,383,297 151,579,532
Common Equity/ Total Assets 70.77% 72.30% 70.74%
Total Shareholders Equity 267,670,331 254,915,472 240,068,993
Acccumulated Minority Interest 8,277,685 7,964,949 7,684,184
Total Equity 275,948,016 262,880,421 247,753,177
Liabilities and Shareholders Equity 378,235,718 352,564,497 339,357,244

Samsung Cash Flow

Samsung Electronics Co., Ltd
Cash Flow
2020, 2019, 2018
In millions of Korean won, in thousands of US dollars
2020 2019 2018
Operating activities
Profit for the year 22,370,853 18,415,633 38,049,231
Adjustments 35,256,304 31,718,800 37,414,045
Changes in assets and liabilities arising
from operating activities 103,708 -2,156,585 -8,515,406
Cash generated from operations 57,730,865 47,977,848 66,947,870
Interest received 1,880,804 1,953,820 1,534,604
Interest Paid -470,429 -491,317 -470,434
Dividends Received 206,417 204,837 185,328
Income Tax Paid -4,041,108 -11,199,983 -10,681,998
Net cash from operating activities 55,306,549 38,445,205 57,515,370
Net increase in short-term financial instruments -17,255,702 -1,720,446 -10,612,375
Net decrease (increase) in short-term financial assets
at amortized cost 155,960 -693,027 -1,232,856
Net decrease in short-term financial assets at fair value
through profit or loss 1,443,942 317,658 -119,839
Disposal of long-term financial instruments 10,321,680 3,885,453 219,527
Acquisition of long-term financial instruments -6,793,354 -10,780,116 -6,588,518
Disposal of financial assets at amortized cost 866,713 588,403 -
Acquisition of financial assets at amortized cost - -698.905 -136,183
Disposal of financial assets at fair value through other comprehensive income 27,217 1,334 13,910
Acquisition of financial assets at fair value through other comprehensive income -207,968 -54,024 -391,377
Disposal of financial assets at fair value through profit or loss Acquisition of financial assets at fair value 33,670 54,488 68,761
through profit or loss -71,315 -115,062 -166,327
Disposal of investment in associates and joint ventures - 10.292 127
Acquisition of Investment in associates and joint venture -70,549 -10,825 -43,953
Disposal of Property, plant and equipment 319,151 434,802 477,900
Acquisition of property, plant and equipment -31,845,320 -21,489,774 -25,360,292
Disposal of intangible assets 5,953 6,134 10,241
Acquisition of intangible assets -2,270,120 -2,753,098 -875,635
Cash outflow from business combinations -41,865 -863,568 -85,038
Cash inflow (outflow) from other investing activities -48,453 39,008 -1,965
Net cash used in investing activities -45,430,360 -33,153,349 -44,823,892

Sony Income Statement

Sony Group Corp
Income Statements
2020, 2019, 2018
In thousands of US dollars
2020 2019 2018
Total Revenue 8,259,885,000 8,665,687,000 8,543,982,000
Operating Revenue 8,155,937,000 8,580,943,000 8,452,848,000
Cost of Revenue 5,925,049,000 6,263,196,000 6,230,422,000
Gross Profit 2,334,836,000 2,402,491,000 2,313,560,000
Operating Expenses 1,502,625,000 1,576,825,000 1,583,197,000
Selling General and Administrative 1,502,625,000 1,576,825,000 1,583,197,000
Other Operating Expenses -3,611,000 0 0
Operating Income 832,211,000 825,666,000 730,363,000
Net Non Operating Interest Income 8,188,000 9,151,000 6,218,000
Interest Income Non Operating 19,278,000 21,618,000 19,784,000
Intertest Expense Non Operating 11,090,000 12,467,000 13,566,000
Other Income Expense -40,949,000 176,831,000 -37,532,000
Pre Tax Income 799,450,000 1,011,648,000 699,049,000
Tax Provision 177,190,000 45,098,000 151,770,000
Net Income Common Stockholder 582,191,000 916,271,000 490,794,000
Diluted NI Available to Com Stockholders 582,191,000 916,271,000 490,794,000
Basic EPS 471.64 723.41 388.32
Diluted EPS 461.23 707.74 379.75
Basoc Average Shares 1,234,408 1,266,592 1,263,895
Diluted Average Shares 1,262,255 1,294,646 1,292,420
Total Operating Income as Reported 845,459,000 894,235,000 734,860,000
Total Expenses 7,427,674,000 7,840,021,000 7,813,619,000
Net Income from Continuing & Discontinued Operation 582,191,000 916,271,000 490,794,000
Normalized Income 622,033,158 745,358,470 520,660,752
Interest Income 19,278,000 21,618,000 19,784,000
Interest Expense 11,090,000 12,467,000 13,566,000
Net Interest Income 8,188,000 9,151,000 6,218,000
EBIT 810,540,000 1,024,115,000 712,615,000
EBITDA 0 0 0
Reconciled Cost of Revenue 5,925,049,000 6,263,196,000 6,230,422,000
Reconciled Depreciation 746,451,000 722,519,000 720,718,000
Net Income from Continuing Operation Net Minority Interest 582,191,000 916,271,000 490,794,000
Total Unusual Items Excluding Goodwill -51,211,000 178,966,000 -38,144,000
Total Unusal Items -51,211,000 178,966,000 -38,144,000
Normalized EBTIDA 1,608,202,000 1,567,668,000 1,471,477,000
Tax Rate for Calcs 0 0 0
Tax Effect of Unusual Items -11,368,842 8,053,470 -8,277,248

Sony Balance Sheet

Sony Group Corp (SONY)
Balance Sheet
2020, 2019, 2018
in thousands of US dollars
Total Assets 23,039,343,000 20,981,586,000 19,065,538,000
Total Liabilities Net Minority Interest 18,249,808,000 16,544,896,000 15,418,381,000
Total Equity Gross Minority Interest 4,789,535,000 4,436,690,000 3,647,157,000
Total Capitalization 4,760,272,000 4,314,749,000 3,590,817,000
Common Stock Equity 4,125,306,000 3,746,377,000 2,967,366,000
Capital Lease Obligations 383,778,000 0 0
Net Tangible Assets 2,435,108,000 2,059,859,000 1,909,706,000
Working Capital -505,298,000 -833,203,000 -444,445,000
Invested Capital 5,600,255,000 5,105,828,000 4,312,432,000
Tangible Book Value 2,435,108,000 2,059,859,000 1,909,706,000
Total Debt 1,858,727,000 1,359,451,000 1,345,066,000
Net Debt 0 0 0
Share Issued 1,261,059 1,271,230 1,266,552
Ordinary Shares Number 1,220,160 1,250,747 1,265,425
Treasury Shares Number 40,899 20,483 1,127

Sony Cash Flow

Sony Group Corp (SONY)
Cash Flow
2020, 2019, 2018
in thousands of US dollars
2020 2019 2018
Operating Cash Flow 1,349,745,000 1,258,738,000 1,254,972,000
Investing Cash Flow -1,352,278,000 -1,307,445,000 -822,197,000
Financing Cash Flow 65,658,000 -122,884,000 246,456,000
End Cash Position 1,512,357,000 1,470,073,000 1,586,329,000
Income Tax Paid Supplemental Data 216,922,000 210,499,000 101,092,000
Interest Paid Supplemental Data 10,000,000 10,882,000 12,169,000
Capital Expenditure -439,761,000 -312,644,000 -262,989,000
Issuance of Capital Stock - - -
Issuance of Debt 118,447,000 94,351,000 125,092,000
Repayment of Debt -198,055,000 -382,671,000 -44,561,000
Repurchase of Capital Stock -200,211,000 -100,177,000 -
Free Cash Flow 909,984,000 946,094,000 991,983,000

Historical And Competitor Ratio

Common sized balance sheet

Apple Inc.
Common Sized Balance Sheets
As of September 25, 2021, September 26, 2020, September 28, 2019
$'s In thousands
Assets
Current Assets
Cash and cash equivalents 11.7
Accounts and notes receivable 5
Inventories 1.3
Prepaid expenses and other current assets
Defered Income tax benefit
Advertising funds restricted assets
Total current assets
Properties
Goodwill
Other intangible assets
Investments
Other assets
Total Assets

common sized income statements

Horizontal analysis balance she

Horizontal analysis income stat

Porjected income statements

Projected balance sheets

Projected Statement of cash flo