Compensation Scenarios

This appendix covers the following topics:

Introduction

Oracle Incentive Compensation uses modular components to build compensation plans. All components used in the system can be configured and reused in different combinations.

For a detailed description of the compensation plan creation process, see Overview of Building Compensation Plans.

This appendix contains scenarios that show typical ways that companies use Oracle Incentive Compensation to calculate compensation for resources. It contains all of the valid formula combinations and illustrates how transactions are calculated using those options.

Creating a Compensation Plan and Using the Plan Component Library

You can create a compensation plan directly from the Main Menu by clicking the Create Compensation Plan link. You can create an empty compensation plan, and create and add plan components to the plan as necessary.

You can navigate to the plan component library directly from the Main Menu by clicking the Maintain Component Library link. In the Plan Component Library, you can use the component library sidebar to choose which component to search, and either create a new component or edit an existing component from the search page. You can also export search results to a CSV file.

The following sections describe the information that you can enter for compensation plans, plan elements, formulas, and rate tables, and the expression builder page for expressions.

Update Compensation Plans

The Update Compensation Plan page consists of three tabs:

See Creating a Compensation Plan for more details.

The following table describes fields and other components on the Design tab of the Update Compensation Plans page.

Component Type Description
Operating Unit LOV Required
Name Text input Required
From Date picker Required
To Date picker Optional
Context Value Pick list Flexfield.
Description Text input Optional
Allow Eligible Products Overlap Check box Check if you are using an eligible product in more than one plan element.
Plan Elements Table You can add Plan elements to this plan by adding them here. Refer to the Plan Elements section to see the Plan Element-related information
Save Button Save and stay on the page.
Apply Button Save and leave page.

Plan Element Details

The plan element creation process uses a Plan Element Checklist:

  1. Define General Information (required)

  2. Define Earnings Rule (required)

  3. Assign Eligible Products (required)

  4. Assign Rate Tables (required)

  5. Define Default Quotas

The following table describes the fields from all five steps in the checklist, in order.

Component Type Description
Operating Unit LOV Required
Name Text input Required.
From Date picker Required.
To Date picker Optional.
Calculation Type Pick list  
Element Group Code Pick list  
Description Text input Optional, but useful for differentiating plan elements with similar names.
Context Value Pick list Flexfield
Formula Type and Name LOV Select Formula or External. With Formula, select from the Choose Formula drop-down list. With External, enter a package name. This field is required.
Interval Type PIck list The period of time for which the plan element calculates compensation, for example, Period (month), Quarter, or Year.
Payment Group Code Pick list User-defined; Standard is the default setting.
Credit Type Pick list Select functional currency (default) or any predefined currency type.
Paid Through Third Party Radio group Use if you want to assign payment to a resource other than the one who earned sales credit.
Liability Account LOV Use this field to set up a liability account with Oracle Payable at the plan element level. Optional.
Expense Account LOV Use this field to set up an expense account with Oracle Payable at the plan element level. Optional.
Compensate Indirect Credit Radio group  
Eligible Products Table You can add rows to the table.
Target Text input Set a target that you can distribute on the Distribute Variables page. Optional.
Fixed Amount Text input Set a fixed amount that you can distribute on the Distribute Variables page. Optional.
Performance Goal Text input Set a goal that you can distribute on the Distribute Variables page. Optional.

Formula Definition

In addition to header-level information, formula options are organized in tabs:

The following table describes fields and other components of the Formula definition page. See Define Formulas.

Component Type Description
Operating Unit LOV Required
Name Text input Required.
Calculation Type Pick list Select Commission or Bonus.
Status Read-only field This field says Incomplete until the formula is generated.
Description Text input Optional, but useful for differentiating formulas with similar names.
Process Transactions Pick list Select Individually or Grouped by Interval. This affects how calculation runs.
Split Option Pick list This affects how transactions are applied to the rate table.
Accumulate Transactions Check box Check to use accumulated transactions to determine the rate tier applied to transactions.
Interval To Date Check box Check to use interval-to-date calculation.
Planning Check box Check if this formula is to be used for planning only.
Input    
Output    
Performance Measure    

Rate Tables

The following table describes fields and other components of the Rate Table pages. See Define Rate Tables.

Component Type Description
Operating Unit LOV Required
Name Text input Required.
Rate Type Pick list Select Commission or Bonus.
Rate Dimensions Table Add rate dimensions to the table.

Rate Dimensions

The following table describes fields and other components of the Rate Dimension page. See Define Rate Dimensions.

Component Type Description
Operating Unit LOV Required
Name Text input Required.
Type Pick list  
Description Text input Optional.
Tiers Table Add tiers to table.

Calculation Expressions

Use the Expression Builder to pick and choose components and operators to create an expression. See Define Calculation Expressions for details.

Scenario Management

