Bank Statement Reconciliation - Process Uploaded Statement

Overview

The Process Upload Statement reconciliation method imports bank statement data sent by the bank in electronic form. The user then matches bank statement lines to previously entered Rootstock system transactions, either automatically (using the Auto-Match feature - see below) or manually.

Transactions created by the bank previously unknown to the Rootstock system (e.g., bank fees) are displayed, and when they are selected, matching Rootstock system transactions are created.

Navigation

General Ledger > Bank Statement Reconciliation

  IMPORTANT  Rootstock released a new version of Bank Statement Reconciliation - Process Upload Statement 3.0. If the user still wants to continue using the older version 2.0, they can skip the change to the Site Map- Menu Bar record for Bank Statement Reconciliation at the time of deployment. Otherwise, the user will always be redirected to the newer version 3.0 of the VF page.

Prerequisites

• The Bank Account must be set up (see Bank Account), including the various Mapping / Translation String fields (see Bank Statement Reconciliation - Setup Requirements). The Bank Account’s Reconciliation Method must be set to Download.

• If the Bank Account’s currency is the same as the company's home currency, the Single Currency Payment Variance Account on the AR Control object must be populated. If the Bank Account’s currency is other than the company's home currency,  the Multicurrency Payment Variance Account on the AR Control object must be populated (see AR Control).

• The Bank Reconcile - Interest Income Acct on the AR Control object must be populated (see AR Control).

• The Bank Reconcile - CC Processing Fee Acct on the AR Control object must be populated (see AR Control).

• The Bank Reconcile - Interest Expense Acct on the AR Control object must be populated (see AR Control).

• If the statement to be uploaded is the first statement for the Bank Account to be entered in Rootstock, an initial bank statement for the Bank Account must have been entered and Finalized. See Bank Statement Reconciliation - First Reconciliation. Otherwise, the Opening Balance of the statement to be uploaded will be incorrect.

• If the statement to be uploaded is not the first statement for the Bank Account to be entered in Rootstock and the previous statement still needs to be Finalized, the new statement can be uploaded and reconciled, but it cannot be Finalized. This matters because General Ledger (GL) entries for variances and transactions originating on the statement (e.g., bank fees) are created when a statement is Finalized. Therefore, these entries can only be posted once all prior statements have been Finalized. Unless fiscal periods for prior, un-posted bank statements have been held open when these entries eventually are posted, they will post in later open periods.

Bank Account Section

The screen that appears at the start of the process shows the Company and Account Number fields.
 

Company:  Displays the user’s current company.

Account Number:  The user selects the GL cash account assigned to the Bank Account for which a statement will be processed. Suppose the user selects a Bank Account assigned to the Manual Entry Reconciliation Method. In that case, the Bank Statement Reconciliation - Process Statement Manually method will be initiated instead of the Process Upload Statement reconciliation method.
 

Bank Account and Statement Section

The top section of the main screen shows the Bank Account and Statement-related Fields and four buttons labeled Upload New Bank Statement, Bank Balance to GL, Undo All, and Finalize.

Fields
 

Company:  Financial Company for which this record pertains.

Bank Account Name:  User-defined Bank Account Name, up to 50 characters in length.

Statement Date:  The date of this bank statement. No Bank Statement Lines dated after the Statement Date will be accepted.

Statement Header: Initially, this shows a drop-down menu of existing uploaded statements. The user will either call up an existing statement or upload a new one by pressing the Upload New Bank Statement button.

Last Statement Date:  The date of the last bank statement before the current upload.

Status:  Indicates the status. Values are:

• Loaded: Transactions have been loaded into GLBANKSTMTD from BRATO, but nothing has been reconciled yet.

• In Progress: Reconciliation has started but is incomplete for downloaded statements. For manually entered statements, the header.

• Reconciled: The statement has been reconciled with the System Transactions.

• Posted: The statement has been finalized, and any variances and/or newly created system transactions added via the reconciliation process are posted to the GL.

• Posted with Errors: An unexpected error occurred during the Finalize step.

Buttons
 

Upload New Bank Statement: If pressed, a grid of not-yet-loaded Bank Account Reconcile Upload (BRATO) records is displayed (see Bank Statement Reconciliation - Bank Statement Upload). The user can select the records from the grid and save them to be available to select on the 'Statement Header' field.

The remaining three buttons are activated and visible once an existing statement is selected on the 'Statement Header', or a new statement uploaded from BRATO is selected.

Bank Balance to GL:  When clicked, a pop-up displays important fields from the Statement Header in Bank and Home Currency.

Undo All: If clicked, everything done since the Finalize button was last clicked is reversed.

Finalize: If clicked, the statement status is set to Posted, and GL postings are made for variance transactions and transactions originating on the statement (e.g., bank fees).

Reconciliation of Statement to System Transactions (Bank Currency) Section

Fields (Display only)
 

Button

Refresh System Transaction Values:  When clicked, the system will refresh the accumulated values on the above fields populated from the statement header.
 

Unreconciled Transactions Section

Buttons:
 

Auto Match: This button initiates the Auto Match function.
 

Importance: The Auto Match function is vital because, in most cases, it can perform most of all the individual reconciliations in only a few clicks - in some cases, only two.

Overview: The Auto Match function suggests matches between statement lines and system transactions by comparing the unreconciled system transactions displayed in the System Transactions Grid to the unmatched statement lines displayed in the Statement Transactions Grid. The matches it suggests are then displayed in the Reconciled Transactions Section, but before they are reconciled, the user must review and confirm the system's suggestions. It will be common for the user to accept most of the suggested matches and reject a few.

