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.jpg

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!

Tagged:

Best Answer

  • Misbah
    Answer ✓

    @NathanB74 

     

    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

    Misbah_0-1664979672865.png

    Step 2: Crete SYS TIME module

    Misbah_1-1664979761494.png

     

    Step 3: Create CALC Monthly module 

    Misbah_2-1664979778806.png

    Misbah_3-1664979789854.png

     

    Step 4: Create CALC Yearly module

    Misbah_4-1664979812334.png

    Misbah_5-1664979826127.png

     

    Thanks,

    Misbah 

    Miz Logix

Answers

  • @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.

    Misbah_0-1664968574595.png

     

    Misbah

    Miz Logix

  • Hi Misbah,

     

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

     

    Excel.jpg

    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!   

     

    Thanks

  • @NathanB74  


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

     

    https://community.anaplan.com/t5/Academy-Training/Level-2-Sprint-3-INV01-Not-1st-week-of-Timescale/td-p/72778

     

    Thanks,

    Misbah

    Miz Logix