WELCOME TO GMS ACCOUNTING ONLINE HELP
The help you need at your fingertips.
You are here: Tools > Bank Reconciliation
Bank Reconciliation
Function
Prepares a Bank Reconciliation for all cash accounts in the GMS Accounting System.
Things You Should Know
Operating Instructions
Starting Date: Look at the oldest outstanding date for deposits, checks or journal entries that were listed on your previous month's bank reconciliation. Click on the combo box to select the first day of the month and year for the oldest outstanding item.
Ending Date: Click on the Combo Box to select the month for which you are preparing the Bank Reconciliation.
GL Account: Click on the Combo Box to select the GL Cash Account for which you are preparing the Bank Reconciliation.
Ending Statement Balance: Enter the ending statement balance as it appears on your bank statement. Once this is entered, it will be retained. If you import the bank file, it will automatically fill this amount in.
Bank Reconciliation Tab: Click on the print button to print the Bank Reconciliation for the Month and Account selected. This should be the last step done.
Note: The GL balance of the account you are reconciling will show on this report with the variance when compared to the Ending Book Balance.
Deposits Tab
Clear (Y/N): Click on this field on the grid and a checkmark will be entered denoting the item has been cleared. If a Deposit was erroneously cleared and the Remove Cleared Deposits has not been done, you can double click on this field and the checkmark will be removed, making the item outstanding once again.
Listing: Click this button to print a listing of cleared and outstanding deposits. Cleared deposits will appear at the beginning of the report and the outstanding deposits will appear at the end. Should you wish to only print one or the other, select the specific page numbers when you send the print job to the printer. After verifying the total cleared deposits, you may want to wait until you have removed cleared deposits before printing the list of outstanding deposits on paper.
Remove Cleared Items: Click on this button after verifying that all of the proper deposits have been cleared and the balance of outstanding deposits is the correct figure used to complete your bank reconciliation. This step will remove any deposits with a checkmark in the Clear (Y/N) field in the grid.
Edit Cleared Items: Should you accidentally remove a deposit you had marked as cleared, but the deposit had not cleared the bank, select this option. You will be asked to enter the Deposit Batch number. Enter the Batch number, click OK, then click Save. This will place the deposit back in the grid.
Checks Tab
Clear (Y/N): Click on this field on the grid and a checkmark will be entered, denoting the item as cleared. If a check was erroneously cleared and the Remove Cleared Checks has not been done, you can double click on this field and the checkmark will be removed, making the item outstanding once again.
Listing: Click this button to print a listing of cleared and outstanding checks. Cleared checks will appear at the beginning of the report and the outstanding checks will appear at the end. Should you wish to only print one or the other, select the specific page numbers when you send the print job to the printer. After verifying the total cleared checks, you may want to wait until you have removed cleared checks before printing the list of outstanding checks on paper.
Remove Cleared Items: Click on this button after verifying that all of the proper checks have been cleared and the balance of outstanding checks is the correct figure used to complete your bank reconciliation. This step will remove any checks with a checkmark in the Clear (Y/N) field in the grid.
Edit Cleared Items: Should you accidentally remove a check you had marked as cleared, but the check had not cleared the bank, select this option. You will be asked to enter the Check number that was cleared by mistake. Then click on save and the check will be returned to the grid as outstanding.
You will then be asked if you are sure you want to edit this cleared check. If you say Yes, the cleared check information will appear on the form with the save option. Should you say No, the system will return to the checks tab.
Journal Entries Tab
Note: If you issue payroll checks, the net pay amount of those checks will also be included in the payroll journal entry reflected in this tab. Make sure you clear the payroll journal entry to avoid duplication.
Clear (Y/N): Click on this field on the grid and a checkmark will be entered, denoting the item as cleared. If a Journal Entry was erroneously cleared and the Remove Cleared Journal Entries has not been done, you can double click on this field and the checkmark will be removed, returning the item to outstanding status.
Listing: Click this button to print a listing of cleared and outstanding Journal
Entries. Cleared Journal Entries will appear at the beginning of the report and the outstanding Journal Entries will appear at the end. Should you wish to only print one or the other, select the specific page numbers when you send the print job to the printer. After verifying the total cleared GJ’s, you may want to wait until you have removed cleared GJ’s before printing the list of outstanding GJ’s on paper.
Remove Cleared Items: Click on this button after verifying that all of the proper Journal Entries have been cleared and the balance of outstanding Journal Entries is the correct figure used to complete your bank reconciliation. This step will remove any Journal Entries with a checkmark in the Clear (Y/N) field in the grid.
Edit Cleared Items: Should you accidentally remove a Journal Entry you had marked as cleared, but the Journal Entry had not cleared the bank, select this option. You will be asked to enter the General Journal document number. Enter the document number, click OK, then click Save. This will place the document back in the grid.
Adjustments: Should you need to make a manual adjustment to the Bank Reconciliation, use this option to record the entry. This will typically be used for an item that cleared the bank, but was not entered into the accounting system and will be entered in the following month.
Import Matrix Tab
Downloading the bank file: If available, download your monthly transactions in a csv or xls format from your bank’s website. If a csv format is not listed, see if one of their formats can be downloaded and saved in a csv format.
It is recommended that when you save the file, put your GL code and month as part of the name, i.e. GL10000 May bank download.csv. You may want to store them all in a folder called Bank downloads.
After downloading the file, open it and look at all the fields. If any of the fields, such as the date field is not in a date format, make sure you format it as a date, with an xx/xx/xx format.
Things You Should Know
Choose a bank format you wish to import the statement
Note: When you are going to import for the first time, after downloading your bank file, open it up. Make a note of which column and field the ending statement balance is listed. Then make a note of which column the following is in:
Check/Deposit Number
Post Date
Amount
Credit
Debit
Description
If your bank is already listed, choose that bank. Edit and change any of the fields to match your bank file format and save it.
If your bank name is not listed, see if the format of your bank’s download matches any of the bank formats listed. If so, you can change the bank name to the bank that you use.
If your bank’s format does not match one that is listed, you can either modify one that is listed or add a new bank name and designate which columns and fields the information requested in the grid is located.
Setting up a new format:
File Name: Click on Open and find the file you downloaded from the bank.
Import: Click on the Import button, and it will import the data. When the data is imported, it will give you a list of any deposits, checks or miscellaneous debits that were found in the bank file that were not found in your GL. It is very important that you print those out for reference during the reconciliation, since they are not saved in the database.
Note: If you try to import a bank file using the same cutoff date previously used you will receive a message saying you are about to do so.
Note: if you use supplement #405 A/P Direct Deposit, you will notice on the listing of miscellaneous items found in bank file but not on the GL that the total amount of each A/P direct deposit file will be listed. The description of these amounts should include ACH Settlement. These items can be ignored since you know they cleared the bank on the settlement date. This of course is assuming the date is in your current month reconciliation.
Bank Rec Utility
This Bank Reconciliation Utility is specifically designed to help clients when you first begin using the GMS Accounting and Financial Management System. It gives you the ability to build the list of outstanding Deposits, Checks and Journal Entries prior to the beginning of entering live data into the accounting system.
Things You Should Know
Operating Instructions
Ending Date: Enter the date for which you are creating the outstanding lists.
GL Account: Select the GL cash account for which you are creating the outstanding lists.
Deposits
Utility Batch #: This field automatically numbers each deposit batch. This field cannot be edited. Each deposit utility batch should equal the amount of an entire deposit you need to account for. Do not enter each item in a deposit separately.
Date: Enter the date of the deposit.
Description: You can enter an optional description in this field specific to the deposit.
Amount: Enter the amount of the deposit.
Save: Once your outstanding deposits have been entered, be sure to save.
Listing: You may print a listing of deposits entered by selecting this option.
Post to Bank Rec: This will post the deposit(s) to the bank reconciliation tables.
Checks
Check #: Enter the outstanding check number you are adding to this utility.
Date: Enter the date of the outstanding check.
Payer: Enter the payer name to which the check was issued.
Amount: Enter the amount of the outstanding check.
Save: Once your outstanding checks have been entered, be sure to save.
Listing: You may print a listing of checks entered by selecting this option.
Post to Bank Rec: This will post the check(s) to the bank reconciliation tables.
Journal Entries
Note: an example of a journal entry needed in this utility may be for an online payment to a vendor that had not cleared the back as of the specified date.
Utility Doc #: This field automatically numbers each document on the Journal Entries tab. This field cannot be edited.
Date: Enter the date of this entry.
Description: You can enter an optional description in this field specific to the entry.
Debit: If this entry is a debit to the specified cash account enter the amount in this column.
Credit: If this entry is a credit to the specified cash account enter the amount in this column.
Save: Once your outstanding journal entries have been entered, be sure to save.
Listing: You may print a listing of journal entries entered by selecting this option.
Post to Bank Rec: This will post the journal entries to the bank reconciliation tables.
Prepares a Bank Reconciliation for all cash accounts in the GMS Accounting System.
Things You Should Know
- Importing the bank file is the most efficient way to do your bank reconciliation and if a bank download is available, it should be utilized every month.
- If you forget to remove cleared Checks, Deposits or Journal Entries and close out, those entries that were check marked to be cleared will still be checked when you return to the program.
- If you are manually clicking to enter check marks to mark cleared deposits, checks or journal entries, you can use your mouse. One click will put a check mark in the field and double click will remove the check mark.
- When clearing Journal Entries, if the entry is for two different accounts, such as a debit to cash account 10000 and a credit to cash account 10100, the system will only mark cleared the side of the entry for the account you are reconciling at that time.
- Please see the Bank Rec Utility section at the bottom of this Bank Reconciliation chapter for features specifically designed for organizations just starting with GMS.
Operating Instructions
Starting Date: Look at the oldest outstanding date for deposits, checks or journal entries that were listed on your previous month's bank reconciliation. Click on the combo box to select the first day of the month and year for the oldest outstanding item.
Ending Date: Click on the Combo Box to select the month for which you are preparing the Bank Reconciliation.
GL Account: Click on the Combo Box to select the GL Cash Account for which you are preparing the Bank Reconciliation.
Ending Statement Balance: Enter the ending statement balance as it appears on your bank statement. Once this is entered, it will be retained. If you import the bank file, it will automatically fill this amount in.
Bank Reconciliation Tab: Click on the print button to print the Bank Reconciliation for the Month and Account selected. This should be the last step done.
Note: The GL balance of the account you are reconciling will show on this report with the variance when compared to the Ending Book Balance.
Deposits Tab
Clear (Y/N): Click on this field on the grid and a checkmark will be entered denoting the item has been cleared. If a Deposit was erroneously cleared and the Remove Cleared Deposits has not been done, you can double click on this field and the checkmark will be removed, making the item outstanding once again.
Listing: Click this button to print a listing of cleared and outstanding deposits. Cleared deposits will appear at the beginning of the report and the outstanding deposits will appear at the end. Should you wish to only print one or the other, select the specific page numbers when you send the print job to the printer. After verifying the total cleared deposits, you may want to wait until you have removed cleared deposits before printing the list of outstanding deposits on paper.
Remove Cleared Items: Click on this button after verifying that all of the proper deposits have been cleared and the balance of outstanding deposits is the correct figure used to complete your bank reconciliation. This step will remove any deposits with a checkmark in the Clear (Y/N) field in the grid.
Edit Cleared Items: Should you accidentally remove a deposit you had marked as cleared, but the deposit had not cleared the bank, select this option. You will be asked to enter the Deposit Batch number. Enter the Batch number, click OK, then click Save. This will place the deposit back in the grid.
Checks Tab
Clear (Y/N): Click on this field on the grid and a checkmark will be entered, denoting the item as cleared. If a check was erroneously cleared and the Remove Cleared Checks has not been done, you can double click on this field and the checkmark will be removed, making the item outstanding once again.
Listing: Click this button to print a listing of cleared and outstanding checks. Cleared checks will appear at the beginning of the report and the outstanding checks will appear at the end. Should you wish to only print one or the other, select the specific page numbers when you send the print job to the printer. After verifying the total cleared checks, you may want to wait until you have removed cleared checks before printing the list of outstanding checks on paper.
Remove Cleared Items: Click on this button after verifying that all of the proper checks have been cleared and the balance of outstanding checks is the correct figure used to complete your bank reconciliation. This step will remove any checks with a checkmark in the Clear (Y/N) field in the grid.
Edit Cleared Items: Should you accidentally remove a check you had marked as cleared, but the check had not cleared the bank, select this option. You will be asked to enter the Check number that was cleared by mistake. Then click on save and the check will be returned to the grid as outstanding.
You will then be asked if you are sure you want to edit this cleared check. If you say Yes, the cleared check information will appear on the form with the save option. Should you say No, the system will return to the checks tab.
Journal Entries Tab
Note: If you issue payroll checks, the net pay amount of those checks will also be included in the payroll journal entry reflected in this tab. Make sure you clear the payroll journal entry to avoid duplication.
Clear (Y/N): Click on this field on the grid and a checkmark will be entered, denoting the item as cleared. If a Journal Entry was erroneously cleared and the Remove Cleared Journal Entries has not been done, you can double click on this field and the checkmark will be removed, returning the item to outstanding status.
Listing: Click this button to print a listing of cleared and outstanding Journal
Entries. Cleared Journal Entries will appear at the beginning of the report and the outstanding Journal Entries will appear at the end. Should you wish to only print one or the other, select the specific page numbers when you send the print job to the printer. After verifying the total cleared GJ’s, you may want to wait until you have removed cleared GJ’s before printing the list of outstanding GJ’s on paper.
Remove Cleared Items: Click on this button after verifying that all of the proper Journal Entries have been cleared and the balance of outstanding Journal Entries is the correct figure used to complete your bank reconciliation. This step will remove any Journal Entries with a checkmark in the Clear (Y/N) field in the grid.
Edit Cleared Items: Should you accidentally remove a Journal Entry you had marked as cleared, but the Journal Entry had not cleared the bank, select this option. You will be asked to enter the General Journal document number. Enter the document number, click OK, then click Save. This will place the document back in the grid.
Adjustments: Should you need to make a manual adjustment to the Bank Reconciliation, use this option to record the entry. This will typically be used for an item that cleared the bank, but was not entered into the accounting system and will be entered in the following month.
Import Matrix Tab
Downloading the bank file: If available, download your monthly transactions in a csv or xls format from your bank’s website. If a csv format is not listed, see if one of their formats can be downloaded and saved in a csv format.
It is recommended that when you save the file, put your GL code and month as part of the name, i.e. GL10000 May bank download.csv. You may want to store them all in a folder called Bank downloads.
After downloading the file, open it and look at all the fields. If any of the fields, such as the date field is not in a date format, make sure you format it as a date, with an xx/xx/xx format.
Things You Should Know
- Look in the bank file to see if you have several records at the top of the file that represent daily opening and balance information. All of this should be deleted prior to importing except for the balance at the end of the month. If you wish to delete all of those top records, you can just type in your ending balance from the Statement.
- In the bank file, if any records in a date or dollar amount field have #NAME? listed, remove that field (leaving all remaining fields in that record) since it can’t be converted and will give you an error message.
- If you are using a bank format that is listed in the combo box, make sure you delete any blank records at the beginning of your bank download file.
- If you click on import more than once for the same cutoff date you will receive the message "the bank file has previously been imported for the current month. Are you sure you want to import it again?"
Choose a bank format you wish to import the statement
Note: When you are going to import for the first time, after downloading your bank file, open it up. Make a note of which column and field the ending statement balance is listed. Then make a note of which column the following is in:
Check/Deposit Number
Post Date
Amount
Credit
Debit
Description
If your bank is already listed, choose that bank. Edit and change any of the fields to match your bank file format and save it.
If your bank name is not listed, see if the format of your bank’s download matches any of the bank formats listed. If so, you can change the bank name to the bank that you use.
If your bank’s format does not match one that is listed, you can either modify one that is listed or add a new bank name and designate which columns and fields the information requested in the grid is located.
Setting up a new format:
- Enter the new bank name
- Click on Edit
- Enter the column and record for the Ending Statement Balance
- Enter the column for the remaining fields.
- For the Amount field, use the applicable example that matches your bank's file:
--If your debits and credits are in one column and both have positive numbers, there should be another column that shows debit or credit. For this you put both column letters with a comma in between, i.e. D, F.
--If your debits and credits are in one column with positive and negative dollar amounts, just enter the one column letter.
--If your debits and credits are in different columns, leave the field next to Amount blank and enter the applicable column letter for each next to Credit and Debit. - Click on Save edit.
File Name: Click on Open and find the file you downloaded from the bank.
Import: Click on the Import button, and it will import the data. When the data is imported, it will give you a list of any deposits, checks or miscellaneous debits that were found in the bank file that were not found in your GL. It is very important that you print those out for reference during the reconciliation, since they are not saved in the database.
Note: If you try to import a bank file using the same cutoff date previously used you will receive a message saying you are about to do so.
Note: if you use supplement #405 A/P Direct Deposit, you will notice on the listing of miscellaneous items found in bank file but not on the GL that the total amount of each A/P direct deposit file will be listed. The description of these amounts should include ACH Settlement. These items can be ignored since you know they cleared the bank on the settlement date. This of course is assuming the date is in your current month reconciliation.
Bank Rec Utility
This Bank Reconciliation Utility is specifically designed to help clients when you first begin using the GMS Accounting and Financial Management System. It gives you the ability to build the list of outstanding Deposits, Checks and Journal Entries prior to the beginning of entering live data into the accounting system.
Things You Should Know
- The items added through this utility have no effect on the general ledger cash balances. This information is only stored in the tables used to prepare the bank reconciliation(s).
- Once all necessary items have been added through this utility and your bank reconciliation ties to the General Ledger you should go to Tools\Organization\Default GL Codes and uncheck the Bank Rec Utility box. This will prevent you from accidently opening the utility and making a change that could have an adverse effect on your bank reconciliation.
Operating Instructions
Ending Date: Enter the date for which you are creating the outstanding lists.
GL Account: Select the GL cash account for which you are creating the outstanding lists.
Deposits
Utility Batch #: This field automatically numbers each deposit batch. This field cannot be edited. Each deposit utility batch should equal the amount of an entire deposit you need to account for. Do not enter each item in a deposit separately.
Date: Enter the date of the deposit.
Description: You can enter an optional description in this field specific to the deposit.
Amount: Enter the amount of the deposit.
Save: Once your outstanding deposits have been entered, be sure to save.
Listing: You may print a listing of deposits entered by selecting this option.
Post to Bank Rec: This will post the deposit(s) to the bank reconciliation tables.
Checks
Check #: Enter the outstanding check number you are adding to this utility.
Date: Enter the date of the outstanding check.
Payer: Enter the payer name to which the check was issued.
Amount: Enter the amount of the outstanding check.
Save: Once your outstanding checks have been entered, be sure to save.
Listing: You may print a listing of checks entered by selecting this option.
Post to Bank Rec: This will post the check(s) to the bank reconciliation tables.
Journal Entries
Note: an example of a journal entry needed in this utility may be for an online payment to a vendor that had not cleared the back as of the specified date.
Utility Doc #: This field automatically numbers each document on the Journal Entries tab. This field cannot be edited.
Date: Enter the date of this entry.
Description: You can enter an optional description in this field specific to the entry.
Debit: If this entry is a debit to the specified cash account enter the amount in this column.
Credit: If this entry is a credit to the specified cash account enter the amount in this column.
Save: Once your outstanding journal entries have been entered, be sure to save.
Listing: You may print a listing of journal entries entered by selecting this option.
Post to Bank Rec: This will post the journal entries to the bank reconciliation tables.