Back to F110: Data Migration

Lesson 6: Importing Unreconciled Payments

This lesson explains how you import unreconciled payments and deposits in process to Acumatica ERP so that

bank reconciliation can be performed later.

Migration of Unreconciled Payments: General Information

If you are reconciling an account from a financial institution, you compare its statement to the transactions of the cash account as tracked in your system. Regular reconciliations can reduce the number of errors on accounts and make it easier to find overlooked transactions, such as missing payments or checks that have not been deposited or cashed. Aer the initial balances have been imported into the system, you need to import outstanding checks and deposits in progress and perform an initial reconciliation for the cash accounts.

Learning Objectives

In this chapter, you will learn how to do the following:

• Import the outstanding checks and deposits in transit into the system
• Create a first reconciliation statement

Applicable Scenarios

You import unreconciled payments during the data migration process to prepare the system for bank reconciliation to be performed before the beginning of Acumatica ERP usage. Then you match the balances in the company’s accounting records for a cash account to the corresponding information on a bank statement to perform the first bank reconciliation.

Creation of GL Transaction with Unreconciled Payments

The initial balance of the cash accounts is imported into the system with the trial balance import procedure. When you import payments with open balances in migration mode, these payments affect neither cash account balances nor account balances in the general ledger. If you are planning to perform bank reconciliation, you must post general ledger transactions for all unreconciled checks and deposits so that these GL transactions posted to the bank account will be available for further reconciliation. To create a journal transaction with the outstanding checks and the deposits in transit, you should add the following lines:

• A line that credits the cash account with its GL balance
• A line that debits the cash account with the cash account balance from the bank statement
• A line or lines that credit the cash account with the outstanding checks
• A line or lines that debit the cash account with the deposits in transit If you have added all outstanding checks and deposits in transit, in the resulting GL transaction, Debit Total must be equal to Credit Total.

Steps of the Reconciliation Process

Reconciliation generally has the following goals:

• To find discrepancies between account balances that have been tracked by different means (by your company in Acumatica ERP, and by a third-party financial institution through its soware)  Lesson 6: Importing Unreconciled Payments | 43
• To identify any errors
• To make the needed corrections or adjustments Aer you import the cash account balances and outstanding payments, you perform the first bank reconciliation in the system. In general, you perform the following steps when you reconcile a cash account:

1. Preparing for the reconciliation: During or aer each financial period, on the Process Bank Transactions (CA306000) form, you clear transactions for the account as you receive information that the financial institution has processed them. For details, see Bank Reconciliation: Uploading and Processing of Bank Transactions.
2. Verifying the beginning balance of the cash account: At the end of the financial period, on the Reconciliation Statements (CA302000) form, you verify that the beginning balance of the cash account in Acumatica ERP matches the beginning balance on the bank statement (or on your record of the petty cash account); if they do not match, you void the earlier statement and fix all errors. Also, you can review unreconciled transactions from previous financial periods and see which of them have been preliminarily cleared.

              If a transaction has been matched to an entry on a bank statement on the Process Bank
              Transactions form, the Cleared check box is selected for the transaction on the Reconciliation
              Statements form. Also, a transaction is cleared if a user has selected the Cleared check box for
              it on the form where the transaction was entered.
   

3. Creating the reconciliation statement: You create a new reconciliation statement for the cash account on the Reconciliation Statements form and enter the statement balance—for instance, the ending balance from the bank statement.
4. Clearing the transactions: If you have used the Process Bank Transactions form to clear bank transactions, on the Reconciliation Statements form, you click the Reconcile Processed button on the table toolbar, and the system selects the Reconciled check boxes in the table for all released documents that were processed on the Process Bank Transactions form. If you have been manually clearing transactions during the financial period for which you have created the reconciliation statement and are sure that the clearing is valid, you select the Reconciled check box for each cleared transaction. If no transactions have been cleared, by using a bank statement or other paper documents confirming transactions, you compare the transactions to the lines of the bank statement by using transaction identifiers, dates, and amounts. For each confirmed transaction, you select the Reconciled check box.

              You can perform reconciliation in as many sessions as you need. You can save the
              reconciliation statement at any time to continue to work with it later.
   

