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.
Solved! Go to Solution.
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
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.
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.
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.
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.
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.
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.
Just following up on this - I've created it as a separate best practice article