How to Map and Upload a Trial Balance Using Excel Add-in

You can use ONESOURCE Excel Add-in to upload trial balance data to the Import Queue of Import Trial Balances in General Ledger Manager. To do so, you must do the following:

• Arrange the trial balance data in an Excel worksheet in either one of two different layouts, depending upon whether the trial balance has a single reporting unit code or multiple reporting unit codes.
• Select a "trial balances" type of mapping, and then link its fields to the corresponding cells in the Excel worksheet where the actual data for those fields resides.
• Validate the data in the mapped Excel worksheet cells.
• Send the Excel workbook to General Ledger Manager.

Lay Out the Trial Balance in the Worksheet

In order to properly map and successfully upload a trial balance, its data must be arranged in one of two layouts, depending on whether the trial balance has a single reporting unit or multiple reporting units. The tabular format of these layouts is a common one for systems that may be producing the files you want to use with Excel Add-in. Check your files for adherence to the following characteristics:

Single Reporting Unit Layout

If the trial balance data that you want to upload has only a single reporting unit, then do the following:

• Enter the name of the chart of accounts in General Ledger Manager that you want to use to upload this trial balance in a cell of the worksheet.
• Enter the tax year of the selected charts of accounts/trial balance in a cell.
• If trial balance data is for less than a year, enter the period of time for the trial balance that you want to upload in a cell or cells in the following manner. If the data is for particular month of the trial balance year you entered, enter that month in numeric format. If your data is for the trial balance year up to a certain date, enter the month in numeric format in one cell and the day in another.
• Enter the reporting unit code for the trial balance data in a cell.
• Optionally, enter the name of the reporting unit.
• Enter all of the account codes in the trial balance in one column.
• Optionally, enter the account descriptions in a column adjacent to the account codes.
• Optionally, enter the beginning balances for the accounts in a column adjacent to the account codes or descriptions.
• Enter the ending balances for the accounts in a column adjacent to those you've already created.

An example of a trial balance for November of 2013 in the single reporting unit layout is shown below.

Multiple Reporting Unit Layout

If the trial balance data that you want to upload has multiple reporting units, you must enter the same data in the Excel worksheet with the exception of the column of beginning balances, which cannot be uploaded for trial balances with multiple reporting unit codes.

The data you import using this layout must be arranged in with the account codes (and their descriptions, if you choose) on the Y axis and the reporting units (and their descriptions, if you choose) on the X axis at the top of the table. The ending balances for each account should be entered in the appropriate cell at the intersection of an account code and a reporting unit.

An example of a trial balance for November of 2013 in the multiple reporting unit layout is shown below.

Mapping a Trial Balance in Single Reporting Unit Layout

To map a trial balance that has been arranged in the single reporting unit layout, do the following:

1. Open the Excel workbook and click the Data Import tab in the Excel ribbon. The Data Import menu selections are be displayed on the ribbon as shown in the example below.

2. Click New Map on the Data Import tab to add a "Trial Balances" mapping to your Excel worksheet. The New Mapping dialog is displayed as shown below.

3. In the New Mapping dialog, click the Trial Balances type of map, enter a unique name for the map (and the resulting trial balance in the Import Queue) in the Name field at the bottom of the dialog, and then click OK. The new mapping appears in the XML Source pane on the right side of the worksheet. It shows the trial balance fields grouped in folders in a data tree structure. Fields with a red asterisk next to them are required fields, and you must link them to the cells in the worksheet where that data that belongs in them resides. An example of a worksheet with a trial balances mapping in its XML Source pane is displayed below.

4. Link the fields in the trial balances map in the XML Source pane to the cells in the worksheet where the data that belongs in those fields resides as follows:
   1. Highlight the cell in the worksheet that has the data that corresponds to a field in the mapping. Then, double-click that field in the XML Source pane. (You can also drag the field from the XML Source pane to the cell you want to link.) Mapping fields to the cells in a worksheet is a one-to-one process, with the exception of account codes and reporting unit codes.
      
      To map a column of account codes, you merely need to establish in Excel Add-in the range of cells that contain account codes. Map the cell of the first account code in the column to the AccountCode field. This is the beginning of the range. Next, map the cell of the last account code in the column to the Accounts_Depth field. This row represents the end of the vertical range of the data in this mapping. For example, if you map the AccountCode field to an account code on row 5 and you map the Accounts_Depth field to an account code on row 10 of same column, only the rows of trial balance data from row 5 to row 10 are included in the trial balance upload.
   2. Add headers to the mapped cells, as desired.
      
      • Double-click the field in the map. A yellow border highlights the mapped cell and a Header Options icon appears next to it as shown below.
        
        
        
      • Click the Header Options icon, and select the option that is most appropriate to the linked cell.
        
        
      
      
      

