This is some text inside of a div block.

Template: Team Commission Tracking in Google Sheets

Save time with our easy-to-use template which will help you manage commissions for all your sales reps in one workbook. Download the template today and follow the steps below for a smooth process.

Get a copy now
Template: Team Commission Tracking in Google Sheets

How to use the template workbook?

This template comes with sample data, so that you can immediately test it out and learn how to adapt it to your needs. The workbook logic is as simple as possible, while demonstrating some key principles of commission management. Once you're comfortable with how the workbook functions, you should then customize it to better match the requirements of commission management at your organization.

In summary, to configure the workbook for use with your team:

  1. Insert the sales data
  2. Configure commission plan sheets
  3. Make a copy of the commission plan sheets for each rep
  4. Share a commission statement via the “IMPORTRANGE()” function

Below we elaborate on this process in more detail.

1. Insert the sales data

1. Open the “Sales Data” sheet.

2. Copy the data from your CRM system that is required for your commission calculations and paste it into this sheet.

      2.1 Columns A-F must contain the same data as in the test data:

  • Column A: Deal ID
  • Column B: Deal Name
  • Column C: Deal Type (either "New Business" or "Existing Business")
  • Column D: Deal closing date
  • Column E: The size of the deal
  • Column F: The sales rep who "owns" the deal

       If you want to change the contents of these columns, you'll need to make corresponding changes to the other sheets in the workbook.

      2.2 The column titles (row 1) can be modified.

      2.3 You can add new columns without issue after column F.

2. Configure commission plan sheet

You can choose to build your own commission plan sheet or use one of the two templates: Relative Commission Plan (RCP) and Straight Commission Plan (SCP). 

To configure the Relative Commission Plan:

1. Open the “Relative Commission Plan (RCP)” sheet.

2. Enter the sales rep's name (C5). Note, it must match the Deal owner in 'Sales Data'!F.

3. Specify the parameters:

  • Quota (C6):  this is a yearly sales target.
  • On target commission (C7): this is the amount that would be paid upon achieving 100% of the Quota.

4. Specify the payout starting month (C12)

The data specific to the rep should now be visible, and the commissions should be automatically calculated.

5. Review the calculations.

To configure the Straight Commission Plan:

1. Open the “Straight Commission Plan (SCP)” sheet.

2. Specify the sales rep's name (C5). Note, it must match the Deal owner in 'Sales Data'!F.

Specify the parameters:

  • Commission rate - new business (C6):  this is the commission % for deals of type "New Business"  ('Sales Data'!C)
  • Commission rate - existing business (C7): this is the commission % for deals of type "Existing Business" ('Sales Data'!C)

4. Specify the payout starting month (C11).

The data specific to the rep should now be visible, and the commissions should be automatically calculated.

5. Review the calculations.

3. Make a copy of the commission plan sheets for each rep

1. Duplicate the relevant commission plan sheet to create an individual results sheet for every sales rep.

2. Follow the steps in “2. Configure Commission Plan sheet” for every individual results sheet.

4. Share a commission statement via the “IMPORTRANGE()” function

You should now have a single workbook, containing a commission statement for each sales rep. But you have a problem: how can you share with each rep their specific commission statement? If you were to simply grant a rep access to the entire workbook, then that rep could see every other rep's statement. This might not be acceptable.

To address this, you can provide to each rep a read-only copy of their (and only their) commission statement as follows:

1. Create a new workbook for each sales rep

2. In cell Sheet1!A1, use the IMPORTRANGE() function to retrieve the commission statement for a given rep. Eg:

  • =IMPORTRANGE("<Link to commission Sheet>","Sales Rep 2!A:R")

3. Grant the rep "Commenter" access to the workbook. It is important to limit the rep's access to "Commenter" so that they are unable to modify the IMPORTRANGE() function and access other sales data in the source workbook.

The rep now has live access to their commissions, and only their commissions.

Get a demo now

Sign up for our newsletter

Want to stay in the loop and learn more about our next-generation commission management platform?

Consent to communications according to the terms of our privacy policy.
Other recommended resources