Bank Reconciliation - Overview

The Bank Reconciliation feature is designed to assist you in reconciling the transactions on your Bank Statement to the transactions in your GL. The bank reconciliation process leverages the PointForce iTopia (PFiT) interface, and is designed to automatically present you with all your accounts payable transactions, accounts receivable summary postings, invoicing EFT transactions and your journal entry postings for matching to the corresponding Bank statement.

The following diagram displays how the PointForce Enterprise (PFE) programs and PFiT views interact in the Bank Reconciliation process: (a more detailed explanation follows below the diagram)

Here is a high level overview of the bank reconciliation process:

• A Bank Account Code record is created in the Bank Master view which has a specific Bank GL Account associated with it.

• Once the Bank Account Code record is saved in the Bank Master view, the replication process is triggered for transactions in specific programs in PointForce Enterprise (PFE) that are posted to the Bank GL Account. The PFE programs that are utilized by the Bank Reconciliation process include:
  • Computer Check Register (AP43)
  • Manual Check Register (AP51)
  • Cancelled Check Register (AP81)
  • Cash Receipts/Adjustments (AR31)
  • Print/Post Journal Vouchers (GL31)
  • Post Standard Journals (GL80)
  • Invoice Register (IN44) (*only for EFT transactions that hit the bank directly)

• These transactions are replicated to the GL Transactions view in PFiT. The GL Transaction view works dynamically with the Bank Statements view.
  • Once you have your bank statement, you can create a Bank Statement record in the Bank Statements view. The Bank Statement record includes the following manually entered fields: bank opening and closing balances, bank statement unrecorded debit/credit amounts, bank errors, depositor errors, total number of transactions on the statement, and comments (as required).

  • In the GL Transactions view, assign the bank statement date to the transactions associated with that bank statement. This identifies to the system the statement to which each transaction belongs. If a transaction has no statement date assigned to it, then it remains in the GL.

  • In the Bank Statement view, the system calculates the following fields based on the statement date assigned to the transaction in the GL Transactions view and the transaction date associated with the record: deposits in transit, outstanding checks, outstanding postings, reconciled bank balance, GL closing balance, reconciled GL balance and reconciled variance.

• When all of the transactions in the GL Transactions view have the appropriate statement date assigned to them, then the Bank Statement view should display a reconciled variance of zero for the statement date.

 
Setting up Bank Reconciliation

In order to utilize Bank Reconciliation, you must set up the following views in PFiT along with the following programs in PFE. For complete details on each field, please refer to the Bank Reconciliation online help for each view.

1. Bank Master:

   The Bank Master view allows you to create and maintain a Bank Account Code(s) which is used to associate your financial institution’s banking details with a Bank GL Account from PFE.

   • Note: It is the assignment of the Bank GL Account in this view that triggers the process of accumulating AP/AR/GL/IN transactions (in the GL Transactions view) for reconciliation purposes.

2. GL Transactions:

   The GL Transactions view is automatically populated with GL transactions from the following PFE programs that relate to the Bank GL Account defined in the Bank Master:

   • Computer Check Register (AP43)
   • Manual Check Register (AP51)
   • Cancelled Check Register (AP81)
   • Cash Receipts/Adjustments (AR31)
   • Print/Post Journal Vouchers (GL31)
   • Post Standard Journals (GL80)
   • Invoice Register (IN44) (*only for EFT transactions that hit the bank directly)

   There is no set up required for the GL Transactions view.

3. Maintain Bank Codes (AP01):

   In AP01, you must set up an Accounts Payable Bank Code that corresponds to the Bank Account Code you set up in the Bank Master view. This Bank Code will have the same Bank GL Account as the Bank Account Code in PFiT.

   • Vendors are assigned this Bank Code in Maintain Vendors (AP13).

   • This Bank Code is used when creating Accounts Payable transactions in AP30, AP40, AP50 and in AP80.

   • It is this bank code and the associated Bank GL Account that triggers the transaction to be replicated from AP43, AP51 and AP81 to the GL Transactions view in PFiT.

