Friday, January 21, 2011

How to Balance Your Checkbook or Bank Account Using an Excel


1. Create a new worksheet in your spreadsheet software. Save the file in the folder of your choice with a name that includes the month and the account reconciling.
2. Type the label 'Bank Balance' in the first row of the first column. Next type 'Register Balance' in the second row of the first column. Leave the next two rows blank. Type the balance shown on your bank statement in row one of column two. Enter the balance shown in your check register in row two of column two.
3. Go to the fifth row to enter labels for your reconciliation columns. Use the first column for 'Dates.' Label column two 'Type.' The label for column three is 'Bank Credits' and the fourth column is 'Bank Debits.' Label column five 'Register Credits' and use the label 'Register Debits' for column six.
4. Enter all debits shown on your bank statement not recorded in your check register. List the date for each debit in column one under 'Dates.' Input the transaction type: check, bank fees or ATM withdrawal in the 'Type' column. Type the amounts in the column labeled 'Bank Debits.'
5. Total all 'Bank Debits' in the first empty row after all the entries made in this column. Place the cursor in column three of row two and subtract the 'Bank Debits' total from the register total.
6. Enter all deposits shown on the bank statement not in the check register. Input this data in the 'Date,' 'Type' and 'Bank Credits' column. Total the 'Bank Credits' column below the last entry that you made. Use column four of row two to add the 'Bank Credits' total to the revised register balance in row two column three.
7. Enter any deposits made that do not appear on the bank statement. Fill in the 'Date,' 'Type' and 'Register Credit' columns. Total this column after the last entry and use column three of row one to add this total to the statement balance.
8. List all outstanding checks, ATM withdrawals in the register, but not shown on the bank statement. Fill in the 'Date' and 'Type.' Put the amounts for each transaction under the 'Register Debits' column and total this column.
9. Use column four of row one to subtract the totaled 'Register Debits' from the revised bank statement balance in column three of row one. The result will equal the register balance unless you have overlooked a credit or debit.

Blogger news