A scenario is a end-to-end setup required to process transactions and calculate payments. A scenario includes:

the picture is described in the document text

You can perform the following operation on a scenario:

Search Scenarios

As a Plan Administrator, you can search for an existing scenario, in a operating unit and perform various operation on these scenarios. Oracle Incentive Compensation supports Simple Search, Advanced Search, and Views. Using Simple Search you can search for a scenario using Operating Unit and/or Scenario Name. You can click Advanced Search to search based on additional search criteria. View allows you to personalize and save searches.

Oracle Incentive Compensation displays list of scenarios that matches your search criteria. You can create a duplicate copy of these scenarios, delete them, or view and update the scenarios, by clicking the appropriate links.

Navigation

Plan Administrator > Maintain Scenario

Create/ View/ Update Scenario

You can create a new scenarios, and view or update an existing scenario. When you change a plan, or elements within a plan, which are included in a scenario, Oracle Incentive Compensation will automatically update the scenario.

To create a scenario, click Create in the Search Scenario page. Enter a scenario name, choose an operating unit, and add plans to the scenario, to create one. After you have created a scenario, you can view and update the scenario. To view a scenario, click the applicable scenario, from the Search Scenario page. Oracle Incentive Compensation displays all the compensation plans in the scenario. You can add more compensation plans to the scenario, or delete existing ones.

You have the ability to export the plans in the scenario, to your local system. The application saves the details in an XML file. When you click Export Plans, Oracle Incentive Compensation creates an export request, with the following parameters:

For more information on exporting compensation plans, see: Creating Export Request.

Common Compensation Scenarios Based on Formula Options

On the Update Formula page, there are various calculation options involving rate tier splits, how to process transactions, whether to accumulate transactions, and whether to use Interval-to-Date in the calculations. This section describes the valid formula combinations and illustrates how transactions are calculated using those options.

Scenario Process Transaction Split Option Accumulate Transaction Interval to Date
A Individually No Split No No
B Individually No Split Yes No
C Individually No Split Yes Yes
D Individually Non-Proportional No No
E Individually Non-Proportional Yes No
F Individually Non-Proportional Yes Yes
G Grouped by Interval No Split Yes No
H Grouped by Interval Non-Proportional Yes No

For the example test case, the following Percent rate table is used for scenarios A through H. An Amount rate table (presented later in this document) is used for scenarios I through L.

Tier Rate
0-1,000 1%
1,000-3,000 2%
3,000-8,000 3%
8,000-20,000 5%

For scenarios A through L, these are the transactions:

Name Date Amount
T1 Jan-01-2007 200
T2 Jan-2-2007 300
T3 Jan-15-2007 1,500
T4 Feb-01-2007 1,200
T5 Feb-15-2007 2,000
T6 Mar-01-2007 4,500

Now, we will describe each scenario and list the commission calculation results for the transactions for each scenario.

Scenario A: Transactions Processed Individually

Individual calculation is the simplest way to set up compensation calculation for transactions. Each transaction is applied to a rate table separately without regard for the period in which it takes place or any other transactions. Compensation is calculated using a single rate dimension. In this scenario, all transactions are processed individually against the rate table. The total amount of commission earned is 234.

January

Name Date Amount Commission Rate Commission Amount
T1 Jan-01-2007 200 1% 2
T2 Jan-02-2007 300 1% 3
T3 Jan-15-2007 1,500 2% 30

February

Name Date Amount Commission Rate Commission Amount
T4 Feb-01-2007 1,200 2% 24
T5 Feb-15-2007 2,000 2% 40

March

Name Date Amount Commission Rate Commission Amount
T6 Mar-01-2007 4,500 3% 135

Scenario B: Transactions Processed Individually, Rates Based on Accumulated Transactions

Use a cumulative formula when you want to apply individual transactions to a rate table but still use the accumulated total for the period to calculate the compensation. This type of calculation rewards resources for increasing sales volume. However, unlike the group-by-interval formula, a cumulative formula does not create a dummy transaction at the end of a period and apply the total to the rate table all at once.

In this scenario, each transaction is processed individually, but rates for each transaction are based on accumulated achievement for the interval. The total amount of compensation earned is 254.

For Transaction T3, the accumulated amount is 200+300+1500=2000. For Transaction T5, the accumulated amount is 1200+2000=3200.

January

Name Date Amount Commission Rate Commission Amount
T1 Jan-01-2007 200 1% 2
T2 Jan-02-2007 300 1% 3
T3 Jan-15-2007 1,500 2% 30

February

Name Date Amount Commission Rate Commission Amount
T4 Feb-01-2007 1,200 2% 24
T5 Feb-15-2007 2,000 3% 60

March

Name Date Amount Commission Rate Commission Amount
T6 Mar-01-2007 4,500 3% 135

Scenario C: Transactions Processed Individually, Accumulated Interval-to-Date