4. Maintain Bank Codes (AR02):

   In AR02, you must set up an Accounts Receivable Bank Code that corresponds to the Bank Account Code you set up in the Bank Master view. This Bank code will have the same Bank GL Account as the Bank Account Code in PFiT.

   • Customers are assigned this bank code in the Customer Master (SU13) view.

   • This bank code is used when creating Accounts Receivable transactions in AR30.

   • It is this bank code and the associated Bank GL Account that triggers the transaction to be replicated from AR31 to the GL Transactions view.

5. Maintain Standard Journals (GL20)/Post Standard Journal Vouchers (GL80):

   In GL20, you must set up each standard journal voucher that you want to see in the GL Transactions view with the Bank GL Account that was used to set up the Bank Account Code in the Bank Master view. When you run the Post Standard Journal Vouchers (GL80) program, all standard journal entries that are posted to the Bank GL Account will be replicated to the GL Transactions view.

6. Enter Journal Vouchers (GL30)/Print/Post Journal Vouchers (GL31):

   In GL30, you must set up each voucher that you want to see in the GL Transactions view with the Bank GL Account that was used to set up the Bank Account Code in the Bank Master view. When you run the Print/Post Journal Vouchers (GL31) program, all vouchers that are posted to the Bank GL Account will be replicated to the GL Transactions view.

   • Note: You cannot create 'accrual' type GL transactions for a Bank Reconciliation GL Account. Because accrual transactions are reversed when the Final GL Report (GL81) runs, the system will not allow accrual transactions to be entered for a GL Account that is associated with Bank Reconciliation.

7. Invoice Register (IN44):

   There are two scenarios in which EFT transactions will be replicated to the GL Transactions view once acceptance codes are entered in IN44:

   1. When the AR02 Bank for the EFT record (Visa, MasterCard, American Express, etc.) is set to a Bank GL account that is associated with bank reconciliation.

   2. When the Payment Method (IN08) has a GL override set to a bank reconciliation GL account for EFT type transactions (Visa, MasterCard, American Express, Company Credit-Retail, etc.).

8. Bank Statements:

   In the Bank Statements view, you must set up a new Bank Statement record for each Bank Statement. When you create a new Statement, you enter the bank opening and closing balances, bank statement unrecorded debit/credit amounts, bank errors, depositor errors, total number of transactions on the statement, and comments (as required).

   • The system calculates the following fields based on the statement date assigned to transactions in the GL Transactions view: deposits in transit, outstanding checks, outstanding postings, reconciled bank balance, GL closing balance, reconciled GL balance and reconciled variance.

   • As you assign the current statement date to transactions in the GL Transactions view, you create a complete picture of what is on the bank statement and what remains is in the GL.

   • The reconciliation process is outlined in the next section.

We are going to walk through an example in order to illustrate the reconciliation process. This example is for a Bank Statement dated January 31, and we are working on the reconciliation at the end of the month or in the first week of February.

All of the transactions from PFE have been replicated into the GL Transactions view because we have set up our Bank record (that identifies the Bank GL Account) in the Bank Master view. As a result, transactions from the AP, AR, GL and IN systems in PFE are all posting to the correct Bank GL Account and have been replicated into the GL Transactions view as expected.

1. In the bank reconciliation process, we are recreating a manual bank reconciliation with system generated data. For the January 31st reconciliation, here is an example of the manual bank reconciliation:

   

2. To begin the bank reconciliation process, we will set up our current Bank Statement in the Bank Statements view. We will base this on a fictitious bank statement dated January 31st; to see the details of the bank statement, click here.

   Using the details from this bank statement, we will enter the following information as we create our January 31 bank statement record in the Bank Statements view:

   • Opening Bank Balance
   • Closing Bank Balance
   • Bank Errors from the previous bank statement or as reported by the customer
   • Bank Statement Unrecorded Credit Amount (bank charges, monthly fees, etc. that are not yet recorded in the GL).
   • Bank Statement Unrecorded Debit Amount (interest earned, additions/debits by the bank that are not yet recorded in the GL).
   • Depositor Errors
   • Comment
   • Total Statement Transactions

   Here is an example of what the Bank Statement view looks like for our January 31st statement:

   

   • The fields that correspond to the manual reconciliation have been identified in (red).

3. In the GL Transactions view, we will assign the current statement date to all transactions in the GL that are on the bank statement.

   Here is an example of the GL Transactions view:

   

   • The first two records in this example have been assigned to the January 31st statement.

   • The last two records remain in the GL to be assigned to a future statement date.

