Build a Marginal Payout Rule

Learning Objectives

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

• Set up a datasheet for a marginal payout rule.
  
• Create an accumulated variable and describe its function.
  
• Explain how to make calculations dynamic.
  

Set Up a Datasheet

Remember that in a marginal payout rule, only the amounts that fall in each range, or threshold, get the defined rate or dollar amount. So you use the rule when you want to apply different rates or dollar amounts to the amount that falls within specific ranges or thresholds. To build the rule, you need a datasheet and an accumulated variable. The datasheet is based on a range table that has the appropriate tiers and rates according to your commission plan. Here’s an example of a range table that you’ll use.

We strongly recommend building your payout rule as a line-by-line rule to provide better tracing capabilities, reporting, and visibility to your reps.

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

1. Create a folder for the rule, under the plan that this rule will apply to.
   
2. Create a range table in the rule folder you created.
   
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 be visible on your datasheet, or any fields needed to calculate the commission payout, such as ARR.
   

With your datasheet set up, you build the logic for the commission payout. For the marginal payout rule, let’s use the mamount function. Click + to add a new field to the datasheet and name the calculation for your payout, say MarginalCommissionPayout. After giving the field a name, click into a cell and type the mamount function into the function bar.

The syntax is ​​=mamount(ARR, SalesRep_MarginalPayoutTable, PreviouslyEarnedARR, “Rates”).

The initial_value argument in the function—PreviouslyEarnedARR—will be an accumulated variable. You use this variable to allocate new deals into appropriate tiers, based on previously closed amounts. Let’s learn how to create one.

Create an Accumulated Variable

Imagine a rep has already closed $145,000 in ARR and they’ve just closed a new $20,000 deal. The $145,000 of previously closed ARR is the initial_value that informs Spiff where to allocate the new $20,000 deal.

Given the initial_value of $145,000, Spiff allocates $5,000 in ARR to Tier 2 using our sample range table, and $15,000 in ARR to Tier 3. This initial_value of $145,000 is often called an accumulated variable or the accumulation of ARR for the designated period. On your datasheet for the marginal payout rule, create a calculated field called Accumulated_ARR. Enter the variable that needs to be accumulated. In this case, ARR is being accumulated.

In the sidebar, you’ll find an expandable menu called Accumulation. Click to expand it and then enable Accumulated.

You'll find some options for an accumulation filter and an accumulation sort. The accumulation filter is a data filter that has already been created. Often the same data filter being used to build the commission rule will be applied here. For our example we want to accumulate the rep’s ARR for the entire year, before the current period. ‌So you'll create a data filter that is CloseByRepInYear to accumulate the ARR from the beginning of the year.

When you have the appropriate data filter created, enter that as the Accumulation Filter in the sidebar menu. Enter CloseDate as the first Accumulation Sort. This will sort the individual deals or obligations on the accumulation filter. An additional field, such as Id, is required to sort deals that closed on the same day. Once saved, the field shows the accumulation!

Notice that the accumulated ARR field grows throughout the period. The last value is the sum of all the ARR__c‌ values.

There’s just one more calculation to add. The accumulated field is running a total of ARR after each deal is closed. You want to know where a rep stands in their accumulation before the deal is closed. This allocates the current deal into the proper tier for the mamount function. For the PreviouslyEarnedARR variable you entered into the mamount function, subtract the current deal from the accumulation: =Accumulated_ARR - ARR.

Congratulations! You’ve now built a marginal payout rule.

Go for Dynamic Calculations

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

And how do you make your calculations dynamic? For the range table that we used in the marginal payout rule, instead of leaving the return column as “Rate”, you name it the year. Next year, you just add a new column to the existing range table with the name of that year. You don’t have to rebuild everything to reference a new range table with new rates.

Consider the return_column argument in the mamount function. Instead of using Rates, you create a dynamic calculation that returns the current year, which Spiff uses to reference the right return value.

Here’s the updated range table and function.

In this module, you learned how to calculate blended commission rates, explored the mamount and mpercent functions, and built a marginal payout rule. With what you learned here, you have the basic tools to develop ever-more-complex marginal payout rules for your company. Good luck!