Determination of PCG sums, Compa Ratio and Range Penetration in People Analytics Story Reporting

[FrankErle]

Abstract

There are a number of so-called ‘transient’ fields in EC Compensation; this means values as “Pay Component Group  (PCG) Sums” like e.g. “Annual Totals” are not stored on the database but are (re‑) calculated whenever the employee profile is displayed. Further examples of transient fields are the 'Compa Ratio' and the 'Range Penetration'. To allow a proper reporting, it is required to use the same calculation logic in the reporting tools. The blogpost describes the challenges to persist these numbers and how reporting is dealing with the recalculation of these transient fields.

Introduction

To get an initial impression of the calculated figures, see Figure 1 with the respective numbers for employee “Steffie Sieglinde Abel”; which shows as an example (1) the “Pay Component Group Sum” which are often labelled as “Annualized  Totals”, “Total Target Salary” or something similar (indicated in red), (2) “Compa Ratio” and (3) “Range Penetration” (both indicated in yellow).

First, it is explained in detail by which attributes and configurations these calculated figures are influenced and how these figures are calculated. That is the prerequisite to understand how the query in reporting is built and how to do the identical calculation in reporting.

• See the 4 items of the recurring payments shown in Figure 1 indicated in blue. All 4 'Pay Components' “Car allowance DE”, “Meal Allowance”, “AccidentInsurance_DE” and “Base Salary Germany” are part of part of the PCG “Annual Salary” (shown in Figure 2).
• Note that the 3rd item 'Pay Component' “AccidentInsurance_DE” is 10% of the 'Base Pay Component' "Annualized Salary". The 'Base Pay Component' comes from the configuration of 'Pay Component' “AccidentInsurance_DE”, see Figure 3. You can see that the 'Pay Component Type' is configured as "Percentage". Furthermore, multiple 'Pay Components' incl. “Car allowance DE”, “Meal Allowance” and “Base Salary Germany” are assigned to the PCG "Annualized Salary" (not shown in any of the Figures).
  Remark: It is also possible to have 'Pay Components' of Type “Number” (for example, part of the salary of employees in a refinery with a certain amount of fuel); however, conceptually, this works in the same way as with 'Pay Components' of the type "Percentage".
• For the calculation of the PCG Sums the “Annualization Factor” of the object 'Frequency' of the respective item needs to be considered (Figure 4 shows as an example the “Annualization Factor” 6 for the Frequency ‘Bi-Monthly’).
• It is important to emphasize that the values need to be calculated for “Today”, see the green arrow in Figure 1. This means, that the respective time slices of (1) the EC compensation objects and (2) for the Pay Component Assignment to the PCG need to be considered.
• For the concrete example this means for the PCG “Annual Salary”:
         2580 EUR (= 430 EUR x 6 )       “Car Allowance DE”
           560 EUR (= 140 EUR x 4 )       “Meal Allowance DE”
        5.474 EUR (= { 10% x (430 EUR x 6 + 140 EUR x 4 + 4.300 EUR x 12) } x 1 ) "AccidentInsurance_DE”
      51.600 EUR (= 4.300 EUR x 12 )   “Base Salary Germany”
  ----------------------------------------------
       60.214 EUR “Annual Salary”
• For the Compa Ratio calculation it is required to divide the PCG Sum which is configured to be used for the compa ratio calculation (indicated in yellow in Figure 2) by the Mid-Point of the assigned 'Pay Range'.
  Remark: There is also an additional attribute “Use for Range Penetration”. This means, it is possible to configure one PCG to be used for compa ratio and one PCG that is used for the range penetration calculation.
