My client has purchased the Connector and Report Designer modules for Sage Intelligence Reporting (SIR), plus I have installed the Report Designer Add-In. They are on Sage 500 v. 7.4, with the SIR version for that. They have two companies for which they need to do consolidated reporting: a manufacturing company (Co A) and a distribution company (Co B). Co A sells more than half its manufactured goods to Co B, and those sales go through a general Sales GL Account, so are not separated out in Co A's GL. In accordance with Canadian accounting principles, when reporting on two such closely related companies, you must eliminate intercompany amounts. So on an income statement, you must have a column, called Eliminations, which excludes the sales of Co A to Co B. In FRx, I used the purchases of Co B, since they would be at the same amount as the sales of Co A, and since Co B buys only from Co A. But it is a fussy calculation: Purchases = Ending Inventory (an Asset account) for the period you are reporting on MINUS Opening Inventory for the year you are reporting on PLUS Cost of Sales YTD. This works in FRx, but I cannot figure out how I am going to do this using SIR. Has anyone done this yet? (This is for YTD totals only, not for period data.)
Also, in the canned reports, SIR is consolidating the two companies' data into a single column, whereas my client needs to see a column per company, plus the elimination column, plus a total column Does anyone have any suggestions on how to break that out, without starting from scratch?
have you spoken to Sage support about this? I have had very good experience with the guys from the intelligence store they usually are very helpful with creating reports.
please try going to they site and see if you can get someone to assist with the report creation
The Intelligence Store: http://theintelligencestore.com/ which should be the first place customers look for reports which have already been done, and if they do not find what they looking for here to contact a consultant.
For the list of certified consultants for Report Customisation, please go to the following link on our website:
Thanks for your reply. However:
Followed the store link, but there is nothing there for Sage 500, which is my focus.
Followed the FindABusinessPartner link, and got this: "Oops! This webpage no longer exists."
Any other thoughts?
Can I ask how the intercompany eliminations are done in FRx?
This is the link Nanah referred to:
Not sure about Sage 500, but with Sage 300, we have Report Designer Add-In that using Reporting Trees to report by company is pretty straight forward.
I would consider using dummy company for the elimination entry.
Company A sales account = 4000-A $10000
Company B sales account =4000-B $20000
Elimination required $5000
I will create the 3rd consolidated company that just process for the $5000
In this 3rd company, you only need relevant account created, eg: account 4000 in this case.
The above has assumed that you chart of account is consistent for the companies.
Otherwise you might need to have a field in the individual company for mapping.
In Sage 300, we have something called Optional Field.
Company A sales account = 4000-A-001 $10000, map to consolidated account 4000
Company B sales account =4000-B $20000, map to consolidated account 4000
In the dummy consolidated company, it captures elimination entry against 4000 account.
Hi Shwu Hua, is this refer to Profit Elimination? I'm a bit new to Sage 300 SI. Need some guidance for this. Thanks
It refers to intercompany elimination, which can be intercompany sales /profit elimination, intercompany loan, intercompany payment etc.
In short, create some logic that the program to identify intercompany data by turning manual process to automation.
In normal consolidation excel worksheet, it has company A, company B, elimination columns. The questions we need to answer is how do we setup in Sage so that we can retrieve the data and populate the elimination columns for intercompany transactions.
Thanks for sharing. There is a difference of process and reporting.
Ultimately, if you need to produce consolidating reports, it comes down to what data you have, how they are captured and how to retrieve and displayed into reports that meet reporting purpose.
Hi, if you are looking for consolidation and currency conversion of Sage 300 companies, we have several clients doing it. You can email me to firstname.lastname@example.org