Category: Salesforce Automation

Introduction

Our client sells trrvel and concierge services to consumers. They needed a solution to automatically calculate the sales commission for each of their commission earners.

They have target based sales commission and bonus programmes that are applied to different roles in the company, e.g. Sales Reps, Deal Managers, Territory managers, Marketing Managers, Collections managers, Training Managers.

Sales commissions and any bonus amounts are paid every 2 weeks according to the sales performance exceeding the bonus criteria within a specified 4 week Bonus Calendar period.

We developed the solution using Process builder and headless Visualforce Flows. The flows we created included the following functionality.

• Identify records that satisfy OR logic filter criteria
• Count the number of different reps who won deals in a period
• Evaluate bonus eligibility for each deal compared with deals won in a specified period
• Create Commission Payment records and statements for each eligible commission earner every 2 weeks

This note describes the design of the application.

Commission Scheme Overview

Deals are won and lost by Reps and Deal Managers in separate Sales locations which are supervised by Territory Managers (SSM’s)

When a deal is won by the sales team, the deal is deemed ‘Accepted’ on the date that the contract is signed by the client. An ‘Accepted ‘or won deal counts towards the bonus target.

However the deal is only eligible for commission when the full payment for the contract has been received. At this point the deal is deemed to be ‘Completed’

Commission is only paid on deals that have been completed. If a deal is completed before the next Cut off Date , then the commission will be paid out in the next fortnightly payment run, the next working day following the Cut Off date .

Some commission earners are entitled to a Bonus commission.

The company operates a Target Bonus calendar of 4 – week periods throughout the year. For example:

Deal dates                                     Cut Off Date

04/01/2016        – 31/12/2016         03/02/2016 

01/02/2016        – 28/02/2016        02/03/2016

29/02/2016        – 27/03/2016         30/03/2016

etc

A sales rep will receive a bonus commission on a deal if they won more than N deals within the Deal Date four week period that the deal was Accepted.

e.g if the rep signed a deal as Accepted on 07/02/2016 and this deal was fully paid for on 29/03/2016, then if the rep won N deals between 01/02/2016 – 28/02/2016, he would receive a commission and bonus commission during the commission Payable Period commencing 31/03/2016.

A more complicated criterion applies for a Deal manager who supervises the sale of the rep.

A Deal Manager is entitled to bonus commission on a new deal if he/ she has supervised more than X deals from at least 3 separate Reps within the Deal dates that include the Accepted date of the new deal.

The names of the Rep and the Deal Manager are logged on each contract, but other commission earners are not.

A Territory manager is entitled to a bonus on the new deal if there are more than Y deals won in his / her Sales locations within the Deal Accepted dates window.

Other commission earners only receive a basic commission percent and are not entitled to a bonus commission.

Objects to Enable Commission Automation Solution

Custom Setting: Bonus Target Dates.

This is a public list custom setting that defines the Cut off date for each ‘From – Accepted Date’  to ‘To – Accepted date’ period for each calendar year.

Commission Account:

This record identifies

• the name of the commission earner, employment, contractor status and role
• the invoice address of his/ her account,
• the IGIC/ Tax rate to be applied to the invoice
• The commission / bonus rates that this earner is entitled to and any bonus target criteria that must be met.

Commission Payable Period:

• This record is a child of each Commission account record.
• It defines the period of deals that are eligible for payment up to the Cut off date .
• The record also calculates the sum of all commissions and bonuses due for each deal that was completed prior to the end of the cut off date.
• The record sums the deductions that should be applied for this period and calculates the net amount to be paid to the commission earner.

Commission Payable:

A Commission Payable record is automatically created each time a Subscriber contract is completed and marked as ‘eligible for commission ‘ by populating the field Commission Eligible Date.

This record contains the following information;

