Variance Analysis With Native Versions Made Easy

[#1]

When creating a model using versions, one of the first questions is, “Should I use Native Versions, or should I create a Custom Versions list?” Will I need to do version comparisons; do the comparisons need to be dynamic? With Native Versions, I can create another Variance version and create a formula, but a) it is not dynamic, b) it creates additional sparsity since Native Versions cannot be subsetted, and c) creating a Variance version goes directly against Planual Rule 1.02-02. Before coming to a decision on which approach to use, you should first read this article— To Version or Not to Version—which speaks to the pros and cons of each method from a performance perspective. Within that article, it correctly points out that you cannot use Versions as a list formatted line item, which leads you to believe you can’t do dynamic version analysis because you can’t use Lookups. This article will demonstrate exactly how dynamic version analysis can be accomplished by utilizing a line item subset (LISS).

Step 1: Create your Native Versions in Version Settings.

Step 2: Create a data module.

This module consists of three lists: Product, Native Versions, and Time and has one line item, Data.

Blueprint view:

Step 3: Create a calculation module.

Since Native Versions cannot be list-formatted line items, we need to create a “dynamic” list of the versions in order for the user to choose which version they want. In order to do this, we must turn the versions into line items. Create a CALC module (CALC01 Versions) with the same dimensionality as the data module excluding versions. Create line items for each version.  Each line item will have a formula pointing to the DATA module (DAT01) using a SELECT statement (Selects are ok to use because Native Versions are structural data).

Step 4: Create a Line Item Subset (LISS).

Go to Settings (the gear), below General Lists, and you will see Line Item Subsets. Create a new one by clicking Insert and then type the name of the Line Item Subset (more information on Line Item Subsets can be found here and here). Please be aware of Planual Rule 1.07-01 which speaks to the naming convention of the LISS. Click OK to save the LISS.

Now that it is named, you will need to associate a module(s) to this LISS. Click the ellipses in the right corner and chose the CAL01 module.

The next step is to choose which line items you want to be associated with this LISS. Click Open and select all “version” line items.

Step 5: Create a Collection module to pull the data in from the Line Item Subset.
The dimensionality should be the same as your DAT module with the only difference being the LISS in place of Versions.

For the formula, type in COLLECT() and make sure you turn your Summaries to None.

As you can see, the LISS now looks and behaves like a regular list, a list that can be used in list-formatted line items.

Step 6: Create a module where the end user will select which versions to render.
Since you will not want users stepping on each other, when line items are used, it is best to dimensionalize them by the User list so the results reflect what the user chose. More information on the User list can be found here. Create two line items, Version 1 and Version 2, both formatted as LISS Versions Transactions.

Step 7: Create a reporting module that will eventually be published to an App or a Dashboard.
Again, the dimensionality should be Time, P2 Product, as well as Users. Create three line items, Version 1, Version 2, and Variance with all formats being Number. The formulas should be as follows:

Version 1: 'COL01 Versions'.Data[LOOKUP: 'INP01 Versions'.'Version 1']
Version 2: 'COL01 Versions'.Data[LOOKUP: 'INP01 Versions'.'Version 2']

Variance: 'Version 1' - 'Version 2'

Since we are using a LISS, we can now use the Lookup function which we couldn’t do using Native Versions.

Now, whenever the Input line items are changed, this module automatically reflects the results.

In summary, these are the items needed:

• Create Native Versions in Version settings.
• Transaction module with all Native Versions defined.
• Create a calculation module bringing in data from the transaction module(s) to line items where the line items are your versions (will need to use the Select function).
• Create a Line Item Subset (LISS) from the above module and line items.
• Create a collection module which “dimensionalizes” the line item versions.
• Create a selection module where the user selects which “version.”
• Create a reporting module that will get the data from the collection module using a lookup.

[JaredDolich]

@rob_marshall I knew there had to be a better way. Thanks for sharing this.

[kavinkumar]

Fantastic idea! Great thought process to achieve comparison reports. Thanks Rob for sharing such valuable content. But I am not sure about the feasibility of having user list as it would highly cost the size.

[rob_marshall]

@kavinkumar 

The use of the User list is because the drop downs are line items and due to that, multiple people can be using the drop downs at the same time and thus, you want the system to render the results you are looking for.  Now, you don't have to employ the User list, but it is certainly possible.

Rob

[andrewtye]

As I was reading this I knew it was one of yours @rob_marshall !

Will be having a better read of this and seeing if it can be easily retrofitted. As always excellent way around something that's a bit sticky otherwise.

[rob_marshall]

@andrewtye 

You of all people would have figured that out :). No way I could have hidden this from you.

[einas.ibrahim]

@rob_marshall 

Thanks for laying down this process so clearly
IQ + 1 🙂

[TedemanCPA]

Yep, a Line Item Subset is def. the way to go.  Was looking for exactly this kind of explanation. Thanks so much for the write-up!

[tscott]

I think a major drawback with this method is you still need to create a line item for each version, i.e. a model builder has to get involved every time a new version is created.

In a model where users are creating new scenarios (in the form of versions), the end-user will expect this new version to be automatically included in any variance analysis...

Personally, I think "Custom Versions" are the best approach if there is ever a version variance analysis requirement.

[rob_marshall]

@tscott 

You are correct in that new line items will need to be created, but really, how often are new versions being created?  If you are doing many, it is likely you are doing it wrong and not taking advantage of multi dimensionality.  As for Custom versions, absolutely, those can be used be when you use them, you lose native switchover, formula versions, as well as formula scope.  Again, please understand what this article was meant for, this is more of a How To and not a Pro/Con.

Rob

[tscott]

@rob_marshall 