With accumulated interval-to-date, transactions are applied to a range, but the table uses expressions that indicate percentage of achievement toward the period quota instead of specific amounts. These percentages apply equally regardless of the amount of the quota for the period. This means that the resource receives a consistent rate of compensation regardless of the amount of quota for that period. Interval-to-date formulas calculate compensation for the period of the interval and then revert to 0 at the beginning of each new interval.

For example, a resource in the holiday decoration sales industry is placed on a compensation plan with a Quarter-to-Date interval. For the first three quarters of the year, the quota is $10,000 per quarter. However, the quota for the busy fourth quarter is $50,000. The annual quota is $80,000, but the resource works to attain a realistic quota each quarter.

In this scenario, each transaction is processed individually, but rates for each transaction are based on accumulated achievement for the interval. Interval-to-date (ITD) means that rates calculated for the current transaction are applied retroactively (minus the commission already calculated for past transactions). The total amount of compensation earned is 271.

For transaction T2, the accumulated amount is 200+300=500. Commission is 500*1% - 2 = 3. For transaction T3, the accumulated amount is 200+300+1,500=2,000. Commission is 2,000*2% - (2+3) = 35. For transaction T5, the Accumulated amount is 1,200+2,000=3,200. Commission is 3,200*3% - 24 = 72.

January

Name Date Amount Commission Rate Commission Amount
T1 Jan-01-2007 200 1% 2
T2 Jan-02-2007 300 1% 3
T3 Jan-15-2007 1,500 2% 35

February

Name Date Amount Commission Rate Commission Amount
T4 Feb-01-2007 1,200 2% 24
T5 Feb-15-2007 2,000 3% 72

March

Name Date Amount Commission Rate Commission Amount
T6 Mar-01-2007 4,500 3% 135

Scenario D: Transactions Processed Individually, but Split Nonproportionally Across Rate Tiers

Rate tiers can be split, allowing compensation to be calculated by applying transactions to more than one rate tier. Use a rate tier split if you want compensation to accurately reflect the exact amount of the transaction up to the maximum amount of each rate tier. Splitting rate tiers normally results in lower commission payouts, because only the part of the transaction amount that is over the maximum threshold of a tier is paid at the next higher tier's rate.

In this scenario, all transactions are processed individually against the rate table, but can be split non-proportionally across rate tiers. The total amount of compensation earned is 164.

For transaction T3, the first 1,000 applies to 1%, and the remaining 500 applies to 2%. Commission is 1,000*1% + 500*2% = 20. For transaction T4, the first 1,000 applies to 1%, and the remaining 200 applies to 2%. Commission is 1,000*1% + 200*2% = 14. For transaction T5, the first 1,000 applies to 1%, and the remaining 1,000 applies to 2%. Commission is 1,000*1% + 1000*2% = 30. For transaction T6, the first 1,000 applies to 1%, the next 2,000 applies to 2%, and the remaining 1,500 applies to 3%. Commission is 1,000*1% + 2,000*2% + 1,500*3% = 95.

January

Name Date Amount Commission Rate Commission Amount
T1 Jan-01-2007 200 1% 2
T2 Jan-02-2007 300 1% 3
T3 Jan-15-2007 1,500 1.33% (1% for 1,000, 2% for 1,000) 30

February

Name Date Amount Commission Rate Commission Amount
T4 Feb-01-2007 1,200 1.167% (1% for 1,000, 2% for 200) 14
T5 Feb-15-2007 2,000 1.5% (1% for 1,000, 2% for 1,000) 30

March

Name Date Amount Commission Rate Commission Amount
T6 Mar-01-2007 4,500 2.11% (1% for 1,000, 2% for 2,000, 3% for 1,500) 95

Scenario E: Transactions Processed Individually, Accumulated Transactions, Non-Proportional Split

In this scenario, each transaction is processed individually, but rates for each transaction are based on accumulated achievement for the interval. The accumulated achievement can be split non-proportionally across rate tiers. The total amount of compensation earned is 181.

For transaction T2, the accumulated amount is 200+300=500 (1st tier). For transaction T3, 1% is applied to the first 500 to finish the 1st tier. 2% is applied to remaining 1,000. For transaction T4, 1% is applied to first 1,000 to finish the 1st tier. 2% is applied to the remaining 200 (2nd tier). For transaction T5, 2% is applied to the first 1,800 to finish the 2nd tier. 3% is applied to the remaining 200 (3rd tier). For transaction T6, 1% is applied to the first 1,000 to finish the 1st tier. 2% is applied to the next 2,000 to finish the 2nd tier. 3% is applied to the remaining 1,500 (3rd tier).

January

Name Date Amount Commission Rate Commission Amount
T1 Jan-01-2007 200 1% 2
T2 Jan-02-2007 300 1% 3
T3 Jan-15-2007 1,500 1.67% (1% for 500, 2% for 1,000) 25

