Track Commission for a Team using Google Sheets

Learn how to manage commissions for a team of individuals using a single Google Sheets workbook.

10/6/2022

Share

Share via LinkedInShare via FacebookShare via Twitter
Dmitri Kemenev
Dmitri Kemenev

Template: Commission plan for sales rep team

Download template
Track Commission for a Team using Google Sheets

Keeping track of sales commissions for multiple people

Commission management needs a good tracking system in order to work effectively. 

The commission structure you choose depends on the complexity and the volume of your commissions. You may choose to use a commission automation platform, or, if you have a smaller team, Google Sheets can also work well. Regardless of the solution you implement, there are some best practices you should adhere to:

  • Provide a single "source of truth" for your commission calculations.
  • Define a robust process for modifying and deploying changes to your incentive plans.
  • Transparent commission calculations for sales reps, so that they can try to answer questions about their earnings themselves.
  • Ensure you have a change log to support compliance and auditability.

In this article I will share an example of how to effectively track commissions for multiple  in Google Sheets when you have more than one sales rep.

Excel or Google Sheets for commissions?

I recommend Google Sheets over Excel for tracking sales commissions:

1. Better version control and traceability

In Google Sheets, you can review all changes made to your workbook:

Therefore, in the case of an error, you can always roll back to the correct version. You can also see who made a change, when the change was made, and to which specific cell. This is critical for tracing back any issues and fixing them quickly, as mistakes are inevitable in a spreadsheet-based commission workflow. 

2. More data source connection options

Google Sheets have a better range of options for connecting to external data sources, such as your CRM system. Getting data into a spreadsheet automatically eliminates the chance of manual entry errors and frees up time.

3. Robust linking between documents

When you calculate commission for multiple reps in one spreadsheet, you can't share the whole spreadsheet for confidentiality reasons. However, you can automatically send individual results to separate spreadsheets.

To share the results with each individual sales rep, linking via the IMPORTRANGE function in Google Sheets comes very handy. Note that you should only grant "Viewer" or "Commenter" access to the sales rep, to prevent them from modifying the IMPORTRANGE function to view the rest of the source workbook.

The IMPORTRANGE function allows you to import data from a specific sheet to a separate spreadsheet.

Compared to Excel, this linking functionality works seamlessly in Google Sheets. The linking doesn’t break once files are renamed or moved to a different location. 

4. More convenient sharing and collaboration

You can let multiple people to view, edit or comment in a Google Sheet spreadsheet at the same time. It is likely there are several people involved in your commission calculations, therefore this functionality becomes crucial for a robust and efficient workflow.

Furthermore, Google Sheets provides more convenient access control than Excel, facilitating collaboration across teams and functions.

How to set up commission tracking for a team in a single workbook?

I recommend the following high-level structure for managing commissions for a team in a single workbook:

  1. Sales Data sheet: contains the raw sales data for all team members, for which commissions will be calculated
  2. Commission Plan sheets: one sheet per team member, where each sheet imports data from the Sales Data sheet using the FILTER function.
  3. Sharing results with reps: you can securely share results with reps by granging "Commenting" access to a workbook, unique to each rep, where you use IMPORTRANGE to pull results from the above workbook.

STEP 1 - Create the Sales Data sheet

The first thing that you need to start with is your data.

Create a sheet that will contain all the sales information required for your commission calculations.

Then, you need to determine how you get your data. Your options include:

  • manually enter your sales data, or
  • export it from your CRM and import it into Google Sheets, or
  • automatically send the data to Google Sheets using a tool like Zapier or HubSpot workflows.

STEP 2 - Create your first commission plan sheet

Create a new sheet to implement your commission plan:

I'd recommend you highlight in blue the fields that can be modified as part of plan configuration. Some key fields:
  • Sales Rep name: to retrieve the sales data for the rep
  • Bonus start mo: starting month for the commission calculation
  • Other commission plan parameters, such as "target" or "on target bonus".

This workbook should use a FILTER formula to retrieve the sales data for the rep for whom we're calculating commissions. 

For instance, if the rep's name is stored column F of the Sales Data sheet, and the commission plan sheet stores the current rep's name in cell C4, then this formula can be placed to import the sales sold by the current rep:

=FILTER('Sales Data'!A:H,'Sales Data'!F:F=C4)

STEP 3 - Make a copy of the commission plan sheet for each rep

Now, duplicate the relevant commission plan sheet to create an individual results sheet for every sales rep. Their data should be automatically visible when you specify their Sales Rep name, and the results should be automatically calculated as per your plan logic.

STEP 4 - Share the results via the “IMPORTRANGE()” function

Sales commission transparency is important to your team. However, unless you only have one sales rep, you can’t share the whole workbook with everyone. 

Nevertheless, if you want your sales reps to see their payment breakdown, there is a simple way of doing that:

  1. Create a new workbook for each sales rep
  2. Use IMPORTRANGE to display in that workbook their commission data
  3. Grant the rep "Commenter" access to the workbook

The rep now has live access to their commissions, but not their colleagues.

Google Sheets Template

Need a quick way to jump-start your commission process with Google Sheets? Use our template to set up and run your commission management today.

How Sales Commission Software can Help Scale your Process

Google Sheets can effectively help manage a small team’s sales commissions; but when scaling, it isn’t enough. If you find that your spreadsheet process is getting tiresome to manage, is causing mistakes, and lacks clarity for your team, I recommend trying a sales commission software such as EqualTo.

EqualTo is the true no-code commission platform that lets you automate, control and scale your commission process. Get a demo now

Get a demo now

Template: Commission plan for sales rep team

Download template
Other recommended posts