Help on circular reference

LouiseDepaquis
LouiseDepaquis
in Modeling

Hello, 

 

During my training, I had to build the Inventory ordering module for the supply chain model (level 2). After building it following the instruction, I'd like to test new formulas to determine the Suggested Order Amount for the Month.

I'd liked it to be equal to the Forecast demand + Safety stock target - beginning Inventory

However, when I type this formula, an error window shows a circular reference. 

 

LouiseDepaquis_0-1643271149953.png

I really can't figure out where the problem comes from since none of the line items considered refer to themselves : 

 

Beginning Inventory = IF 'SYS01 Time Settings By Week'.First Week of Timescale? THEN 'DAT01 Beginning Inventory'.Beginning Inventory ELSE PREVIOUS(Ending Inventory)

 

Forecast Demand = 'DEM03 Demand Forecast'.Final Forecast

 

Safety Stock Target = HALFYEARVALUE('DAT04 Distribution Center Parameters'.Weekly Safety Stock)

 

The only thing that could be blocking is the fact that the beginning inventory is determined according to the ending inventory itself determined by the Suggested Order Amount for the Month. However the reference to the ending inventory is supposed to be delay regarding time and therefore avoid any circular reference. 

 

I hope I was clear and that someone can answer my questions. If there is a need to add any other elements to answer, please let me know.

 

Thanks in advance ! 

 

Tagged:

Best Answer

  • JohnP
    JohnP
    Answer ✓

    Hi @LouiseDepaquis,

     

    I'm afraid I don't know how far you are through Level 2, so I hope the below reflects what's in your model.

    I agree that the issue arises from the reference to 'Beginning Inventory' (a good way to identify the problematic part of a formula is to try out each part of the formula individually i.e. if you just make the formula for 'Suggested Order Amount for the Month' equal to 'Beginning Inventory', you will get the same circularity error).

    The likely reason that 'Beginning Inventory' is causing issues is that it contains PREVIOUS(Ending Inventory) and 'Ending Inventory' references 'Confirm Purchase Order Receipt'. 'Confirm Purchase Order Receipt' is the problem, as the formula contains POST(Final Shipment Amount, Final Shipping Time Weeks).

    The POST function can actually take a negative number as its second argument (the number of periods to offset the value in the first argument), which means it then posts the value into the past instead of the future. So if 'Final Shipping Time Weeks' is negative, the function posts the value for 'Final Shipment Amount' into the past. 

    This is a problem as the formula for 'Final Shipment Amount' references 'Suggested Order Amount for the Month' so, if somehow the 'Final Shipping Time Weeks' is a negative number (in this example -1), the formula references result in the below circular reference:

    JohnP_0-1643284090368.png

    As I mentioned above, the formula for 'Confirm Purchase Order Receipt' contains POST(Final Shipment Amount, Final Shipping Time Weeks). Naturally, in practice it's impossible to have a negative shipping time however, as the second argument refers to a line item (which could return a negative value), the calculation engine cannot exclude the possibility that the second argument in POST might be negative and thus result in circularity, so it throws the circularity error that you have seen.

    (In this particular example, the circularity does not occur when a positive value is used as the offset instead of 'Final Shipping Time Weeks' in the formula for 'Confirm Purchase Order Receipt', e.g. if the formula contains POST(Final Shipment Amount, 2) instead, 'Suggested Order Amount for the Month' can reference 'Beginning Inventory' without any circularity occurring.)

     

    This is my understanding of it, but if anyone spots any mistakes please jump in and correct me!


    Hope this helps 🙂

     

    Best,

    John

     

     

Answers

  • LouiseDepaquis
    LouiseDepaquis

    Thank you very much for this answer it's very clear ! Therefore, do you have in mind any other calculation function that would fit for this use and avoid the circular reference problem ? 

     

Categories