Contributor

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. 

 

14 REPLIES 14
Super Contributor

Re: How to add a Line item with a formula in a list within a module?

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

New Contributor

Re: How to add a Line item with a formula in a list within a module?

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

 

Certified Master Anaplanner

Re: How to add a Line item with a formula in a list within a module?

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-reportin...

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. 

image.png

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.

 

image.png

 

Also make sure the code is updated and same as the actual COA Code for each member of the LIS

 

image.png

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

 

image.pngimage.png

 

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

 

Community Boss

Re: How to add a Line item with a formula in a list within a module?

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)
Highlighted
Certified Master Anaplanner

Re: How to add a Line item with a formula in a list within a module?

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...?
Super Contributor

Re: How to add a Line item with a formula in a list within a module?

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.

 

 

Contributor

Re: How to add a Line item with a formula in a list within a module?

@anirudh I follow your step but I got confused at step 4. Are you saying to create a new module with the COA LIS line item subset? I tried it and it still acts like list. What do you mean by Node? Can you provide more details?

Contributor

Re: How to add a Line item with a formula in a list within a module?

@anirudh Yea, I tried the line item subset suggestion. I don't see how it will solve my issues? I am trying to add a line item call gross profit between my COA of account number. Since the line item subset is a list, I still can't add extra line item under them. 

Certified Master Anaplanner

Re: How to add a Line item with a formula in a list within a module?

@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

image.png

 

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:

 

image.png

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!

 

image.png

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!