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)
you should use Excel formulas

Answer 1

A

Income Statement	2019		2018		2017
Sales Revenue		$                                            693,470		$        534,293		$                            526,945
Other Revenue		$                                              12,000		$          16,854		$                              24,859
Total		$                                            705,470		$        551,147		$                            551,804
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
Total		$                                            547,364		$        441,376		$                            449,770
Profit/ (Loss)		$                                            158,106		$        109,771		$                            102,034
Balance Sheet'
Assets
Cash	$             66,650		$          49,865		$          22,553
Accounts receviable	$             89,627		$        120,313		$          89,515
Inventory	$             94,071		$          71,097		$          55,116
Prepaid expenses	$             12,430		$          18,200		$            5,060
Property and Equipment	$          755,857		$        645,120		$        594,330
Total		$                                        1,018,635		$        904,595		$                            766,574
Liabilities
Accounts 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 earnings	$          516,465		$        406,694		$        304,660
Profit for the year	$          158,106		$        109,771		$        102,034
Total		$                                        1,018,635		$        904,595		$                            766,574

B

	2019	2018	2017
Income	$          705,470	$                                            551,147	$        551,804
Expenses	$          547,364	$                                            441,376	$        449,770
Profit	$          158,106	$                                            109,771	$        102,034
Balance Sheet
Assets'	$       1,018,635	$                                            904,595	$        766,574
Liabilities	$          239,064	$                                            283,130	$        292,880
Capital	$          779,571	$                                            621,465	$        473,694
Vertical Trend	2019	2018	2017
Income Statement
Sales Revenue	100.00%	100.00%	100.00%
Other Revenue	1.73%	3.15%	4.65%
Total	101.73%	103.15%	103.28%
Cost of Goods Sold	40.94%	43.40%	48.16%
Operating expenses	25.50%	21.14%	19.37%
Interest expenses	1.76%	2.15%	2.67%
Income Tax expenses	10.74%	15.92%	13.98%
Total	78.93%	63.65%	82.61%
Profit/ (Loss)	22.80%	15.83%	20.55%
Balance Sheet'
Assets
Cash	6.54%	5.51%	2.94%
Accounts receviable	8.80%	13.30%	11.68%
Inventory	9.24%	7.86%	7.19%
Prepaid expenses	1.22%	2.01%	0.66%
Property and Equipment	74.20%	71.32%	77.53%
Total	100.00%	100.00%	100.00%
Liabilities
Accounts payable	4.95%	9.43%	8.74%
Accrued expenses'	1.04%	1.42%	5.98%
Long term debt	17.47%	20.45%	23.48%
Common stock	0.59%	0.66%	0.65%
Additional paid in capital	9.72%	10.94%	8.09%
retained earnings	50.70%	44.96%	39.74%
Profit for the year	15.52%	12.13%	13.31%
Total	100.00%	100.00%	100.00%
Horizontal Trend	2019	2018	2017
Income Statement
Sales Revenue	131.60%	101.39%	100.00%
Other Revenue	48.27%	67.80%	100.00%
Total	127.85%	99.88%	100.00%
Cost of Goods Sold	110.31%	90.11%	100.00%
Operating expenses	170.89%	109.18%	100.00%
Interest expenses	85.35%	80.42%	100.00%
Income Tax expenses	99.72%	113.87%	100.00%
Total	121.70%	98.13%	100.00%
Profit/ (Loss)	154.95%	107.58%	100.00%
Balance Sheet'
Assets
Cash	295.53%	221.10%	100.00%
Accounts receviable	100.13%	134.41%	100.00%
Inventory	170.68%	129.00%	100.00%
Prepaid expenses	245.65%	359.68%	100.00%
Property and Equipment	127.18%	108.55%	100.00%
Total	132.88%	118.00%	100.00%
Liabilities
Accounts payable	75.31%	127.24%	100.00%
Accrued expenses'	23.11%	28.06%	100.00%
Long term debt	98.89%	102.78%	100.00%
Common stock	120.00%	120.00%	100.00%
Additional paid in capital	159.68%	159.68%	100.00%
retained earnings	169.52%	133.49%	100.00%
Profit for the year	154.95%	107.58%	100.00%
Total	132.88%	118.00%	100.00%