Data Validation and Input Controls If you add extra controls features, you will
ID: 2578768 • Letter: D
Question
Data Validation and Input Controls
If you add extra controls features, you will also submit a brief note, typed in Word, as the documentation of the extras. You will submit the electronic document(s) in ONE ZIP archive to yu.cong@morgan.edu. Make sure that you name the Excel and Word documents and the zip archive following the standard naming convention. You will use the same naming convention to type the subject of the email in which you submit the zip archive. The project will be assigned a grade of zero if any of the requirements above is not satisfied.
Create a spreadsheet in EXCEL to reconcile a bank statement. Organize it as follows:
All the cells must be appropriately formatted, e.g. dollar amounts must use the accounting format, etc. Another formatting such as bold, underline and italic are expected to be used in appropriate places.
All the negative adjustments must be typed in as negative values. The input must be validated by using data validation tools. The values must also be displayed in RED by using conditional formatting.
Appropriate input controls, such as range check or length check, must be applied to attributes/elements such as check numbers.
All the (sub-)totals of continuous ranges must be prepared by using the aggregation functions provided in EXCEL.
The IF function should be used to make logical judgment.
Additional control features are encouraged but not required.
Use the numbers below to show how your spreadsheet works.
Balance Per Book $107,525.33
balance per book $106,928.68
collection of notes recievable
principal $10,000.00
Interests $30.00
Fee charged by the bank for collection $20.00
Interests on the reconciled account $11.95
Monthly charged by the bank $25.00
Deposit in transit
note Amount
Payment, Beethoven $12,000.00
Payment, Mozart $13,000.00
Payment Mikey $8,000.00
Refund, Pooh $ 7,000.00
Outstanding checks
Check number amount
1234 $2,256.75
1235 $4,775.50
1237 $3,567.45
1239 $7,500.00
1240 $12,500.00
Explanation / Answer
CASH BOOK, ADJUSTED FOR BANK UPDATES
$
$
B/B DOWN
106928.68
BANK CHARGES
25
ACCOUNTS RECEIVABLE
10000
FEES FOR COLLECTION
20
INTEREST
30
INTEREST ON RECONCILED AMOUNTS
11.95
B/ CARRIED DOWN
116901.73
116958.68
116958.68
BANK RECONCILIATION STATEMENT
$
$
BALANCE AS PER BANK STATEMENT
107525.33
ADD: DEPOSITS IN TRANSIT
BEETHOVEN
12000
MOZART
13000
MIKEY
8000
POOH
7000
40000
LESS: CHEQUES UNPRESENTED
#1234
2256.75
1235
4775.5
1237
3567.45
1239
7500
1240
12500
30599.70
116925.63
“DIFFERENCE”
23.90
BALANCE AS PER CASH BOOK
116901.73
CASH BOOK, ADJUSTED FOR BANK UPDATES
$
$
B/B DOWN
106928.68
BANK CHARGES
25
ACCOUNTS RECEIVABLE
10000
FEES FOR COLLECTION
20
INTEREST
30
INTEREST ON RECONCILED AMOUNTS
11.95
B/ CARRIED DOWN
116901.73
116958.68
116958.68
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.