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

  • anirudh
    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

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

  • 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

     

  • 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)
  • 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...?
  • 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.

     

     

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

     

  • @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?

  • @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. 

  • @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!

     

  • @anirudh I tried your method but the formula won't work. I follow your step and use Full Upload COA.Amount[SUM: Full Upload COA.COA, SUM: Full Upload COA.Department, SUM: Full Upload COA.Date] to try to get the data in. 

     

    Any advice?

  • Thank you. It worked.