Complex GL Account Mapping to Cost Centre for Allocation

edited March 11 in Modeling

Hi Anaplaners,

I want to mapp certain GLs to CCs and vice versa. Additionally I want to map certain cost centre to all GL Account and vice versa.

Based on the mapping I would input an Allocation % for those mapped GL & CC. The mapping is done on the lowest levels of the hierarchy.

It will also have the Parent of the GL mapped alongside the child level.

My out put is by (Custom Version, Time, GL and Cost centre) dimension. Below is the screenshot prototype for mapping.

Has anyone implemented such build and what is the best way to implemented this build?.

Best Answer

  • Hi @Monpero,

    This might be a possible option for you to consider:

    Step 1 : Create a mapping module dimensioned by GL and Cost Center (lowest level) and create a Boolean line item (name it as Mapping). In this module allow the users to define GL and CC mapping relation. Now, add one more line item to this module and name it as 'Valid Code'. The formula for this 'Valid Code' line item can be If Mapping then GL Code&"_"&CC Code else blank.

    Step 2 : Now basis the valid code line defined in step 1 create a concatenated list of GL and CC

    Step 3 : Create 'Allocation % and Cost' Module with the following dimensions: Concatenated GL and CC list, Version, Time and line items - Alllocation %, Final Cost (before allocation) and Final Allocated amount. This module can now be utilized to input Allocation % and for the final cost calculation.

    Hope this helps!



  • I would suggest to create a system module and add versions as dimension. And GL list as rows, add line items CC with CC list to pick from and GL list to pick from, plus add a line item with number where you would be able to input % allocation for each version (version of forecast).

  • Thanks @samval. I've tried it this way but to no avail.

  • Hi @Monpero,
    First, I would create an Allocation list, where you would group your GLs, this is where you are gathering Amount that you later want to distribute to CCs.

    Then I would create a mapping module dimensioned by Cost Center and new Allocation list. This is where you map CCs with Allocation list items. Create an "Allocation %" line item where you can input %. Careful with the input to make sure you'll have 100%. With that approach, you can have more than one CC in and Allocation item.

    Next I would distribute amount what you've gathered from GLs into your Cost Centres based on the mapping above.

    In the end you want to have a module that shows the Distributed Amount by Cost Centre. Versions and Time shouldn't be the problem.
    Let us know if that approach makes sense ;)

  • Monpero
    edited March 11

    Thanks @fczerniawski for the input. What the request was is to take the value of the CAL 100 - Forecast which is dimensioned by GL, Cost centre, Custom version list, and Time.

    In the Final Cost Line: I want to take the values of the Final Cost (Before Allocation) * SYS Module GL/CC Mapping - Allocation %. ( I'm transferring the values to assigned it to the mapped GL / CC as a CREDIT)

    I've created a SYS Module dimensioned by GL/CC and created 3 line items.

    (1) Line item GL (Formatted as a list) and

    (2) Line item CC (Formatted as a list) and also have mapped the GL & CC against the dimensioned list

    (3) Line item Allocation % (Formatted as a Number %)

    It's not working from the way I set it up.

    IS there a workaround on how to go about this? Your response will be really helpful.

  • Thanks a lot @Sampriti.Anand this was very helpful, I appreciate a lot.