Circular References and 5 year Forecast




Circular References and 5 year Forecast.


I am trying to use the Prior Year's final forecast as the starting point for the new year and get circular references because of the calculations in between.

I am using the Previous year's Actuals as the starting point which is fine as this line does not relate to the calculation.

I am using a summary module by year and a detailed module by month.

I'm sure this has been done many times before so I am hoping someone has a simple workaround for me?


Thank you


Best Answer

  • LaurieW
    Answer ✓

    Hi Andrew,


    I managed to resolve this with help from my manager.

    I was using LOOKUP on a Year or a previous year which was causing the problem.

    I had to change this to YEARVALUE. Where it was part of a formula that did other LOOKUPs I created an extra line item for the YEARVALUE and then used that line item.


    I was using LAG in another line item and it is really great but that wasn't where my problem was.


    Much appreciated 





  • Hi @LaurieW 

    Does the following thread help you?

    If you're using LAG or similar then include the STRICT parameter.


  • @LaurieW 

    I have found that if you reference all you data sources independently and do not link line item you will be able to untie the circular reference. 


    However, for this to work you also need to write out all the calculations in full which originate from using the source data. 

    Create a section in you model which pulls the source data in and reference this line item in all calculations. 

    You will start to see quite large formulas develop especially where you use the source in multiple calculations which are then brought together in downstream calculations. 

    Despite this it is the simplest method of eliminating circular references in your modules. 

  • Thank you very much. Please see my reply above.