How to add a Line item with a formula in a list within a module?
I am creating an income statement module.
Inside the module I want to create a few line item that is sandwich within a list.
List - COA, Department,
Line Item - Amount
Example.
5000 (A part of my COA list)
50001 (A part of my COA list)
5002 (A part of my COA list)
Cost of Good Sold (A part of my COA list)
Gross Profit (Line Item with a formula I want to create)
Expense (A part of my COA list)
6000(A part of my COA list)
etc.
I included a screen shot with Gross profit highlighted to show you an example. If you can show me the best way to approach this, this would be great.
Best Answer
-
Try this:
Create a new line item called COA LIS in the Full Upload COA module with the format of the COA Line item subset. Input this formula:Chart of Account List.COA LIS[LOOKUP: COA]
Then go over to the destination module and input this formula instead:
Full Upload COA.Amount[SUM: Full Upload COA.COA LIS, SUM: Full Upload COA.Department, SUM: Full Upload COA.Date]
Let me know if this works
1
Answers
-
Can you review Lineitem Subset, it would be helpful for this case. Line itemsubset is treating a set of line items from different modules as a list itself.
https://help.anaplan.com/anapedia/Content/Modeling/Dimensions/Line_Item_Subsets.html
Thanks
Arun
0 -
Hi Kdoan,
Yes.. as per the suggestion given by Arun , you can try Line Item Subset here to make use of one /more line items from other modules but make sure the respective line items should be in number Format bcz LIS accepts only number formatted line items .
Thanks
1 -
Hi kdoan,
I would like to understand why you want to input a different formula just for Gross Profit but want to have another formula for the other accounts in the COA. Is it a problem with aggregation? For example: Revenue - Cost of Goods Sold = Gross Profit but in Anaplan the automatic aggregation is Revenue + Cost of Goods Sold = Gross Profit which is totally wrong.
If this is the issue, please refer to this excellent thread on how you can achieve a custom aggregation in a chart of accounts: https://community.anaplan.com/t5/Anaplan-Platform-Discussions/Managing-expenses-as-positive-reporting-as-negative/m-p/37609
Also attaching David Smith's doc for a quick guide on custom aggregation in a COA.
For any other reason, there is another way to solve this using Line Item subsets and mapping of the Chart of Accounts to the Line Item subset created.
As suggested by Arun, you can try using Line Item subset in the following way:
1. Create a module with all Line Items the same as your chart of accounts list.
Example above using the COA you attached.
2. Create a line item subset based on this module and add the lowest nodes to the LIS. In the example above, add only 5110, 5120, 5130 and 5200 to the LIS.
Also make sure the code is updated and same as the actual COA Code for each member of the LIS
3. Create a mapping between the COA List and COA Line Item Subset (LIS) by creating a property in the COA List or follow best practice and maintain this mapping in a module dimensionalised by the COA List
4. Now go back to the module with the LIS and feed your data (lowest nodes) as per the regular formula. If mapping between COA List and LIS subset is needed use the property created in step 3. Repeat the same formula for each member of the COA LIS
5. The upper nodes (Total COGS, Gross Profit etc) of the COA are now 'unshackled' from the default +++ aggregation in Anaplan and also from the formula applied to the lower nodes of the COA. Gross Profit can now have it's own formula!
Disadvantage with this method is that newly added COA members must be added into the module; there is no way to automatically import line items into a module.
If this is not clear enough, I can explain it on a call as well!
Regards,
Anirudh
10 -
I really like your solution Anirudh and I don't see a disadvantage. I would add that you can, however, import line items into a module and automatise that, only that the import must be done in Blue Print (so no data will be affected but the line items)2
-
I did not know this and will give it a try soon!
But I assume there is still no way to import into the formula itself...?0 -
When you import in to the blueprint, if you can build a formula in the source, we can import it, i have not tested it though. It should work.
1 -
The solutions suggested appear unnecessarily complicated when it would be straightforward to add a parent to the COA list for all items relevant to Gross Profit. The hierarchy would produce the required roll up and aggregation.
Another alternative is to create a consolidated P&L module using a line item subset as suggested by others and allow the user to drill down to the COA list where required.2 -
Remember "simple is better than complex"
https://community.anaplan.com/t5/Planual/ct-p/Planual
Try and think more widely around the problem, rather than focussing on the detail
"what are you actually trying to achieve"
David
1 -
@kdoanAre you using the LIS as a dimension in another module? That's not exactly what I was describing. There is no need to create another module at all!
The purpose of the LIS is just to create a mapping between the actual COA list and the line items in a COA module.
By lowest nodes, I mean the the accounts in the COA which are not roll-ups. For instance, in the screenshot below the accounts 5110, 5120, 5130, 5140 and 5200 qualify as the lowest nodes
The other members of teh COA ie. 5100 - COGS, Total - Cost of Sales and Gross Profit are higher in the heirarchy and should not be a part of the LIS.
In other words, the solution is to recreate your whole COA list as line items in the module.
I have extended my example module to demonstrate:
All the COA members above are line items. The ones in blue are what I referred to as the lowest nodes and are the line items where you will have to input a formula to fetch data. This is the same formula that you would use had the module been dimensioned with the COA List. But now you have to use the mapping created in Step 3 and a SUM formula to get the data into this module, thereby bypassing the COA list.
And as you can see above, the Gross Profit is sandwiched between the other COA members with its own formula!
Hope this helps!
I'd also like to reiterate here that if the reason you want to input a formula only into Gross Profit is because you want to avoid the default +++ aggregation in Anaplan, then the whole solution above is unnecessarily complicated and you can achieve this by using the guide attached!
1 -
Thank you. It worked.
0
