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