February

Name Date Amount Commission Rate Commission Amount
T4 Feb-01-2007 1,200 1.167% (1% for 1,000, 2% for 200) 14
T5 Feb-15-2007 2,000 2.1% (2% for 1,800, 3% for 200) 42

March

Name Date Amount Commission Rate Commission Amount
T6 Mar-01-2007 4,500 2.11% (1% for 1,000, 2% for 2,000, 3% for 1,500) 95

Scenario F: Transactions Processed Individually, Accumulated Achievement, Split Non-Proportionally, Interval-to-Date

In this scenario, each transaction is processed individually, but rates for each transaction are based on accumulated achievement for the interval. The accumulated transactions can be split non-proportionally across rate tiers. Interval-to-Date (ITD) means that rates calculated for the current transaction are applied retroactively (minus the commission already calculated for past transactions). The total amount of compensation earned is 181.

For transaction T2, the accumulated amount is 200+300=500. Commission is 1%*500 – 2 = 3. For transaction T3, the accumulated amount is 200+300+1,500=2,000. Of the 2,000, the first 1000 is applied 1% from the first tier, and the remaining 1,000 is applied 2% from the 2nd tier. Commission= 1%*1,000 + 2%*1,000 – (2+3) = 25. For transaction T4, of the 1,200, the first 1,000 is applied 1% from the 1st tier, and the remaining 200 is applied 2% from the 2nd tier. Commission= 1%*1,000 + 2%*200 = 14. For transaction T5. the accumulated amount is 1,200+2,000=3,200. Of the 3,200, the first 1,000 is applied 1% from the first tier, the next 2,000 is applied 2% from the 2nd tier, and the remaining 200 is applied 3% from the 3rd tier. Commission= 2%*1,800 + 3%*200 = 42. For transaction T6, of the 4,500, the first 1,000 is applied 1% to finish off the 1st tier, the next 2,000 is applied 2% to finish off the 2nd tier, and the remaining 1,500 is applied 3% from the 3rd tier. Commission= 1%*1,000 + 2%*2,000 + 3%*1,500 = 95.

January

Name Date Amount Commission Rate Commission Amount
T1 Jan-01-2007 200 1% 2
T2 Jan-02-2007 300 1% 3
T3 Jan-15-2007 1,500 1.5% (effective) 25

February

Name Date Amount Commission Rate Commission Amount
T4 Feb-01-2007 1,200 1.167% (effective) 14
T5 Feb-15-2007 2,000 1.75% 42

March

Name Date Amount Commission Rate Commission Amount
T6 Mar-01-2007 4,500 2.11% (effective) 95

Scenario G: Calculation at End of Interval, Commission Rate Based on Accumulated Achievement of Grouped Transactions

In this scenario, calculation occurs at the end of the interval (month, in this case). Because calculation is grouped by interval, only a single commission record is created for each interval. The commission rate used for calculation for the group is based on the accumulated achievement for the group. The total commission for each month is the same as in Scenario C, because Interval-to-Date (ITD) Targets are not used. When ITD Targets are distributed across periods and used in calculation, calculation will use the period ITD Target during calculation of that period. The total amount of compensation earned is 271.

For the January Sum, the accumulated amount is 200+300+1,500=2,000. Commission= 2%*2000 = 40. For the February Sum, the accumulated amount is 1,200+2,000=3,200. Commission= 3%*3,200 = 96. For the March Sum, the commission= 3%*4,500 = 135.

January

Name Date Amount Commission Rate Commission Amount
January Sum   2,000 2% 40

February

Name Date Amount Commission Rate Commission Amount
February Sum   3,200 3% 96

March

Name Date Amount Commission Rate Commission Amount
March Sum   4,500 3% 135

Scenario H: Calculation at End of Interval, Non-Proportional Split, Interval-to-Date

In this scenario, calculation occurs at the end of the interval (month, in this case). Because calculation is grouped by interval, only a single commission record is created for each interval. The commission rate used for calculation for the group is based on the accumulated achievement for the group. The accumulated achievement can be split across rate table tiers. The total commission for each month is the same as in Scenario F, because ITD Targets are not used. When ITD Targets are distributed across periods and used in calculation, calculation uses the period ITD Target during calculation of that period. The total amount of compensation earned is 181.

For the January Sum, of the 2,000, the first 1,000 is applied 1% from the 1st tier, and the remaining 1,000 is applied 2% from the 2nd tier. Commission= 1%*1,000 +2%*1,000 = 30. For the February Sum, of the 3,200, the first 1,000 is applied 1% from the 1st tier, the next 2,000 is applied 2% from the 2nd tier, and the remaining 200 is applied 3% from the 3rd tier. Commission= 1%*1,000 + 2%*2,000 + 3%*200 = 56. For the March Sum, of the 4,500, the first 1,000 is applied 1% from the 1st tier, the next 2,000 is applied 2% from the 2nd tier, and the remaining 1,500 is applied 3% from the 3rd tier. Commission= 1%*1,000 + 2%*2,000 + 3%*1500 = 95.

