How do I Create Intercompany Charges using the Excel Template?

Creating a Re-Usable Excel Spreadsheet for Inter-Company Transactions.

Written By Gerry Mckeown ()

Updated at March 10th, 2019

1.     Introduction:

This is a highly functional alternative to the standard method of raising one-off Intercompany Charges and is used in situations where there are a lot of intercompany transactions between several companies which are repeated and re-used regularly such as monthly, quarterly or annually.  It is very important that you read and fully understand the detail of the Article “How do I Create Intercompany Transactions- most especially in the setup of the various intercompany sending and receiving accounts, the intercompany control accounts, and the establishment of the various connections between companies.  All of this is covered in Sections 3., 4. and 5. of the aforementioned article and all of which is prerequisite to the performance of this functionality.

Although these templates require some effort to set up in the first instance, the fact that they can be saved and subsequently re-used time and again (with perhaps subsequent modifications to the Amounts and Dates data if required) means that you can set up many intercompany charge templates to be recalled and invoked/re-used on a recurring basis. 

The first example shown here is that of a Holding Company receiving a multi-line Purchase Invoice from an Insurance Company on behalf of the Group which it wishes to re-charge to its subsidiary companies. However, you should note that, if you set up the Holding Company purely as a Consolidation Entity, then it will not have a Sales system and a Purchasing system (just General Ledger functionality) and will not, therefore, have the capability of raising intercompany transactions. Consequently, it is necessary to set up the Holding Company as a Non-Trading Company in addition to its set up as a Consolidation Entity.  The Holding Non-Trading Company will subsequently form part of the Consolidation process along with all the subsidiaries.  In this example we have called this Holding Non-Trading Company “Head Office”.

2.     Download the Intercompany Transactions Excel Template:

In each Company in the Group you should set up the following to allow for complete and total intercompany interaction by way of sales and purchase transactions (i.e. Sending and Receiving Companies and vice versa);

i) Receiving Accounts (Purchase Ledger) for all the other Companies in the Group, 

ii) Sending Accounts (Sales Ledger) for all the other Companies in the Group,

iii) Intercompany General Ledger Sales Accounts for all other Companies in the Group,

iv) Intercompany General Ledger Purchase Accounts for all other Companies in the Group,

v) An Intercompany Creditors Control Account (only one is necessary in each Company),

vi) An Intercompany Debtors Control Account (only one is necessary in each Company).

Here are the relevant Sale GL Accounts for RenuMe Spa Products (Head Office) - the “Sending” Company - that have been setup;

And here are the Customer Accounts (i.e. Subsidiary Companies) in the Head Office Non-Trading Co.;

And here are the Purchase General Ledger Accounts for one of the “Receiving” Companies – RenuMe Spa Products (Leeds).  The following two sets of Accounts should also be setup in all the “Receiving Companies”;

And their accompanying Purchase Ledger Accounts;

N.B.  It is imperative that these Control Accounts are also set up as “System Accounts” under Codes Maintenance – otherwise data corruptions may occur.

The foregoing set of Sales Accounts, Purchase Accounts and General Ledger Accounts facilitates traffic in one direction only – from Head Office to each of the Subsidiaries.  If you want the reverse, then you will have to set up, in each company, “Sending” Accounts to Head Office and “Receiving Accounts in Head Office.   And if you want complete Intercompany Trading, allowing all companies to “Send” to all other companies, and all Companies to “Receive” from all other companies, then you’ll need to set up the requisite set of “Sending” and “Receiving” accounts in each individual company.

N.B.  In addition you will have to establish connections between the Head Office Company and each of the Subsidiary Companies (as detailed in the Article “How do I Create Intercompany Transactions”) and also the reverse if you want the Subsidiaries to be able to “Send” transactions to Head Office.   You’ll also need to establish, in each individual company, a connection between the company you’re logged into and all the other companies.

