How do I Create Intercompany Charges using the Excel Template?

Creating a Reusable Excel Spreadsheet for Inter-Company Transactions.

Written By Grainne Reidy (Super Administrator)

Updated at October 21st, 2022

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

Note

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

How do I Create Inter-Company Transactions?‍ 

 

Setting up an Intercompany Transaction template

Although setting up Intercompany Transaction templates can be initially time-consuming, you can save, reuse, and copy them. 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:

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

Setting up the Intercompany Transaction Accounts

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 corruptions 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):
 

 

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

Setting up the Intercompany Transaction Excel template

Viewing Intercompany transactions

  1. Log into the company.
  2. Go to Set Up > Intercompany Transactions Setup.

Graphical user interface, text, application, email

Description automatically generated

Setting up the template

  1. Log into Head Office.
  2. Go to Setup > Data Importer.

  1. Under Start, click on Transactions Importer.

  1. Click Download Intercompany Transactions Template File.

  1. In the spreadsheet heading area, open the newly generated Excel file. Tick Enable Editing and Enable Content.

Delete

Completing the Intercompany Transactions template

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.

Completing the Intercompany Purchase Invoices Importer 

This is not a prerequisite.

Select the relevant data from the dropdowns.
 

Completing the Intercompany Recharges 

This is a prerequisite.

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

Validating the contents of the Excel Template

This is an optional but highly recommended step, especially in the case of future reuse of this (and other saved) Intercompany Recharge templates where values may change.
 

To validate the data from the previous templates:

  1. Open the Validations template. This example displays the validations against the first five entries on the Intercompany Recharges template. The formulae are in cells A1 to A5. Cells C1 to C5 describe them.

Graphical user interface, text, application

Description automatically generated

  1. Before continuing, review the Validations template to ensure all details are correct.
  2. Click Save and save the template to your desired location.
Delete

Generating and uploading the Data File

You need to generate a .CSV (comma separated vales) data file from this set of Excel templates.

  1. In the Excel template file, click Generate Data File > Generate Data Upload File. The file is now on your clipboard.

 

  1. Log into the sending company (Head Office).
  2. Go to Setup > Data Importer. Your newly created template should be on the clipboard. If it isn’t there, click Browse to find it. Copy and paste the filename.
  3. 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.

 

  1. The Data Importer screen displays any errors not resolved during the validation stage. Correct any errors in the original template, generate another .CSV file and then upload it.

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

  1. The screen displays any errors. Click Next.

  1. Click Clean Up.

Summary

The system has now:

  • Posted the Insurance Company to the Head Office Purchase Ledger.
  • Raised the Intercompany Charges in the Head Office Company by way of Sales Invoices to each of the subsidiaries.
  • Created Purchase Ledger Transactions in each of the other Companies for the Intercompany Charges.

Delete

Accepting or rejecting the 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.

  1. Either click the highlighted globe symbol which details the number of intercompany transactions is awaiting acceptance or go to Purchases > Intercompany Transactions.

 

  1. In Intercompany Transactions, you can choose department codes from the dropdowns.
  2. Click Process to accept and post the transactions into the Purchase Ledger.

Graphical user interface, application

Description automatically generated

  1. Go to Purchases > Suppliers. Find your account. Click on the Balance to display Supplier Transactions.

  1. 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

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. You can post a Journal or series of Journals to the Head Office General Ledger and recover some or all the costs from the subsidiaries. Do this by way of Head Office Sales Transactions to the subsidiaries and corresponding Purchase Transactions from Head Office (or other company) in each subsidiary.
 

The completion of the Intercompany Charge Journals Importer template is optional, as with the Intercompany Purchase Invoices. You can process Head Office Purchases (and Payments) throughout the Period using the facilities of the Purchase Ledger. You can then distribute these costs by way of month end Intercompany Journals from the appropriate Purchase General Ledger Accounts to the subsidiaries.

Example

In the following example, we are:

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

    Graphical user interface, application

Description automatically generated
     
  • recharging several other Purchase Ledger Costs which accumulated throughout the period.

 


Follow the previous instructions:

  • to create, download, complete, and save the template for future reuse
  • to generate the Data Table and upload it
  • to accept each subsidiary company the Intercompany Transactions
Delete

Using Formulas in the Excel Template:

You can use Formulas in the Excel Template.

Example

This example is based on the Insurance Recharges Template. Another spreadsheet was added to the Template containing the Allocation Percentages of 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