5. Adjusting the cash account balance: As you progress through the list on the Reconciliation Statements form, you can view the updated value of the difference between the reconciled balance of the cash account and the balance of the statement you have entered. You can create cash adjustments for transactions (such as bank interest or service charges) that have occurred but were not recorded to the account in Acumatica ERP. The reconciliation is finished when the difference between the reconciled balance of the cash account and the balance of the statement is 0.
6. Confirming the reconciliation results: When you have finished comparing the cash account transactions to a bank statement on the Reconciliation Statements form and the reconciled balance of the cash account is the same as the balance of the statement, you save the reconciliation statement. You can now release the reconciliation statement, which confirms that the cash account balance is reconciled for the financial period.

       If you need to learn about bank reconciliation that is performed on a regular basis, see the topics of
       the Performing Bank Reconciliation chapter.
   

 Lesson 6: Importing Unreconciled Payments | 44

Migration of Unreconciled Payments: To Import Payments and Reconcile a Cash

Account

The following activity will walk you through the process of importing to Acumatica ERP unreconciled payments (outstanding checks or deposits in transit) and then reconciling the cash account balance.

Story

Suppose that on 11/30/2024, the accountant received a bank statement with an ending balance of $288,416.25 for the 10200WH checking account. The ending balance of the checking account for 11-2024 is $284,416.25. Aer the review of the bank statement dated 11/30/2024, the accountant realized that the amounts of three outstanding checks are not included in the bank statement and that a deposit in transit has not yet arrived at the bank account. They will appear in the next bank statement, which the accountant will receive on 12/31/2024. You need to create the transactions for currently unreconciled documents in the 10200WH cash account in the system and create the first reconciliation statement corresponding to the bank statement dated 11/30/2024.

Configuration Overview

In the U100 Basic Company dataset, the following tasks have been performed for the purposes of this activity:

• On the Enable/Disable Features (CS100000) form, the minimum set of financial features has been enabled.
• On the Companies (CS101500) form, the SweetLife company without branches has been configured by performing the steps described in Company Without Branches: To Configure a Company Without Branches.
• On multiple forms, the required financial configuration has been performed, as described in the Implementing Basic Financials chapter of the Implementation Guide, including the creation of the 10200WH cash account on the Cash Accounts (CA202000) form.

                 In the provided dataset, multiple cash accounts have been configured for the SweetLife
                 company. For training purposes, you will perform the reconciliation procedure for only one
                 cash account (10200WH).
  

Process Overview

You will review the prepared Excel file with the data. Then on the Journal Transactions (GL301000) form, you will create and release a GL transaction that represents the unreconciled payments. On the Reconciliation Statements (CA302000) form, you will create the first reconciliation statement for the checking account and reconcile its balance.

System Preparation

Download the SweetLifeUnreconciledTransactions.xlsx file with the list of unreconciled transactions provided with the course.

Step 1: Importing Outstanding Checks and Deposits To create the needed batch of transactions for the outstanding checks and the deposit in transit, do the following:

1. On the Journal Transactions (GL301000) form, create a new transaction batch and specify the following settings in the Summary area:

• Module: GL  Lesson 6: Importing Unreconciled Payments | 45
• Transaction Date: 11/30/2024
• Post Period: 11-2024
• Description: Unreconciled transactions for 10200WH as of 11/30/2024

2. On the table toolbar, click Load Records From File and upload the transactions from the SweetLifeUnreconciledTransactions.xlsx file. Make sure that the batch has the rows shown in the following screenshot.

      Figure: GL batch with unreconciled transactions
   

3. In the Summary area, make sure the batch total (in the Debit Total and Credit Total boxes) is $295,416.25.
4. On the form toolbar, click Remove Hold and then Release.
5. On the Cash Account Details (CA303000) form, select the 10200WH cash account, and specify start and end dates of 11/1/2024 and 11/30/2024, respectively. Review the transactions for the cash account and the date range. (See the following screenshot.)

      Figure: Transactions and balances of the 10200WH cash account
   