After establishing the required inter-company connections from Head Office to each “Receiving” Company, your Intercompany Connections table should look like this;

Log into the Head Office Company and Go To > Setup >  Import/Export Data > Data Importer;

Click on Transactions Importer under “Start”;

Followed by “Download Intercompany Transactions Template File”;

This will generate an Excel File which you should now “Open” followed by “Enable Editing” and “Enable Content” in the Spreadsheet Heading area;

3.     Complete the Excel Template:

The Excel Template, as well as facilitating Intercompany Recharges, also facilitates the population of the Purchase Ledger Account with the details of the incoming Invoice from the Insurance Company.   While this could also be accomplished by using the facilities of Item Invoicing in the Purchasing System, it is better to do it here as you will be saving this template for future re-use and where all details of the originating Purchase Invoice(s) and all Re-Charging Sales Invoices are all in one place for subsequent amendment of Amounts and Dates when re-used.  However, you should note that filling out the “Intercompany Purchase Invoice” spreadsheet is not a pre-requisite as the system will function properly if you’ve only filled out the “Intercompany Recharges” spreadsheet which will result, in any case, the generation of Sales transactions in the “Sending” company and the equivalent Purchase transactions in the “Receiving” company.

Fill in the details as follows, using all the available Drop Downs;

We are now going to re-charge 95% of the Professional Indemnity Cost and 90% of the Public Liability and Employers Liability to the subsidiary companies as follows;

i) 25% of Professional Indemnity and 20% of the other two to Leeds

ii) 25% of Professional Indemnity and 24% of the other two to Bristol

iii) 23% of Professional Indemnity and 23% of the other two to Norwich

iv) 22% of Professional Indemnity and 23% of the other two to Glasgow

leaving the remaining 5% of Professional Indemnity as a charge in Head Office and 10% of the Public Liability and Employers Liability bill.

Still logged into the Head Office Account, move onto to the Intercompany Recharges page of the Excel download;

And enter the Sales re-charges;

When this Sheet is subsequently uploaded, it will create Sales Transactions in the designated Accounts in the Head Office (“Sending”) Company and equivalent Purchase Transactions nominated in the “Connections” (as detailed in the Article “How do I Create Intercompany Transactions) to each of the Subsidiary Companies (“Receiving”).  The General Ledger Code, Tax Code and Project Code (BI Code) (highlighted in Yellow) are those that will be posted with the Sales Transactions.  The Destination General Ledger Code, Destination Tax Code and Destination Project Code (highlighted in Green) are the equivalent Codes for the Purchase Transactions.  If any of these latter entries are left blank, they will be assumed to be the same as those recorded against the Sales Transactions (Yellow). 

4.     Validate the contents of the Excel Template:

Having filled both the Intercompany Purchase Invoices and the Intercompany Recharges sheets, now go to the Sheet named “Validations”.  Here you can optionally prepare validations to check you data and computations;

In this example I am showing the validations against the first five entries on the Intercompany Recharges sheet.  The formulae are in cells A1 to A5.  Cells C1 to C5 are just a description of these formulae in cells A1 to A5.

This is an optional but highly recommended step – especially in the context of future re-use of this (and other saved) Intercompany Re-Charge templates where amounts may vary and have to be changed.

Before commencing with the next step, the end-user should be advised to review this Sheet in the Template in order to ensure that there are no “Incorrect Amounts” shown.

You should now “Save” this Spreadsheet in a memorable location in order to recall for future use.

5.     Generate and Upload the Data File:

You now need to generate a .CSV data file from this set of Excel Sheets using the following two step process;

Log into the “Sending” company (Head Office) and re-open the Data Importer. If the clipboard copy doesn’t work for any reason, then “Browse” to the location where you saved your template in 4. above and copy the filename of the .CSV file (Comma Separated Values) and paste it as shown.  After that click on “Upload Data”.

N.B.  If you are re-using the Template subsequently, after you have made any required changes to Dates, Amounts, etc., you need to repeat this step 5. and generate a new updated .CSV file for upload.

