Accounting Information System Excel Test

ExcelAssessment.xlsm

Home >Business & Finance homework help >Accounting homework help >Accounting Information System Excel Test

Test

Grade	Part.Page	#
0
		1		Write a formula that would indicate if the sum of the first column
		1		of numbers does not equal the sum of the second column by displaying
		1		either Equal or Not Equal and if not equal show
		1		Not equal by { and enter the amount of the difference }
		1			2000	2345.5
		1			1000
		1				500
		1				2500
		1
		2		On the sheet called Income Statement, in cell E14 create the formula
		2		for the Income Tax.
		2
		2
		3
		3		On the sheet called Income Statement, make cell C14 is the only cell
		3		that the enduser can type in. Protect the sheet without a password.
		3
		4
		4		Freeze column A of the spreadsheet to make sure it does not
		4		scroll off the left side of the screen.
		4
		5
		5		The only valid entry is an account number from the chart of accounts
		5
		5			chart of accounts
		5			1110	cash
		5			1130	prepaid
		5			1211	office equip
		6
		6		Custom format cell to display "The report is due on Tuesday, February 28, 2012"
		6
		6		2/28/12
		6
		6
		7
		7		Display an error if there is a value in the Current and Late cells for the same invoice.
		7
		7
		7		Invoice	Amount	Current	Late
		7		AM1254	120.00	100.00	20.00
		7		AM1255	200.00		200.00
		7
		7
		7
		7
		8
		8		Write a formula that will finds the code for the color
		8		or return #N/A if the color is not on the list.
		8			Color	Code
		8			Red	101
		8			Blue	102
		8			Yellow	103
		8			Orange	104
		8			Green	105
		8			Black	106
		8			White	107
		8					Green
		8
		9
		9		Write a formula that sums the assets
		9
		9
		9					Amount
		9		1110	Cash		1500
		9		1130	Prepaid Insurance		1000
		9		5100	Telephone		200
		9		5120	Rent		400
		9		1140	Accounts Receivable		600
		9
		9
		9
		10
		10		Format the cell to show the amount owed after the words "You owe $100.00."
		10
		10			Amount
		10			100
		10
		10
		10
		11
		11		Indicate if the balance is different than the normal balance.
		11
		11
		11			1110	Cash	Debit
		11			1130	Prepaid Ins	Debit
		11			3100	Capital Stock	Credit
		11			2200	Dividends	Debit
		11
		11				Account	Balance
		11				1110	-$100.25
		11				3100	$200.00
		11
		11
		12
		12		On the sheet called Invoice name the range A2:A27 , Dates and the range B2:B27, Amount.
		12		Write a formula using the named range to determine the last date and the largest amount.
		12				Last Date
		12				Largest Amount
		12
		13
		13		Roundup to the next number of cans and use cans in the format.
		13
		13
		13					2,123.60	2,124 cans
		13
		14
		14		Write a formula that will indicate that ALL the values
		14		are " All Greater than 10" or that they are "Not all greater than 10".
		14		12.00	14.00	45.00
		14
		14
		14
		15
		15		Displays "Markdown" if the product is a Marker, or if the product
		15		is a Red Pen that costs more than $6. Displays "Regular" if not.
		15		Product	Color	Price
		15		Pen	Red	2.00
		15		Marker	Black	6.00
		15		Pen	Red	7.00
		15		Marker	Black	4.00
		15		Pen	Red	5.00
		15		Marker	Black	3.00
		15
		15
		16
		16		Indicate the sum of the prices of the product selected
		16		.
		16				Marker
		16
		17
		17		Follow the rules in the following table and write "Discount" or "No Discount"
		17
		17		RED and >10		Discount
		17		GREEN and >15		Discount
		17		Blue and > 20		Discount
		17
		17			RED	12.00
		17
		17
		18
		18		Item	Description	A/R
		18		1000	John's Cavern	$ 10,000.00
		18		2000	IHOP	$ 330.00
		18		3000	Grace Land	$ 40,000.00
		18		4000	Fisher Island	$ 100,000.00
		18
		18				Item	Description	A/R
		18				2000
		18
		19
		19		Display the latest date in the format:
		19		" The last date entered was January 02, 2009."
		19
		19				Dates
		19				1/2/09
		19				2/4/09
		19				10/18/18
		19				4/1/09		The last date entered was January 02, 2009.
		19				12/29/09
		19				11/25/09
		19				10/21/09
		19
		19
		19
		19
		19
		20		Married Filing Jointly
		20		Taxable income over	Rate on excess	Tax on amount in "E"
		20		$ - 0	10%	$ - 0
		20		16,700.00	15%	1,670.00
		21		67,900.00	25%	9,350.00
		21		137,050.00	28%	26,637.50
		21		208,850.00	33%	46,741.50
		21		372,950.00	35%	100,894.50
		21
		21		Using the table above determine the tax due if the taxable income
		21		Income was..		$ 68,000.00		$ 9,375.00
		21
		22
		22
		22
		22	On the sheet name Depreciation Schedule
		23
		23
		23		Create a Double Declining Depreciation Schedule that restricts:
		24		Cost to positive numbers
		24		Scrap to positive numbers less than the cost
		24		Life to between 3 and 10 years
		25		And shows only years that are equal to or less than life
		25		Use Named Ranges

Sheet2

Income Statement

Byte of Accounting
Income Statement
For the Period Ending June 30, 2009
	Revenue
4100	Computer Revenue			$ 600.67
	Expenses
5090	Interest Expense		$ 100.00
5100	Insurance Expense		200.00
				200.00
	Operating Income			$ 400.67
5120	Tax Expense	40%
	Net Income After Taxes			$ 400.67

Invoice

Dates	Amount
12/5/12	500.00
10/4/12	400.00
12/9/12	550.00
4/5/12	600.00
9/8/12	520.50
11/11/12	420.50
9/10/12	570.50
11/15/12	620.50
3/12/12	541.00
8/15/12	441.00
10/18/12	591.00
8/17/12	641.00
10/22/12	561.50
2/17/12	461.50
7/22/12	611.50
9/24/12	661.50
7/24/12	582.00
9/28/12	482.00
1/24/12	632.00
6/28/12	682.00
8/31/12	602.50
6/30/12	502.50
9/4/12	652.50
6/4/12	702.50
8/7/12	623.00
6/6/12	523.00

Depreciation Schedule

	On the sheet name Depreciation Schedule
		Create a Double declining Depreciation Schedule that restricts:
		Cost restricted to positive numbers
		Scrap restricted to positive numbers Less than the cost
		Life restricted to between 3 and 10 years
		And shows only years that are equal to or less than life
		Use Named Ranges