6. Save your changes to the form.

Step 2: Reconciling the Cash Account Balance with the Bank Statement To create the reconciliation statement for the 10200WH cash account in the system for the November bank statement, do the following:  Lesson 6: Importing Unreconciled Payments | 46

1. On the Reconciliation Statements (CA302000) form, create a reconciliation statement and specify the following settings in the Summary area:

• Cash Account: 10200WH
• Reconciliation Date: 11/30/2024
• Load Documents Up To: 11/30/2024
• Statement Balance: 288,416.25 On this form, you reconcile the total amount of the transactions in the cash account in the system with the balance shown in the bank statement for this period. By selecting a transaction or multiple transactions in the table, you reconcile the total amount of the transactions for the 10200WH cash account in the system with the balance of the bank statement for 11/30/2024.

2. In the table, select the Reconciled check box for the following rows:

• The row with the 10/31/2024 date and the 235,205.51 amount in the Receipt column. This is the result of the trial balance import for 10-2024.
• The row with the 11/30/2024 date and the 49,210.74 amount in the Receipt column. This is the result of the trial balance import for 11-2024.
• The row with the 11/30/2024 date and the 284,416.25 amount in the Disbursement column. This is the offset entry for the bank statement balance for 11-2024, which you have imported with the list of unreconciled transactions.
• The row with the 11/30/2024 date and the 288,416.25 amount in the Receipt column. This is the bank statement balance for 11-2024, which you have imported with the list of unreconciled transactions.

3. Make sure the Cleared check box is cleared for the other listed transactions (see the following screenshot). These transactions are the outstanding checks and the deposit in transit that you have imported; they will probably be available for reconciliation with the bank statement for December 2024.
   Figure: First reconciliation statement
4. Save your changes.
5. On the form toolbar, click Remove Hold and then click Release to release the reconciliation statement.

          If you find an error in the last released statement for a cash account, you can void this
          statement. This removes the reconciliation marks from the documents and makes the
          documents available again for proper reconciliation.
   

6. On the Reconciliation Statement (CA627000) report form, select 10200WH as the cash account and 000001 (the reference number of the reconciliation statement that you have just released) as the Ref. Number.  Lesson 6: Importing Unreconciled Payments | 47
7. Click Run Report on the report form toolbar. As the following screenshot shows, the reconciled balance of the 10200WH cash account for the 11-2024 period is $284,416.25; this balance is the same as the account’s balance in the general ledger. Four GL transactions for the outstanding checks and deposits in transit that have not shown up in the bank statement (with the balance difference of –$4,000) still remain unreconciled and will most likely appear in the bank statement for the next period.

   Figure: Reconciliation statement report for the 10200WH cash account
   

You have imported unreconciled transactions for the cash account and reconciled its balance with the bank statement.  Additional Materials | 48

Additional Materials

This part provides supplemental information related to the processes and examples in the course.

Data Migration Process: Recommendations for Data Verification

Aer migrating data to a new ERP system, you should verify its accuracy, consistency, and completeness. This process helps ensure a smooth transition from a legacy system. To verify the data that has been imported into the system, use the following methods:

