Occasional Contributor

Managing expenses as positive, reporting as negative

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.  

9 REPLIES 9
Community Boss

Re: Managing expenses as positive, reporting as negative

@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

Super Contributor

Re: Managing expenses as positive, reporting as negative

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.

Contributor

Re: Managing expenses as positive, reporting as negative

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.

Super Contributor

Re: Managing expenses as positive, reporting as negative

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
Contributor

Re: Managing expenses as positive, reporting as negative

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.

Highlighted
Contributor

Re: Managing expenses as positive, reporting as negative

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.

Super Contributor

Re: Managing expenses as positive, reporting as negative

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.

 

 

 

 

 

New Contributor

Re: Managing expenses as positive, reporting as negative

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.

Community Boss

Re: Managing expenses as positive, reporting as negative

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