Intercompany Transactions Data Importer

Creating a Reusable Excel Spreadsheet for Inter-Company Transactions.

Written By Grainne Reidy (Super Administrator)

Updated at February 20th, 2025

Introduction

The Data Importer lets you raise Intercompany Transactions using an import template. This is useful if several companies raise multiple intercompany transactions against each other regularly as Intercompany Transaction templates can be saved, reused, and copied. 

Before you begin, all the relevant IC and control accounts must be set up correctly. For more, see Using the New Intercompany Module.

Info: User Permissions

To use the data importer, your user profile must have the Data Importer permission. For more on user permissions, see Setting Up System Users‍. User role determines the range of available imports. Admin users have access to the full suite, while Users only have access to a subset.

 

Example

A holding company and an insurance company want to create Intercompany Transactions. The holding company receives a multiline purchase invoice from the insurance company on behalf of the group. The insurance company wants to recharge the purchase invoice to all the subsidiary companies.

Setting up the holding company only as a Consolidation Entity would only allow it to have General Ledger functionality, without Sales, and Purchases. Therefore, the holding company would not be able to raise Intercompany Transactions. To allow for Intercompany Transactions, the holding company needs to be set up as a non-trading company as well as a Consolidation Entity. The holding non-trading company will then form part of the consolidation process along with all the subsidiaries.

IC Transactions Templates

The Intercompany Transactions template facilitates:

  • Intercompany recharges: When uploaded, this template will create Sales Transactions in the sending company’s IC Accounts and matching Purchase Transactions in the receiving company's IC Accounts.

    Note

    Before continuing you must understand the standard method of raising once-off intercompany charges.

    Using the new Intercompany Module‍ ‍ 

     
  • Invoice auto-generation: The Purchase Ledger Account will populate with the details of the incoming Invoice.

See:

26.1 - Getting Started with Data Importers

New! Introduction to Intercompany module (13.0) - AIQ Academy

New! Creating Intercompany Connections (13.1) - AIQ Academy

New! Creating Intercompany Transactions (13.2) - AIQ Academy

New! Managing Intercompany Accounts (13.3) - AIQ Academy

Using the new Intercompany Module 

 
 

Downloading the Template

  1. Go to Setup > Data Importer.
  2. Under Start, click on Transactions Importer.
  3. Click Download Intercompany Transactions Template File.
  4. Open the newly generated Excel file. Check Enable Editing and Enable Content. The template contains the following worksheets:
    • Intercompany Purchase Invoices
    • Intercompany Charge Journals
    • Intercompany Recharges
  5. Fill in the required fields referring to the relevant sections below.
  6. Save as a macro-enabled file.
 
 

Intercompany Purchase Invoices Importer

This is not mandatory.

Select the relevant data from the dropdowns.

 
 

Intercompany Recharges Importer

This is mandatory.

Transaction types and associated data

Sales and Purchase transactions are posted with different data. If you leave any blank, the system posts the same as those recorded against the Sales Transactions. 

  • Sales Transactions: Posted with the General Ledger Code, Tax Code, and Project Code (BI Code) (highlighted in yellow). 
  • Purchase Transactions: Posted with the Destination General Ledger Code, Destination Tax Code, and Destination Project Code (highlighted in green).
 
 

Intercompany Charge Journals Importer

Recovering Costs

To recover costs that may not have a Purchase Invoice as their source, or to collect them in Head Office for month-end distribution, the process is as described previously:

  • Post a Journal or series of Journals to the Head Office General Ledger. Use Head Office Sales Transactions to the subsidiaries.
  • Recover some or all the costs from the subsidiaries. Use corresponding Purchase Transactions from Head Office (or other company) in each subsidiary. 

The Intercompany Charge Journals and Intercompany Purchase Invoices templates are optional. Instead, you can process Head Office Purchases (and Payments) throughout the period:

  • Use the Purchase Ledger.
  • Distribute these costs using month end Intercompany Journals from the appropriate Purchase General Ledger Accounts to the subsidiaries.

Example of Intercompany Transactions

In the following example, we have:

  • posted a single Journal to Head Office in respect of Directors Salaries, allocating £6,000 to the subsidiaries.

    Graphical user interface, application
                Description automatically generated
     
  • Recharged other Purchase Ledger Costs which accumulated throughout the period.

 
 

Using Excel Formulas in the Template

Example

This example is based on the Insurance Recharges Template. Another worksheet titled "Allocation Percentages" was added containing the Head Office Insurance Purchase Invoice.

Table
            Description automatically generated
This example shows the application of these percentages in the Intercompany Recharges Sheet:

Note: Creating a Template

The process of generating the Data File overwrites the formulas rendering the template unusable next time. Instead, save a copy of the Master Template for reuse, update any required fields, and use this copy to create the required Intercompany Transactions while leaving the Master Template complete with the formulas untouched.

 
 
 

Import the Template

Step One: Generate the Data File

When you have completed all worksheets correctly, go to the Generate Data File tab and click Generate Data Upload File to generate the CSV file.

This is the first stage of error validation. If there are errors, correct them and click Generate Data Upload File again.

You will find the CSV file in the same location as the macro-enabled Excel file. This file will be used to import all the data you added. During upload the system will check for format and that the CSV file is in the same location as the XLS file. You will receive confirmation if this is the case.

 

Step Two: Import the Data File

  1. Log into the sending company (Head Office).
  2. Go to Setup > Data Importer > Transactions Import > Intercompany Transactions
  3. Click Browse to find your saved CSV template.
  4. Click Upload Data. If everything is setup correctly you will receive a confirmation message. If there are any errors, correct them in the Excel file, generate another csv file and upload it.

Step Three: Transactions Import

  1. Select Reconciled. Unreconciled is only relevant to the importation of Bank Transactions and does not apply to Intercompany charges. Click Apply.
  2. The screen will display any errors. Correct any errors in the original template, generate another CSV file, and then upload it. If the import is successful, you can click Next.

Step Four: Clean Up

  1. Go to Transactions Import > Clean Up.
  2. Click Clean Up. You will receive a confirmation message when the process is complete.