Native Version to Fake Version

Misbah

Hey Champs,

 

I was asked to provide a solution to one of the problems that one of our community member (@Naveed - not sure if this is your right handle) is facing. I couldn't arrive at the solution but was able to provide him the workaround. Let me know your thoughts on this

 

He is using Native Versions in his planning model and there are 6 of these. And now he wants to switch it to Fake Versions because he has his reservations on continuing with this approach.(Model Open time is gradually increasing with the increasing number of versions, he has formula written in some of the versions etc)

 

Long Term Goal: How can he switch to Fake Versions effectively & efficiently- without blowing up the size up due to redundancy, also by taking efforts into consideration

Short Term Requirement: He wants to do Fake Version comparison Analysis by pulling the data from Modules which have got Native Versions in their dimensions.

Note: Keeping it Static isn't an option. E.g., Budget Vs Forecast in one line item, Actual Vs Budget in Second line item. It should be dynamic where users can do comparison as per the selections that they make.

It would have been easier if we had Version Formatted Line item and Looking up on these line items would have come handy but since it is not available LOOKUP can't be used. SUM also can't be used with the help of version mapping module because it pulls only current version and not every other version.

 

Thanks,

Misbah

LipChean_Soh

Hi @Misbah 

 

Thanks for the clarification. Yes standard versions does need some improvement.

 

Do see if the following works:

1. Profit & Loss - Simple module with Standard Versions, Standard Time, Countries and simple P&L line items.

2. Profit & Loss (Collect) - A Line Item subset, 'LIS P&L' is created, where it grabs all the line items from 'Profit & Loss' in item 1.

This new module uses 'LIS P&L', Countries, Standard Time, Standard Version.

It also has 1 line item, Value = COLLECT()

3. Version Selection - A selection module made up Standard Versions, and 2 line items, Version 1 and Version 2.

This module will drive the values to be shown in the Variance Analysis report below.

 

4. Variance Analysis - As you rightly stated, LOOKUP cannot be used on Standard Versions, hence a nested IF THEN ELSE is used. Fortunately we have to do once for Version 1, and once for Version 2.

And we can do a SELECT statement on the same COLLECT statement from 'Profit & Loss (Collect)'.

The additional benefit of this approach is the different format that we can apply to Variance ($) and Variance (%).

5. Actual vs Version

 

6. Actual vs Budget.

Note: Here's the caveat, because it's a nested IF THEN ELSE, the first selection that meets the criteria will be chosen. So if both Forecast and Budget are chosen in Version 2, then Forecast will still be referred in Version 2.

 

As you can see, there's a lot of SELECT statement and a heavy use of nested IF THEN ELSE, so it's not ideal.

And there's a duplicate of data in 'Profit & Loss' and 'Profit & Loss (Collect)'.

Do proceed only after discussing the pros and cons with the business users.

 

Thanks,

LipChean

 

usman.zia

Thanks for sharing @Misbah 

 

Great tips @LipChean_Soh 

 

For me it is clear that fake versions are much more flexible than native versions.

There are so many product limitations when using native versions as you mention:

- Cannot use line item format as version

- Cannot use LOOKUP

- Cannot use SUM

- Cannot use subsets of versions

I believe versions with the above functionality has been expressed on the idea exchange and I can't wait till it is on the roadmap! 

Therefore in most cases I'll will always begin using fake versions due to flexibility.

 

The specific case that you mention which requires a change is only as complex as the model you are trying to implement the change of native versions to fake versions in. 

If it is a model with versions in most places with great complexity then this will take time and if the model is big in size taking up a large part of the workspace it may be difficult. 

 

The best way to approach this is to first create a fake version list with only a small number of versions such as actual and forecast.

Create module shells of the modules you wish to copy (module shell = module with line items but no formulae and minimal dimensions (consider using subsets)) these will be all the modules with native versions. Easiest way is to copy existing modules and then delete formulae and delete versions etc.

The risk you have to consider here is that the data stored against the native versions will most likely be lost so it is worth creating exports and saving the data so you can re-import it at a later stage.

Check your actions and imports! you will need to note the ones you need to recreate because of the changes and in some case you will need to delete them as they may prevent you from making changes.

Once you have completed all the shells, export the formulae from the modules with native versions and delete the native version modules.

Evaluate the formulae and begin pasting them back, expect some dimensionality issues it won't completely  straight forward.

Re-create your imports and you should be good to go! But make sure you TEST! 

 

If you had unlimited space you could copy the model and do a similar task but you wouldn't have to delete the existing native version module right until the end because you want to compare and export the data from those modules. 

 

I hope this gives some guidance! 

It will be challenging but it can be done! 

 

Thanks,

 

Usman

LipChean_Soh

Hi @Misbah 

 

For our reference, do you have a link to the workaround you suggested to Naveed?

 

Thanks,

LipChean

Misbah

@LipChean_Soh 

 

Unfortunately No. We connected offline and my solution was not even close enough to the requirement. It gave him option of displaying the data in a better way by exploiting Display Control with the help of Line Item Subsets. 

 

I wanted to check if anybody else has faced the similar challenge - Unless and Until we get Version formatted line items this is going to be a pain point

 

 

Misbah

@LipChean_Soh 

 

Thanks for the detailed write up. You are exceptional in articulating your thoughts.

 

But this is exactly what I am trying to avoid - hard coding, hard coding & hard coding.  

 

Thanks,

Misbah

LipChean_Soh

Hi @Misbah 

 

Thanks for your kind words.

Yeah at the moment Standard Versions are intricately connected with the SELECT statement.

 

Another option i can think of is the following, it's a modification from the suggestion above:

1. A 'Fake Version' list is created that mirrors the members in Standard Version

 

2. 'Version Selection - 2' is modified to be 2 line items, i.e. Version 1, Version 2. Both have 'Fake Version' as format.

2. 'Profit & Loss - 2' is similar to 'Profit & Loss', but instead of using Standard Versions, it uses 'Fake Version'.

To populate data in this module, we will however need to use an import action from 'Profit & Loss'.

 

3. 'Profit & Loss (Collect) - 2' does the COLLECT() action. This module uses a line item subset that grabs from 'Profit & Loss -2'

 

4. Notice that 'Variance Analysis - 2' now uses LOOKUP instead of SELECT.

'Variance Analysis - 2' uses Country, LIS P&L 2, Time as lists.

 

Thanks,

LipChean

karank_*4578

This is brilliant!
Thanks for sharing

Misbah

@usman.zia Thanks for sharing your thoughts. Yes I agree with you that it is going to be labor intensive and that's why I wanted to seek community's support and thoughts on this. Its far better to spend good amount of time on designing it & ask ourselves "To Version or Not to Version" - great article by @DavidSmith aligning with the strategy "Measure twice Cut Once" By the way I like the way you call it as a module shell:) Thanks @LipChean_Soh  for the great tips and tricks.

shoaib986

@LipChean_Soh

What about if we have Net Margin %?

We cannot have it formatted as % in the same value = collect ()

When we P&L variances, we usually see absolutes and ratios comparison both vs last year. 

rhightower

@LipChean_Soh Sidebar - what add-in or setting are you using to present the formula editor by line with color shading?