Bank statement reconciliation

[brianju]

How would this look in excel...Bank Reconcilation Statements.

On August 14th,2008 One of our Client's cash book showed a debit balanceof $4,000.00.
His bank statement showed a balance of $4,270.00.
On comparison the following were found:
* check issued amounting to $2,500.00 has not been cashed
* The bank rejected checks amounting to $140
* Standing order for a staples order of $700 was noted.
* A customer paid $170 directly into the bank without any notice to our company
* Bank charges of $160 were entered in the bank statements only.
* A dividend of $250 was paid directly into the bank and not recorded in the cashbook
* Checks for $1,650.00 were entered into the cash book and deposited in
the bank but had not been cleared (deposited).
Prepare a bank reconciliation statement for the month .

balance per bank statement $ $
(Add )
uncredited checks

standing order
bank charges
dishonored checks _____________________
(Less)
unpresented checks
dividend
credit transfer ______________________
Balance per cash book
Have them done and Submitted in MS Excel Spread Sheet .

[Sisyphus]

Hi,

Make a spreadsheet with at least 4 columns. Name them as Date, Description, Debit and Credit.
Put your book balance at the top and the bank balance at the bottom. Enter your own balance on the side of the Reconciliation Statement that it has in your ledger (Cashbook). Enter the bank balance on the side of the statement that it occupies in the BANK's books. So, if both balances are identical your statement is balanced.

Differences fall into two categories, the items you have but the bank doesn't, and items the bank has but you don't. Enter the first category so as to modify your own books' balance, the second category so as to modify the bank's balance. When all differences have been entered the Statement will be balanced because the two modified balances are equal if they are fully reconciled.

It is easy to enter items on the wrong side of the statement. The trick is to visualize the result of each entry. Take a check, issued but not cashed. This is an entry you have but the bank doesn't. Therefore the bank's balance must be modified. Select the side of your statement which they will book on. When they pay the check they will debit your
account. So, you must enter this item on the debit side. In fact, would you plan on canceling this check and, therefore, modifying your own balance, it would also end up on the debit side, albeit for different reasons.

I hope this helps!