• The 'Pay Range' in the SAP default configuration depends on (1) 'Pay Grade', (2) 'Geo Zone' and (3) 'Legal Entity'. However, the SAP default configuration can be adjusted in the corporate data model by each customer individually; this is indicated in Figure 5.
• Note that the 'Pay Range' is not part of job information and not stored with the employee but it is only a ‘display field’ which is always new determined when the employee profile is called based on a set of parameters. This is a conscious design decision because in case of a change of an employee (e.g. a promotion from "Pay Grade 1" to "Pay Grade 2" or an internal transfer from "organizational unit 1" to "organizational unit 2") it is not required to change the 'Pay Range' manually but the correct ‘Pay Range’ is determined automatically. This will later have consequences for reporting, because the ‘Pay Range’ can not simply read from the data base.
• The employee “Steffie Sieglinde Abel” is assigned to (a) 'Legal Entity' "BestRun Germany", (b) 'Pay Grade' "Salary Grade 06" and (c) ‘Location' "Berlin". Within the configuration of 'Location' the 'Geo Zone' is configured. For the example in this blogpost the 'Geo Zone' "GERMANY" is derived for 'Location' "Berlin". Based on these attributes the 'Pay Range' "Germany Salary Range 06" is derived for employee “Steffie Sieglinde Abel”.
• To allow the comparison of the compa ratio of employees with different working hours, the FTE needs the be considered in the compa ratio calculation. E.g. if an employee only works 50%, then the respective salary is multiplied with a factor of 2.
  Important: Under Admin Center ->  Company System and Logo Settings it is possible to set the flag for “Ignore FTE During Compa Ratio and Range Penetration Calculation”. By default this flag is unchecked; however, when this flag is set, the FTE is ignored in the compa ratio calculation.
• For the sample employee of Figure 1 this means for the compa ratio calculation (see yellow box in Figure 1 and for the Mid Point see Figure 5):
    60.214 EUR “Annual Salary” divided by ( 0.88 FTE x 64.800 EUR ) = 105.59% “Compa Ratio”
• The calculation rule for the 'Range Penetration' is:
      ( “PCG sum” x FTE – “Minimum Pay” ) divided by (“Maximum Pay” - “Minimum Pay”)
  However, since the 'Range Penetration' follows in general the same calculation principle as for the calculation of the 'Compa Ratio' and there are no additional aspects to be considered in reporting, in the following the focus is on the 'Compa Ratio'.

Figure 1   Example of a PCG sum, Compa Ratio and Range Penetration

Figure 2   Pay Component Group “Annual Salary” with assigned pay components

Figure 3   Pay Component “AccidentInsurance_DE” with respective attributes

Figure 4   Frequency with field “Annualization Factor”

Figure 5   Pay Range configuration. Note that the Pay Range depends by default from Pay Grade, Geo Zone and Legal Entity

Why the PCG sums, Compa Ratio and Range Penetration are ‘transient’ fields?

Based on the presented example it became clear that storing the PCG sums as well as 'Compa Ratio' and 'Range Penetration' on the database is challenging, in particular because the “ingredients” are time dependent. Let’s again summarize these time dependent “ingredients”; these were (3rd point of only relevant for 'Compa Ratio' and 'Range Penetration')

1. the EC recurring payment items
2. the assignments of 'Pay Components' to the PCGs
3. FTE, Legal Entity, Location/Geo Zone, Pay Grade from “Job Information”   

One important consequence of all of that is that the history of the PCG Sums, 'Compa Ratio' and 'Range Penetration' need to consider the effective dated time slices of all “ingredients” mentioned above. To store the history of the PCG Sums, 'Compa Ratio' and 'Range Penetration' with the goal to have all amounts and values always 100% precise, the effective dated time slices would need to be very fine granular. So-to-say the time slices of PCG Sums, 'Compa Ratio' and 'Range Penetration' would be a kind of superset of time slices of all the ingredients. Finally, changes of the configuration (such as e.g. adding/removing 'Pay Components' to historic time slices of the PCGs, changes of the historic FTE, etc.) lead also to changes of the history of the PCG Sums, 'Compa Ratio' and 'Range Penetration'.

One aspect which was up to now not yet addressed is the currency conversion. However, it is a common use case that the recurring payment items are paid in different currencies (e.g. base salary in EUR and particular allowances or annual bonus in USD). Furthermore, it might be possible that the Pay Range amounts (Maximum/Minimum Pay, Mid-Point) are maintained in different currencies compared to the compensation amounts. Of course, the currency conversion rate is – under circumstances even highly – time dependent. In extreme cases, e.g. if the currency exchange rates are updated on a weekly or monthly basis, then also the PCG Sums, 'Compa Ratio' and 'Range Penetration' change on a weekly or monthly basis (of course, only slightly; however, the amounts and values are not 100% precise if currency conversion aspects are ignored).

