Managing expenses as positive, reporting as negative

bradmalis

we have a client that manages expenses at GL account level as positive numbers.  they rollup the GL accounts to reporting lines (they have multiple GL accounts for salary and report salary on a P&L).  the reporting lines are hierarchical (total income and total expenses aggregate to profit before tax).  they would like to use Anaplan natural aggregation to sum up their P&L hierarchy.  what is the best way to keep the treatment of expense line items as positive while at the same time subtracting expenses from income to get to profit before tax? thanks in advance for any suggestions.  

DavidSmith

@bradmalis

Yes, this is a little challenging but I think the attached should cover most use cases.

It's a little involved so I've attached a document to explain

David

harish_bk

Hi Brad

 

We have GL Codes for SAP and they are normally as below:

Code starting from:

1 for Liabilities and Equity

2 for Assets

3 for Revenue and Income

4 and above for Expenses

 

So we created a signage property in GL List:

Which creates value of -1 for Expenses and Liabilities and 1 for other accounts.

 

Using this factor we acheived what was said in post before by David.

michael_svolos

Thanks for this!  Ive used it in our internal system and it works great.  This makes creating plan vs.  actual reporting much easier as well.  Another tip.  You can use the signage module to enable accurate b/(w) calculations by line.

paul.rosal

This is a great solution for the use case.  It would be nice to understand the logic behind Max(Signage) as the denominator - it works but it seems like magic.

harish_bk

Hi Paul

There are two things you have to observe:

 

1) Signage where the value would be 1 or -1 and max is always 1 (which is ratio)

2) Temp value which is value entered by user multiplied by Ratio to get the required value but with respective signs

3) Then the summary line of actual value will be ratio and the ratio is nothing Temp Value*Signage, basically summary line item of value is Temp Value which is created using signage.

 

The summary line item in value is pointing to temp value, just create a build and do a drill down to Value you will get your answer .

 

Attaching screenshot with drill down.

paul.rosal

Hi Harish,

I know that the Ratio line item is there strictly to control how the the Value items are displayed at sub-total level (since the true sub-totals are accurate as per the sign-flip).  I had my doubts about how this would work in all cases and I did create a scenario where it doesn't work the way it should.  The problem scenario is if the parent total is supposed to be a natural negative (e.g. Expense Total) - if there are any contituent children that is a natural sign positive the parent is assigned natural sign positive.  So when I created a child under expense that was a natural sign positive, Expense Total "Value" showed up as a negative number (when it still should be positive).

 

I think the ideal solution is to be able to tag parent members as -1 or 1 explicitly, rather than trying to calculate it with the Ratio Summary.

harish_bk

Hey Paul

Got it,

the best is to use these GL Accounts as Line items and incase we cant use them and you have the above issue we can try below option:

1) Create GL Account list hierarchy using parent account list (List items would be Gross profit, Total Revenue, Total Cost so on)
2) Maintain the signage in the parent list
3) Value will be using GL Account list
4) Ratio(Signage) and Temp would be using parent list

See if this could solve your issue please

paul.rosal

I did build something to fix the stated problem scenario.  I didn't want to create something that relied on a fixed number of parent lists since that's a maintenance problem and requires List specific logic (and we all know P&L hierarchies are ragged).  Instead I built the P&L hierarchy within a single list and created a formula to compute the Sign based on IF/THEN logic that used ISANCESTOR to reduce the number of IF cases.  I was able to specify the Sign at all levels of the P&L hierarchy and apply that sign to correctly display the sub-total members.

 

Thanks again to David Smith for the idea - a brilliant use of Anaplan functionality.

DavidSmith

Just following up on this - I've created it as a separate best practice article

https://community.anaplan.com/t5/Best-Practices/Changing-the-Sign-for-Aggregation/ta-p/53812

 

David

Ron Baylo

Something I had to do a client was create a "Cascading" sign flip that worked Tops down the composite hierarchy and allowed the Admin to selectively override the the "Flip" of the parent. So a user could work down the hierarchy, but maintain branches where the exceptions occur to effectively create custom rules with the least amount of maintenance possible. The Parent "Flip" is brought down to the Child list as a calc line item with a lookup which is contained in an algorithm that also refers to a floor level line item that lets the user override the "Flip". So the "Calc Flip" line item formula follows as: "IF Sign Flip = Sign Flip.Flip THEN Sign Flip.Flip ELSE IF Sign Flip = Sign Flip.'Un-Flip' THEN BLANK ELSE 'Acct L1 Sign Flip'.Sign Flip[LOOKUP: 'Acct L2 Properties'.'Acct L1']" The "Sign Flip" line item is used at floor level for the override and so on down the composite hierarchy. I created a dashboard with all the floor level sign flip modules so users can navigate the hierarchy and only effect the nodes where necessary.

 

For this client I built this module in the Hub, and cascading flip logic was pulled into the Account level Transaction data before it was imported into the Spoke FP&A model.

MirBorishkevich

@Ron Baylo Could this build be applied at the spoke model level? I'm in need of a solution like this for EBITDA Income Statement reports while using the natural account hierarchy at the lowest level of detail (too many accounts to go the line item route).
Would love if you could share some visuals/screenshots of the build/result.

Thanks,