Line Item or Line Item Subset or Line Item Subset mapping to Accounts

J_wheeler

Hi, Anaplan friends,

 

In the attached file, there are three options for simple P&L by Stores. May I know what factors need to be considered in choosing one out of these three options ? What determines which option you should choose ? What motivates you to apply option 3 where you need to take three steps to derive ??

Your cooperation would be much appreciated.

Thanks !

 

John

PaulRitner

Hi John,

 

Here's how I think about the three options you listed, particulary with respect to the primary P&L, BS, or Cash Flows resident in most or all FP&A models:

1.  P&L Rows as Line Items:  this is my preferred method for most clients because it looks nicer and we can embed formulas in the Parent Line Items that aren't simple addition (which isn't the case for Line Item Subsets and Lists/Hierarchies) (a good example is Net Income).  The only challenge here is if the line items are very detailed and change often... because the client would then possibly have to open the hood on the module too often (I still prefer this approach anyway, but it depends on the gifts & talents of the client's Anaplan Admins...)

 

2.  P&L Rows as a Line Item Subset:  This implies we're doing #1 above, and is commonly used for either ancilary or directly related activities of the primary report.  Some examples where we commonly use line item subsets include: CM/YTD reporting, mappings, data staging, some data inputs, and snapshots.  Line Item subsets can vastly reduce otherwise redundant maintenance, as well as simplify formulas (e.g. Collect()), but there are formatting limitations that some clients are luke warm on, and some reports don't work quite as well when based on a LIS... depends on aggregation/formula complexity in the source module.

 

3.  P&L Rows as a List (i.e. the P&L section of the Accounts Hierarchy):  I really really dislike using lists for the P&L rows in Anaplan, because, as stated above, Anaplan parent list items can only aggregate using addition.  For a P&L, this means that the Net Income line, for example, can't be calculated correctly unless we flip the signs on either Revenue or Expense (i.e. show one of them as a negative number)... which diminishes user experience (for me, its a non-starter).  In addition, when using a list/hierarchy for the P&L, in partiucular, it becomes very difficult to include KPI's in the report (which isn't an issue when using Line Items).  There are other drawbacks, but the last one I'll bore you with is this:  When using the Accounts Hierarchy as the P&L rows, the formula for linking in all the data can be very long depending on how many modules/line items you need to aggregate into the (often single) P&L line item.. this can create troubleshooting challenges.  Notwithstanding this diatribe, certain analytics & inputs are often facilitated by having the Accounts hierarchy resident in the module.  For example, if the need is to present for review & edit, say, Expense by Department & Account, then using a hierarchy to input & summarize is not only easy, but low maintenance & usually easy to map into summary reports.

 

Cheers!

 

J_wheeler

Hi, Paul

 

Thank you for having elaborated on the differences b/w the options their evaluations. It really helped me understand one aspect of why line item subsets ever exist. What I understand from your explanation is maintainance is the most important perspective in deciding whether line item subsets/mapping to Account hierarchy should be applied.

Thanks again for the pointers.

 

John

Jaakko

Hi all,

 

Interesting to read experiences of other people how you have decided to implement P&L. I've also seen multiple types of solutions and tried to list some of my experiences below.

 

1. P&L Rows as Line Items

 

I agree with Paul that this option is the one for best formatting options (summaries, bolding, subtotals, percentage figures etc). It also allows most flexibility in formulas because you have separate formulas for each account. It should be kept in mind that flexibility is not always a good thing, sometimes it's better that you are "forced" to plan your architecture to be logical and structured well for maintability. It is really easy to make the P&L/model overly complex with each account as own line item. In my opinion P&L itself should only gather data from other modules but it is quite easy to fall into including too much logic to P&L with this option. Of course this is not a problem if you are aware of it.

 

With this option you must define formula for each account separately and if you have a long chart of accounts which changes often (new accounts, minor changes in hierarchy...) it might require lots of manual work in the long run for maintaining your P&L. Therefore I would consider this option with quite simplified and static financial statement, not for a P&L with a large number of G&L accounts. Conditional formatting is also defined for each line item separately, so you have to edit conditional formatting options (in addition to formulas) every time you have new account in your report. Line items also don't synchronize between different modules in dashboards which means if you have same "account" (line item with a same name) in two modules, you won't be able to synchronize selections between them on dashboards although they would have same names. If you have accounts as a list in other modules, you probably need to hard code select-statements to your formulas which means the model is not that dynamic from technical perspective.

2. P&L Rows as a Line Item Subset

I'd skip this one as a real option. Like Paul, I've seen this one used only in combination with the option number 1. This is because the only dimension with calculation other than summing (that is line items) is used for calculating figures to accounts. You need to have another module for YTD calculations, moving averages, time sums. In a way you convert your line item dimension to account dimension (which is the option 3). In my opinion the appearance of line items subsets is not that nice either, therefore I mainly use them only as technical items, not on dashboards.

3. P&L Rows as a List (i.e. the P&L section of the Accounts Hierarchy)

Paul described the limitations of this option well already (aggregation is based only on summing, formulas might be long if there are many source modules). Opposite to Paul's preference, this is my favourite out of all these options. Reasons for this are mostly technical but there also some benefits for end users. Technical benefits include automatization of account hierarchy updates without the need for manual maintenance of formulas, easy linking of data between modules with account list/subset as dimension or parent hierarchy (linking is also handy by using collector modules and mapping tables), possibility of using line items in modules for other calculation than using them for accounts (could be YTD, rolling figures, currency conversion, comparisons between versions, etc), integration/exporting data with account codes and properties (with line items you export their names), synchronizing P&L on dashboards with planning modules with account as dimension. In my opinion also graphs work better when you have accounts as a list and different kind of measures as line items. End users have liked the option of account hierarchy with parent lists. The downside is that you must have the same number of levels for each account with this solution which sometimes might look stupid, but often users like when they can see the detailed account data with "reselect levels to show" button before actually using the drill down option to drill down to source modules. KPI reports and more stylished financial statements must be built separately in this case, but I don't see that as a big problem because you can publish them to the same dashboard.

 

Br,

Jaakko

Sahil.Iyer

Hello Friends,

 

Here is a technical video that can provide further insight into building and maintaining line item subsets!

 

https://www.youtube.com/watch?v=JiaEmMZGVCg