Search Tips
Overview
Pgm: BANKRECFM - Bank Reconciliation; standard Treeview path: General Ledger > Utilities > Bank Reconciliation
The purpose of reconciliation is to account for differences between the actual bank balance and the balance recorded in the accounting system. This is done by reviewing AP and AR checks to determine which were not deposited, and by entering adjusting transactions to make the book cash account balance equal to the bank balance. AP and AR clerks can manually mark checks and receipts as reconciled within the AP and AR modules, or alternatively, bank reconciliation can be performed by a GL administrator using the import function in the Bank Reconciliation screen.
Depending on the organization’s practices, bank reconciliation can be performed three ways.
AP/AR/PY Clerk Manual Reconciliation – Method 1
The AP, AR, and PY modules require that clerks manually mark checks, vouchers, and receipts to be reconciled. Within the Bank section of the Bank Reconciliation screen, the GL administrator can see if there are any outstanding AR deposits, AP checks, PY checks, or miscellaneous adjustments, and have the clerks reconcile these amounts within their respective screens.
GL Manual Reconciliation – Method 2
GL administrators can use the Bank Reconciliation screen to manually reconcile any outstanding AR deposits, AP checks, and PY checks by selecting the [Details] button on the Bank Reconciliation screen (Bank section) and manually marking the boxes to reconcile.
GL Import – Method 3
The [Import] button on the GL Bank Reconciliation screen allows the GL administrator to import electronic bank reconciliation files and statements for processing, and overriding the manual entry of reconciling checks and receipts. Select the bank that is being reconciled for and click the [Import] button to upload the file. Click [Process] to process the reconciliation. The Alert and Status fields display messages to verify that the import and process ran successfully, or if there is an error.
This guide shows how to carry out the steps of reconciliation within the Enterprise system.
How to Reconcile AP Checks
Pgm: APCHQREC – Reconcile Checks; standard Treeview path: Accounts Payable > Check > Reconcile Checks
This screen is used to mark unreconciled checks as reconciled, and for checks that have been previously marked as reconciled (mistakenly), it can be used to mark them as unreconciled.
Per the entered fields in the Selection Criteria section, this screen lists unreconciled AP checks on the Unreconciled tab, and AP checks that have been marked as reconciled (either manually via this screen, or automatically via the import functionality of the Bank Reconciliation screen) on the Reconciled tab.
This screen is used in conjunction with the Bank Reconciliation screen of the GL module, if AP clerks are responsible for manually marking AP checks as reconciled. If all relevant AP checks are marked as reconciled through this screen, then the Outstanding AP Checks field on the Bank Reconciliation screen will display “0.00”. Otherwise, there are unreconciled AP checks making up the outstanding balance, which can be viewed on the Unreconciled tab of this screen.
Step 1: Verify/Modify Company Code
Step 2: Verify Statement Date
This should be the date on the bank statement that is being reconciled. This date will limit the display of unreconciled checks to checks that were issued on or before the entered statement date.
Step 3: Verify Cash Account
The module will default the standard bank account for this company to this field.
Step 4: Select Checks to Reconcile
Once the previous information has been entered, the Unreconciled tab displays all the checks that match the criteria. Check the ‘Reconcile’ box beside each check to be marked as reconciled and click [Save].
To view checks that have been previously reconciled, use the Reconciled tab. This tab allows the user to unreconcile a check that has been previously marked as reconciled either manually or by the system during the check posting process.
How to Reconcile AR Receipts
Step 1: Open Reconcile Receipts Screen on the AR Receipts Menu
Step 2: Verify/Modify the Company Code
Step 3: Verify the Statement Date
This should be the date on the bank statement that is being reconciled. This date will limit the display of unreconciled checks to checks that were issued on or before the statement date.
Step 4: Verify the Cash Account
The system will default the standard bank account for this company to this field.
Pgm: ARCHQREC – Reconcile Receipts; standard Treeview path: Accounts Receivable > Receipt > Reconcile Receipts
Step 4: Select Receipts to Reconcile
Once this information has been entered, the Unreconciled tab displays all the receipts that match the criteria. The user now needs to indicate which receipts are to be reconciled - to do this, check the ‘Reconcile’ box beside each receipt that is being reconciled and click [Save].
To view receipts that have been previously reconciled, switch to the Reconciled tab, which displays all the receipts that have already been reconciled. This tab allows the user to un-reconcile a receipt that has been previously marked as reconciled.
Full Reconciliation
Checks and receipts can be manually reconciled in AP and AR, but the full bank reconciliation process must be performed in the Bank Reconciliation screen found in General Ledger > Utilities > Bank Reconciliation.
Initially, the Bank Reconciliation screen will open the first account within the query, which lists all accounts by bank code alphabetically. Select [Table Mode] to view the full list of available bank reconciliation statements.
Pgm: BANKRECFM – Bank Reconciliation; standard Treeview path: General Ledger > Utilities > Bank Reconciliation
Now, either click [Add New] or select a statement from the list.
If adding a new statement, select the bank, the account number, and enter the statement date and a description. The statement date and statement balance come from the statement that has been received or printed and is being used to perform the reconciliation.
Pgm: BANKRECFM – Bank Reconciliation; standard Treeview path: System > Global Tables > Banking > Electronic Reconciliation > Bank Reconciliation Query
Once saved, the data auto-populates. Verify/enter the statement balance and press [Display Data]. This will populate the figures for outstanding checks.
Check Reconciliation
The AR, AP, and PY Reconciliation pop-up windows can be launched through the [Details] buttons.
Select the checks to reconcile. They will be reconciled as of the statement date. Press [Save&Close] to apply changes.
Miscellaneous Adjustments
Enter miscellaneous adjustments that will not be posted as transactions (e.g. bank errors) through the Miscellaneous Adjustments pop-up window.
Adjusting transactions are entered for miscellaneous GL journal entries for such items as bank fees, interest income, or foreign exchange. Initiate adjusting transactions through the [Details] button, next to the unposted adjustments field.
The Unposted Adjustments field’s [Details] button opens the Enter Transactions pop-up window, which allows the entry of GL transactions. Once the transactions are saved, their batch must be posted using the Enter Transactions screen (Enterprise standard Treeview path: General Ledger > Transactions > Enter Transactions).
It is only possible to link to one batch of adjusting transactions. Thus, it is preferable that the adjustment batch only be posted after the reconciliation process is complete. If the adjustment batch is posted before the reconciliation process has been completed, and it turns out that additional adjustments are required, then the adjustments can be entered through the Enter Transactions screen. The batch will not be linked to the bank rec, but it will have the desired effect of making the required adjustments.
It is not possible to unlink an adjustment batch number, except by deleting a bank reconciliation record. Fortunately, deleting a bank reconciliation record does not reverse any of the work that was done (e.g., marking checks as reconciled or unreconciled).
In bank accounts that have large numbers of transactions, there might often be a small unaccounted variance between the book and bank balance. The Miscellaneous Adjustments pop-up window is intended to record specific adjustments, their amounts and the reason. If no reason can be discovered for the variance, note the variance in the Prior Period Adj Variance field and it will be carried forward into each bank reconciliation until it can be addressed. As it usually happens, there will continue to be unaccounted variances, and over time they may cancel out. This field does not create a journal entry and it is not calculated.
Pgm: BANKRECFM – Bank Reconciliation; standard Treeview path: General Ledger > Utilities > Bank Reconciliation
When entering a value into the Prior Period Adj Variance field, it will carry forward to future bank reconciliations. When a bank reconciliation is finished and it’s ready to be closed, enter a value in the Prior Period Adj Variance field. Click [Display Data] – if the prior bank reconciliation value is 0, it will wipe out the amount that was entered.
The [Open/Close] button can be used to freeze the bank reconciliation record at any time.
When performing the next month’s bank reconciliation, insert a new row and repeat the above steps.
The checks that appear in the [Details] pop-up window are those that were unreconciled as of the statement date. In the below example, the statement date is May 31st, 2017. Therefore, the pop-up window shows any currently unreconciled checks, or checks that were reconciled after May 31st, 2017:
Any checks that are already reconciled cannot be unreconciled from this screen. Instead, use their respective screens for reconciling or un-reconciling AP and AR checks in the AP and AR user guides.
If the import functionality of the Bank Reconciliation screen is used, which imports a bank statement for the reconciliation process, then this screen (Reconcile Checks) would not be used to manually mark checks as reconciled, since the import functionality would automatically mark checks as reconciled. This screen could, however, be used to manually adjust any checks that should or should not be reconciled. To access this screen from the Bank Reconciliation screen, click the [Details] button that corresponds to the Outstanding AP Checks field (shown above).
The reconciled date displays on the far right. The checkbox to reconcile checks is disabled for checks that have already been reconciled.
Reports – Bank Reconciliation
During the process of reconciliation, a report can be printed using the [Print] button. There are also two reports available to show reconciled and unreconciled checks and deposits, in the Reports submenu of AR and AP.
The only required field is the company. To show both reconciled and unreconciled AR deposits, change the value of the Show Non-Reconciled Only field.
Importing Bank Reconciliation Files
CMiC supports many bank file formats for the reconciliation of checks. If this feature is going to be used, please contact us to arrange for a session to review the process for using EFT files. The bank file format is specified for each bank in Maintain Accounts.
General Import File Layout Specifications
NOTE: The import file must have a blank line at the top.
Refer to List of Bank Reconciliation Formats for the list of Bank Reconciliation File Formats supported by CMiC.
Bank Reconciliation Example (Citizen’s Bank)
Here is an example of a bank reconciliation performed using a file supplied by Citizen’s Bank.
Specifications of File Layout
These are the specifications received from Citizen’s Bank.
Sample Data
Here is an example of a typical file that would be supplied by Citizen’s Bank.
00120469730000100001R000000712837101101
00120469730000100002R000000707009101201
00120469730000100003R000000345276101101
00120469730000100004R000000368646101801
00120469730000100007R000002005087102901
00120469730000100103R000000125336101601
00120469730000100104R000000199692101501
00120469730000100105R000000122396101801
00120469730000100106R000000108637101501
Following is the file used in this example:
NOTE: The file must be saved as a CSV file. This is the mapping of the above data to Citizen’s Bank’s specifications. In ADF, it is necessary to have the first line be blank.
Steps to Reconcile
Go to the Bank Reconciliation screen and press [Import] to launch the Import pop-up window.
Press [Import] again to select the import file.
Once reconciled, the record is removed from the Outstanding Checks pop-up window.
List of Bank Reconciliation File Formats
Refer to List of Bank Reconciliation Formats for the list of Bank Reconciliation File Formats supported by CMiC in the Standard R12 product.