Lesson 4: Importing Trial Balances

This lesson describes how you migrate account balances to Acumatica ERP by using the trial balance import

functionality.

Migration of Trial Balances: General Information

When migrating company data from a legacy system to Acumatica ERP, you import trial balances to the system to initialize the open balances of the general ledger accounts without migrating general ledger transactions for each historical period.

Learning Objectives

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

Prepare Acumatica ERP for importing trial balances
Prepare the trial balance to be imported
Import the trial balance
Review the generated general ledger transactions

Applicable Scenarios

You import trial balances to migrate account balances to the system for each period and to be able to prepare accurate financial statements for the periods that precede migration to the new system.

System Configuration Before Import of the Trial Balance

Before importing the trial balance, you need to choose the period to start keeping records in Acumatica ERP and decide for which periods you want to import the history of balances. We recommend that you import trial balances for at least a year to be able to produce comparative financial statements for the company later. For example, if you start using Acumatica ERP in 01-2025, you should import the trial balance for the 2024 financial year. In the legacy system, you should prepare the data to be imported—that is, run the trial balance report for each period that you are going to import. Depending on the way the account balances are presented in the legacy system, you select the Sign of the Trial Balance option on the General Ledger Preferences (GL102000) form as follows:

If the Normal sign is selected for the trial balance representation in the system, then debit balances of asset and expense accounts and credit balances of liability and income accounts are shown with the plus sign.
If the Reversed sign is selected, then debit balances of asset and expense accounts are shown with the plus sign, and credit balances of liability and income accounts are shown with the minus sign. You specify the sign of the trial balances based on the following equations:
If credit balances of liability and income accounts are presented with a plus sign, you select the Normal option. Normal balances satisfy the following validation criterion: Total Balance of (Asset Accounts + Expense Accounts) = Total Balance of (Income Accounts + Liability Accounts)

If the credit balances of liability and income accounts are presented with a minus sign, you select the Reversed option. Reversed credit balances, which are implemented in some applications, satisfy the following validation criterion. Lesson 4: Importing Trial Balances | 32

     Total Balance of (Asset Accounts + Expense Accounts) = – Total Balance of (Income
      Accounts + Liability Accounts)

    This criterion can also be expressed as follows.
     Total Balance of (Asset Accounts + Liability Accounts + Income Accounts + Expense
      Accounts) = 0


       The Sign of the Trial Balance setting on the General Ledger Preferences form also aﬀects the
       representation of the trial balance reports and inquiries. Aer you have finished the trial balance
       import, you may change the Sign of the Trial Balance option to have the needed representation of
       reports and inquires.

Preparation and Editing of the File to Be Imported

You prepare the trial balances for each company or branch, considering the following rules:

If you are migrating data to a company whose type is Without Branches, you import the trial balance for entire company—that is, in the created trial balance entry, you select the company in the Company/Branch box on the Trial Balance form.
If you are migrating data to a company whose type is With Branches Not Requiring Balancing, you import the trial balance for entire company. In the created trial balance entry, you select the company in the Company/ Branch box on the Trial Balance form. The prepared trial balance must contain account balances for all company branches.
If you are migrating data to a company that has branches requiring balancing (that is, if the company type is With Branches Requiring Balancing), in the created trial balance entry, you select an individual branch in the Company/Branch box and import data to each branch separately. The prepared trial balances should contain account balances for each of the company branches. From the legacy ERP soware, you should export the prepared trial balance data to a CSV file or to an Excel spreadsheet. The prepared file should include the following columns:
Account: This column contains the numbers of the accounts whose balances will be imported. Do not include the balance of the YTD Net Income account because it is calculated automatically from the imported balances of income and expense accounts. If you use subaccounts to record your financial data, the Subaccount column is also required for import. For more information about subaccounts, see Subaccounts: General Information.
YTD Balance: The ending balance of the account-subaccount pair for the period in the base currency. For this column, the Currency or Text format setting can be specified in the Excel file. You can specify normal balances or reversed balances of accounts.