• For customers and vendors, make sure that the total number of master records that have been imported into the system is equal to the number of customers and vendors in the source file. You can review a summary of all imported vendor accounts by using the Vendor Summary (AP655000) report. For customer accounts, you can use the Customer Summary (AR650500) report.
• Randomly verify the information imported into particular customer accounts and particular vendor accounts. We recommend verifying the first account in the file for import, the last account, and a number of additional accounts. For example, if you have imported 90 customers, you should verify 9 customers: the first one, the last one, and 7 chosen at random. You can review the information of the imported vendor accounts by using the Vendors (AP303000) form or the Vendor Profiles (AP655500) report. For customer accounts, you can use the Customers (AR303000) form or the Customer Profiles (AR651000) report.
• Verify open balances of the customers and vendors and make sure they match with the records in the legacy system.
• For AP and AR documents, verify that the total number of the imported documents in the system is equal to the number of the documents in the corresponding source file.
• Verify a randomly selected group of the imported AP and AR documents. To review the documents, use the following reports:
• The AP Edit (AP610700) report for AP documents that are balanced and on hold
• The AR Edit (AR611000) report for AR documents that are balanced and on hold
• The AP Register (AP621500) report for released AP documents
• The AR Register (AR621500) report for released AR documents
• Verify the balances of a randomly selected group of the imported AP and AR documents on the following lists of records:
• Bills and Adjustments (AP3010PL)
• Checks and Payments (AP3020PL)
• Invoices and Memos (AR3010PL)
• Payments and Applications (AR3020PL)

                To review the open balances in the document’s currency, review the Balance column. This
                column is hidden by default; you can add it by using the Column Configuration dialog box.
  

 Additional Materials | 49

Data Migration Process: Migrating Multicurrency Documents

If your company works with foreign vendors and customers, when migrating from a legacy system, you need to first configure Acumatica ERP for working with multiple currencies, and then import the documents and upload the balances of any accounts denominated in a foreign currency.

Support of Multiple Currencies

Acumatica ERP supports the processing of documents and transactions in foreign currencies in the following functional areas:

• General ledger
• Cash management
• Accounts payable
• Accounts receivable
• Contracts
• Taxes (you could report taxes in a currency other than the base currency)
• Sales orders
• Purchase orders
• Purchase requisitions
• Time and expenses
• Projects

         Transactions involving fixed assets, deferred revenue, and inventory can be processed in the base
         currency only.
  

Import of Documents in Foreign Currencies

To prepare the system for importing documents in foreign currencies to the system, the following requirements must be met:

• The Multicurrency Accounting feature must be enabled on the Enable/Disable Features (CS100000) form.
• The currency rate types and currencies specified in the import data must be activated on the Currency Rate Types (CM201000) form and the Currencies (CM202000) form, respectively.
• Currency rate override must be allowed for the vendors and customers for which you are going to import documents in foreign currencies. Currency settings are specified for these records on the Vendors (AP303000) form and the Customers (AR303000) form, respectively. This is needed so that the system can change the rate in the imported documents to upload exactly the same document amounts in the base and in foreign currencies as you have in your legacy system.
• In import scenarios that will be used for import, the appropriate fields with the currency, currency rate type, and currency rate must be mapped to the appropriate columns in the files with the data to be imported. Aer you import the documents to the system, you need to verify the balances of customers and vendors in the base and foreign currencies to make sure that all data was imported correctly.

Import of Trial Balances

If you have accounts maintained in a foreign currency (or accounts denominated in a foreign currency), you need to import balances in both base and foreign currencies for each of these accounts. Thus, in the Excel file with the data, you need to create two columns with balances: YTD Balance, which holds the balance of accounts in the base  Additional Materials | 50

currency, and Currency YTD Balance, which contains the balance of accounts in the foreign currencies assigned to these accounts. Both columns must have the currency or text format. For the accounts that are not denominated or are denominated in the base currency, the YTD Balance and Currency YTD Balance columns hold the same value. Aer you have released the imported trial balance with multiple currencies, the generated batch on the Journal Transactions (GL301000) form has the following specifics:

• The only currency available in the Currency box in the generated batch is the base currency of the company. The system always shows the base currency as the transaction currency in the trial balance batches, even though the imported account balances might be denominated in different currencies.
• When you open the trial balance batch on the form, the amounts are shown in the transaction currency which may be different for different accounts: the currency of the denomination for denominated accounts and the base currency for other accounts. In this currency mode, the debit total is not supposed to be equal to the credit total, because the summed amounts are the balances in different currencies. When you toggle the currency in the batch to the base currency, the debit total becomes equal to the credit total because in this mode all the amounts are reflected in the base currency.
• In the records with the denominated accounts specified in the lines, the debit and credit amounts are shown in the currency of denomination. In all other records in the table, the debit and credit amounts are shown in the base currency.