Remapping Line Items between Modules
Is there a best practice way to remap line items between modules?
What I mean by this is taking a set of line items from a module and remapping them to another set of line items in a module, as per the screenshot below?
My use case for this would be to summarise P&L lines or to reclassify line P&L lines into a different format.
For my first attempt at this - I did the following.
- Created my source P&L and target P&L, creating a line item subset for each module.
- Create a line item mapping module with the following attributes:
- Dimensioned by the source line item subset.
- One line item called 'Target Line Item' formatted as the target line item subset.
- (This is shown in the earlier screenshot)
- Another line item to COLLECT() the values of the source module. This line item is dimensioned in the same way as the source P&L.
- In the target P&L module, reference the COLLECT() line item in the mapping module, SUM'ing on the 'Target Line Item' line item in the mapping module.
To me this approach is OK - but not perfect. The most significant disadvantage is the fact that I effectively have to build the source P&L module twice - using up valuable model space. Once for the original source P&L module, and once again in the COLLECT() line item in the mapping module. This is because the COLLECT() line item is dimensioned in the exact same way as the as the source module. Unfortunately, I cannot remove the dimensionality from the COLLECT() line item as it is required to pull through my organisation list, LOB list, time, and version detail to my target P&L module.
What I like about this approach is the flexibility, line items can be remapped from a quick pick list, on the fly. It is also easy to ensure that all the line items from the source P&L module are being mapped somewhere (it is for this reason that I don't want to simply link the two modules via referencing the line items themselves. It is very easy to lose track of which line items in the source module have been referenced and which ones have not).
Any other ideas or thoughts on this would be fantastic - open to any suggestions.
Best Answer
-
Yes
Have a "data" line item with a list for the P&L
You can use a "ragged" or "unbalanced" hierarchy; We advocate using composite or balanced hierarchies, but P&Ls and Chart of Accounts is one of the exceptions
As long as the line items are all numeric and the totals are a natural aggregation, the ragged hierarchy should still work
David
0
Answers
-
That is the most commonly used approach to be honest for the reasons you gave and is much better than a series of IFs that we often see.
One question worth asking - Is is not possible to use two lists instead of line items? That would reduce the size concern. If the list is a simple aggregation, then it would work just fine
David
0 -
Hi David,
By two lists - do you mean setting up one line item per P&L module and using a list as a proxy set of line items?
If so, did consider that, but unfortunately my organisation does not consistently sum their accounts.
Thanks,
Nic0 -
Cheers David!
I'll have a look at that too.
0 -
Regarding your Org not doing a straight sum, there is trick that you can utilize (using RATIO as the summary). Take a look at the below:
Create a Signage module:
- In the P&L Module
- Create two additional line items
- Temp New Way
- Ratio New Way
- Add the following formulae
- Temp New Way= IF Sign Flippage.Mixed Child? THEN New Way * Sign Flippage.New Sign ELSE New Way * Sign Flippage.Sign
- Ratio New Way = Sign Flippage.Sign
- Create two additional line items
- Change the summary option to MAX for the Ratio line item
Amend the summary option for the New Way line item to Ratio
Make sure for the Ratio piece, it is Temp New Way / Ratio New Way
***obviously, you can change the line item names, but I didn't want to confuse you or others with different names vs what are in my pictures.
The Result:
Hope this helps,
Rob
1 - In the P&L Module
