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

Options

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:

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:

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:

Options

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

Thanks,

Misbah

Miz Logix

• Options

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

Miz Logix

• Options

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.

• Options
Thank you so much Misbah
• Options

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

• Options

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