January

Name Date Amount Commission Rate Commission Amount
January Sum   2,000 1.5% (effective) 30

February

Name Date Amount Commission Rate Commission Amount
February Sum   3,200 1.75% 56

March

Name Date Amount Commission Rate Commission Amount
March Sum   4,500 2.11% (effective) 95

Proportional Split Examples

Splitting rate tiers using a proportional split usually involves a rate table that is of the Amount type. Where the rate table used rate percentages, such as 1% of 2%, the Amount type rate table uses specific amounts, such as 10 or 40. The table below shows the details of the four proportional split scenarios, I through L. The four scenarios use the same six transactions as those used for scenarios A through H.

Scenario Process Transaction Split Option Accumulate Transactions Interval To Date
I Individually Proportional No No
J Individually Proportional Yes No
K Individually Proportional Yes Yes
L Grouped by Interval Proportional Yes No

The table below shows the Amount rate table. It uses the same tiers as the Percent rate table used in scenarios A through H.

Tier Amount
0-1,000 10
1,000-3,000 40
3,000-8,000 100
8,000-20,000 2,000

Scenario I: Transactions Processed Individually, Proportional Split

In this scenario, all transactions are processed individually against the rate table. A proportional split occurs when a transaction crosses rate table tiers. The total amount of compensation earned is 149.

For transaction T1, 200 is 20% of the 1st tier. Commission = (200/1,000)*10=2. For transaction T2, 300 is 30% of the 1st tier. Commission =(300/1000)*10=3. For transaction T3, the first 1,000 fills up the 1st tier, and the remaining 500 is 25% of the 2nd tier. Commission =10+(500/2,000)*40=20. For transaction T4, the first 1,000 fills up the 1st tier, and remaining 200 is 10% of 2nd tier. Commission =10+(200/2,000)*40=14. For transaction T5, the first 1000 fills up the 1st tier, and the remaining 1,000 is 50% of the 2nd tier. Commission=10+(1,000/2,000)*40=30. For transaction T6, the first 1,000 fills up the 1st tier, the next 2,000 fills up the 2nd tier, and the remaining 1,500 is 30% of the 3rd tier. Commission= 10+40+(1,500/5,000)*100=80.

January

Name Date Amount Commission Rate Commission Amount
T1 Jan-01-2007 200 N/A 2
T2 Jan-02-2007 300 N/A 3
T3 Jan-15-2007 1,500 N/A 25

February

Name Date Amount Commission Rate Commission Amount
T4 Feb-01-2007 1,200 N/A 14
T5 Feb-15-2007 2,000 N/A 30

March

Name Date Amount Commission Rate Commission Amount
T6 Mar-01-2007 4,500 N/A 80

Scenario J: Transactions Processed Individually, Proportional Split, Accumulated Transactions.

In this scenario, all transactions are processed individually. When looking up the rate table amount, the accumulated achievement is used. A proportional split occurs when the accumulated achievement crosses rate table tiers. Because the amounts are accumulated, the subsequent transactions in a period hit higher rate tiers than in Scenario I (illustrated in the calculation for T2 and T5). The total amount of compensation earned is 164.

For transaction T1, commission= (200/1,000)*10=2. For transaction T2, the accumulated amount is 200+300=500. Commission= (300/1,000)*10=3. For transaction T3, the accumulated amount is 200+300+1,500=2,000. Of the 1,500, the first 500 accounts for the remaining 50% in the 1st tier, and the remaining 1,000 accounts for 50% of the 2nd tier. Commission= (500/1,000)*10 + (1,000/2,000)*40=25. For transaction T4, the first 1,000 fills up the 1st tier for $10, and the remaining 200 accounts for 10% of the 2nd tier. Commission= 10 + (200/2,000)*40 = 14. For transaction T5, the accumulated amount is 1,200+2,000=3,200. Of the 2,000, the first 800 accounts for the remaining 40% of the 2nd tier, and the remaining 1,200 accounts for 24% of the 3rd tier. Commission= (800/2,000)*40 + (1,200/5,000)*100 = 40. For transaction T6, the first 1,000 fills up the 1st tier for $10, the next 2,000 fills up the 2nd tier for $40, and the remaining 1,500 accounts for 30% of the 3rd tier. Commission= 10+40+(1,500/5,000)*100 = 80.

January

Name Date Amount Commission Rate Commission Amount
T1 Jan-01-2007 200 N/A 2
T2 Jan-02-2007 300 N/A 3
T3 Jan-15-2007 1,500 N/A 25

February

February

