Investigating Intercompany / Location Differences (Using IRIS Tool)
Intracompany variances can occur due to the following:
1) Intracompany Rule is turned off meaning no automatic INTR document is posted
2) A document which was originally posted across two or more locations has been changed and is now only one location. This would leave an INTR document due to the original posting that would need to be reversed.
3) A document has been changed which is posted across multiple locations, but the INTR documents have not been updated to reflect the changes made.
This guide will show you how to use a tool developed by the Support Team at PS Financials to assist in finding these variances. The tool is a spreadsheet which requires at least intermediate knowledge Excel to use. If you do not wish to use this, we have provided a manual process which can be found
The Support Team have developed a tool which may help assist in finding some of the documents causing the imbalances across the locations.
The tool is an Excel spreadsheet which cross references where INTR documents exist and compares these to the source documents to identify whether these are still needed. In addition it checks where there are value differences between the INTR documents and the source documents where amendments have been made by end-users.
The Tool provided in the guide is not official IRIS Financials Software. It has been developed and tested by the Support Team and providing the instructions are followed carefully, it identifies documents that will cause an unbalanced Trial Balance per location.
IRIS Financials will not provide any additional support or guidance in the use of this tool or fix the tool in relation to attempted amendments by the end-user. It is recommended that any results from the tool are sense checked in IRIS Financials before any attempted corrections are made. IRIS Financials will not will not be held accountable for any mis-postings arising from use of this tool.
Use of the tool requires an Intermediate knowledge of Excel as you may need to add in extra columns and copy formulas. Due to the number of formulas and dependant on the size of the data being imported, you may find that some calculations can take some time. If this is the case, we would recommend turning off the automatic calculation of formulas. An internet search will be able to advise you on how to do this. This tool will allow analysis for up to 14 locations at a time.
If you require any further locations, you will need to copy the formulas in the hidden columns on the relevant tabs. We would recommend someone with Advanced Excel knowledge complete this as any errors will lead to incorrect results.
Exporting the Data from Accounting
-
Log into Accounting as CUSTADMIN or PSFADMIN.
-
Please check the Hold Database to ensure any CHAR or INTR documents are not saved here. To do this, please follow the guidance
-
First you will need to identify which locations do not balance to zero. Go to
It is recommended that you use the Tool to check one period at a time, rather than attempt multiple periods at once due to the amount of data being returned.
-
When the Trial Balance has been run,
-
You will need to run
-
-
In General,
-
Select OK to load the report.
You must ensure you note the correct order of the columns and remove any which appear on the
-
You will need to move/re-arrange the Columns to be in the following order:
-
Nominal
-
Account
-
Doc Date
-
Yearcode
-
Period
-
Value
-
Doctype
-
Docnum
-
Input Date
-
Input Time
-
Userfield 2
-
Userfield 3
You can remove unwanted columns by dragging them upwards off the enquiry
-
-
Once the columns are in the correct order,
-
Save this file.
-
Open a new Detail Enquiry.
-
This time run for the same period, but select One of Intracompany and CHAR document types
-
The columns will need to be in the order as shown below (Please note this is slightly different to the first
-
Nominal
-
Account
-
Doc Date
-
Yearcode
-
Period
-
Value
-
Doctype
-
Docnum
-
Input Date
-
Input Time
-
Userfield 2
-
-
Use the same method as before to re-arrange and remove any unrequired columns and save to Excel.
Using the Tool
-
Open the tool which can be found at the bottom of this guide along with the first
-
On the Tool, select the Red D_DETAILS tab at the bottom. Copy and paste the data into the Blue cell - C2 as indicated below - please do not copy the Headers or 'Grand total' at the bottom of the spreadsheet:
-
Once the data has been pasted, it should look something like the below:
-
Repeat this with the second CHAR/INTR export from Accounting but paste onto the orange INTR_CHAR tab in column B2.
-
Once this has been pasted, click on the green Pivot tab, right-click on the blank pivot table and select 'Refresh'.
-
Remove any (Blanks), Full Stops or other erroneous data from both the Column and Row filters as shown below.
If locations are not showing across the pivot table, check the filters to ensure you have not unselected them.
-
Go back to the orange INTR_CHAR tab and copy the data from column 'L' (not the header) and paste into column A2 on the blue Amended Documents tab.
-
The next step is to remove the duplicates from the tab. To do this, highlight column A, go to the Data section on the ribbon and select the Remove Duplicates icon.
-
When prompted, select 'Expand the Current Selection' and then select only the 'Document' header and click OK.
Investigating Potential Issues
After removing the duplicates, any entries shown in columns AD to AQ are potential issues.
If you check on the orange INTR_CHAR tab, you will be able to see which INTR document relates to the potential issue.
If you do a Document Enquiry on both documents, you will be able to see the issue.
LLPI60 is an LLPI relating to Location 'LLL' for -120.00. On this document 85.00 relates to location 'PSF'. The INTR for this should also be for 85.00 to ensure the Trial Balance balances.
If you look at the INTR document, this is only for 75.00, which is causing the 10.00 variance as shown on the Excel Tool.
The INTR83 document will need to be amended to 85.00 so that it matches the source document.
Once you have worked through all of the required corrections on the orange INTR_CHAR tab, click on thepurple INTR Check tab. This tab will indicate any documents that may be missing an INTR document.
If you check on the orange INTR_CHAR tab in column 'L', you can see there is no POIN.8 which would indicate there is no INTR document for POIN 8.
If you do a Document Enquiry for POIN 8, you can see it is an 'LLL' Location document but also has a detail line for the 'PSF' location for 500.00.
There should be an INTR document for this POIN, but as per the spreadsheet, there isn't one. To correct this an INTR can be added manually form the document input screen.
Once all of the other documents on the purple INTR Check tabhave been investigated and corrected, you can re-run the Trial Balance.
The examples above are generated from a Test environment where the different scenarios have been created in order to show how the Tool should be used. You may find that you have a greater number of documents that require investigation. This tool may not capture all variances, but our own testing has 100% success rate at the time of publishing.