Avoiding a Circular Reference

neg177
neg177
in Modeling

Hi,

 

Having a problem avoiding a circular reference when it comes to this Anaplan Formula. 

 

 

neg177_0-1621553703345.pngneg177_1-1621553930039.png

The circular refence comes up in the Line Item's  "Excess Funding Sweep" when trying to make equal to the "Calculated MCA Sweep to Excess Funding", because the "Calculated MCA Sweep to Excess Funding" references the "Securitization Master Account Closing" Line Item, which is a sum that includes the "Excess Funding Sweep" Line Item. 

 

Please advise on how to avoid the circular reference, 

 

Thank you 

Tagged:

Best Answer

  • ChrisAHeathcote
    ChrisAHeathcote
    Answer ✓

    @neg177 

    Trying reducing all references to the line item driving the circular reference to the calculation used to derive that line item. 

    This should break the chain and ensure the formulas work in parallel rather than in series which is why it is causing the circular reference. 

Answers

  • neg177
    neg177

    Hi, 

     

    Still having a problem getting around this circular reference. The formula I'm using is below. The Opening Balance Line Item is causing the circular reference because it is being called upon twice. 

     

    Formula For Line Item that is saying Circular Reference 

    IF 'S01 Time Settings'.'10th of every Month?' THEN Borrowing Base Adjustment[LOOKUP: 'S01 Time Settings'.Last day of Previous Month] + Cash Collected[LOOKUP: 'S01 Time Settings'.Last day of Previous Month] + '(Cash Sweep to Wells)'[LOOKUP: 'S01 Time Settings'.Last day of Previous Month] + 'BALANCING FIGURE FOR x5000'[LOOKUP: 'S01 Time Settings'.Last day of Previous Month] + 'Securitization Master Acct (x5000 Opening)'[LOOKUP: 'S01 Time Settings'.Last day of Previous Month] ELSE 0

     

    Formula that is also calling the Opening Balance 

    Borrowing Base Adjustment + Cash Collected + '(Cash Sweep to Wells)' + 'BALANCING FIGURE FOR x5000' + 'Securitization Master Acct (x5000 Opening)' + Excess Funding Sweep

     

    If you need more explanation I can try and be more specific. 

     

    Thank you 

     

     

  • ChrisAHeathcote
    ChrisAHeathcote

    @neg177 

    If you are trying to force a closing value based on a balancing figure then you may not be able to avoid the circular reference by continuing to reference the offending line item.

     

    The only other method available to you is to use an action to effectively cut and paste the balancing figure.

    Create a new line item which contains no formula. Import into that new line item the calculated balancing figure and then use this in all downstream calculations.  

Categories