Method of Matching: The criteria used for matching varies with the transaction type. The most precise matching available happens with Accounts Payable (AP) checks because check numbers uniquely identify a check. Therefore, the system will not suggest that check number 100 on the bank statement refers to check number 1001 in the AP data. Likewise, if the bank statement shows that the bank paid 1,234.00 units of currency for check number 100 and the AP data shows that check number 100 had a value of 1,234.99, the system will suggest matching them with a variance of 0.99.

That level of assurance is lacking for other kinds of payments, bank deposits, and transfers. It is possible that the bank's references for EFT and ACH payments, deposits, and transfers will match the user company's internally generated references as saved in the original Rootstock system transactions, but nothing guarantees that they will. Therefore, Auto Match will not suggest matches for these types of transactions unless the amounts of the bank statement transaction and the system transaction are completely identical. Users may still perform imperfect matches and create the resulting variance transactions, but they must do so manually (see the Reconcile Selected button below).

An area of the screen is reserved for displaying suggested matches. Each suggested match has a checkbox indicating whether it is to be confirmed. All suggested matches are checked initially, but users may selectively uncheck them.

Accept Auto Match: This button does nothing unless the Auto Match button has been pressed first. It causes a menu of checkboxes to display, from which users can select the degree of certainty that they wish to accept:

• Accept perfect matches.

• Accept perfectly matching references with variances.

• Accept partially matching references with no variances.

• Accept partially matching references with variances.

If no suggested matches fit a particular Accept option, the checkbox for that option will be disabled. In general, it is always safe to accept perfect matches. For the other degrees of certainty, it is best to review the selected matches and, if necessary, deselect inappropriate ones.
 

Clear Auto Match: This button does nothing unless the Auto Match button has been pressed first. It causes the Auto Match area to be cleared.

Reconcile Selected: This button is used for manual matching (as opposed to Auto Match).

The usual way to manually match system and statement transactions is to select a single statement transaction from the Statement Transactions Grid and a single system transaction from the System Transactions Grid, then press Reconcile Selected. This (1) will move the system transaction and the statement transaction to the Main Screen - Reconciled Transactions Section, and (2) will create a variance transaction if the system and statement amounts don't agree. 

Alternatively, a single statement transaction may be matched to multiple system transactions (for example, if the bank reported multiple cash receipts as a single deposit). The user can also select a statement transaction of a positive or negative amount and can reconcile it with single or multiple system transactions. When doing this, only one statement transaction may be selected, and the sum of the selected system transaction amounts must equal the selected statement transaction amount (i.e., there can be no variance).

  IMPORTANT  When using the Reconcile Selected button to perform manual reconciliation between a Statement Transaction and a System Transaction with different Transaction Types, the flag 'No Statement Recon Txn Type Restriction' on the Bank Account must be set to true. Otherwise, the message “The System & Statement Transaction Types should match to reconcile" will be displayed, and the reconciliation will not be performed.
 

 Query Unreconciled: clicking this button causes the System Transactions grid to be refreshed.

Add System Transaction for Selected:  When clicked, the Add System Transaction for Downloaded popup is displayed, and the user is allowed to enter transactions of the following types that originated with the bank and not from the Rootstock system.

• AP ACH/EFT Fees

• AR ACH/EFT Payment Reported by Bank

• Bank Fees/Service Charges

• Bank Reports Credit Card Deposit

• Credit Card Processing Fee

• Interest Income

• Interest Expense

• All Other Adjustments

System Transaction Summary:  When clicked, the Add System Transaction Summary popup is displayed and will show the accumulated values of System Transactions as a summary.

Statement Transactions Grid:
 

Fields
 

Date:  Bank transaction date.

Reference:  Identifying reference for the bank transaction. For deposits, it is the deposit identifier entered in cash receipts. For AP checks, it is the check number.

Tran Type:  Bank transaction type must match the transaction type of the system transaction to which the statement transaction is matched.

Stmt Amount:  The Bank transaction amount is stated in Bank Account currency.

System Transactions Grid:
 

Fields
 

Date: System transaction date.

Payment Reference: Identifying references recorded by users.

Tran Type: System transaction type must match the bank-supplied transaction type from any statement line to which the system transaction is matched.

Check #: Check the number assigned by the system.

System Amount: System transaction amount stated in Bank Account currency.

Payee:  Displays the Vendor Name associated with this System transaction.

Cash Receipts:  Displays the Cash Receipt associated with this System transaction.

Reconciled Transactions Section

In this section, reconciled system and statement transactions are displayed together.

Fields
 

Payment Reference: See System Transactions Grid->Payment Reference

System Check#: See System Transactions Grid->Check #

Stmt Tran Reference: See Statement Transactions Grid->Reference

System Txn Date: See System Transactions Grid->Date

Date per Bank: See Statement Transactions Grid->Date

System Tran Type: See System Transactions Grid->Tran Type

Stmt Tran Type: See Statement Transactions Grid->Tran Type

System Txn Amount: See System Transactions Grid->System Amount

Reported Stmt Amount: See Statement Transactions Grid->Stmt Amount

Added Transaction?:  If the transaction was added using Add System Transaction for Selected button, then this will be set as true.

SEE ALSO
 

Bank Account
Bank Statement Reconciliation - Overview
Bank Statement Reconciliation - Setup Requirements
Bank Statement Reconciliation - First Reconciliation
Bank Statement Reconciliation - Bank Statement Upload 
Bank Statement Reconciliation - Process Statement Manually