Version Formulae & Formula Scope
I need some assistance understanding the limitation of Version Formulae and how it interacts with Formula Scope.
I have inherited a model build which has a comprehensive P&L module (down to GL level) split into its respective areas - revenue lines, cost lines, etc.
Each respective area naturally points to a different part of the model for forecast - so revenue forecast drivers go to the revenue forecast driver modules and same with cost etc.
Previously the model was set up such that actuals were imported directly into actual version against line items and we want to move to a flat trial balance method instead. However; the model utilises bulk copy method to snapshot historic versions for budget, forecast 1, 2 etc.
Hence, formula scope is set to Current Version with rollup summary lines (i.e Total Revenue) as formula scope of "All Versions".
I would expect it to clash with Version Formula - but the problem is how do we best overcome this limitation?
Implementing a data hub (whilst best practice) probably isn't going to suit retrospective implementation based on the WS size, and client needs at this moment in time, but might be possible at some point in the future.
Advice much appreciated.
TIA
Answers
-
Hi @BoltConsulting
The major difference between a Version formulae and Formula Scope is the use of line item subset for Version formulae. The moment we speak about Version formulae you would have to resort to line item subset. Whereas, the Formula Scope works without any additional prerequisites (it works on a text formatted field as well)The effort needed to use Version formulae would be considerably more when compared to formula scope. You must create line items subsets wherever applicable and enter the Version formulae. This is just my personal observation and I may not be seeing the complete picture with respect to the workings of your model. However, you are just creating the line item subset and not actually creating modules which was a concern for you with respect to WS size impact.
You have the flexibility to write formulae accordingly for each version using Version formulae and also use Formula Scope at the same time. You can store the Actuals data in a staging module and refer those with formula scope and write the respective version formulae for other versions. Here is a test example that I have tried. However, this is only for number formatted line items as you know that the line items subsets work on number formatted line items. For any other formatted line items use Formula Scope or the combination of ISACTUALVERSION, ISCURRENTVERSION.
Hope this helps and if I find anything more on this, I will post it here for your reference.
Warm regards,
Adithya
4 -
Thanks @Adithya S
Yes the model is already using line item subsets and fulfils all criteria specified in the technical documentation on anapedia.
Interesting you've been able to get this working - clearly there is an additional complexity within my model that I'm not seeing as mine pretty much mimics your scenario but on a significantly larger scale.
The exact error message I get is "Formula for line item [insert line item here - generally a roll up line item eg 'Total Contribution Margin'] (Formula Scope All Versions) clashes with module formula on version 'Actual'.
Incidentally I thought there might be conflict with some formulas utilising "if isactualversion()" formulas (which are being used for some manual adjustments), but removing those also did not solve the issue...0 -
So, I tried to create what you said about 'formula clash.' It happens when you have a formula selected as 'All versions' and you have used a version formulae for Actual. What you may need to do is break those cross connections of formulae on version. Target specific version for formula using subset and the rest can be taken care of formula scope or a system versions boolean module.
Check for those line items which have been added for version formulae and try the below example. You probably would have to change a lot of formulae but this breaks the cycle of Version formulae clash.
In the below example, what I have tried is to target specific line items for those versions which have formulae and the rest would be using a system module, combination of formula scope, and IF ELSE statements for specific formulae.
I may not be knowing the inner workings of your model, but, if that clash does occur you may have to resort to the use of system versions module and target the versions for specific formulae.
I hope this helps.
Thanks
Adithya
1