• Commission Type: e.g Rep Deal, TOM Deal (Deal manager), SSM Deal (Territory manager) , . These values determine how the commission and bonus due is calculated.
• Subscriber Contract.
• Commission Opportunity
• Rep Name, TOM Name
• Deal Gross value, Deal Nett value, Balance Amount
• Deal Commission value ( e.g: Deal Nett value x commission percent for this commission earner)
• Target dates: these are the start and end dates of the commission period for this deal whose Accepted date is within these dates.
• Deal written. : The number of deals written by this commission earner or manager within these target dates
• Bonus Percent: The percentage that should be applied if the bonus criteria has been met or exceeded
• Bonus Value: Deal net Value x Bonus percent
• Spiff Commission; A place to record a discretionary value for a Spiff deal payment.

The workflow – VisualFlow solution

Process Builder Workflow

A master headless flow and a series of subflows are launched by a Process Builder workflow ‘Trigger flow to Create Commission Payment records’

This workflow launches a headless flow when the field ‘Commission Eligible Date’ is entered into the ‘Subscriber Contract’ object record.

[fusion_builder_container hundred_percent=”yes” overflow=”visible”][fusion_builder_row][fusion_builder_column type=”1_1″ background_position=”left top” background_color=”” border_size=”” border_color=”” border_style=”solid” spacing=”yes” background_image=”” background_repeat=”no-repeat” padding=”” margin_top=”0px” margin_bottom=”0px” class=”” id=”” animation_type=”” animation_speed=”0.3″ animation_direction=”left” hide_on_mobile=”no” center_content=”no” min_height=”none”]

Commission workflow trigger for headless flow

The Master Headless, Autolaunched Flow

A high level overview of the Master and subflows is illustrated below:

The master flow and subsequent sub flows all start by identifying the Subscriber Contract or Deal record that is eligible for commission and assigning SObject variables to each of the fields that will used later in the flow.

The master flow and sub flow elements are shown below: Elements 1-15 are used to execute Box 1 and 2 of the high level overview

Elements 16,17,18 are the subflows in boxes 3,4,5 of the high level overview.

Flow design:  Create Rep Commission record

1. The FLuDSR_Deals Fast lookup looks up all subscriber contract records where accepted date is within bonus target dates.
2. Record Create: Create New Commission Payable Record for a rep. This element populates a Commission payable record (as a child of the Commission Payable Period record created in the prior flow element or found in element 6) with commission value earned by the rep for a deal. The record will be updated with the Bonus commission value in step 15.The commission value payable to a rep is calculated using a Formula {!RepCommPayValue}. This applies the commission percent that the rep is entitled to (specified on the reps Commission account record) for this type of deal, to the Deal Nett value.Record Create: New Commission Payable record for a RepThis flow element contains two formulas to populate field values on the new record.The field Cut Off date needs to be populated with the next Cutoff date that immediately follows the Commission Eligible Date of the Subscriber Contract record that triggered this flow.
   
   The run/ Cutoff dates are every other Friday commencing the First cut off / Run date for a calendar year. This formula finds the remainder days in a week when dividing the (Commission Eligible Date – Start date of the Run Cut off custom setting) by 14. If the remainder is 0, (i.e the commission eligible date is Friday)  then the Run CutOff date = Commission Eligible date. If the remainder is > 0 then the Run CutOff Date = the next alternate Friday  (Commission Eligible Date +14-Remainder)
3. Decsion: Does an existing Commission Payable Period record exist for this rep?This element checks to see if the Id of the Commission Payable period is null or not null. If Null the next step is to create a new Commission Payable Period record for the Rep.
   
4. Record Lookup: Commission Payable PeriodThis looks up an existing Commission Payable Period record for a rep if the Cut off date is greater or equal to the eligible date.
   
5. Record Lookup: Subscriber Opportunity
   This record is a parent of the Subscriber contract and contains the names of the Rep and Deal Manager (TOM)
6. Record Lookup: Commission Account
   This finds the Commission Account record for the Rep who is eligible to receive commission on the deal. It sets variables for commission and bonus rates and the bonus target criteria.
7. Record lookup; Public List Custom Setting. Run/Cut Off Calendar DatesThis custom setting defines the first and last Cut off dates of each calendar year. This data is required to work out the next Payment run / cut off date compared to the Commission eligible date of the subscriber contract that triggered the flow. Note that the field filter {!CommEligYear} is a formula that converts the Commission Eligible date to a Year value
   