4. Now that all of the transactions for the January 31st statement have been identified in GL Transactions, we will open the Bank Statement view again and look at the January 31st record. Here is an example of what our January 31st record looks like:

5. Using the data that remains in the GL, the system calculates the following information:
   1. The Deposits in Transit or the AR31 Cash Receipts that have not been posted to the bank for this statement (with a transaction date that is less than or equal to the current statement date).
      • Following our example, the Deposits in Transit are $31,437.90. This is the same as the Plus any additions or deposits not on bank statement ("in transit"), item (1) from the manual bank reconciliation.

   2. The Outstanding Checks or the AP43/51 checks that have not been posted to the bank for this statement (with a transaction date that is less than or equal to the current statement date). Note: Checks that have been cancelled by AP81 do not display in the GL Transactions view.
      • Following our example, the Outstanding Checks are $290.00. This is the same as the Minus any checks issued but not yet cleared by the bank ("outstanding checks"), item (2) from the manual bank reconciliation.

   3. The Outstanding Postings or the GL31 and GL80 transactions that have not been posted to the bank for this statement (with a transaction date that is less than or equal to the current statement date).
      • Following our example, the Outstanding Postings are $0.00. This category is not included in the manual reconciliation, but this amount would be found in item (1) (for debit transactions) or in item (2) (for credit transactions) from the manual reconciliation.

6. The system generates the reconciled balance for the Bank and the GL:

   The Reconciled Bank Balance is calculated as follows:

   (Bank Closing Balance) + (Deposits in Transit) - (Outstanding Checks) +/- (Outstanding Postings) +/- (Bank Errors)

   • Following our example, the Reconciled Bank Balance is $199,938.52. This is the same as the Reconciled Bank Balance, item (B) from the manual reconciliation.

   The Reconciled GL Balance is calculated as follows:

   (GL Closing Balance) - (Bank Statement Unrecorded Credit Amount) + (Bank Statement Unrecorded Debit Amount) +/- (Depositor Errors)

   • Following our example, the Reconciled GL Balance is $199,938.52. This is the same as the Adjusted GL Balance, item (D) from the manual reconciliation.

7. When the 'Reconciled Bank Balance' equals the 'Reconciled GL Balance' then the Reconciled Variance field equals zero, indicating that your Bank and your GL are in balance for the current statement. Following our example, the Reconciled Variance is $0.00.
   • If the Reconciled Variance is anything other than zero, you must investigate which transactions on your bank statement are not on your GL and vice versa.

   • You must follow standard account procedures to reconcile your Bank Statement to your GL.

8. At some point, you will create Journal Entries to properly add the 'Bank Statement Unrecorded Credit Items', 'Bank Statement Unrecorded Debit Items' and 'Depositor Error' transactions to the GL via GL30/31.
   • Once the GL31 transactions are posted to the GL Transactions view, you must assign the current statement date to these transactions.

   • These transactions are now in the GL twice: once in the manually entered 'Bank Statement Unrecorded Credit Amount', 'Bank Statement Unrecorded Debit Amount' and 'Depositor Errors', and a second time as the Journal Entry posting in the GL Transactions view which have a statement date assigned to them thereby affecting the 'Reconciled Bank Balance' and 'Reconciled GL Balance' for the statement period.

   • In order to return to the current statement and GL to balance, you must enter a zero into the 'Bank Statement Unrecorded Credit Amount', 'Bank Statement Unrecorded Debit Amount' and 'Depositor Errors' fields in the Bank Statement view. If the transactions entered via GL30/31 equal the values manually entered, then your reconciled variance will again return to zero.

When you set up bank reconciliation at your site, the GL balance is set to zero which may not match your current GL balance. In addition, there may be transactions that already exist in your GL that you want brought over to the GL Transactions view i.e. transactions that pre-date the start up of your bank reconciliation.

In order to set your GL balance to the correct amount, and to add older transactions to the GL Transactions view, a Microsoft Excel spread sheet has been created. With the assistance of your Tecsys SMB Business Consultant, the information entered in this spread sheet is loaded directly into the the GL Transactions view.

• This spread sheet is called BankRecBRTRANS.xls and is available from your Tecsys SMB Business Consultant.