My comment was not meant to be taken as an attack on this workaround; I was simply stating a significant drawback when circumstances require dynamic variance analysis and scenario modelling. 

It may be that, in your experience, clients do not need functionality to create scenarios (versions) regularly. However, in my industry, this is a very typical requirement for end-users and would not at all be possible with native versions and this workaround.

Further, all the functionality you list as lost when using custom versions can easily and readily be created and, if you would like, I would be more than happy to share how.

Tom

[PhilippErkinger]

Great example, I like it... but it could add significantly to the model size and workspace load if you have many users and the need to analyse several larger dimensions. I had this recently at a customer, where this would have required a >30 GB module. In the end I had to make several smaller modules with not all dimensions and a SUM-LOOKUP logic. I know, not great for the performance but the only option to make it fit into their workspace.

[ChrisHeathcote]

I have recently built a variance report which takes this further.

By using two sets of LISS; one for the headers such as Actual, Forecast, Act v For etc and another for the report line items you are able to create a situation where there is no need to use LOOKUP and whereby you can incorporate ratios such as margin % and other KPIs into your variance analysis.

1. Create a report calculation module and add the header line items that you would like to see in your report. Create a LISS containing all the header line items. Lets call this REP01 : Report Calc
2. Create a report module adding the LISS from step 1 and list all the required line items that you would like to include in the final report. Create a LISS for all line items. Lets call this the REP02 : Report Module.
3. Return to the REP01 : Report Calc module and add the LISS from REP02 as a dimension. 
4. Now working in the REP01 : Report Calc module map all the relevant data into each line item; Actual, Forecast, Budget, Prior Year etc. Also, add formulae to calculate the variances. 
5. If you have multiple versions of each header such as multiple budgets or forecasts create flat lists for each and add these to the module Applies To. If space is a factor remove these dimensions from all other line items except those that reference them directly or are used in the variance calculation. A number of subsidiary line items will be created but the user will be able to select the relevant versions of both budget and forecast via a page selector so the enhanced user experience in this situation outweighs any inconvenience of have these in place.
6. Switching back to the REP02 : Report use COLLECT() to pull through all those line items that do not require a calculation. For items such as Profit, Margin etc that require a calculation enter these and use IF THEN ELSE to check whether the header LISS is a variance. Set up a systems module and use a boolean to query whether the corresponding LISS is a variance and set up the formula to calculate accordingly. Example, if you use positive variances to indicate favourable performance and negative variance to represent adverse performance you will need to flip the Profit calculation from revenue - costs to revenue + costs for LISS items that are variances.
7. If you are including ratios in the report use a series of nested IF THEN ELSE functions to query the nature of the variance using the systems module set up in the previous step. For each variance such as Act v For, Act v Bud, Act v PY etc reference the relevant line items in the report and use SELECT() to isolate the LISS item required to calculate the corresponding variance. Close out the IF THEN ELSE with the final calculation where the value for non variance headers are finally calculated. 
8. For all the calculated line items change the summary setting to FORMULA. This will ensure that the ratios calculate correctly up and across any dimensions. This using COLLECT() can be set to SUM.  

By not using LOOKUP we are able to utilise formula as a our summary method and allow for the calculation of ratios up all relevant hierarchies. The use of two interconnected LISS also simplifies the collection of data into the final report and allows the the isolated calculation of each header in the report calc module. By adding additional lists which allows for multiple versions of a key comparator the user is able to select the relevant budget, forecast or any other comparator. This also means that we do not need to add the users list. 

In my example I also added currency type which allowed the user to convert the report between three different currencies which are set as properties of one of the key dimensions. This list was small consisting of; local, reporting and group, but demonstrates that the reporting can be built up using a number of key dimensions that all the user to view their report across a variety of different views. 

I am still developing the report and would therefore love to hear others thoughts on the above and whether the approach is sound. 

Thanks, 

Chris Heathcote 

[einas.ibrahim]

@ChrisHeathcote 

Very interesting approach. Is it possible to provide a screenshot of how the report(s) look like. I am very curious and intrigued 🙂

-Einas

[rob_marshall]

@PhilippErkinger 

The Users list was used only due to the dynamic style of being able to pick which version (line items) you want to do the variance on.  If that is not the requirement (users not being able to pick), then the Users list is not needed.

[rob_marshall]

@ChrisHeathcote 

You really should write that up in a document (let me know if you are interested and I can facilitate it).  I am sure it would be very helpful for others as it sounds very intriguing.

Rob

[PhilippErkinger]

@rob_marshall  That's clear. 🙂

My experience is just that the number of customers asking for a "flexible user level analysis" is constantly increasing. They see Anaplan developing towards the swiss army knife for planning and BI-like analysis.

And I think that's fantastic. Yet, with todays modelling features, it almost always requires very creative approaches to offer user friendly solutions, like you demonstrated with this variance analysis approach.

However, it's almost never that all users require the same type of analysis. Hence, creating a fake user list with a subset and selective access can also minimise the model size.

[rob_marshall]

@PhilippErkinger 

Agreed, and soon, that will not be a problem (the sparsity that you are talking about) with the Users/Custom Users list.

Rob

[Stacey_Gibbens]

@rob_marshallWhere would we learn about an upcoming native custom users list functionality?  I need that.  🙂     I see the "workaround" at the link below, but your note implies native functionality................ Do tell!

https://community.anaplan.com/t5/Best-Practices/User-List-Subsets-A-Workaround/ta-p/93871

Stacey

[rob_marshall]

@Stacey_Gibbens 

That functionality really hasn't been published just yet, but I do believe it was mentioned in CPX this year.  I was hinting at the Enhanced Calculation Engine (ECE), but again, that will not be released until later this year or beginning of next year.