FIFO Allocation

[jorelb]

I am building a Revenue Recognition cube (named FIFO Allocation)) using FIFO Allocation on product shipments and encountering some difficulties. The cube has 10 dimensions with a measure and 2 time dimensions; Period and Period RevRec. Shipments are recorded in the intersection of ‘Period Rev Rec’:’Period 0’ and the appropriate shipment period. When it is determined that we can recognize X number of shipments at a particular revenue recognition period, the user just needs to enter the X number in the intersection of the appropriate Period Rev Rec element and Period 0 of the Shipment period dimension and the model does the rest – it spreads the number of shipment that needs to be recognized. The problem is when we need reach the next revenue recognition period, the model restarts from the beginning.

Attached is an excel file with some screen shots and below is the rule that does the unit spread.

#Spreads the amount to be recognized based on what was shipped. First and second IF statements are trying to determine whether the shipment period is the the first period from the Quantity LTD (Life to Date) measure

['Quantity Allocated'] = N: IF ( DB('FIFO Allocation',!Forecast,!Version,!End Customer,!Deployment Type,!Pipeline Category,!Solution,!Revenue Type,'Quantity LTD',!Period,'Period 0') = 0, 0 ,
IF ( DB('FIFO Allocation',!Forecast,!Version,!End Customer,!Deployment Type,!Pipeline Category,!Solution,!Revenue Type,'Quantity LTD',!Period,'Period 0') <> 0 &
DB('FIFO Allocation',!Forecast,!Version,!End Customer,!Deployment Type,!Pipeline Category,!Solution,!Revenue Type,'Quantity LTD',TRIM(STR(NUMBR(ATTRS('Period',!Period,'SeriesNum'))-1,6,0)), 'Period 0') = 0,

#As soon as the 1st shipment is found, then it determines if the amount to be allocated is less than what was shipped that period. If it is less than what was shipped, it plugs the whole amount and if the amount that needs to get recognized
#the model only plugs in the max which is the quantity shipped for the period

IF(DB('FIFO Allocation',!Forecast,!Version,!End Customer,!Deployment Type,!Pipeline Category,!Solution,!Revenue Type,'Quantity Allocated','Period 0', !Period Rev Rec)<=
DB('FIFO Allocation',!Forecast,!Version,!End Customer,!Deployment Type,!Pipeline Category,!Solution,!Revenue Type,'Quantity',TRIM(STR(NUMBR(ATTRS('Period',!Period,'SeriesNum'))-0,6,0)),'Period 0'),
DB('FIFO Allocation',!Forecast,!Version,!End Customer,!Deployment Type,!Pipeline Category,!Solution,!Revenue Type,'Quantity Allocated','Period 0',!Period Rev Rec),
DB('FIFO Allocation',!Forecast,!Version,!End Customer,!Deployment Type,!Pipeline Category,!Solution,!Revenue Type,'Quantity',!Period,'Period 0') ),

#If the period is not the 1st shipment, then it determines if the 'Quantity Balance' the prior period is less than the current quantity, if it is then it plugs the 'Quantity Balance'. If not, it plugs the current Quantity number

IF( DB('FIFO Allocation',!Forecast,!Version,!End Customer,!Deployment Type,!Pipeline Category,!Solution,!Revenue Type,'Quantity Balance',TRIM(STR(NUMBR(ATTRS('Period',!Period,'SeriesNum'))-1,6,0)),!Period Rev Rec)<=
DB('FIFO Allocation',!Forecast,!Version,!End Customer,!Deployment Type,!Pipeline Category,!Solution,!Revenue Type,'Quantity',TRIM(STR(NUMBR(ATTRS('Period',!Period,'SeriesNum')),6,0)),'Period 0'),
DB('FIFO Allocation',!Forecast,!Version,!End Customer,!Deployment Type,!Pipeline Category,!Solution,!Revenue Type,'Quantity Balance',TRIM(STR(NUMBR(ATTRS('Period',!Period,'SeriesNum'))-1,6,0)),!Period Rev Rec),
DB('FIFO Allocation',!Forecast,!Version,!End Customer,!Deployment Type,!Pipeline Category,!Solution,!Revenue Type,'Quantity',TRIM(STR(NUMBR(ATTRS('Period',!Period,'SeriesNum')),6,0)),'Period 0'))
));

#Then it loops.

I modified the code to compare the Total Allocated number with Period 0 (code below).

