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