This appendix covers the following topics:
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.
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.
The Update Compensation Plan page consists of three tabs:
Design: Enter important information for plan design setup.
Eligibility: Choose roles to be assigned the compensation plans. Resources assigned to the role are assigned this compensation plan.
Notes: Contains system-generated and user-entered notes relating to changes to the plan or to any of its components. This is not used for setting up the compensation plan.
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. |
The plan element creation process uses a Plan Element Checklist:
Define General Information (required)
Define Earnings Rule (required)
Assign Eligible Products (required)
Assign Rate Tables (required)
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. |
In addition to header-level information, formula options are organized in tabs:
Options: Five choices affecting transactions
Expressions: Input, output, or performance measures
Rate Tables: Add a rate table to be used by the formula
Interdependencies: Lists expressions referencing this formula
Notes: Contains system-generated and user-entered notes relating to changes to the formula or to any off its components. This is not used for setting up the formula.
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 |
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. |
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. |
Use the Expression Builder to pick and choose components and operators to create an expression. See Define Calculation Expressions for details.
A scenario is a end-to-end setup required to process transactions and calculate payments. A scenario includes:
Start and End Date
Setup information
One or multiple compensation plans and its sub-elements
Transactional data
You can perform the following operation on a scenario:
Create
Create a duplicate of an existing scenario
Update an existing scenario
Delete an existing scenario
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
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:
Request Name: "<Request Name>:PlansXXX", where XXX is a random number generated by the system.
Operating Unit is the scenario operating unit.
List of plans include all the plans in the scenario.
For more information on exporting compensation plans, see: Creating Export Request.
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.
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
This topic contains four scenarios that a little more complicated than the ones in the previous topic. They include:
Multiple Input Formula
Bonus Compensation
Interdependent Plan Elements
Compensation for Transactions Using Multiple Rate Dimensions
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
Input Expression: Transaction Amount, State Code
Output Expression: Rate Result*Transaction Amount
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.
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:
Input Expression: Aggregated transactions
Output Expression: Rate Table Result
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 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.
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.
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.
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.
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.
Set up these expressions for the first plan element:
Input Expression: Transaction Amount
Output Expression: Rate Result*Transaction Amount
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% |
Set up a second plan element that uses achievement information from the first plan element to calculate a bonus.
Use these expressions for the second plan element:
Input Expression: Aggregated transactions
Output Expression: Rate Table Result
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 |
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.
Rate tables can use four different kinds of dimensions:
Amount: The rate dimension is defined in quantities.
Percent: The rate dimension is defined in percentages.
String: The rate dimension uses alphanumeric data.
Expression: The rate dimension uses previously defined expressions.
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:
Input Expression: Units Sold, State
Output Expression: Rate Table Result
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.
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
Bonus Based on External Data
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:
Input Expression: Transaction Amount * Employee Code Number
Output Expression: Rate Table Result * (Previous Year Sales/Previous Year Goal) * Transaction Amount * Employee Code Number
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
$7,000 * 3 = $21,000
The amount is applied to the rate table
$21,000 is in tier 3, so $21,000 * .03 = $630.
The rate table result is multiplied times the sales/goal ratio (1.0)
$630 * 1.0 = $630
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
$3,000 * 1 = $3,000
The amount is applied to the rate table
$3,000 is in tier 1, so $3,000 * .01 = $30
The rate table result is multiplied times the sales/goal ratio (1.5)
$30 * 1.5 = $45
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
$4,000 * 2 = $8,000
This amount is applied to the rate table
$8,000 is in tier 2, so $8,000 * .02 = $160
The rate table result is multiplied times the sales/goal ratio (.9)
$160 * .9 = $144
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.
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:
Input Expression: Salary Amount
Output Expression: Rate Table Result
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.