Sum data from a module with line items to another module with a matching list

kinga.markovics
kinga.markovics
in Modeling

Hi

 

Could someone suggest a solution? screenshots attached also.

I have a module (PER01) where I have these line items - the data is by employee (from employee list):

- base salary

- incentive scheme

- other social costs

 

I have another module (PER02), where I want the above data to fit in with only  1 line item called "input" & dimension accounts list. So in this, the accounts are in a list, not a line item. There are more accounts in the list than the 3 above mentioned ones, which is OK because in actual version, I will need those extra accounts. I noticed that I can write formula and mark the scope to be "all versions but actual", so actual version can still be with data input.

- I added versions dimension to this module - I will only need to get the data into the budget version.

- I added Cost Center dimension to this module

- Employees are not a dimension in this module, but each employee belongs to a cost center so it should be possible to sum the figures.

 

I have also created an intermediate module (PER03),a matrix, where I connect the line items from the first module (PER01), to the list in the 2nd module (PER02). But I'm kind of stuck here - I'm not sure if this 3rd module is needed, or can I manage without it..

 

How can i get the data into PER02 by cost base, for budget version from PER01?

Can someone suggest a formula?

 

Thank you

Kinga

 

 

Tagged:

Best Answers

  • alexpavel
    alexpavel
    Answer ✓

    @kinga.markovics  Having a numbered list, referring directly to a particular element in a formula... there are some "best practices" you go against and you will close some doors. 🙂

    For example, you will not be able in the future to flag the list "AC02 Sub-Accounts selected" as Production Data. 

    But, in the end, the formula will work.

     

    The reason why your formula does not work is that the Anaplan Versions list does not accept the ITEM() function and a way to check for a version if is Budget is to create a system module dedicated to Versions and flag the Budget version for a boolean line-item like "Budget?"... and in the formula, just refer to the line-item Budget? (see below)

     

    alexpavel_1-1616661317988.png

     

    Example:

     

    IF SYS01 Versions.Budget? THEN

     

         IF ITEM(AC02 Sub-Accounts selected) = 'AC02 Sub-Accounts selected'.'#144' THEN 'PER01 Salaries Plan'.Base Payroll ELSE 0

     

    ELSE

    0 

  • alexpavel
    alexpavel
    Answer ✓

    I totally agree that there are situations (especially with HR costs use cases) where there is needed to switch between line-items and lists. 

    I always try in these cases to avoid the hard-coding and use the line-item subsets with a mapping module. 

    I think you wanted to achieve this parametrization with your mapping module PER03. 

     

    A solution could be like this, using line-item subsets and a mapping module: 

    1. create a line-item subset from PER01 line-items

    2. Create a "mirror" module PER01.b where you collect data from PER01  (so data from PER01 will be duplicated for the only reason to change the layout of data). 

    3. Have a mapping module PER03 where you can map the Line-item subset from point 1 with the Accounts list

    4. Summarize data in PER02 from PER01.b using the mapping table PER03 

     

    Below the simplified examples:

     

    PER01.b to collect data from PER01

    alexpavel_0-1616666194880.png

     

    Mapping module PER03

    alexpavel_1-1616666243651.png

     

    Final module PER02 to have data in 1 line-item with the Accounts list. 

     

    alexpavel_2-1616666314816.png

     

     

    Just be aware that the line-item subsets work only on numeric formatting line-items. 

     

    Hope it helps

    Alex

Answers

  • alexpavel
    alexpavel

    @kinga.markovics  The most direct way to solve this ( not very clean and quite hard-coded) is to use this kind of formula in PER02 ( not really necessary PER03 mapping module). 

    I notice that in PER02 you also have Version...you will need to add some IF in the below formula in order to associate the data to the correct Version. 

     

    IF ITEM(Accoutns list) = Accoutns list.Base Salary THEN
        'PER01'.Base Salary
    ELSE IF ITEM Accoutns list) = Accoutns list.Incentive Scheme THEN
         'PER01'.Incentive scheme
    ELSE IF ITEM(Accoutns list) = Accoutns list.Other Social Costs THEN
        'PER01'.Other Social Costs
    ELSE
    0

     

    Being numeric line-items in PER01 and if you want to make it more parametrical you could also create a line-item subset and create the mapping module using the line-item subset from PER01.

     

    Hope it helps

    Alex

     

  • kinga.markovics
    kinga.markovics

    Hi @alexpavel, I think that should work thank you.

     

    For now, i removed my cost center dimension to make it simpler and have only versions as extra.

    I added the IF for version and shortened the formula for only 1 of the accounts (base payroll) to test it out, but it's not accepted ("input is invalid")

     

    IF Versions=Versions.Budget AND ITEM(AC02 Sub-Accounts selected) = 'AC02 Sub-Accounts selected'.'#144' THEN 'PER01 Salaries Plan'.Base Payroll ELSE 0

    'AC02 Sub-Accounts selected'.'#144 --- this is Base Payroll account from the accounts list.

    'PER01 Salaries Plan'.Base Payroll --- this is the line item Base Payroll

     

    Is there something wrong with my formula writing? 

     

    Regards

    Kinga

     

  • kinga.markovics
    kinga.markovics

    @alexpavel, would you have a better way to build this?

     

    the whole model is for cost center reporting (actuals) & planning (budgeting):

    • we have account (e.g personnel costs) and subaccounts (salaries, social costs, bonus etc) in 2 non-related lists, and then use a module to map which subaccounts should be under which accounts. We then have a 3rd list, which is imported from the module using the mapping of accounts & subaccounts. This was done to avoid having so many unncessary account & subaccount combinations (selected accounts).
    • we divided the selected accounts for "personnel" and "other costs" subsets. 
      • for personnel, we need to have the planning done on employee level who each belong to 1 cost center (budget) but actuals come through import (which is possible with the formula scope)
      • for other costs, it's done on cost center level, similarly the budgeting is done manually, and actuals come through import.(which is possible with the formula scope)

    It didn't seem possible to have formula for some accounts (from a list), but input for the others --that's why we made the 2 subsets

    As a result, our plan is to have 4 modules

    1. module for personnel planning - here we need to set some formulas based on the country of the employee (social cost %, increase % etc), while other lines need to be input (base pay). So these needed to be line items.
    2. module for retrieving personnel costs - here we'll use the personnel subset list account and "input" line item to import actuals, and retrieve budget from the 1st module
    3. module for other costs - here we'll use other subset list account and "input" line item to import actuals & input budget
    4. and a module to consolidate and take data from 2. and 3.

    Not a very simple model 😃 

     

  • kinga.markovics
    kinga.markovics

    Hi

     

    I got it working your way, thank you very much.

    so this way, the account list can be production data right?

     

    Regards

    Kinga

  • alexpavel
    alexpavel
    yes. correct. if the formulas do not reference elements from a list except for the Top-level element, that list will be able to be setup, if necessary, as "Production Data"

Categories