Name Date Amount Commission Rate Commission Amount
T4 Feb-01-2007 1,200 N/A 14
T5 Feb-15-2007 2,000 N/A 40

March

Name Date Amount Commission Rate Commission Amount
T6 Mar-01-2007 4,500 N/A 80

Scenario K: Transactions Processed Individually, Proportional Split, Accumulated Transactions Interval-to-Date

In this scenario, all transactions are processed individually. When looking up the rate table amount, the accumulated achievement is used. A proportional split occurs when the accumulated achievement crosses rate table tiers. Interval-to-Date (ITD) means that the rate table amount for the current transaction is applied retroactively (minus the commission already calculated for past transactions). The total amount of compensation earned is 164.

For transaction T1, 200 is 20% of the 1st tier. Commission = (200/1,000)*10=2. For transaction T2, the accumulated amount is 200+300=500. Commission = (500/1,000)*10 – 2 = 3. For transaction T3, the accumulated amount is 200+300+1,500=2,000. The first 1,000 fills up the 1st tier, and the remaining 1,000 is 50% of the 2nd tier. Commission= 10 + (1,000/2,000)*40 – (2+3) = 25. For transaction T4, the first 1,000 fills up the 1st tier, and the remaining 200 is 10% of the 2nd tier. Commission= 10 + (200/2,000)*40 = 14. For transaction T5, the accumulated amount is 1,200+2,000=3,200. The first 1,000 fills up the 1st tier, the next 2,000 fills up the 2nd tier, and the remaining 200 is 4% of the 3rd tier. Commission= 10 + 40 + (200/5,000)*100 – 14 = 40. For transaction T6, the first 1,000 fills up the 1st tier, the next 2,000 fills up the 2nd tier, and the remaining 1,500 is 30% of the 3rd tier. Commission= 10 + 40 + (1,500/5,000)*100 = 80.

January

Name Date Amount Commission Rate Commission Amount
T1 Jan-01-2007 200 N/A 2
T2 Jan-02-2007 300 N/A 3
T3 Jan-15-2007 1,500 N/A 25

February

Name Date Amount Commission Rate Commission Amount
T4 Feb-01-2007 1,200 N/A 14
T5 Feb-15-2007 2,000 N/A 40

March

Name Date Amount Commission Rate Commission Amount
T6 Mar-01-2007 4,500 N/A 80

Scenario L: Grouped by Interval, Proportional Split, Accumulated Transactions

In this scenario, calculation occurs at the end of the interval (month, in this case). Because calculation is grouped by interval, only a single commission record is created for each interval. When looking up the rate table amount, the accumulated achievement is used. The accumulated achievement can be split across rate table tiers. The total amount of compensation earned is 164.

For the January Sum, the first 1,000 pays out $10 for the 1st tier, and the remaining 1,000 accounts for 50% of the 2nd tier. Commission= 10+(1,000/2,000)*40 = 30. For the February Sum, The first 1,000 pays out $10 for the 1st tier, the next 2,000 pays out $40 for the 2nd tier, and the remaining 200 accounts for 4% of the 3rd tier. Commission= 10+40+(200/5,000)*100 = 54. For the March Sum, the first 1,000 pays out $10 for the 1st tier, the next 2,000 pays out $40 for the 2nd tier, and the remaining 1,500 accounts for 30% of the 3rd tier. Commission= 10+40+(1500/5000)*100 = 80

January

Name Date Amount Commission Rate Commission Amount
January Sum   2,000 N/A 30

February

Name Date Amount Commission Rate Commission Amount
February Sum   3,200 N/A 54

March

Name Date Amount Commission Rate Commission Amount
March Sum   4,500 N/A 80

Complex Compensation Scenarios

This topic contains four scenarios that a little more complicated than the ones in the previous topic. They include:

Multiple Input Formula

A scenario that uses a Multiple Input formula generates commission based on information in addition to the transaction amount. This scenario uses a state code. This scenario uses these expressions

This scenario uses this rate tables. Columns 2 through 4 show the commission percentage for each state code:

Transaction Amount State Code: CA State Code: NV State Code: OR
0-5,000 1% 2% 3%
5000-10,000 2% 3% 4%
10,000-30,000 3% 4% 5%
30,000-999,999,999 5% 6% 7%

Below are the transactions used in this scenario.

Resource Processed Date Amount State Code Transaction Type Commission
Rep 1 02-Jan-07 $3,000 CA Revenue $30
Rep 1 15-Jan-07 4,000 OR Revenue 120
Rep 1 29-Jan-07 25,000 NV Revenue 1,000

When the individual transactions are applied to the rate table, the first transaction falls into the first tier of the rate table, for state code CA, so it pays commission at 1%. The second transaction is still in the first tier, but for state code OR it pays 3%. The third transaction falls into the third tier of the rate table, for state code NV, so it generates commission at 4%. Total commission for the three transactions is $1,150.