Currency YTD Balance: The ending balance of the account-subaccount pair in the currency of denomination specified for the account for the period. For this column, the Currency or Text format setting can be specified in the Excel file. This column is required if you plan to use multiple currencies—that is, if the Multicurrency Accounting or Multiple Base Currencies feature is enabled on the Enable/Disable Features form.

               Make sure that you have specified the currency YTD Balance for the accounts denominated to
               a foreign currency. Otherwise, the balances in the account currency will be incorrect, and you
               will not be able to reconcile the account balances with the bank statements.

    If the account is not denominated or is denominated to the base currency, the YTD Balance and Currency
    YTD Balance columns hold the same value.

Description (optional): An optional description of the account that you can add for your convenience while you work with the file. The description will not be uploaded to the system. Lesson 4: Importing Trial Balances | 33
```
        You can include multiple columns with the balances for diﬀerent financial periods in one file.
```

Import of Trial Balances

If you are importing multiple trial balances, you have to import the trial balances one by one, from the earliest period to the latest one, because each subsequent general ledger batch is generated in the amount of the diﬀerence between the trial balance for the previous period and the current imported balance. You import trial balances by performing the following operations:

You import the trial balance and validate the imported data on the Trial Balance (GL303010) form. As a result of the validation process, the system maps the accounts (or account-subaccount pairs, if applicable) of the trial balance to the internal accounts (or account-subaccount pairs, if applicable) in the system. The system shows an error for the records that cannot be mapped during validation.
Aer you complete the mapping and make sure the debit total and credit total are in balance, you release the trial balance on the same form.
You release the generated batch on the Journal Transactions (GL301000) form. If the Automatically Post on Release check box is cleared on the General Ledger Preferences (GL102000) form, you also need to post the generated batch on the Post Transactions (GL502000) form. For details on processing batches, see GL Transactions: General Information. On release and posting of the batch, the system does the following:

Updates the account balances to match those in the imported trial balance data

Sets to 0 the account balances for which no data was imported

               If you are importing the trial balance in multiple currencies, the debit total may be not equal
               the credit total in the general ledger transaction generated on release of the trial balance. For
               more information, see Data Migration Process: Migrating Multicurrency Documents.

You verify the imported balances by using the Trial Balance Summary (GL632000) or Trial Balance Detailed (GL632500) report.

              If you have uploaded and released an incorrect trial balance or if you skip a period during
              import, an incorrect batch might be generated from the imported trial balance for the next
              period. You can delete the generated general ledger batch before it is released. If you have
              already released an incorrect batch, you can again import the correct trial balance so that the
              balances will be adjusted.

You import the next trial balance by performing these same actions in the stated order. For each subsequent trial balance, review the accounts listed on the Exceptions tab, if any.

Review of the Exceptions Tab

Records on the Exceptions tab of the Trial Balance (GL303010) form do not reflect mistakes. When you import the second trial balances (or the first balance for the second time), the Exceptions tab shows the accounts that have a nonzero balance for the period in the system, but their balance for the currently selected period has not been uploaded from the file. (Each subsequent import works similarly.) If accounts appear on the Exceptions tab, make sure the balance of these accounts is 0.00 for the period for which you are importing the trial balance. If the balance should be nonzero, verify the account balances in the Excel file from which you are importing the trial balance. If the Exceptions tab is empty, this means that all accounts that have a nonzero balance in the system for the period are listed on the Transaction Details tab for import. On release of the trial balance, for each account listed on the Exceptions tab, the system generates a transaction that makes the account balance 0.00 for the period for which you are importing the trial balance. Lesson 4: Importing Trial Balances | 34

Migration of Trial Balances: To Import Trial Balances

The following activity will walk you through the process of importing trial balances into the system.

Story

