Question

2. Using the trial balances data on the following page complete the following in Excel using two worksheets (Total 345 points): a. For worksheet 1: i. Enter the data as provided (36 pts) ii. Use the SUMO function to check your data entry, (12 pts) iii. Format the columns using appropriate formats ($ signs in first and last numbers in a column; numbers line up, commas for 000's, underlining of numbers only (not entire cell) before/after totals) (48 pts) b. For worksheet 2 you will be performing a financial statement analysis: i. Create a comparative Income Statement and Balance Sheet for the three years using formulas to refer to your original data in worksheet 1. 66 pts) Format the financial statements using appropriate formats. (40 pts) ii. Prepare a common sized income statement and balance sheet for the current year (Use the column to the left of the most recent income statement and balance sheet). (25 pts) iii. Prepare a trend analysis (use the columns immediately to the right of each year's financial statements) to show a year over year change and a year over year percentage change. (50 pts) Format appropriately. (50 pts)
MUST BE DONE ON EXCEL USING EXCEL FORMULAS

Answer 1

a.	Adjusted Trail Balances
Ans.	For the Years Ending
	2019			2018			2017
Sales Revenue		$693,470			$534,293			$526,945
Other Revenue		12,000			16,854			24,859
Cost of goods sold	$283,877			$231,878			$257,334
Operating Expenses	176,820			112,968			103,469
Interest Expenses	12,188			11,484			14,280
Income Tax Expenses	74,479			85,046			74,687
Cash	66,650			49,865			22,553
Account Receivable ,net	89,627			120,313			89,515
Inventory	94,071			71,097			55,116
Prepaid Expenses	12,430			18,200			5,060
Property & equipment (net)	755,857			645,120			594,330
Account payable		50,464			85,261			67,010
Accrued Expenses		10,600			12,869			45,870
Long -term Debt		178,000			185,000			180,000
Common stock		6,000			6,000			5,000
Additional paid in capital		99,000			99,000			62,000
Retained Earning	_________	516,465___		_________	406,694___		_________	304,660___
Total	$1,565,999	$1,565,999		$1,345,971	$1,345,971		$1,216,344	$1,216,344

B.1

Income Statement	2019	2018	2017
Sales Revenue	6,93,470	5,34,293	5,26,945
Other Revenue	12,000	16,854	24,859
Total Income	7,05,470	5,51,147	5,51,804
Cost of goods sold	2,83,877	2,31,878	2,57,334
Gross Profit	4,21,593	3,19,269	2,94,470
Operating Expenses	1,76,820	1,12,968	1,03,469
Interest Expenses	12,188	11,484	14,280
Net Profit before tax	2,32,585	1,94,817	1,76,721
Income Tax Expenses	74,479	85,046	74,687
Profit After tax	1,58,106	1,09,771	1,02,034
Balance Sheet	2019	2018	2017
Liability
Shareholders Equity
Coomin stock	6,000	6,000	5,000
Addional paid in capital	99,000	99,000	62,000
Retained Earning	5,16,465	4,06,694	3,04,660
Net Profit	1,58,106	1,09,771	1,02,034
Long -term Debt	1,78,000	1,85,000	1,80,000
Current Liability
Account payable	50,464	85,261	67,010
Accrued Expenses	10,600	12,869	45,870
Total Liability	10,18,635	9,04,595	7,66,574
Assets
Fixed Assets
Property & equipment (net)	7,55,857	6,45,120	5,94,330
Current Assets
Cash	66,650	49,865	22,553
Account Receivable ,net	89,627	1,20,313	89,515
Inventory	94,071	71,097	55,116
Prepaid Expenses	12,430	18,200	5,060
Total Assets	10,18,635	9,04,595	7,66,574

B.2.

Income Statement	Percentage	2019
Sales Revenue	98.30%	6,93,470
Other Revenue	1.70%	12,000
Total Income	100.00%	7,05,470
Cost of goods sold	40.24%	2,83,877
Gross Profit	59.76%	4,21,593
Operating Expenses	25.06%	1,76,820
Interest Expenses	1.73%	12,188
Net Profit before tax	32.97%	2,32,585
Income Tax Expenses	10.56%	74,479
Profit After tax	22.41%	1,58,106
Balance Sheet		2019
Liability
Shareholders Equity
Coomin stock	0.59%	6,000
Addional paid in capital	9.72%	99,000
Retained Earning	50.70%	5,16,465
Net Profit	15.52%	1,58,106
Long -term Debt	17.47%	1,78,000
Current Liability
Account payable	4.95%	50,464
Accrued Expenses	1.04%	10,600
Total Liability	100.00%	10,18,635
Assets
Fixed Assets
Property & equipment (net)	74.20%	7,55,857
Current Assets
Cash	6.54%	66,650
Account Receivable ,net	8.80%	89,627
Inventory	9.24%	94,071
Prepaid Expenses	1.22%	12,430
Total Assets	100.00%	10,18,635

B.3.

Income Statement	2019	yoy Change	yoy % Change	2018	yoy Change	yoy % Change	2017
Sales Revenue	6,93,470	1,59,177	29.79%	5,34,293	7,348	1.39%	5,26,945
Other Revenue	12,000	(4,854)	-28.80%	16,854	(8,005)	-32.20%	24,859
Total Income	7,05,470	1,54,323	28.00%	5,51,147	(657)	-0.12%	5,51,804
Cost of goods sold	2,83,877	51,999	22.43%	2,31,878	(25,456)	-9.89%	2,57,334
Gross Profit	4,21,593	1,02,324	32.05%	3,19,269	24,799	8.42%	2,94,470
Operating Expenses	1,76,820	63,852	56.52%	1,12,968	9,499	9.18%	1,03,469
Interest Expenses	12,188	704	6.13%	11,484	(2,796)	-19.58%	14,280
Net Profit before tax	2,32,585	37,768	19.39%	1,94,817	18,096	10.24%	1,76,721
Income Tax Expenses	74,479	(10,567)	-12.43%	85,046	10,359	13.87%	74,687
Profit After tax	1,58,106	48,335	44.03%	1,09,771	7,737	7.58%	1,02,034