TM1 Forum

Hi All
9.4 SP4
MS Excel 2003

I have written a rule that works perfectly, it acts as a balancing item in our Balance Sheet cube. The general contex is
If the Absolute value of Assets are < than the Absolute value of Liabilities, place Assets Minus Liabilities in an Account that rolls up into Assets. However when Itry to write the feeder i get #N/A's in the cube.

Rule:['Local Currency','Periodic','CG11210_b','A000001_IAS','INTL - Primary']=N:IF(
ABS(['Local Currency','Periodic','CG11210_b','A000010_IAS_CG','End of Month Balance','INTL - Primary'])<ABS(['Local Currency','Periodic','CG11210_b' ,'L000012_IAS_CG','End of Month Balance','INTL - Primary'])
,ABS(['Local Currency','Periodic','CG11210_b','A000010_IAS_CG','End of Month Balance','INTL - Primary'])-ABS(['Local Currency','Periodic','CG11210_b','L000012_IAS_CG','End of Month Balance','INTL - Primary'])
,0);

Can anyone provide assistance with the best way to go with feeding the above

Regards Jon

Could you have a infinite loop? e.g. Does A000001_IAS roll up into A000010_IAS_CG or L000012_IAS_CG?

Matt

It would be unusal for a feeder to trigger a #NA error, unless there was some nesting or rolling forward logic happening.
I've not thought hard about it but is the logic a bit broken? If assets are > than liabilities, you'll get the wrong balancing figure?

Also IMO self balancing balance sheets are the devils work. If the balance sheet does not balance then your data feed is wrong or double entry has not been preserved and you need to know that something is broken not hide it with a balancing amount.

All that said the feeder would be

['Local Currency','Periodic','CG11210_b','L000012_IAS_CG','End of Month Balance','INTL - Primary']=>['Local Currency','Periodic','CG11210_b','A000001_IAS','INTL - Primary'];
['Local Currency','Periodic','CG11210_b','A000010_IAS_CG','End of Month Balance','INTL - Primary']=>['Local Currency','Periodic','CG11210_b','A000001_IAS','INTL - Primary'];

HTH

Steve many thanks for the reply, totaly agree with you on the false balancing item however this will only operate at a department level with further rules placing the unbalanced figure at a total company level. Tried you feeder suggestion but still getting #N/A's.

Long shot but should the rollups for Assets and Liabilities not include the balancing account for the rules as to me this seems almost like a circular reference ? Just thinking aloud (hoping noone can hear me)!
Actually just noticed Matts post above just thinking the same thing thanks for your assistance guys
Regards Jon

Try taking out the N: on the right hand side, and re-running the rule.

Regards

jonchrista wrote:Long shot but should the rollups for Assets and Liabilities not include the balancing account for the rules as to me this seems almost like a circular reference ?

You could do that, but you probably just want to re-write your rule to not pull from the Assets consolidation but instead sum up all subconsolidations to avoid including your balancing element. e.g.:
Matt

Matt, Steve

Hi Guys

Created the new rollups without the balancing item in them and using Steve's feeder all works perfectly
Thanks very much have a virtual beer on me !

Regards jon