Variance Analysis With Native Versions Made Easy
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). 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. 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.
Comments
-
@rob_marshall I knew there had to be a better way. Thanks for sharing this.
3 -
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.
1 -
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
3 -
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.
0 -
You of all people would have figured that out
. No way I could have hidden this from you.1 -
@rob_marshall
Thanks for laying down this process so clearly
IQ + 1 🙂2 -
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!
0 -
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.
1 -
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
3 -
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
0 -
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.
0 -
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.
- 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
- 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.
- Return to the REP01 : Report Calc module and add the LISS from REP02 as a dimension.
- 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.
- 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.
- 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.
- 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.
- 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
3 -
@ChrisAHeathcote
Very interesting approach. Is it possible to provide a screenshot of how the report(s) look like. I am very curious and intrigued 🙂-Einas
0 -
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.
0 -
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
2 -
@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.
2 -
Agreed, and soon, that will not be a problem (the sparsity that you are talking about) with the Users/Custom Users list.
Rob
2 -
@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
0 -
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.
1 -
If there are multiple line items with different number formats like % and $. These formats dont flow into the variance analysis.
Is there a workaround for that?0 -
Sadly, I don't believe so because the format of a line is the format of line item, meaning it can't change depending on the line item subset member.
0 -
Yup. Thanks for the confirmation @rob_marshall
0 -
@rob_marshall Thanks for sharing the detailed steps to use Native Versions capabilities for variance analysis. I guess this would best when we do not have ALM in place. If we leverage ALM, we make to first make changes to "Standard" version of the model and then sync it with "Deployed" version of the model, which might work too. However, if we could make "Native Versions" as Production data, that would really help in leveraging Native versions over Custom Version list.
Any thoughts?
AB
0 -
ALM really should not factor into this. Now, if you are in Deployed mode and you add new versions, then yes you would need to add new line items to capture that data. But really, that is the same as adding any new functionality. And, as you stated, Native Versions are not production data, so your Dev environment will be the same as your Prod.
Does that help?
Rob
0 -
@rob_marshall This totally make sense. Currently we are using "SwitchOver", "Edit From", Edit To", "Bulk Copy" options and we rename versions in Native versions and business contributors are expecting to manage these in Production environment. So the challenge is, if we use ALM + Deployed mode in Production environment, business contributors / workspace admin needs to update the Native versions in "Standard" mode version of the model, create revision tag, sync it with "Deployed" mode version of the model, update "SwitchOver", "Edit From", Edit To" via dashboard (if they are not the workspace admins in Deployed mode version of the model), contact production model workspace admin to run "Bulk Copy", every forecast cycle.
Even if we make business contributors as workspace admin in Production model / "Deployed" mode version of the model, they can run "Bulk Copy" themselves, however they still need to rely on "Standard" mode version of the model to update Native Versions name, "Actual" and "Current" checkboxes.
And if we remove "Deployed" status from the Production model and grant workspace admin access to business contributors, then we loose the code control in Production model.
But I guess we might need to find a best fit from above options currently available for business contributors.
AB
0 -
Ok, a lot in there and let me see if I can knock these out one by one:
- This article was really about variance analysis using Native Version and since Native Versions can't be used as a line item format, the variances were hard to do. Hopefully, this article helps with that.
- Switchover, Edit TO/From, Bulk Copy have absolutely no impact on this article
- The renaming of versions in a prod environment would, or it really, it could get confusing. The article will still work, but if you originally named a version Plan, and now it is Plan+4, the line item storing Plan+4 would still be named Plan.
- Never, never, never take the production model out of Deployed mode to update the names of the versions. As soon as you do this, the ALM sync is now broken because it is not longer in sync to what you have in the Development model. If you "need" to do this, you should make the changes in Dev, create a revision, and then sync it to Prod. But again, do not take Production out of Deployed mode. That is a MAJR no no.
- Side question, why are you wanting to rename the versions?
- Business users should not have access to a) rename versions, edit the Edit To/From, update the Switchover, and currently run the Bulk Copy (I think this is changing soon). The only people who can do this are folks with WSA (Workspace Admin) access.
If you would like to continue this conversation which has turned into more of an ALM question, ask the question in the forum and tag me and I will be happy to respond.
Hope this helps,
Rob
0 -
Thanks @rob_marshall for sharing the detail explanation and asking great question " why are you wanting to rename the versions?"
This help us review our current process in Anaplan on versioning and we were able to simplify the whole process allowing them to manage the version control directly in production model without being a workspace admin (except Bulk Copy).
Really appreciate your help and advice on this.
AB
0 -
That's a good way to analyze data not in cost/UoM type of figures.
For instance, when it comes to variance in USD/ton (using, let's say some of mix effect drivers to arrive at the final number from a reference one) it will probably not work with quarters, YTD, FY without additional technical calculations aimed at those specific time periods as you can not set 'formula' summary method when your formula has lookup.
May be you have any hint (as simple as you described in a post) for such type of usd/ton variances for Q,FY,YTD?
Thanks,
Mikhail.
0 -
Thanks for reading. As for using a formula for "parent" information, do the lookup in a "staging" line item and then for the "reporting" line item, set the summary to Formula and have the line item formula refer to the staging line item.
Rob
0 -
@rob_marshall @ChrisAHeathcote Hi Both, do you have a chance to write a document to include ratios in Variance Analysis
)Thanks
0
