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:
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!
Best 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
Thanks,
Misbah
Miz Logix
1
Answers

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
0 
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.
0 
Thank you so much Misbah
0 
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
0 
It took me around 15 mins to find out this post of mine which goes back to 2020. This will helpThanks,
Misbah
Miz Logix
1