Budget based on prior year outputs whilst avoiding circular references - help needed!


I have some minor account / cost centre values that I want to flex year on year based on the % change of a proxy and inflation.


The solution I have tried below is creating circular references in the final part:


Year 1: Period 1

              Base value (final month of actuals)

              Inflation %

              Period budget (base value * inflation %)


Year 1: Period 2 onwards           

              Prior period value

              Proxy change %

              Period budget (prior period value * proxy change %)


Year 2, Year 3 and Year 4:

              Prior full year value

              Proxy change %

              Inflation %

              Full year budget (prior full year * proxy change % * inflation %)


The following are what the formulas are:


Formulas Final.jpg

The above embedded IF THEN ELSE (for the line £s) works if the final ELSE has 0 but when I add the final element of the calculation it creates a circular reference:

Circular Ref.jpg

If any of you can spot where I’m going wrong or have a more elegant solution (trying to avoid manual interventions) then please let me know.


Thank you!


Best Answer

  • Misbah
    Answer ✓



    Reiterating that there is no telescopic view in Anaplan hence my recommendation is to split the modules. See if this helps

    Step 1: Create an Input module which stores base value and inflation percentage


    Step 2: Crete SYS TIME module



    Step 3: Create CALC Monthly module 




    Step 4: Create CALC Yearly module






    Miz Logix


  • @NathanB74 


    Would it be possible for you to create a working calculations in excel?


    Not sure what do you mean by Year 2 onwards, Anaplan doesn't support telescopic view for Time. You have mentioned periods in Year 1 but no periods from year 2 onwards. I started creating but I got lost in between.




    Miz Logix

  • Hi Misbah,


    Thanks for looking into this.  In excel it would look like this:



    In Anaplan for year 2 onwards I only produce full year numbers in P12 so I am picking up the full Year 1 with the formula LAG(YEARTODATE(£s), 12, 0) in the 'Prior Year OY £' line item.  It does seem that this is what is creating the circular reference.  It is ok by itself but when I bring that line item into the nested IF THEN ELSE in line item '£s' the warning is shown.

  • Thank you so much Misbah
  • Finished updating my model and your suggestion worked a dream.


    I have a question about part of your final model in the example - you put the following in an IF THEN ELSE and I couldn't work out how (why) it works:  offset(1,-1,0)  I get the -1,0 part but confused why the value to offset is 1.  Don't get me wrong - it works but would be good to understand why!   



  • @NathanB74  

    It took me around 15 mins to find out this post of mine which goes back to 2020. This will help 






    Miz Logix