Here are the steps to populate the BankRecBRTRANS.xls spread sheet and then add the data to the GL Transactions view:

1. Here is an example of a completed spread sheet:

   

   Notes concerning the spread sheet:

   • Do not remove the header line.
   • Checks must be entered with a positive value.
   • The opening balance muse be a positive value. If a negative opening balance exists, enter a positive value on the spread sheet and your Tecsys SMB Professional Services Consultant will move the amount from a debit to a credit in the BRTRANS file.
   • The STARTREC field must have a value for every record: Y for the starting record and N for every additional record.
   • Formatting is important:
     • Negative values must be entered with a negative sign, not brackets (keeping in mind that checks cannot be entered as negatives).
     • Do not include commas.
     • The number of decimals is irrelevant.
     • The format "Number" works well.
   • Use capital letters in the TRANSTYP field.

2. The following columns exist in the spread sheet:
   • Bank - enter the Bank code that corresponds with the Bank Master record. Warning: Do not enter the one digit bank code that was created in Maintain Bank Codes (AP01) or (AR02) in PFE.

   • Transtyp - enter the one character code for the Transaction Type; ensure to enter the characters in upper-case. The following list identifies the valid options:
     • C - Check
     • D - Deposit
     • J - Journal Entry
     • M - Manual Check
     • S - Standard Journal

   • Refrnc - enter the Reference Number associated with the transaction. Be sure to enter the transactions as they will appear on the bank statement.

   • Transdat - enter the Transaction Date associated with the record. You must enter the date in YYYYMMDD format.

   • Amount - enter the Amount of the transaction. If the transaction is a computer or manual check, enter the transaction as a positive value. For all other transactions with a credit amount, enter the amount as a negative.

   • Startrec - the starting record or opening balance of the GL (please see #3 below for details on calculating the GL opening balance). Enter a 'Y' in this field (without the quotes) if the record is the Starting Record for the GL, otherwise enter an 'N' in this field.
     • Note: If this field has a value of 'Y' or blank, the record will be assigned a transaction date of January 1, 2008.

     • Note: Only one record can have a Startrec=Y, unless you are manually populating multiple banks. In this case you can have more than one record flagged as Y, but you must ensure that the REFRNC field is unique, for example:

     

3. If your GL opening balance is not zero, then you must determine the GL opening balance and create a record in the spread sheet that will add this value to the GL Transactions view. If you have been manually reconciling your bank to the GL, then this value should not be difficult to determine.
   • Let's assume that you begin bank reconciliation in February. You want to ensure that the GL balance is accurate as of your last reconciliation (January 31st). In the example above, the GL opening balance for February is $168,790.60.
     • The GL opening balance is calculated as the GL balance less all of the outstanding entries.
     • This net amount is then flagged as the start rec in the spread sheet, i.e. enter a 'Y' in the STARTREC column associated with this record.

   • Because this is a fictitious entry, we have given the record a Transaction Type of 'J', a Reference Number of '999', and a Transaction Date that is in the past so it is guaranteed to be the first record in the GL. (Note: The system will assign a date of January 1, 2008 to the starting record.).
     • If you are entering an opening balance for multiple banks, we recommend using the following reference numbers: 999, 998, 997, etc. depending on the number of banks you have.

4. Next, you must identify the transactions in your GL that belong in the GL Transactions view.
   • Assume that you begin bank reconciliation on February 5th (i.e. you create the Bank Master record that identifies the Bank GL Account and transactions begin to populate the GL Transactions view on February 5th), and there are transactions from February 1st to February 4th that are not in the GL Transactions view. In addition, there are transactions from the end of January that will likely be on the February Bank Statement that need to be brought into the GL Transactions view.

   • Enter these transactions on the spread sheet as they will appear on the bank statement. Note: These records must not be included in the GL opening balance amount.

   • In the Starting Record field, enter a 'N' for each transaction.

5. When all of the information is correctly added to the spread sheet, save the spread sheet as a tab delimited text file in the 'cnvrsn ' directory.

6. Contact your Tecsys SMB Professional Services Consultant to run a "pass" program designed to move the transactions from your PFE GL file into the GL Transactions view.

7. Once the pass program has been successfully run, verify that the transactions have been added to the GL Transactions view correctly.