8. Record lookup: Subscriber Contract record
   This element looks up the subscriber contract record ID passed to the flow by the Process workflow.
   It sets the SObj variables for each field that will required in this flow.
9. Record Lookup: Bonus Target Dates
   This looks up the Custom Setting public list that defines the start and end dates of the bonus target periods.
   It identifies the record where the Accepted date of the subscriber contract lies within one of the calendar bonus periods.
   It sets variables for the CutOff date for that period and the Start and end dates.
10. The loop through the collection of FLuDSR deal records, checks to see if the rep name is the same as the Commission Earner account name who won the contract that triggered this flow. If the rep names match then, a count of these records is assigned  to the variable {!VarCountRepWrittenDeal}.
    (Note that if  the Subscriber Contract included  the Rep name field value this loop process could have been simplified.)
11. This element finds the rep name of each loop record
    
12. This decision element checks a match the  the rep name
13. This assignment element adds a count of 1 to the variable {!VarCountRepWrittenDeal}.
    
14. Record update: Commission Payable record
    This element updates the Commission element record with the bonus percentage entitlement  and calculated bonus value using formulas.
    

    Deal Manager Commission subflow

15. This subflow is similar to the Master flow that calculates the commission for a rep.It has a more complex Loop to calculate to check the bonus criteria of n deals from 3 different reps in a period.
    a. The fast lookup creates a collection of records where the Deal Manager (TOM) has approved a deal which was signed within the target bonus period.
    As a lookup does not support a cross object field filter value, The collection of records is listed by a custom field that contains the Rep Id on the Subscriber record.b. The Loop element is straightforward
    c. The Record lookup of the loop record identifies the Id of the rep so it can be compared with the variable {!PriorRepId} which has a null initial value in the following decision element.
    d. The assignment element counts the no of deals approved by the deal manager in the loop .e. Decision element compares the loop variable of the Rep ID with the variable {!PriorRepId} which initially has a null value.
    Hence the first record  will have a decision  that the Rep Id does not match the prior value.
    This record will be counted as a deal record with a unique Rep ID.
    f. This assignment element updates the variable {!PriorRepId} with the Rep Id of the current Loop record
    g. This  assignment record counts the number of records with a unique Rep IDh. The record update calculates the Deal manager bonus for the commission payable record and records the no of deals signed in the target bonus period

16. Territory Manager (SSM) Commission workflow

    

    The initial elements of this subflow are similar to the Master flow that calculates the commission for a rep.

    a.The Record lookup element needs to find the Commission account for the SSM. The SSM for this deal is determined by the Sales location of the deal.
    The Lookup element only supports AND filter logic which prevents the filtering such as

    

    Note that since this work was completed , users have discovered undocumented existence of OR logic filters. Please see this post: https://explorationsintosalesforce.wordpress.com/2015/11/22/hidden-logic-in-visual-flow-lookup-filters/
    In this case we have used a formula to find the Id of the SSM

    b. This subflow contains a loop that counts the deals in the Fast Lookup collection of records which are under the jurisdiction of the SSM

    

    c. Assignment: Add One to record count

    

    d. Update SMM Commission Payable record
    At the end of the loop this element calculates the bonus entitlement and commission value for the Commission payable record

    

    Compliance Commission (excluding bonus) subflow

    

17. A standard flat rate commission is also paid to administration staff associated with the deal.
    These staff do not receive a bonus percentage related to a target period for signing deals.a. The Fast Lookup in this subflow identifies the Commission Account records for each admin staff user who is entitled to commission.b.  This record lookup finds any existing Commission Payable periods which have a cut off date later than the Commission eligible date.c. This record Create element creates a new Commission Payable record if none were found in the prior step.d. This record Create element created the Commission Payable record for the commission earner account in the loopEND OF DOCUMENT

[/fusion_builder_column][/fusion_builder_row][/fusion_builder_container]