Intercompany Transactions Data Importer

Creating a Reusable Excel Spreadsheet for Inter-Company Transactions.

Written By Grainne Reidy (Super Administrator)

Updated at July 11th, 2024

Introduction

AccountsIQ allows you to raise Intercompany Transactions using a template. It is useful if several companies raise multiple intercompany transactions against each other regularly. 

Delete

Info

Any user can have access to the data importer if their user profile is set with that permission. For more on user permissions, see Managing Group and Entity Users‍. User role then determines the range of available imports. Admin users have access to the full suite, while Users only have access to a subset.


The Intercompany Transactions template facilitates:

  • Intercompany recharges: When uploaded, this template will create Sales Transactions in the sending company’s (Head Office’s) designated Accounts and matching Purchase Transactions in the nominated receiving companies.
  • Invoice auto-generation: The Purchase Ledger Account will populate with the details of the incoming Invoice from the Insurance Company.
Delete

Note

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

 Creating Intercompany Transactions or if you are using the new module Using the new Intercompany Module - Beta‍ ‍ 

 

Setting up an Intercompany Transaction template

Intercompany Transaction templates can be saved, reused, and copied. This makes them more efficient overall than using Item Invoicing in the Purchasing system. 

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 isompany will then form part of the consolidation process along with all the subsidiaries.

See:

 26.1 - Getting Started with Data Importers

Intercompany Introduction (13.0) - AIQ Academy

Setup Intercompany Control Accounts, AR and AP (13.1) - AIQ Academy

Create Inter-company Connections (13.2) - AIQ Academy

Create Intercompany Transactions (13.3) - AIQ Academy 

Delete

Example Intercompany Accounts Setup

Setting up the accounts

In this example, the holding non-trading company is called Head Office.
 

To allow intercompany transactions in both directions, for all companies in the group, the following were set up:

  • Intercompany General Ledger Sales Accounts
  • Intercompany General Ledger Purchase Accounts
  • Sending Accounts - General Ledger Sales
  • Receiving Accounts - General Ledger Purchase

The following were also set up:

  • An Intercompany Creditors Control Account
  • An Intercompany Debtors Control Account

 

Delete

Warning 

You must set up the Control Accounts as System Accounts under Codes Maintenance, otherwise, data corruption may occur.

The completed accounts

Intercompany General Ledger Sales Accounts (RenuMe Spa Products Head Office):
 


Intercompany General Ledger Purchase Accounts, Non-Trading Company (RenuMe Spa Products - Head Office):
 

Table

Description automatically generated with medium confidence


General Ledger Sales Accounts for one of the sending companies (RenuMe Spa Products - Leeds):
 


General Ledger Purchases Accounts for one of the receiving companies (RenuMe Spa Products - Leeds):
 

Viewing Intercompany transactions

Go to Set Up > Intercompany Connections Setup.

Graphical user interface, text, application, emailDescription automatically generated

Delete

Note

The previous set of accounts allow transactions in one direction only, from Head Office to each of the subsidiaries. To send in the other direction, set up sending accounts in each company to the Head Office, and receiving accounts in the Head Office. 

If you want all companies to be able to send to and receive from each other, set up all relevant sending and receiving accounts in each company. You then need to establish connections between them.

How do I Create Inter-Company Transactions?‍ 

Delete

Preparing 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. See the sections relating to each of the worksheets.
  6. Save the file.
Delete

Intercompany Purchase Invoices Template

This is not mandatory.

Select the relevant data from the dropdowns.
 

Delete

Intercompany Recharges Template

This is mandatory.

Complete the template by recharging 95% of the Professional Indemnity Cost and 90% of the Public Liability and Employers Liability to the subsidiary companies as follows:

  • 25% of Professional Indemnity and 20% of the other two to Leeds
  • 25% of Professional Indemnity and 24% of the other two to Bristol
  • 23% of Professional Indemnity and 23% of the other two to Norwich
  • 22% of Professional Indemnity and 23% of the other two to Glasgow
  • 5% of Professional Indemnity as a charge in Head Office
  • 10% of the Public Liability and Employers Liability bill

Transaction types and associated data

Sales and Purchase transactions post 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).
Delete

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.

Delete

Note

If you make any changes to the template, you will need to generate a new updated CSV file and upload it.

Step Three: Transactions Import

The Data Importer screen displays any errors not resolved during the previous validation stage.

  1. Correct any errors in the original template, generate another CSV file, and then upload it. If the import is successful, you can click Next.



  2. Select Recorded. Unrecorded is only relevant to the importation of Bank Transactions and does not apply to Intercompany charges. Click Apply.



  3. The screen will display any errors that you need to correct. Once corrected, 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.

Delete

Accepting or Rejecting Intercompany Charges

The Purchase transactions sent by Head Office do not automatically post to the receiving companies’ Purchase Ledger Accounts. Instead, each of the receiving company must accept them.

Accept Intercompany Transactions

  1. Go to PurchasesIntercompany Transactions.



  1. In Intercompany Transactions, you can choose department codes from the dropdowns.

    Graphical user interface, application

Description automatically generated

  2. Click Process to accept and post the transactions into the Purchase Ledger.

Reject Intercompany Transactions

  1. Go to Purchases > Suppliers
  2. Click on the relevant Account Balance to display Supplier Transactions.



  3. Click Edit to display Transaction Details. There is no option to reject incoming Invoices. You must accept them. Afterwards, in agreement with Head Office, return them using intercompany posting facilities, or by creating a Credit Note in both Head Office and subsidiary.

Delete

Intercompany Charges arising from Head Office or other Company Journals

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.

Delete

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:

 

 

Delete

Note

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.

Delete