Excel Project: Projecting Financial Statements Assignment

/in /by

Ace your studies with our custom writing services! We've got your back for top grades and timely submissions, so you can say goodbye to the stress. Trust us to get you there!


Order a Similar Paper Order a Different Paper

I need Common Sized Balance Sheets (Tabs 12), Common Sized Income Statements (Tab 13), Horizontal Analysis Balance Sheets (Tab 14), Horizontal Analysis Income Statements (Tab 15), and Projected Financial Statements (Tabs 16-18). attached is the instruction sheet and the previous completed pages. 

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

Excel Project: Projecting Financial Statements Assignment Instructions

Overview

Common Sized Balance Sheets (Tabs 12), Common Sized Income Statements (Tab 13), Horizontal Analysis Balance Sheets (Tab 14), Horizontal Analysis Income Statements (Tab 15), and Projected Financial Statements (Tabs 16-18)

Instructions

Continue in the same Excel workbook prepared in Module 4: Week 4, adding the following tabs:

· The
12th tab
 in your Financial Statement Analysis Template must be labeled Common Sized Balance Sheets.

· Set-up like the Historical Balance Sheets (Tab 3), this worksheet should use formulas to calculate the common sized percentages, linking to the Historical Balance Sheets for the calculations. “Total Assets” should be used as the base for all common sized calculations on the Balance Sheets. Common sized percentages should be calculated for all three historical years presented.

· See Exhibit A for a partial example of this tab.

· The
13th tab
 in your Financial Statement Analysis Template must be labeled Common Sized Income Statements.

· Set-up like the Historical Income Statements (Tab 2), this worksheet should use formulas to calculate the common sized percentages, linking to the Historical Income Statements for the calculations. “Revenue” or “Total Revenue” (depending on your company) should be used as the base for all common sized calculations on the Income Statements. Common sized percentages should be calculated for all three historical years presented.

· The
14th tab
 in your Financial Statement Analysis Template must be labeled Horizontal Analysis of Balance Sheets.

· Start by copying and pasting the Historical Balance Sheets from Tab 3 to this worksheet. Then, add dollar and percentage change columns between each of the set of historical years.

· See Exhibit B for an example of this tab.

· The
15th tab
 in your Financial Statement Analysis Template must be labeled Horizontal Analysis of Income Statements.

· Start by copying and pasting the Historical Income Statements from Tab 2 to this worksheet. Then, add dollar and percentage change columns between each of the set of historical years.

· The
16th tab
 in your Financial Statement Analysis Template must be labeled Projected Income Statements. Three years of projected statements should be included, starting with the most recent year after the company’s last issued report.

· The
17th tab
 in your Financial Statement Analysis Template must be labeled Projected Balance Sheets. Three years of projected statements should be included. Three years of projected statements should be included, starting with the most recent year after the company’s last issued report.

· The
18th tab
 in your Financial Statement Analysis Template must be labeled Projected Statement of Cash Flows. Three years of projected statements should be included, starting with the most recent year after the company’s last issued report.

· Other information relating to projected tabs:

· Projections should be based on research performed on your company and its industry. Use historical, trend, ratio data and current and future economic projections as well as information from the company’s MD&A to aid in preparing projections.

· Clearly state your assumptions in a text box at the bottom of each tab. Cite sources for assumptions based on research.

· The statements should be consistent, reasonable, and accurate. Areas that will be checked for accuracy include:

· The Balance Sheets should balance for all years presented.

· Retained earnings should be properly calculated using the prior year’s balance, net income, and dividends.

· The Ending Cash Balance on the Statement of Cash Flows should agree to the Cash Balance shown on the Balance Sheet for the same year.

· Net Income on the Income Statements should match what is reported on the top of the Statement of Cash Flows (if indirect method is used).

· Other instructions:

· The Excel workbook should be professionally formatted. Professional formatting includes:

· All numbers are formatted similarly. The use of decimals, commas for thousands, and other formatting is consistent.

· Dollar signs are included in the first and last numbers of each column.

· Subtotals and totals are preceded by a single underline. Totals are followed by a double underline.

· Line descriptions are indented/bolded in a manner that makes the spreadsheet easy to read.

· All common sized, horizontal analysis, and projection items should be calculated using formulas. Totals and subtotals should also be calculated using formulas.  For an example of which cells may need formulas, please see Exhibits A & B.

ACCT 370

Page 1 of 1

Exhibit A: Example Common Sized Balance Sheets (Tab 12, Partial View)


(IMPORTANT NOTE: Only Partial Balance Sheet shown. Your Balance Sheet should include Liabilities and Stockholders’ Equity as well.)

Exhibit B: Example Horizontal Analysis of Balance Sheets (Tab 14, Partial View)


(IMPORTANT NOTE: Only Partial Balance Sheet shown. Your Balance Sheet should include Liabilities and Stockholders’ Equity as well.)

Writerbay.net

Looking for top-notch essay writing services? We've got you covered! Connect with our writing experts today. Placing your order is easy, taking less than 5 minutes. Click below to get started.


Order a Similar Paper Order a Different Paper