Excel Homework for Accounting

excel_homework.xlsx

Home >Business & Finance homework help >Accounting homework help >Excel Homework for Accounting

Score

	Your Score	Total Possible
Score from TB	1	25
Score from FS	0	15
Total	1	40

Instructions

Instructions		Assistance
1. In the Trial Balance Tab, create drop down boxes in the classification column so that a user can chose whether the item is an Asset, Liability or part of Owners' Equity.		How to create drop down boxes: 1. Go to cell C6 in the Trial Balance tab 2. Click the Data Tab at the top of the excel worksheet 3. Click on Data Validation 4. In the first drop down select List 5. Click on the source option and enter F4:F7 6. Copy the drop down box created and paste in cells C7:C24 Snips have been provided to the right
2. After you have created drop down boxes, you need to click on the dropdown and enter the appropriate classification for each item. For example, in cell C6 use your drop down box and select that an Accounts payable is a Liability.		How to sort: 1. Within the data highlight cells A5:D24 2. Click on Sort 3. When the box opens click the option that says my data has headers and then sort by classification (Snapshots have been provided below)
3. Sort the Accounts by the classification column. You need to sort them in the following order - Assets, Liabilities and then Owners' Equity.
4. Once the accounts have been sorted by classification, then order the accounts the way they should be listed on the trial balance. For example, a 1 should be entered for Cash in the Order column in the appropriate cell. Use numbers 1 -19. Check figure: Cash should be in row 8 after sorting the accounts in item 3.
5. Once you have determined the proper ordering, then sort the accounts by the order column using the same method that was previously done for Classification. Check figure: Cash should be in row 6 after sorting by order.
6. On the financial Statement tab, complete three financial statements. (Statement of cash flows is omitted for this exercise.) To complete the statements, link sources when possible and use formulas when the item is highlighted in blue. Linking an account requires you to hit "=" and then click on the cell that you want to link. This will pull in the amount from the cell you are linking. You should link from one tab within excel to another.		Check figures: Dividends should be in row 17, classified as Owners' Equity and given the number 12 in the order column Service Revenue should be in row 18, classified as Owners' Equity and given the number 13 in the order column Accounts Receivable should be given the number 7 in the order column
Scoring:
The homework is worth 35 points. It is possible to receive a score of up to 40 points.

Trial Balance

RYAN COMPANY
Adjusted Trial Balance
August 31, 2017
Description	Dr./(Cr.)	Classification	Order	Asset
Accounts Payable	(5,800)			Liability
Accounts Receivable	9,400			Owners' Equity
Accumulated Depreciation - Equipment	(4,800)
Cash	10,900
Common Stock	(10,000)
Depreciation Expense	1,200
Dividends	2,800
Equipment	16,000
Insurance Expense	1,500
Prepaid Insurance	2,500
Rent Expense	10,800
Rent Revenue	(13,100)
Retained Earnings	(5,500)
Salaries and Wages Expense	18,100
Salaries and Wages Payable	(1,100)
Service Revenue	(34,600)
Supplies	500
Supplies Expense	2,000
Unearned Rent Revenue	(800)
	- 0

TB solution

RYAN COMPANY
Adjusted Trial Balance
August 31, 2017
Description	Dr./(Cr.)	Classification	Order
Cash	10,900	Asset	1	FALSE	FALSE
Accounts Receivable	9,400	Asset	2	TRUE	FALSE
Supplies	500	Asset	3		FALSE
Prepaid Insurance	2,500	Asset	4		FALSE
Equipment	16,000	Asset	5		FALSE
Accumulated Depreciation - Equipment	(4,800)	Asset	6		FALSE
Accounts Payable	(5,800)	Liability	7	FALSE	FALSE
Salaries and Wages Payable	(1,100)	Liability	8		FALSE
Unearned Rent Revenue	(800)	Liability	9		FALSE
Common Stock	(10,000)	Owners' Equity	10	FALSE	FALSE
Retained Earnings	(5,500)	Owners' Equity	11	FALSE	FALSE
Dividends	2,800	Owners' Equity	12	FALSE	FALSE
Service Revenue	(34,600)	Owners' Equity	13		FALSE
Rent Revenue	(13,100)	Owners' Equity	14		FALSE
Salaries and Wages Expense	18,100	Owners' Equity	15		FALSE
Supplies Expense	2,000	Owners' Equity	16		FALSE
Rent Expense	10,800	Owners' Equity	17		FALSE
Insurance Expense	1,500	Owners' Equity	18		FALSE
Depreciation Expense	1,200	Owners' Equity	19		FALSE
	- 0
			Note:	Number Right	Number Wrong
				1	24
				1
			Score from TB	1
			Score from FS	0
			Total	1

Financial Statements

Income Statement				Retained Earnings	Balance Sheet
Revenues					Assets
1	Service Revenue			Beginning Retained Earnings	Current Assets
2	Rent Revenue			Plus/Minute Net Income /(Loss)		Cash
	Total Revenues			Less Dividends (enter as a positive)		Accounts Receivable
Expenses				Ending Retained Earnings		Supplies
1	Salaries and Wages Expense					Prepaid Insurance
2	Supplies Expense				Total Current Assets
3	Rent Expense
4	Insurance Expense				PP&E (net)
5	Depreciation Expense
	Total Expenses				Total Assets
	Net Income				Current Liabilities
						Accounts Payable
						Salaries and Wages Payable
						Unearned Rent Revenue
					Total Current Liabilities
			formula needed
			link amounts from trial balance tab		Total Liabilities
					Stockholders Equity
		Tip: Service revenue should be a positive amount on the financial statements				Common Stock
		but it is a credit on the trial balances.				Retained Earnings
					Total Stockholders' Equity
					Total Liabilities and Stockholders' Equity

FS Solutions

Income Statement				Retained Earnings			Balance Sheet
Revenues							Assets
1	Service Revenue	34,600	FALSE	Beginning Retained Earnings	5,500	FALSE	Current Assets
2	Rent Revenue	13,100	FALSE	Plus/Minute Net Income /(Loss)	14,100	FALSE		Cash	10,900	FALSE
	Total Revenues	47,700	FALSE	Less Dividends	2,800	FALSE		Accounts Receivable	9,400	FALSE
Expenses				Ending Retained Earnings	16,800	FALSE		Supplies	500	FALSE
1	Salaries and Wages Expense	18,100	FALSE					Prepaid Insurance	2,500	FALSE
2	Supplies Expense	2,000	FALSE				Total Current Assets		23,300	FALSE
3	Rent Expense	10,800	FALSE
4	Insurance Expense	1,500	FALSE				PP&E (net)		11,200	FALSE
5	Depreciation Expense	1,200	FALSE
	Total Expenses	33,600	FALSE				Total Assets		34,500	FALSE
	Net Income	14,100	FALSE				Current Liabilities
								Accounts Payable	5,800	FALSE
								Salaries and Wages Payable	1,100	FALSE
								Unearned Rent Revenue	800	FALSE
			Amount right	incorrect			Total Current Liabilities		7,700	FALSE
			0	10
			0	4			Total Liabilities		7,700	FALSE
	40		0	16
		Total Correct	0	30			Stockholders Equity
			0.5	1				Common Stock	10,000	FALSE
		Score	0	30				Retained Earnings	16,800	FALSE
							Total Stockholders' Equity		26,800	FALSE
							Total Liabilities and Stockholders' Equity		34,500	FALSE