#The first IF statement test if the Quantity Allocated for All Rev Rec Periods (excluding Period 0) is less than the value of Period 0, if it is less than Period 0, it follows the standard logic
['Quantity Allocated'] = N: IF(DB('FIFO Allocation',!Forecast,!Version,!End Customer,!Deployment Type,!Pipeline Category,!Solution,!Revenue Type,'Quantity Allocated',!Period,'All Rev Rec Periods')<
DB('FIFO Allocation',!Forecast,!Version,!End Customer,!Deployment Type,!Pipeline Category,!Solution,!Revenue Type,'Quantity Allocated',!Period,'Period 0'),

#Same logic as above
IF ( DB('FIFO Allocation',!Forecast,!Version,!End Customer,!Deployment Type,!Pipeline Category,!Solution,!Revenue Type,'Quantity LTD',!Period,'Period 0') = 0, 0 ,
IF ( DB('FIFO Allocation',!Forecast,!Version,!End Customer,!Deployment Type,!Pipeline Category,!Solution,!Revenue Type,'Quantity LTD',!Period,'Period 0') <> 0 &
DB('FIFO Allocation',!Forecast,!Version,!End Customer,!Deployment Type,!Pipeline Category,!Solution,!Revenue Type,'Quantity LTD',TRIM(STR(NUMBR(ATTRS('Period',!Period,'SeriesNum'))-1,6,0)), 'Period 0') = 0,

#As soon as the 1st shipment is found, then it determines if the amount to be allocated is less than what was shipped that period. If it is less than what was shipped, it plugs the whole amount and if the amount that needs to get recognized
#the model only plugs in the max which is the quantity shipped for the period
IF(DB('FIFO Allocation',!Forecast,!Version,!End Customer,!Deployment Type,!Pipeline Category,!Solution,!Revenue Type,'Quantity Allocated','Period 0', !Period Rev Rec)<=
DB('FIFO Allocation',!Forecast,!Version,!End Customer,!Deployment Type,!Pipeline Category,!Solution,!Revenue Type,'Quantity',TRIM(STR(NUMBR(ATTRS('Period',!Period,'SeriesNum'))-0,6,0)),'Period 0'),
DB('FIFO Allocation',!Forecast,!Version,!End Customer,!Deployment Type,!Pipeline Category,!Solution,!Revenue Type,'Quantity Allocated','Period 0',!Period Rev Rec),
DB('FIFO Allocation',!Forecast,!Version,!End Customer,!Deployment Type,!Pipeline Category,!Solution,!Revenue Type,'Quantity',!Period,'Period 0') ),

#If the period is not the 1st shipment, then it determines if the 'Quantity Balance' the prior period is less than the current quantity, if it is then it plugs the 'Quantity Balance'. If not, it plugs the current Quantity number
IF( DB('FIFO Allocation',!Forecast,!Version,!End Customer,!Deployment Type,!Pipeline Category,!Solution,!Revenue Type,'Quantity Balance',TRIM(STR(NUMBR(ATTRS('Period',!Period,'SeriesNum'))-1,6,0)),!Period Rev Rec)<=
DB('FIFO Allocation',!Forecast,!Version,!End Customer,!Deployment Type,!Pipeline Category,!Solution,!Revenue Type,'Quantity',TRIM(STR(NUMBR(ATTRS('Period',!Period,'SeriesNum')),6,0)),'Period 0'),
DB('FIFO Allocation',!Forecast,!Version,!End Customer,!Deployment Type,!Pipeline Category,!Solution,!Revenue Type,'Quantity Balance',TRIM(STR(NUMBR(ATTRS('Period',!Period,'SeriesNum'))-1,6,0)),!Period Rev Rec),
DB('FIFO Allocation',!Forecast,!Version,!End Customer,!Deployment Type,!Pipeline Category,!Solution,!Revenue Type,'Quantity',TRIM(STR(NUMBR(ATTRS('Period',!Period,'SeriesNum')),6,0)),'Period 0'))
)),

# IF the Quantity Allocated for All Rev Rec Periods (excluding Period 0) is greater than the value of Period 0 then I just plug in a 1000 (I am doing this to test if the logic will work. If it works, I will build the rest.
1000);


However, after I save the rule and my view returns #N/A and the rule tracer indicates: RUNTIME ERROR: Circular reference.

I tried different ways of trying to resolve the the circular reference issue but I can’t pass it. Can anyone suggest a solution?

Thanks

[jorelb]

I was able to resolve this issue by using both RULEs and TI. Rules were used to calculate balances and the TI performs the FIFO allocation based on the Rule calculated balances thereby avoiding the circular reference issue.
However, I was almost certain that this particular scenario could be done using rules exclusively. Is there documentation on how rules are processed? From what I know, rules are executed from the beginning to the end of the RUX file but how does it execute within the elements of the different dimensions of the affected cubes – by dimension order, element index?

Thank you

[Gregor Koch]

Hi
The order of Dimensions and Elements have no effect as to how (which order) the Rules are calculated. The Element Index will only come into play if you use DIMIX or similar.
The order of the Rules only matters if you define several Rules for the same area in the cube in which case the first one will apply.
So they are not really executed in the order of the RUX file, this means that a Rule that is at the bottom of the Rule file can depend on the calculation of another which is at the top (or on any other rule in other cubes).

Regards

[tomok]

I was able to resolve this issue by using both RULEs and TI. Rules were used to calculate balances and the TI performs the FIFO allocation based on the Rule calculated balances thereby avoiding the circular reference issue.
However, I was almost certain that this particular scenario could be done using rules exclusively.

Almost anything can be done by rules if you organize your cube to allow it but a circular reference is a circular reference. TM1 works just like an Excel spreadsheet. If Cell A1 = B1 x C1 then B1 cannot be something like B1 = A1 / C1. The same thing holds true in a cube. One area in a cube cannot be dependent on another area which in turn is dependent on the original area. Just like in Excel, the order of the formulas have nothing to do with whether or not something has a circular reference.

Many times the solution to your type of situation is to add another dimension to the model. One element of the new dimension can hold the balances, a second element can hold the FIFO allocation, and then a third element, called something like "Final" has some IF logic that decides whether to populate Final with the balance or FIFO allocation. Sorry, I can't be more specific than that but something along those lines might contain the answer to your situation.