Accounting Information System Excel taskTest
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