This makes clear that storing the PCG Sums, 'Compa Ratio' and 'Range Penetration' together with its history is a challenge. Nevertheless, it’s currently under discussion how such amounts can be persisted, e.g. which values should be persisted in which currencies and if some configuration possibilities are needed for this. However, it’s currently too early to provide any concrete timelines for this.

Consequences for Query Design

The complexity of the determination of the PCG Sums, 'Compa Ratio' and 'Range Penetration' reflects also in the query design; Figure 6 shows the query design.

Figure 6   Query design for the story report “Compa Ratio and Range Penetration”

Some explanations:

• The red box indicates the basic objects, consisting out of
  1. EC compensation objects 'Compensation Information' and 'Compensation'. Here, 'Compensation Information' takes over the role of the parent object while 'Compensation' has the respective child details (e.g. there are 4 records in case of the sample employee “Steffie Sieglinde Abel” from Figure 1)
  2. Related foundation objects like the 'Pay Component', 'Pay Component Group' and 'Base Pay Component Group'.
• The yellow box indicates a 2nd instance of the object 'Compensation'. This becomes relevant in case of 'Pay Components' of type “Percentage”. Since a 2nd instance 'Compensation (Copy)' is joined, a so-called "Cartesian Product" is created. Below it is explained more in detail how the filter is created to avoid unwanted duplicates (for now, please accept that the unwanted columns shown in grey are filtered out); for the sample employee “Steffie Sieglinde Abel” presented in this blogpost this means:
  

• Note that
  • The calculated column called “Annual Amount” is determined as “Compensation-amount” multiplied with the respective “Annualization Factor” from the object 'Frequency'
  • The 3 records indicated in red are derived from the 'Pay Components' of type “Amount”
  • The 3 records indicated in yellow are derived from the 'Pay Components' of type “Percentage”. Here, the “Compensation-amount” is 10 while the respective amounts from 'Compensation (Copy)-amount' are 430, 140 and 4300. The Annualization Factor is “1” (see Figure 1 for the 'Frequency' “Annual” of 'Pay Component' “AccidentInsurance_DE”)

Filter 7 shows the filter to avoid unwanted duplicates:

• The red box indicates the filter for 'Pay Components' of type “Amount”. The additional conditions are:
  1. 'Pay Component' assigned to 'Compensation' (i.e. upper part of the query indicated in red in Figure 6) is equal to 'Pay Component' assigned to 'Compensation (Copy)' (i.e. lower part of the query indicated in yellow in Figure 6)
  2. 'Pay Component Group' assigned to 'Compensation' (i.e. upper part of the query indicated in red in Figure 6) is equal to 'Pay Component Group' assigned to 'Compensation (Copy)' (i.e. lower part of the query indicated in yellow in Figure 6)
• The yellow box indicates the filter for 'Pay Components' of type “Percentage”. The additional conditions are:
  1. 'Base Pay Component Group' assigned to 'Compensation' (i.e. upper part of the query indicated in red in Figure 6) is equal to 'Base Pay Component Group (Copy)' assigned to 'Compensation (Copy)' (i.e. lower part of the query indicated in yellow in Figure 6)
  2. 'Base Pay Component Group' assigned to 'Compensation' is not equal to Null

Figure 7   Filter to remove unwanted records due to the Cartesian product

This is already sufficient to calculate the PCG Sums. However, to derive the 'Compa Ratio' and 'Range Penetration' it is required to determine the 'Pay Range'. As already explained in the chapter "Introduction", the 'Pay Range' is determined by a set of parameters and is not stored on the database for each employee. In the SAP default configuration, the 'Pay Range' depends on (1) 'Pay Grade', (2) 'Geo Zone' and (3) 'Legal Entity'. The solution which is used in People Analytics is (1) to join the ‘Pay Range’ just by one of the 3 parameters and (2) to apply additional filters. This ‘trick’ is so-to-say a possibility to join the ‘Pay Range’ by 3 different fields (what is currently not possible in People Analytics; it’s only possible to use one join field).

In the presented query the ‘Pay Range’ is joined to the employee via the ‘Pay Gade’. Due to the additional dependency of the ‘Pay Range’ from ‘Geo Zone’ and ‘Legal Entity’, this would cause many duplicates. To get rid of them a filter of type ‘field comparison’ is applied as indicated in green in Figure 7:

• “Pay Range – Legal Entity” needs to be equal to the employee’s ‘Legal Entity’ (1st filter condition)
• “Pay Range – Geo Zone” needs to be equal to the ‘Geo Zone’ assigned to the employee’s ‘Location’ (2nd filter condition)

Furthermore, only those PCGs are required that are used for the Compa Ratio- and Range Penetration-calculation. Therefore, the filter as indicated in blue in Figure 7 is applied (note that this filter is not mandatory for the compa ratio- and range penetration logic but helps to reduce the records in the created query result).

Consequences for Report Story Design

We are now ready to use the PCG Sums in the story, so far so good. However, one important aspect needs to be considered for the final 'Compa Ratio'- and 'Range Penetration'-calculation. The 'Compa Ratio'-calculation is an aggregation of multiple proportions contributed by various query records. This is illustrated by the sample employee “Steffie Sieglinde Abel” which is discussed during this blogpost (see e.g. Figure 1; note that 64800€ is the mid-point of the 'Pay Range' and 0.88 is the FTE for the sample employee):

This calculation must happen in a 2-step-approach. Figure 8 and 9 show the two story calculations to derive the 'Compa Ratio'.

Figure 8   Calculated Measure for a ‘pre-step’ to calculate the Compa Ratio

Figure 9   Story Calculation of type “Aggregation” to finally calculate the Compa Ratio

See the calculated measure in Figure 8 and note that the 'Compa Ratio' is not simply calculated by “Annual Amount” divided by the mid-point of the 'Pay Range' and the FTE but needs to be multiplied with the “Count” even twice (indicated in red in Figure 8). This is required due to the calculation logic in SAC; for the sample employee “Steffie Sieglinde Abel” this means in the internal calculation process the system calculates for each of the 6 records:

0.029331 = [ 2580 / (64800 x 6) /0.88 + 560 / (64800 x 6) /0.88 + ….. + 51600 / (64800 x 6) /0.88) ] / 6

Therefore, this value needs to be multiplied with 6 x 6 to get the 'Compa Ratio' of 1.0559 (= 36 x 029331).

The 'Range Penetration' is derived as [ “Annual Amount”/FTE minus “Minimum Pay” ] divided by [ “Maximum Pay” minus “Minimum Pay” ]. E.g. if the employee annual salary is exactly equal to the “Minimum Pay”, then the Range Penetration is 0% (if it’s equal to the “Maximum Pay”, then the Range Penetration is 100%).  The calculation of the 'Range Penetration' happens in a very similar manner as already explained for the 'Compa Ratio', see Figure 10 and 11. Also in this case the calculated measure “Range Penetration - helper” needs to use the “Annual Amount” multiplied by 2 times with “Count”.

Figure 10   Calculated Measure for a ‘pre-step’ to calculate the Range Penetration

Figure 11   Story Calculation of type “Aggregation” to finally calculate the Range Penetration

Conclusion

The story report “Compa Ratio and Range Penetration” which is presented in this blogpost is the one which is described in Compa Ratio and Range Penetration | SAP Help Portal and which is available from the content store. In this portal page you can also see additional analyses which can be done based on the 'Compa Ratio' of individual employees, e.g. such as comparisons of the (average or median) 'Compa Ratio' (and 'Range Penetration') between employees of different gender, between different organizational units, comparisons of parttime versus fulltime employees, and many more.

It is possible to enhance the level of complexity of the story by including currency conversions. It might be a bit of an academic use case; however, it is possible that e.g. one employee has 'Pay Components' in Euros and US dollars. Further it could be possible that the currency of PCG relevant for the 'Compa Ratio'-calculation is a 3rd currency (e.g. Chinese Renminbi, CN¥) and that the 'Pay Range'-amounts (Minimum Pay, Mid-Point, Maximum Pay) are maintained in a 4th currency (e.g. Swiss Francs, CHF). The 'Compa Ratio'- and 'Range Penetration'-calculation for the values shown on the employee profile can handle even such complex configurations. However, since the currency conversion for Story Reporting is available from b2505 onwards, it is also possible to enhance the pre-delivered story template accordingly. However, due to the complexity such enhancements should  only done by experienced report developers; last but not least also under performance considerations such enhancements need to be done with care.