This method of calculating compensation works the same if you use a multidimensional Amount rate table rather than a Percentage table.

Bonus Compensation

A Bonus plan element has no links or references to individual transactions. Bonus formulas calculate only against Individual transaction options. Split options are selectable (each is mutually exclusive).

A bonus plan element uses a formula type of Bonus and a plan element incentive type of Bonus.

This scenario uses a bonus plan that uses achievement information to calculate a bonus.

This scenario uses these expressions:

This Bonus rate table uses a Percent Amount rate table, because the dimension rate tier ranges are measured in percentages but the bonus amounts are measured in dollar amounts.

This Percent Amount Rate Table uses a dimension with tier ranges. When the amount of the resource's aggregated transactions fall in the rate tier range between them.

Percentage of Achievement Bonus Amount
0-50 $0
50-75 1000
75-100 2000
100-999,999 1000

The bonus plan element compares the amount of the aggregated transactions with the resource's target. The percentage of achievement determines whether a bonus is paid and also the amount of the bonus.

Interdependent Plan Elements

Interdependent plan elements use the calculated totals of one plan element to calculate commission for another plan element. This is especially useful if you want to base qualification for bonus compensation on achievement of sales targets in a commission plan element.

To set up an interdependent plan element, you must create an expression that accesses specific plan element totals.

For specific steps to create interdependent plan elements, see Using Interdependent Plan Elements.

As an example, you want your resources to receive an additional bonus if they sell more than 50% of their target. To create an interdependent plan element in the resource's compensation plan, perform these steps.

  1. Create a commission plan element, including the input and output expressions and a rate table to pay regular commission and aggregate credits and the target. Include the appropriate eligible product(s) in the plan element.

  2. Create a second plan element that pays a specified bonus if a specified percentage of the target of the first plan element is met. Use a formula with an input expression that references the achievement of the plan element created in step 1. This input expression divides the accumulated total by the ITD_Target total, which determines the achievement.

  3. Sequence the plan elements in the final compensation plan so that the transactions are collected and calculated for the commission plan element first. That way, the total from all of the transactions for the first (commission) plan element can be used accurately in the input expression of the second (bonus) plan element.

  4. Set up a plan element that calculates commission and determines if the resource has achieved 100 percent of her sales target. It has been kept simple for the purposes of this example.

  5. Set up these expressions for the first plan element:

    • Input Expression: Transaction Amount

    • Output Expression: Rate Result*Transaction Amount

  6. Set up the following rate table, which shows what percentage of commission to pay on ranges of transaction amounts.

    Transaction Amount Commission
    0-5,000 1%
    5000-10,000 2%
    10,000-30,000 3%
    30,000-999,999,999 5%

  7. Set up a second plan element that uses achievement information from the first plan element to calculate a bonus.

  8. Use these expressions for the second plan element:

    • Input Expression: Aggregated transactions

    • Output Expression: Rate Table Result

  9. Use this rate table, which uses a Percent input and outputs an Amount.

    Percentage of Achievement Bonus Amount
    0-50 $0
    50-75 1000
    75-100 2000
    100-999,999,999 1000

  10. The bonus plan element compares the amount of the aggregated transactions with the resource's target. The percentage of achievement determines whether a bonus is paid and also the amount of the bonus.

Compensation for Transactions Using Multiple Rate Dimensions

Rate tables can use four different kinds of dimensions:

See Define Rate Dimensions for more information.

This scenario shows examples of each kind of rate dimension, and how they can work together in multidimensional rate tables as well.

In a company, the sales manager wants to pay commission based on the amount of units sold and also on the territory in which the sale it was made. The amount sold is an amount. The territory is defined by state, which is stored as a string. The manager asks the Incentive Compensation Analyst to create a multidimensional rate table consisting of a Units Sold dimension and a State dimension.

A Units Sold amount dimension by itself looks like this:

Units Sold
1-100
100-250
250-999,999,999

A State string dimension by itself looks like this:

State
California
Oregon
Washington

Combined, they become a multidimensional rate table, which pays different amounts based on units sold and also on where the deal took place (see the setup below).

This scenario uses these two expressions:

Here is a rate table for the scenario.

Units Sold California Oregon Washington
1-100 $100 $200 $400
100-250 200 300 600
250-999,999,999 300 400 800

Here are the transactions:

Resource Processed Date Units State Transaction Type Commission
Rep 1 07-Jan-07 150 California Revenue $630
Rep 1 12-Jan-07 1,000 Oregon Revenue 45
Rep 1 20-Jan-07 50 Washington Revenue 144

When transactions are applied to the rate table, the units sold dimension is used first, and then the territory dimension is applied later. For the three transactions above, the calculation works this way:

The first transaction is applied to the rate table, in the second tier, for California. This generates commission of $200.

