Configure Worksheets

Learning Objectives

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

• Define the appropriate scenarios for using worksheets in Spiff.
  
• Construct formulas within a worksheet for specific payout calculations.
  

The Role of Worksheets in Spiff

In Spiff, worksheets play a fundamental role in creating payout rules by providing a foundation of static data that supports dynamic calculations across deals.

Unlike datasheets, which handle dynamic data and change on a deal-by-deal basis, worksheets store fixed values across multiple transactions.

Worksheets act as ‌storage for mostly static variables and logic for payout calculations that don’t change from deal to deal. Think of a worksheet as a reference guide that provides standard constants, such as flat commission rates or dates, that various deals can rely on for consistency. By storing fixed information, worksheets ensure calculations maintain accuracy and reduce the need for repetitive data entry, especially for values that are universal across transactions.

Imagine you’re running a bookstore. Each book has a specific price that doesn’t change for every customer. Rather than recording the price for every individual sale, you can have a Price List worksheet where you list the price for each book once. Every transaction can then reference this price, making it efficient to calculate the total cost for each customer without duplicating information. Similarly, in Spiff, worksheets help to keep static data organized, saving time and ensuring accuracy across multiple deals.

In this unit, you learn the purpose of worksheets, their role in calculations, and best practices for incorporating them effectively into your payout logic.

Worksheet Structure

Worksheets contain a variety of data types, each offering specific utility in static payout calculations. Unlike datasheets, which handle dynamic data that varies with each deal, worksheets mostly hold fixed values.

Explore the different data types that worksheets can hold, along with practical examples for each.

Dates

Worksheets can store specific dates that remain constant across payout calculations, anchoring your calculations in time-based milestones. A common example is storing the beginning of the fiscal year. Regardless of the current month, a worksheet can reference the beginning of the fiscal year and adjust accordingly each year. For example, in 2024, the worksheet references January 1, 2024; the following year, it adjusts to January 1, 2025. This approach ensures calculations are consistent and adapt each year without manual adjustments.

Setting the beginning of the fiscal year to January 1 helps with prorating commission rates, calculating year-to-date achievements, or anchoring quarterly payouts.

Numbers

Numbers are essential for static constants, especially those representing quantities that don’t change. Worksheets can hold values such as the number of months in a year. For example, 12 months. Because these values are constants, you can easily reference them across deals without needing dynamic updates.

A worksheet can also store the standard number of days in a month. By referencing this value, payout calculations can adjust for partial periods based on months or use it for prorating annual targets. For example, you define a worksheet with a month length of 30 days for April. You can now calculate the commission earned in April by using this static month length for consistency.

Currencies

Currencies are another important data type, especially when working with standardized exchange rates or baseline figures for international deals. If your company uses a set exchange rate for commissions paid out across the world, you can save this value in a worksheet to make sure the currency conversion is the same. This setup reduces differences in payouts between different markets.

Booleans

Booleans in worksheets typically serve as simple True or False flags to guide payout rules. These binary values are useful for setting static conditions, such as determining eligibility criteria, or enforcing caps. Because they’re fixed, they allow the worksheet to reference straightforward yes-or-no conditions.

A worksheet can use a Boolean field to indicate if a certain role is eligible for a quarterly bonus. If the Boolean is set to True, payout rules calculate bonuses. If set to False, the rule skips bonus calculation for that role, providing a simple control mechanism.

Text Fields

Text fields in worksheets are helpful for storing static labels or categories that can be referenced across deals. These can include product names, geographic regions, or a rep’s team name that doesn’t change from one deal to the next.

A worksheet can include a text field for team names—such as North America Sales—allowing a text field to always return the rep’s team name. This setup can help to apply specific commission rates or bonuses based on these predefined teams. This setup also ensures consistency across deals associated with each team, keeping calculations efficient and aligned with the team structure.

Set Up a Worksheet

Worksheets are most effective when they hold calculations that you use multiple times across several payout rules. A worksheet must allow multiple payout rules to reference these values. Here’s how to seamlessly integrate worksheets into your calculations.

Create a New Worksheet

You can create a new worksheet under an existing plan or at a top-level view. In this example, you create a worksheet at a higher level because it holds common functions that multiple plans and payout rules can use.

1. Go to Spiff Designer.
   
2. Make sure you select Plans in the header.
   
3. Click next to the search bar to open the menu.
   
4. Select New Worksheet.
   
5. Add a descriptive name for the new worksheet, such as GeneralWorksheet or MainWorksheet.
   

Configure Data Columns in Your Worksheet

Configuring data columns within a worksheet is essential for keeping your data organized and aligned with the payout structures you’re building. By grouping data into logical categories, worksheets become more than just storage. They become a structured framework that provides clarity, reduces errors, and facilitates efficient referencing in payout calculations.

To create these columns, double click the cell, and type the name of the data type or category you wish to include. Here are some examples.

• Dates such as the beginning of fiscal year and month-end dates.
  
• Quotas such as team quotas, quarterly quotas, and flat bonuses.
  
• Tables such as vlookup tables that hold rates for teams or tier payout tables that pay out different rates depending on attainment.
  
• Booleans such as RepIsNorthAmericaTeam and QuotaIsAbove300k.
  

Define Static Data in Your Worksheet

When you set up a worksheet with columns to help organize where you put your variables, begin by adding fields that store these fixed calculations. These values can include standard time frames, currency exchange rates, or base commission rates.

You can use formulas in these values, such as the fiscal year start date. Double click the cell and type =beginning_of_fiscal_year(BeginningOfPeriod. This returns the beginning of your fiscal year.

Assign and Review Your Worksheet

After configuring your columns, you need special syntax to reference these values in your payout rules. For example, if a payout calculation depends on a flat bonus rate stored in the worksheet, you can call that field using the syntax =[WorksheetName]![FieldName].

Wrap Up

In this module, you discovered that datasheets store data calculated on a line-by-line basis, whereas worksheets store static data. You also learned how to create both datasheets and worksheets. With this knowledge, you can now generate as many calculations as needed for your specific payout plans. Organizing and using datasheets and worksheets in Spiff provide an excellent way to keep payout rule components centralized for you and other admins, making it easier to maintain your plans.