Need exclude inter-company revenue from top level company revenue calculation

[#1]

Hello Eveyone,

I need exclude inter-company revenue (department charge to each other) from the top level company(Level 1) revenue but still keep in under level(Level 2 and below). Do you have any idea to do that.

Thanks in advance,

De

[#2]

Hi,

You cando that if you create fictitious departments'intercodepartments" that will receive intercompany adjustments. You can create them at various level to have intercos at various level.

In order to easily see consolidated data adjusted or not, just use an additional field to mark interco data as “interco”, and use a filter to include them in the query or not

TOTAL                                                 430

TOTALREGION1                                   180

            DEP1.1                                       100

            DEP1.2                                       100

            IntercoRegion1                        -20

TOTALREGION2                                   270

            DEP2.1                                       200

            DEP2.2                                       100

            IntercoRegion2                        -30

INTERCOREGIONAL                               -20

                Intercobetweenregions             -20

... hope this helps

James

[#3]

Hi James,

Thank you very much for you suggestion. I have a indicator fo the inter-copany revenue. But how can I exclude the inter-company revenue when roll-up to the highest level (All Company) and include inter-company revenue when roll-down.

Do you have any expression in mind?

Thanks,

De

[#4]

OK, I see.

What about if you do the opposite of my proposition:

If you have an indicator for interco revenue, you can set it negative on my interco department, so that your total comes back to total sales

	Sales	-     Interco sales	Total sales
TOTALREGION1	200	0	200
Dep1.1	100	10	90
Dep1.2	100	10	90
Interco region1		-20	20

[#5]

Hi James,

Thank you very much.

Based on your data, With normal rollup, the program will show 200 in TOTALREGION1 but we want to show 180 at TOTALREGION but still need to show 100 for each Dept when roll down.

Can I say if rollup to TOTALREGION1 then don't add Interco Sales or others total whatever it is.

I don't know setup rollup and rolldown on different calculation.

Thank you for your thoughts. I appreciate.

De

[#6]

OK,

I guess then you need to split an interco elimination indicator from you intercor sales indicator, to have:

Eliminated Sales = Sales - Interco Elimination, and apart Interco sales details

Header 1	Sales	-Interco elim	=elimated sales	Detailed intercos
Total region1	200	20	180	20
Dep1.1	100		100	10
Dep1.2	100		100	10
ElimRegion1		20	-20

To calculate your Interco Elim indicator, you need detailed interco information and a table that gives what ElimDepartment will be set for a sale between Dep1.1. and Dep1.2, and group by through this relation.

Going back to my first atttemp, you would then specify thaan an interco sales between Dep1.1 and Dep2.1 would eliminate on ElimTOTAL; or between Dep2.1. and Dep2.2 would elim on ElimRegion2.

In that cases, I really prefer to split various indicators and not try to have somehow "180=100+100", because although some users ask for that, you have a 80% probability that they will get back to you in some monthes to say this is confusing some users and they need to change it...

There is also another argument to push the "Detailed interco" out of the total sales: if you load it with details of "from" and "to" department, you will get a new report that gives lots of information for the users. So you have Detailed intercos for one detailed report, and "Interco Elimination" for sales report...

Regards,

James