Suppose that you are an implementation consultant who is performing data migration from the legacy system to
Acumatica ERP. You have imported master records and historical documents.
Now you need to upload the actual balances of the general ledger accounts to the system. You have decided to
upload the trial balances for the last two financial periods in which the company has operated in the legacy system
(October and November 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 chart of accounts uploaded on the Chart of Accounts (GL202500) form.

Process Overview

You will upload the company’s trial balance for October and November on the Trial Balance (GL303010) form. You
will validate the trial balances, correct the error you find, and release the trial balances. Then you will review and
release the generated GL transactions on the Journal Transactions (GL301000) form. On the Trial Balance Detailed
(GL632500) form, you will prepare the trial balance report to verify the results of the import.

System Preparation

To prepare to perform the instructions of this activity, do the following:

Download the SweetLife_TrialBalance.xlsx file with the trial balances provided with the course.

On the General Ledger Preferences (GL102000) form, in the Chart of Accounts Settings section, make sure the Sign of the Trial Balance option is set to Normal.

               If the sign of the trial balance does not correspond to the option that is selected on the General
               Ledger Preferences form, aer you validate the trial balance, the Credit Total box will contain
               the value in the Debit Total box, but with the opposite sign. In this case, you need to change
               the Sign of the Trial Balance option value and upload the trial balance again.

Step 1: Importing the First Trial Balance Import the trial balance for October 2024 by doing the following:

On the Trial Balance (GL303010) form, create a trial balance import entry and specify the following settings in the Summary area:

Import Date: 10/31/2024 Lesson 4: Importing Trial Balances | 35
Period: 10-2024
Description: TB import 10-2024

On the table toolbar of the Transaction Details tab, click Load Records from File.
In the File Upload dialog box, which opens, click Choose File and select the SweetLife_TrialBalance.xlsx file. Click Upload.
In the Common Settings dialog box, which opens, leave the default settings, and click OK.
In the Columns dialog box, map the source columns to the destination columns as follows:

Account to Account
YTD Balance October to YTD Balance

Click OK. The system uploads the data from the file to the table.
On the table toolbar of the Transaction Details tab, make sure that the Validate action is selected, and click Process All to validate all records at once.
Find the line with an error, which has Error in the Status column, and change the 44030 account to 40300. Save your changes.
Click Process All on the table toolbar to validate all records again. Aer all records have been validated successfully, the Debit Total in the Summary area must be the same as the Credit Total ($1,041,106.86) so that the trial balance can be released. 10.On the form toolbar, click Remove Hold to remove the trial balance entry from hold and then Release to release the trial balance. On release of the trial balance, the system generates a batch of general ledger transactions and opens it on the Journal Transactions (GL301000) form. The credit and debit totals, which are the totals of debit and credit amounts for all transactions in the batch, are $1,041,106.86 (as shown in the following screenshot).
Figure: General ledger transaction generated for the first trial balance Lesson 4: Importing Trial Balances | 36
```
11.On the form toolbar, click Release to release the batch of GL transactions.
```

Step 2: Importing the Second Trial Balance Now you need to import the trial balance for November 2024 by doing the following:

On the Trial Balance (GL303010) form, create a trial balance entry and specify the following settings in the Summary area:

Import Date: 11/30/2024
Period: 11-2024
Description: TB import 11-2024

On the table toolbar of the Transaction Details tab, click Load Records from File.
In the File Upload dialog box, again upload the SweetLife_TrialBalance.xlsx file and map the source columns to the destination columns as follows:

Account to Account
YTD Balance November to YTD Balance

In the line with the incorrect account, change the 44030 account to 40300. Save your changes.
On the table toolbar of the Transaction Details tab, make sure that the Validate action is selected and click Process All to validate all records at once. Aer all records have been validated, the Debit Total in the Summary area must be the same as the Credit Total ($1,087,746.29) so that the trial balance can be released. On the Exceptions tab, notice that there are no lines. This means that all accounts that have a nonzero balance in the system for the period are listed on the Transaction Details tab for import.
On the form toolbar, click Remove Hold to remove the trial balance from hold and then Release to release the trial balance.
On the Journal Transactions (GL301000) form that opens, review the batch generated on release of the trial balance entry. Make sure that Debit Total and Credit Total in the Summary area contain 95,274.68. Notice that the debit and credit total of the generated batch are not equal to the debit and credit total of the trial balance. For each account, the system calculates the diﬀerence between the balance in the system and the balance being imported and debits or credits the account based on the sign of the diﬀerence and the account type.
On the form toolbar, click Release to release the GL transaction.
On the Trial Balance Detailed (GL632500) report form, specify 11-2024 in the From Period and To Period boxes. 10.On the report form toolbar, click Run Report. The generated report (see the following screenshot) shows the normal balance representation of accounts. The trial balance shows the balance of the 33000 (Net Income) account, which has been calculated based on the imported data and is $275,011.60. The total YTD Net Income is included in the Liability Total; therefore, the Assets Total is equal to the Liability Total in the report. Lesson 4: Importing Trial Balances | 37
```
Figure: Trial Balance Detailed report for 11-2024
```

You have finished the import of trial balances into the system. Lesson 5: Reconciling Account Balances | 38