Import a Trial Balance

To import a single Trial Balance into a new report:

Click Inputs > Trial Balance
Click Import
Select Number of Trial Balances to import - Single
Select the Division name
Select the Trial Balance import method (unless using the Mapping module select Standard Trial Balance)
Select the file to import
Click OK
When importing a single Trial Balance, the first visible worksheet in the Excel file will be imported into the division selected

The following image shows a valid example file:

Image shows a valid Excel import file with the data in the correct columns.

Column A contains the account number
Column B contains the account name
Column C contains the current period balance
Column D contains the prior period balance
Column E contains the current period allocation
Column F (prior period allocation) is blank, so the prior allocation will be set the same as the current period allocation

As an alternative to including the allocations within the import file, columns E and F can be left blank. Then click the Map button after importing the file. Click here for more information on the different methods to allocate or map a Trial Balance.

Import multiple Trial Balances

To import multiple Trial Balances into a new report:

Click Inputs > Trial Balance
Click Import
Select Number of Trial Balances to import - Multiple
Select the Trial Balance import method (unless using the Mapping module select Standard Trial Balance)
Select the file to import
Click OK

The following image shows a valid example file. If it was imported using Multiple, then 'PAR' would be imported into the division with the alias 'PAR', 'Sheet2' would be ignored (even if it contains valid data) as it is not a valid division alias and 'MAN' would be imported into the division with the alias 'MAN'.

The division alias is set in Edit entity. A warning will be shown after the import is complete for any worksheets in the Excel file that do not have a matching division alias in the entity, any such worksheets are ignored.

To rename a worksheet in Excel, right mouse click the worksheet E.g. 'Sheet2' and select 'Rename', then type a new name.

Image shows an Excel file with multiple worksheets. They are labelled PAR, Sheet 2 and MAN.

Import a Trial Balance into a rolled report

To import a Trial Balance into a rolled report:

Click Inputs > Trial Balance
Click the Import button
Select Number of Trial Balances to import
If importing a single Trial Balance, select the Division name to import the Trial Balance into
Select the Trial Balance import method (unless using the Mapping module select Standard Trial Balance)
Select the file to import
Click OK
If importing a single Trial Balance, the first visible worksheet in the Excel file will be imported into the division selected

The following image shows a valid example file:

Image shows a valid Excel import file with the data in the correct columns.

Column A contains the account number
Column B contains the account name
Column C contains the current period balance
Column D (prior period balance) is blank as the Trial Balance will already have a prior period balance. There is no need to include the prior period balance in the file unless they are being updated.
Column E (current period allocation) is blank as the Trial Balance will already be allocated. There is no need to include the allocations in the file unless they are being updated.
Column F (prior period allocation) is blank as the Trial Balance will already be allocated. There is no need to include the allocations in the file unless they are being updated.

Importing Trial Balances examples

Example 1 - importing an allocated Trial Balance with current and prior period balances into a new report

Column A contains the account number
Column B contains the account name
Column C contains the current period balance
Column D contains the prior period balance
Column E contains the current period allocation
Column F is the prior period allocation. As it is blank, then the prior period will be allocated using the current period allocation from column E

Image shows a valid Excel file import with data in the correct columns. Columns A to F have been populated.

Example 2 - Importing a Trial Balance to update account names and balances

After importing the Trial Balance in example 1 above.

Column A contains the account number
Column B contains the account name. The account name for account number 1127 will be changed from 'ABC Bank electronic clearing account' to 'AAA Bank electronic clearing account'
Column C contains the current period balance. All current period balances will be updated to the new balances in this Trial Balance
Column D is the prior period balance. As it is blank, the prior period balances will not be changed
Column E is the current period allocation. As it is blank, the current period allocations will not be changed
Column F is the prior period allocation. As it is blank, the prior period allocations will not be changed

Note: Not all columns have to be imported. In this example, the current period balances and an account name were updated.

Image shows a valid Excel file import with data in the correct columns. The account name for account number 1127 has been changed to AAA Bank electronic clearing account. Columns A to C have been populated.