If you labeled your mapped cell with text in another cell, accept the first option, My Data Already Has A Heading, which is the default. However, if you want to apply a header to the cell that is exactly the same as the name of the field to which you mapped it, you can choose to display the header either to the left of or above your linked cell.

Fields in the XML Source pane that are mapped to cells appear bold. When you click a bold field, its linked cell is highlighted and vice versa.

Mapping a Trial Balance in a Multiple Reporting Unit Layout

Mapping a trial balance that has been arranged in the multiple reporting unit layout is similar to the mapping of a trial balance arranged in a single reporting unit layout. The difference is that you cannot map beginning balances, since they cannot be uploaded for a trial balance that has multiple reporting units via Excel Add-in.

Also, since a trial balance in the multiple reporting unit layout is based on a matrix with reporting unit codes on the x-axis at the top of the columns of trial balance data, and the account codes should be arranged on the y-axis, you must define the beginning and ending of the ranges of reporting unit codes and account codes.

Define the beginning and ending reporting unit codes on the x-axis as follows:

1. Highlight the reporting unit code over the first column in the trial balance, and then click the ReportingUnitCode field in the XML Source pane. This identifies the beginning of the range of reporting unit codes in the active mapping.
2. Highlight the reporting unit code over the last column in the trial balance, and then click the ReportingUnits_End field in the XML Source pane. This identifies the ending of the range of reporting unit codesin the active mapping.

The following graphic shows an example of how to map the beginning and ending reporting unit codes.

Define the beginning and ending of a range of account codes on the y-axis of the trial balance as follows:

1. Highlight the first cell in the column of account codes, and then click AccountCode in the XML Source pane. This identifies the beginning of the range of account codes in the active mapping.
2. Highlight the last cell in the column of account codes, and then click the Accounts_Depth field in the XML Source pane of the worksheet. This identifies the end of the range of account codes in the active mapping.

The following graphic shows an example of how to map the beginning and ending of the account codes:

To map all of the cell that may contain ending balances, highlight the cell at the intersection of the first entity code and the reporting unit code. Then double-click the EndingBalance field in the XML Source pane. All of the values in the cells within the boundaries established by the ranges of reporting unit codes and account codes will be interpreted as ending balances for accounts and reporting units where they intersect. A trial balance need not be present for all possible combinations created by the tabular format.

Removing a field-to-cell mapping

If you've mapped a cell in your workbook to a field in the XML Source pane, you can remove the data in the cell without affecting the mapping. To remove the link between a cell and a field, right-click the field name in the XML Source pane. In the menu that appears, choose Remove element.

Removing an entire mapping

You can remove all linked cells at once by removing the mapping to which they are linked from the workbook entirely. To remove an entire mapping, click XML Mapsat the bottom of the XML Source pane in ONESOURCE Excel Add-in. XML Maps appears, listing all of the mappings in the active workbook. Highlight the map you want to remove and then click Delete. Then, click OK.

Validating mapped trial balance data

After you link a trial balance's data to a map, you can validate that you have linked data with all of the required fields in the map and that the data is in the correct format. To validate trial balance data and mappings, click Validate Data in the Data Import tab in the Excel ribbon. A message is displayed to indicate that the validation process has started.A status message appears when validation is complete. You must correct any errors before you can send the data to General Ledger Manager.

Sending the Trial Balance

To sendmapped trial balance data in the active workbook to General Ledger Manager, click Send Active Workbook in the Data Import tab in the Excel ribbon. A message is displayed to indicate that the linked data is being validated. If validation completes with no errors, the upload process begins and a message is displayed to inform you.

When the trial balance data has been uploaded, a message is displayed indicating that the "import process" has completed.

If the trial balance data is not uploaded, an upload exception message is displayed. For example, if the message "To import into General Ledger Manager you must select an account" is displayed,then you have not selected a client in Excel Add-in Settings. Click here for more information about selecting a client in Excel Add-in Settings.