Excel Project: Projecting Financial Statements Assignment

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.

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)