Example 3 - Importing a Trial Balance to update prior period comparatives

Column A contains the account number
Column B contains the account name
Column C is the current period balance. As it is blank, the current period balances will not be changed
Column D contains the prior period balance. All prior period balances will be updated to the new balances in this Trial Balance
Column E is the current period allocation. As it is blank, the current period allocations will not be changed
Column F is the prior period allocation. As it is blank, the prior period allocations will not be changed

Image shows a valid Excel file import with data in the correct columns. Columns A, B and D have been populated.

Example 4 - Importing an annual Trial Balance

When rolling forward an Interim Report the software will prompt the selection of an Annual Report to import the annual Trial Balance from.

When not rolling forward or to import the annual period manually the file format would be as follows, assuming current and prior periods are already imported.

Column A contains the account number
Column B contains the account name
Column C contains the current period balance. As it is blank, the current period balances will not be changed
Column D is the prior period balance. As it is blank, the prior period balances will not be changed
Column E is the current period allocation. As it is blank, the current period allocations will not be changed
Column F is the prior period allocation. As it is blank, the prior period allocations will not be changed
Column G contains the annual period balance. All annual period balances will be updated to the new balances in this Trial Balance
Column H is the annual period allocation. As it is blank, then the annual period will be allocated using the current period allocation from column E

Image shows a valid Excel file import with data in the correct columns. Columns A, B and G have been populated.

Import Trial Balances using Mapping by Account

In this example, a mapping by account file has been imported in the Mapping module and looks like the following image:

Image shows an excerpt of the Mapping by Account screen in the Mapping module found in the software. The columns Mapping account, Description and Allocation have been populated.

Click Inputs > Trial Balance
Click Import
Select Number of Trial Balances to import
If importing a single Trial Balance, select the Division name
Select the Trial Balance import method - Mapping By Account Trial Balance
Select the file to import
Click OK

Alternatively:

Import a Trial Balance or Import multiple Trial Balances
Click Inputs > Trial Balance
Click Map
Click Learn
If mapping a single Trial Balance, select the Division name
Select to apply the mapping to All accounts or Unallocated accounts
Click OK

The following image shows a valid example file:

Image shows a valid Excel import file. In Column A are the account numbers. Column B is the account description. Column C is the current period balance. Column D is the prior period balance.

The software searches the mapping file for the account numbers in column A. It allocates based on the allocation for that account number in the mapping file
In cell A1 is account number 1001. It will search for account number 1001 in the mapping file and see it is CACOH. Account 1001 will then be allocated to CACOH
In cell A2 is account number 1002. It will search for account number 1002 in the mapping file and see it is CACAB. Account 1002 will then be allocated to CACAB
This process continues until the end of the Trial Balance is reached
The allocations that accounts are mapped to can be changed at any time in the Trial Balance screen, or at the top level in the mapping module

Import Trial Balances using Mapping by Code

In this example, a mapping by code file has been imported in the Mapping module and looks like the following image:

Note: The mapping code can be a number, a code, text or any combination of letters, numbers and characters.

Mapping by code

Click Inputs > Trial Balance
Click Import
Select Number of Trial Balances to import
If importing a single Trial Balance, select the Division name
Select the Trial Balance import method - Mapping By Code Trial Balance
Select the file to import
Click OK

The following image shows a valid example file:

Trial Balance example mapping code

The software searches the mapping file for the codes in column E, and other allocation columns if applicable. It allocates based on the allocation for the code found in the mapping file
In cell E1 is code 1.1.100. It will search for code 1.1.100 in the mapping file and see it is CACOH. Account 1110 will then be allocated to CACOH
In cell E2 is code 1.1.250. It will search for code 1.1.250 in the mapping file and see it is CACAB. Account 1124 will then be allocated to CACAB
This process continues until the end of the Trial Balance is reached
The allocations that accounts are mapped to can be changed at any time in the Trial Balance screen, or at the top level in the mapping module