How to vary the calculation used for each version for a single line item?

OliverMunns · September 2021

I have a large model that is used to generate a business forecast. Each time we need to generate a new forecast or scenario, a new version is added so that the inputs can be changed and the outputs then compared to previous versions.

However, sometimes the calculations themselves need to change to reflect our evolving business model. When this happens, we don't want to change the results of previous versions, only new versions.

I am familiar with the method of using IF statements, combined with bools to identify versions, to list different calculations for different versions in one long statement. However, as time goes on and with many line items affected, this becomes very untidy and difficult to track.

Is there any other easier/more scalable way to apply a different calculation for different versions on the same line item?

Akhtar.shahbaz · September 2021

Hi @OliverMunns ,

You can refer to the below link, this will give you some idea for optimizing the formula.

https://community.anaplan.com/t5/How-To/Variance-Analysis-With-Native-Versions-Made-Easy/ta-p/98336

OliverMunns · September 2021

Hi @Akhtar.shahbaz,

I've read through the article. This looks useful for addressing variance analysis but I don't see how this could be applied to the problem that I raised, regarding applying different calculations to the same line item for different versions?

ChrisAHeathcote · September 2021

@OliverMunns

You will need to use a series of nested IF THEN ELSE queries to direct the calculation to run a specific formula for each version. Use a systems module of versions to cycle through each iteration of version using a boolean line item each time to indicate which version is being queried.

Example -

=IF VersionModule.Forecast THEN X ELSE IF VersionModule.Budget THEN Y ELSE IF VersionModule.Actual THEN Y ELSE 0

For each IF THEN ELSE you are using a boolean in a systems module to tell Anaplan that for that version(s) run the calculation after the THEN otherwise continue to the next query.

rob_marshall · September 2021

@OliverMunns @ChrisAHeathcote

I would definitely shy away from the nested IF statements for the following reasons:

performance
scalability
maintenance
enablement.

@OliverMunns if you are using Native Versions (out of the box Versions), have you considered Version Formula?

https://help.anaplan.com/10c7423e-7824-4309-a192-93c6c57d3b63-Add-Version-Formula-

In using Version formula, you will need to create a line item subset (LISS), but this will get you home AND get you away from the nasty nested IF THEN ELSE statements.

Rob

ChrisAHeathcote · September 2021

@rob_marshall

Thanks for nudging me in this direction.

I have not used Version Formula before and it really is a great piece of functionality.

Nested IF THEN ELSE are horrid but I didnt realise there was an alternative.

Thanks,

rob_marshall · September 2021

@ChrisAHeathcote

It really is a cool piece of functionality that not a lot of people know about. You should know this by now, I have your back!

OliverMunns · September 2021

Hi @rob_marshall,

I've had a look into version formula. It potentially looks promising but am I reading correctly that a single formula will apply to all of the line items in the LISS that you create? If so, how would this work where all line items have their own (already complicated) formula?

Do you know if there are any example models where this is demonstrated at a reasonable scale?

Thanks.

rob_marshall · September 2021

@OliverMunns

Nothing is stated that you can't have multiple LISS's defined. But in reality, I guess it depends on your calcs and what you are attempting to accomplish.

Rob

Megha27 · February 16

@OliverMunns Any luck with this? I am running into the same issue and I dont quite understand how creating a new version just for a formula change will help me? We create versions on an ongoing basis ( 4-5 every year) and often times we realize that a specific logic needs to be updated to better align with the business or for optimization purposes but I want to make sure I dont change the numbers of any of the previous versions.

I am not keen on using nested if then as it will no longer be dynamic and will need routine maintenance when a new version is created.

@rob_marshall thoughts?

rob_marshall · February 17

@Megha27

First question: Are you using native versions or did you create a custom versions list?

Second question: why type of logic are you doing? Variance Analysis or other?

Megha27 · February 17

Hi @rob_marshall

Thank you for taking the time out to respond to me. I am not exactly sure what native version vs custom version mean. I am not able to find any documentation either so pls point me to the right resources so I can understand the difference between the two, if you can. However here are the examples of versions I created throughout the year - 2022 Budget, 2022 Jan FC, 2022 April FC, 2022 July FC, 2022 Oct FC in the last year. Obviously we have a version called Actual as well. I change the switchover period every time I create new versions like these so I can have a rolling FC and mark the relevant version as Current.

To answer your second question - No its not variance analysis that I am trying to do. Below is what I trying to do:

In a module called Booking Calculation - the current logic is:

IF

ISACTUALVERSION()

THEN

'Sales Actuals-Bookings'.'S/S Amount'

ELSE

MOVINGSUM('S/S Sales', -12, -12) * (1 + Sales Growth Rate Assumption.Sales Growth Rate[LOOKUP: 'PR L3'])

which means its referring to in case all non-actual versions, its referring to a different module called Sales Growth Rate Assumption to get the % growth and apply that to S/S Amount line item.

What I want to do:

Keep the logic as is for the FC versions so far in the year but going forward when I create a new version called 2023 April FC - I dont want to look at the Sales Growth Rate Assumption module and apply a % growth to get to my FC booking number instead, I want this to reference a new Module called Bookings Input ( where my bookings person is straightup able to enter an absolute value for Booking( S/S Amount) for forecast. So thats my logic change to simplify how we get enter the Bookings number in our system.

I want to keep the number as is for the FC/Budget done so far in the year but change it only on a go-forward basis. How do I do that?

I am not keen on nested if else because this is the logic I want to carry forward so I dont want to update the formula everytime I create a new version.

Let me know if this explains my issue better and if you have any questions for me.

Thank you,

Megha