After clicking on “Upload Data”, if everything was setup as it should be you should receive the message;

Now Go To here in the Data Importer;

Where it will display any errors encountered.  In Transaction Ref: 655 the Debits and Credits are out by 100.00 GBP.   This error should have been caught at the Template Validation stage.  You must correct the error in the original Template, Generate the Data File (.csv) again and then Re-Upload it.

This next step is only relevant to the importation of Bank Transactions and does not apply to Intercompany charges.  Just click on “Apply”.

This will then present any errors on this screen;

And the final step;

The system has now;

  1. Posted the Ace insurance Company to the Head Office Purchase Ledger.
  2. Raised the Intercompany Charges in the Head Office Company by way of Sales Invoices to each of the other Companies.
  3. Created Purchase Ledger Transactions in each of the other Companies for the Intercompany Charges.

As follows;



6.     Accept/Reject the Intercompany Charges:

The Purchase transactions sent by Head Office are not automatically posted to the Receiving Companies’ Purchase Ledger Accounts.  Instead they must be “accepted” by each of the receiving company.

This is done by either clicking on the highlighted “Globe” symbol which will inform as to whether and how many intercompany transactions are awaiting acceptance, or by choosing the highlighted program in the drop down.   This will bring up the following window;

You now have the opportunity to add Dept Codes from the drop downs as highlighted.

Click on “Process” to accept and release the transactions into the Purchase Ledger;

As regards rejecting the incoming Invoices, there is no option to do this.  You must accept them anyway and subsequently, on agreement with Head Office, send them back using intercompany posting facilities, or by creating a Credit Note in both Head Office and subsidiary.

7.     Intercompany Charges arising from H.O. (or other Company) Journals:

Much the same process should be followed in the case of recovering costs which may not have a Purchase Invoice as its original source, or indeed where you want to collect such costs in Head Office for month end distribution. In this case, you can post a Journal or series of Journals to the Head Office General Ledger and recover some or all of the costs from the subsidiaries – again by way of Head Office Sales transactions to the subsidiaries and corresponding Purchase transactions from Head Office (or other company) in each subsidiary.

Here’s an example;

Again please note that the completion of the Journals Spreadsheet (above) is optional, as indeed is the case in regard to Intercompany Purchase Invoices.  For example, you may decide to process Head Office Purchases (and Payments) throughout the Period using the facilities of the Purchase Ledger and then decide to subsequently distribute these costs by way of month end Intercompany Journals from the appropriate Purchase Ledger General Ledger Accounts to the subsidiaries.

In this example, we are going to post a single Journal to Head Office in respect of Directors Salaries, allocate £6,000 to the subsidiaries – but also, at the same time, re-charge several other Purchase Ledger Costs which have been accumulated throughout the period.

As before, if this is the first time you are creating the Template, then download it and complete it.  After that “Save” it for future re-use.  Next, generate the Data Table and upload it following the steps detailed in 5. above.   After that, each subsidiary company must accept the Intercompany Transactions as detailed in 6. above.

8.     Using Formulas in the Excel Template:

You can use Formulas in the Excel Template.  Here’s an example based on the Insurance Recharges Template described heretofore.  Here we’ve added another spreadsheet to the Template containing the Allocation Percentages of the Head Office Insurance Purchase Invoice;

And here is an example of the application of these percentages in the Intercompany Recharges Sheet.

N.B.  It’s very important that you Save this Template (and others like it) as a Master Template to be re-used subsequently.  Unfortunately, the process of Generating the Data File overwrites the formulas in a particular instance of usage thus rendering that particular Workbook un-reusable next time round.  Instead you should take a copy of the Master Template that you saved for subsequent re-use, update any required fields such as Amounts, Dates, Transaction IDs, etc.  and then use this copy to create the required Inter-Company transactions and leaving the Master Template untouched and complete with the formulas.