Build a Tier Payout Rule

Learning Objectives

After completing this unit, you’ll be able to:

• Set up a datasheet and worksheet in Spiff Designer.
  
• Build a tier payout formula.
  
• Explain how dynamic calculations are used in a rule.
  

Set Up a Datasheet and Worksheet

With the range table set up, you can build your tier payout rule. To build a tier payout rule, you define the tiers, and create a datasheet, worksheet, and tier payout formula.

Let’s use the example tiers from the last unit for this payout rule. Here’s the table again for your reference.

Tier payout rules can be line-by-line or lump-sum rules, depending on your requirements. We use a line-by-line rule for this module. Why? You can pull more data into your reports, and get better tracing capabilities for reps.

Here’s an overview of the steps to set up the datasheet and worksheet in Spiff Designer.

1. Create a folder for the rule, under the plan that this rule will apply to.
   
2. Move the range table we created in the last unit into the rule folder, or create a new range table.
   
3. Fill out the table according to your rule parameters.
   
4. Create a datasheet in the rule folder and select the object and data filter the datasheet will use.
   
5. Add any fields you want to show on your datasheet, or any fields needed to calculate the commission payout, such as ARR.
   
6. Create a worksheet in the rule folder.
   

Create a Tier Payout Formula

When your datasheet and worksheet are ready to go, you can begin building the logic for the commission payout. ‌On the worksheet, create a sum calculation to get the total ARR for the period with this syntax: =sum(DatasheetName!FieldNameYouWantToSum).

As an example, consider =sum(ClosedInPeriod!ARR__c), where ClosedInPeriod is the datasheet and ARR__c is the field name.

Remember to update the name of the calculation field on the worksheet and the Display Name in the right sidebar. Select the appropriate display format for the field; for example, Currency.

Here are sample names.

• Summed ARR calculation: TotalARRInPeriod
  
• Worksheet: TierPayoutRule_Worksheet
  
• Range Table: TierPayoutAcceleratorTable
  
• Return Value Column Name (on Range Table): Rate
  

Going back to the datasheet, put this sum calculation into a new field using the tier_payout function. Add a new field on your datasheet by clicking the + sign and giving the field a name.

Click into a cell and type the tier_payout function into the function bar. For the tiebreak argument, true returns the lower tier rate while false returns the higher rate. Depending on your commission payout rules, you can indicate how you would like Spiff to handle this in the logic. If you don’t put in a tiebreak argument, Spiff will automatically default to true—meaning the lower range is used. Set it to false to break upward.

Let’s put all of this together. The complete formula with these arguments is =tier_payout(TierPayourRule_Worksheet!TotalARRInPeriod, TierPayoutAcceleratorTable, false, “Rate”).

To finish building the payout rule, put this tier payout formula together with the ARR.

Congratulations! You’ve built a tier payout rule.

Consider Dynamic Calculations

What's a dynamic calculation? One that constantly updates based on the changing inputs required for the calculation. For a tier payout rule, it’s best practice to build the range table and the tier_payout calculation dynamically. Why? You minimize changes needed in the future to your built logic.

Here’s how.

For the range table in the simple tier payout rule you just built, instead of leaving the return column as Rate, you can name it for the current year. ‌Next year, instead of rebuilding everything to reference a new range table with new rates, you only add a new column to the existing range table with that year’s name. So in the tier_payout function, for the [return column] argument, you create a dynamic calculation that returns the current year, which Spiff uses to reference the proper return value.

Once completed, the range table shows each tier with the lower and upper bounds established by your compensation plan, and the rate that's applied to the deal.

Now that you’ve built the rule dynamically, you’re ready to set up metrics in the next unit.