- SAP Managed Tags:
Iterative cost allocation in context of Cost Center and Cost Type planning:
In this article, I would like to describe a typical business challenge in the context of integrated financial- and cost planning and how they are handled within SAP Analytics Cloud.
Problem definition: The business problem with cost centers and cost type planning is to distribute the individual overhead costs driver-based to the profit centers and to unload the overhead costs at the same time. In this particular example, we have 2 cost centers (Production 1 and Production 2) that act as profit centers. In addition, we have 2 cost centers (Heating and Human Resources) that are considered as pure cost centers. Now we have primary costs for all cost centers. The primary costs of the pure cost centers should be now allocated to the secondary costs for the profit centers. In this case, this should be done using value drivers. QM should be used for the allocation of the heating costs and the respective headcounts for the HR costs. Now there is of course the characteristic that the HR department also wants to sit in heated rooms and that the heating department has also employees who are administered by the HR department.
In the picture above you can see primary costs for the profit center production of 500,000 and for production 2 of 1,000,000 and for the pure cost center heating of 100,000 and for the HR department of 80,000 (red rectangles).
In addition, you can see the parameters on which basis the allocation should be carried out. For the allocation of the heating costs, square meters should be used. For production 1 it is 500 square meters, for production 2 it is 1000 square meters, for the HR department it is 100 square meters. No square meters were specified for the heating cost center because the heating costs should be redistributed to the other cost centers in total (green rectangles).
For the allocation of the HR department costs, in this case the headcounts should be used. For production 1 there are 100 headcounts, for production 2 there are 200 headcounts and for the heating there are 5 headcounts (blue rectangles).
At the end of the allocation process, all primary costs of the pure cost center heating and HR department should be allocated to the secondary costs of profit center production 1 and production 2 and the pure cost center heating and HR department should be completely unloaded (black rectangles).
The technical challenge is that if you use square meters to transfer the primary heating costs to all other cost centers, the HR department will again receive heating costs as secondary costs. Additional he heating department also receives costs from the HR department, which are allocated via the headcounts. At the end of the distribution process, however, all heating and HR department costs should be distributed, including the costs that the heating department receives from the HR department and the costs that the HR department receives from the heating system.
This is only possible if you use an iterative distribution process for the distribution, in which the costs are summed up in several runs, step by step on the secondary costs for the profit center cost centers. Only the additional costs for heating department allocated from the HR department and the additional cost for the HR department allocated from the heating department are temporarily saved in the secondary costs of the pure cost centers. In a second step, these temporarily parked secondary costs are transferred to the auxiliary elements "Distribution" for the use in the subsequent next distribution as source values (gray rectangles). The entire redistribution process is then carried out until the entire primary costs of the heating and the HR department are completely distributed and relieved.
Technical Solution: Data Action , Advanced Formulas, Visual:
The technical solution for this purpose is to use Data Actions with Advanced Formulas inside the SAP Analytics Cloud for the allocation. There is also the option to use pure allocations processes within the SAP Analytics Cloud, which can be used very well in other cases. In this special case, the use of simple allocation processes was not useful by the reason of the sending and at the same time receiving cost centers. In this special case, a Data Action with Advanced Formulas was created, which carries out the data transformation of the allocation, the unloading and the summation (see attached Advanced Formulas process).
The Data Action, Advanced Formulas process was initial developed in the Visual user interface. Please find following the resulting Advanced Formulas Script.
Data Action , Advanced Formulas, Script:
CONFIG.TIME_HIERARCHY = CALENDARYEAR
CONFIG.FLIPPING_SIGN_ACCORDING_ACCTYPE = OFF
CONFIG.GENERATE_UNBOOKED_DATA = OFF
// Copy Primary Cost + Seconday Cost Deduction = Redistribution
DATA([d/CostTypes] = "CC32000", [d/CostCenter] = "CT12100") = RESULTLOOKUP([d/CostTypes] = "CC11000", [d/CostCenter] = "CT12100") + RESULTLOOKUP([d/CostTypes] = "CC11001", [d/CostCenter] = "CT12100")
// Copy Primary Cost + Seconday Cost Deduction = Redistribution
DATA([d/CostTypes] = "CC32000", [d/CostCenter] = "CT12200") = RESULTLOOKUP([d/CostTypes] = "CC11000", [d/CostCenter] = "CT12200") + RESULTLOOKUP([d/CostTypes] = "CC11001", [d/CostCenter] = "CT12200")
// Heating Allocation
DATA([d/CostTypes] = "CC12100", [d/CostCenter] = "CT11100") = RESULTLOOKUP([d/CostTypes] = "CC12100", [d/CostCenter] = "CT11100") + ( RESULTLOOKUP([d/CostTypes] = "CC32000", [d/CostCenter] = "CT12100") / ( RESULTLOOKUP([d/CostTypes] = "CC21000", [d/CostCenter] = "CT11100") + RESULTLOOKUP([d/CostTypes] = "CC21000", [d/CostCenter] = "CT11200") + RESULTLOOKUP([d/CostTypes] = "CC21000", [d/CostCenter] = "CT12200") ) * RESULTLOOKUP([d/CostTypes] = "CC21000", [d/CostCenter] = "CT11100") )
DATA([d/CostTypes] = "CC12100", [d/CostCenter] = "CT11200") = RESULTLOOKUP([d/CostTypes] = "CC12100", [d/CostCenter] = "CT11200") + ( RESULTLOOKUP([d/CostTypes] = "CC32000", [d/CostCenter] = "CT12100") / ( RESULTLOOKUP([d/CostTypes] = "CC21000", [d/CostCenter] = "CT11100") + RESULTLOOKUP([d/CostTypes] = "CC21000", [d/CostCenter] = "CT11200") + RESULTLOOKUP([d/CostTypes] = "CC21000", [d/CostCenter] = "CT12200") ) * RESULTLOOKUP([d/CostTypes] = "CC21000", [d/CostCenter] = "CT11200") )
DATA([d/CostTypes] = "CC12100", [d/CostCenter] = "CT12200") = RESULTLOOKUP([d/CostTypes] = "CC12100", [d/CostCenter] = "CT12200") + ( RESULTLOOKUP([d/CostTypes] = "CC32000", [d/CostCenter] = "CT12100") / ( RESULTLOOKUP([d/CostTypes] = "CC21000", [d/CostCenter] = "CT11100") + RESULTLOOKUP([d/CostTypes] = "CC21000", [d/CostCenter] = "CT11200") + RESULTLOOKUP([d/CostTypes] = "CC21000", [d/CostCenter] = "CT12200") ) * RESULTLOOKUP([d/CostTypes] = "CC21000", [d/CostCenter] = "CT12200") )
// HR Cost Allocation
DATA([d/CostTypes] = "CC12200", [d/CostCenter] = "CT11100") = RESULTLOOKUP([d/CostTypes] = "CC12200", [d/CostCenter] = "CT11100") + ( RESULTLOOKUP([d/CostTypes] = "CC32000", [d/CostCenter] = "CT12200") / ( RESULTLOOKUP([d/CostTypes] = "CC22000", [d/CostCenter] = "CT11100") + RESULTLOOKUP([d/CostTypes] = "CC22000", [d/CostCenter] = "CT11200") + RESULTLOOKUP([d/CostTypes] = "CC22000", [d/CostCenter] = "CT12100") ) * RESULTLOOKUP([d/CostTypes] = "CC22000", [d/CostCenter] = "CT11100") )
DATA([d/CostTypes] = "CC12200", [d/CostCenter] = "CT11200") = RESULTLOOKUP([d/CostTypes] = "CC12200", [d/CostCenter] = "CT11200") + ( RESULTLOOKUP([d/CostTypes] = "CC32000", [d/CostCenter] = "CT12200") / ( RESULTLOOKUP([d/CostTypes] = "CC22000", [d/CostCenter] = "CT11100") + RESULTLOOKUP([d/CostTypes] = "CC22000", [d/CostCenter] = "CT11200") + RESULTLOOKUP([d/CostTypes] = "CC22000", [d/CostCenter] = "CT12100") ) * RESULTLOOKUP([d/CostTypes] = "CC22000", [d/CostCenter] = "CT11200") )
DATA([d/CostTypes] = "CC12200", [d/CostCenter] = "CT12100") = RESULTLOOKUP([d/CostTypes] = "CC12200", [d/CostCenter] = "CT12100") + ( RESULTLOOKUP([d/CostTypes] = "CC32000", [d/CostCenter] = "CT12200") / ( RESULTLOOKUP([d/CostTypes] = "CC22000", [d/CostCenter] = "CT11100") + RESULTLOOKUP([d/CostTypes] = "CC22000", [d/CostCenter] = "CT11200") + RESULTLOOKUP([d/CostTypes] = "CC22000", [d/CostCenter] = "CT12100") ) * RESULTLOOKUP([d/CostTypes] = "CC22000", [d/CostCenter] = "CT12100") )
// Allocation preparation for Heating
DATA([d/CostTypes] = "CC11001", [d/CostCenter] = "CT12100") = RESULTLOOKUP([d/CostTypes] = "CC11001", [d/CostCenter] = "CT12100") + ( ( RESULTLOOKUP([d/CostTypes] = "CC32000", [d/CostCenter] = "CT12100") - RESULTLOOKUP([d/CostTypes] = "CC12200", [d/CostCenter] = "CT12100") ) * -1 )
// Allocation preparation for HR
DATA([d/CostTypes] = "CC11001", [d/CostCenter] = "CT12200") = RESULTLOOKUP([d/CostTypes] = "CC11001", [d/CostCenter] = "CT12200") + ( ( RESULTLOOKUP([d/CostTypes] = "CC32000", [d/CostCenter] = "CT12200") - RESULTLOOKUP([d/CostTypes] = "CC12100", [d/CostCenter] = "CT12200") ) * -1 )
DATA([d/CostTypes] = "CC32000", [d/CostCenter] = "CT12100") = RESULTLOOKUP([d/CostTypes] = "CC12200", [d/CostCenter] = "CT12100")
DATA([d/CostTypes] = "CC12200", [d/CostCenter] = "CT12100") = 0
DATA([d/CostTypes] = "CC32000", [d/CostCenter] = "CT12200") = RESULTLOOKUP([d/CostTypes] = "CC12100", [d/CostCenter] = "CT12200")
DATA([d/CostTypes] = "CC12100", [d/CostCenter] = "CT12200") = 0
Solution / Result:
Inside a SAP Analytics Cloud Story a Allocation entry report with a table grid with the entry cells and Data Action Trigger was created.
If now the Data Action Trigger "Allocation" was executed, the allocation process runs once.
After a first run, the result is as follows:
To allocate the complete heating and HR department cost to the other profit cost center it needs 8-10 runs of the allocation process. At the moment there is no possibility to go through the entire assignment process with a max. number of runs or up to a max. residual value. To solve this problem it is a easy way to create 8-10 copies of the Advanced Formulas inside the Data Action and to execute these on a single drop of the Data Action Trigger.
The result of the complete execution is the following:
At the end of the entire process, all primary heating and HR department costs were allocated to the secondary costs of production 1 and production 2. At the same time, all costs on the heating and the HR department are relocated, which corresponds to the desired result.
Outlook:
With one of the future releases of the SAP Analytics cloud, it should be possible to control loops based on the number of runs. In addition, input parameters for the max. number of runs and max. residual values can be queried via prompt pages when starting the Data Action Trigger, which overall will simplifies the process control.
6 Comments