The second transaction is placed in the third tier of the rate table, for Oregon, generating $400 commission.

The third transaction ends up in the first tier, for Washington, generating $400 commission.

Compensation Scenarios Based on External Tables

You can use external tables to calculate compensation. This section contains two examples:

External Table Based Expressions Used in Formula for Both Input and Output

Oracle Incentive Compensation can accommodate external tables, as long as the tables are properly registered in the application. In this example, the commission calculation uses legacy data from the company's Human Resources Department in the input expression, and then uses another table in the output expression to modify commission payment amounts.

The input expression uses an Employee Code Number. Employees with less than two years of experience are assigned code number 1, employees with 2-5 years of experience are assigned code number 2, and employees with 5 or more years of experience are assigned code 3. This multiplier increases transaction amounts and can move transactions up the rate table, paying higher commission to more senior salespeople.

The output expression uses the previous year's achievement as a ratio of sales divided by goal. This legacy data, stored in a table in Accounts Receivable, rewards resources who achieved or exceeded their goal last year and penalizes resources who underachieved last year.

See Define Calculation Expressions.

For the Employee Code Number to appear in the expression builder, the external table in which it appears must be registered. This procedure can be performed by your database administrator using a SQL script.

These are the expressions used in this scenario:

Here's the rate table, which uses transaction amount and commission percent.

Transaction Amount Commission
0-5,000 1%
5000-10,000 2%
10,000-30,000 3%
30,000-999999999999 5%

Here are the transactions.

Resource Processed Date Amount Transaction Type Commission
Rep 1 07-Jan-07 $7,000 Revenue $630
Rep 2 12-Jan-07 3,000 Revenue 45
Rep 3 20-Jan-07 4,000 Revenue 144

Below is a Human Resources table that shows the resource name and employee code number.

Resource Employee Code Number
Rep 1 3
Rep 2 1
Rep 3 2

Here is an Accounts Receivable table, with resource name, sales year, sales amount, and annual goal.

Resource Sales Year Sales Amount Annual Goal
Rep 1 2002 $250,000 $250,000
Rep 2 2002 150,000 100,000
Rep 3 2002 180,000 200,000

The External Input Output formula multiplies the transaction amount times the Employee Code Number and then applies it to the rate table. The rate table result is then multiplied times the ratio of previous year sales to previous year goal.

This is how compensation is calculated for three different resources.

Rep 1 is a long term employee who met his 2002 goal exactly.

The transaction is multiplied times the Employee Code Number

The amount is applied to the rate table

The rate table result is multiplied times the sales/goal ratio (1.0)

By meeting his sales goal the previous year, Rep 1 receives all of his calculated commission amount. Note that if the Employee Code Number was not part of the input expression, or if Rep 1 was a new employee, this transaction would fall into the second tier of the rate table, paying 2% on $7,000, or $140. This input expression rewards seniority and meeting previous goals.

Rep 2 has been working for the company for only a year and a half, so she is assigned Employee Code Number 1. However, she worked very hard last year and exceeded her goal.

The transaction is multiplied times the Employee Code Number

The amount is applied to the rate table

The rate table result is multiplied times the sales/goal ratio (1.5)

Although her commission rate is lower because of her shorter time with the company, Rep 2 increased her earnings on this transaction by 50 percent because of her success in the previous year.

Rep 3 has worked for the company for four years, and does a good job. However, he achieved only 90% of his goal last year, and this will affect his final commission earnings.

The transaction is multiplied times the Employee Code Number

This amount is applied to the rate table

The rate table result is multiplied times the sales/goal ratio (.9)

Rep 2 benefited from his longevity with the company--it doubled the amount applied to the rate table. However, he lost out on 10 percent of his earnings because he did not meet his sales goal for the previous year.

Bonus Based on External Data

Sometimes data in an external table is needed to determine eligibility for a bonus. In this example, the resource's salary is used to determine the amount of a bonus payment. This information is stored in the Human Resources Department.

See Define Calculation Expressions.

For the Salary Amount to appear in the expression builder, the external table in which it appears must be registered. This procedure can be performed by your database administrator using a SQL script.

Here are the two expressions used for this scenario:

This Bonus example uses an Amount rate table. Both rate dimensions are measured in dollar amounts.

Salary Amount Bonus Amount
$25,000-50,000 $1,000
50,000-75,000 2,000
75,000-100,000 3,000
100,000-999,999 5,000

For three sample resources, the calculation works this way when the bonus plan element is applied:

Resource Name Salary Rate Tier Bonus Amount
Sam Smith $42,500 1 $1,000
Joan Jones 68,000 2 2,000
Peter Parker 110,000 4 5,000

For an external formula type, you must enter External in the Formula Type field instead of Formula. You do not enter anything in the Choose Formula field, but you must enter a PL/SQL package name to enable the application to find the external formula.