How to Create a Sales Commission Calculator

Sales commissions are a key payment stream for sales reps. Commissions incentivize sales reps to perform at a superior level, lead to more closed deals, and generate higher revenue for the company.

However, calculating sales commissions is often a challenge for sales orgs. Although the process seems straightforward, there are many roadblocks to creating a flexible, transparent sales commission calculator.

Since our customers routinely face this problem, we’ve designed our own sales commission calculator for Google Sheets. And now we’re sharing it with you!  

The following blog will show you how to build our sales commission calculator in Google Sheets.  

Alternatively, you can simply download our free template for the sales commission calculator to skip the building part.  

Important note: the sales commission calculator is based on your Salesforce CRM data.

Now let’s get started!

Sales Commission Calculator: Powered by Live Salesforce Data from Coefficient

Building a sales commission calculator is simple if you follow the steps in this guide. But the calculator isn’t helpful if you must copy-and-paste new Salesforce data to update the commissions every day.

What you actually need is live data — data that’s synced with your Salesforce CRM — so your sales commission calculator always stays up to date.

That’s where Coefficient comes in. Coefficient enables you to import live Salesforce data into Google Sheets on an automatic schedule. That way, the sales commission calculator is refreshed daily.

Coefficient empowers you to pull data from any company system — such as HubSpot, Tableau, and Snowflake — into Google Sheets and automatically update the data.

With fresh data from Coefficient, you can make daily calculations as to how much you owe salespeople, and share the calculator among your team at all times.

Here’s how you can combine Google Sheets and Coefficient to build a sales commission calculator that updates automatically.

How to Build a Google Sheets Sales Commission Calculator

This example below focuses on the tiered commission structure. But it’s worth noting that Coefficeint offers a range of pre-built (and free) templates and dashboards for different types of sales commission structures like:

• Base Pay + Commission Calculator
• Territory Volume Commission Calculator
• Revenue Commission Calculator

Before you undertake any of the steps in this guide, you must install Coefficient.

Our blog on how to connect Salesforce to Google Sheets offers a full walkthrough of both the install process and pulling Salesforce data into Sheets.

Once you install Coefficient, follow the steps below to build the sales commissions calculator (or copy our free template).

First, open a new spreadsheet and create a set of tables similar to the ones below. They will contain the following columns:

Table 1: Quarterly Sales Commission Rates

• Tier
• Start Tier
• End Tier
• Commission

Table 2: Sales Commission – To Be Paid

• Sale Rep
• Q1 2022
• Q2 2022
• Q3 2022
• Q4 2022

Once you have your tables set up, launch the Coefficient app. Go to Extensions -> Coefficient -> Launch.

Once the Coefficient sidebar launches, choose Import from…

Then select Salesforce as your data source:

Choose From Objects & Fields.

Under New Import, choose the Opportunity object.

Click ‘Next’. Then choose Select fields… 

Add the “Full Name” field.

Add the “Amount” field.

Finally, add the “Close Date” field.

Then click “Done Selecting Fields”.

You will return to the New Import screen. Click “Add filter” to add a filter to your import.

Select the “Opportunity Stage” filter.

Under the Picklist section, choose is one of.

Then choose the Closed Won stage.

Make sure to toggle on Pivot Mode.

Now drag Full Name to Rows.

Drag Close Date to Columns.

Lastly, drag Amount to Values.

Under Columns, choose Group by: Quarter.

Under Amount, choose count: sum.

Now name your import. Then click ‘Import’.

After you finish your import, fill in your Quarterly Sales Commission Rates table with your desired commission structure.

Now navigate to the Q1 2022 cell under Michael Brown (cell C12).

Copy and paste the following formula into cell C12:

=xlookup($B12,’Salesforce Import’!$A$4:$A$7,’Salesforce Import’!B$4:B$7)*vlookup(xlookup($B12,’Salesforce Import’!$A$4:$A$7,’Salesforce Import’!B$4:B$7),$C$6:$E$8,3,true)

Note: Replace ‘Salesforce Import’ with the name of your Coefficient import in the above formula.

Otherwise, it won’t work.

The formula will calculate the Q1 2022 sales commission for Michael Brown.

Now drag the formula across the rest of the table. This will give you the sales commission calculations for all of your sales reps across Q1, Q2, Q3, Q4.

As a last step, set up automatic data refreshes.

This ensures that the Salesforce data in your spreadsheet will update on a daily basis. Q2, Q3, Q4 will gradually fill in as new deals come in throughout the year.

To set up automatic refreshes, open your Salesforce import on the Coefficient sidebar. Select ‘Edit’ on the dropdown menu.

Enable the Refresh Schedule.

Configure the Refresh Schedule: Daily at 7am.

The Salesforce data will update every day at 7am. At the same time, the sales commission calculator will recalculate how much you owe each sales rep.

This makes paying out commissions easy and straightforward. As the year goes on, your sales commissions will fill in for each quarter automatically.

Let’s continue on to discuss other examples of sales commission calculators.

 

Base Pay + Commission Calculator

The base pay plus commission structure combines a fixed salary with commission based on sales performance.

It offers salespeople financial stability with the base pay and incentivizes higher sales with commission.

How to calculate:

Base Pay + (Total Sales * Commission Rate)

Example: 

A salesperson makes a base salary of $3,000 a month and has a 5% commission rate. If they sold $20,000 in a given month, their commission for the month would be $1,000. 

Territory Volume Commission Calculator

In this model, salespeople earn commissions based on total sales within their assigned territory.

Companies can use this structure to maximize sales in a specific region by creating healthy competition among territories. 

How to calculate:

Total Sales in Territory * Commission Rate

Example: 

A salesperson earns a 5% commission on all sales made within their designated territory. If the total sales in their territory for a month amount to $100,000, their commission would be $5,000 (5% of $100,000).

Revenue Commission Calculator

Common in B2B sales, revenue-based commission awards salespeople a percentage of the revenue from their sales. It’s a direct share of the company’s earnings from a sale. 

How to calculate:

Total Sales * Commission Rate

Example: 

Imagine a salesperson earns a 5% commission on the total revenue generated from their sales. If they make $50,000 in sales, their commission would be $2,500 (5% of $50,000).

Automate Sales Commissions: Combine Spreadsheets with Live Salesforce Data

With Coefficient, you can import real-time Salesforce data into Google Sheets, and build a sales commission calculator that updates automatically.

Follow the steps in this guide to build a Google Sheets sales commission calculator. Or download our free template to access the sales commission calculator, already built for you. Love this resource? Want more? Coefficient offers libraries of advanced reporting dashboards and simple spreadsheet templates for you to sift through as well.

Get started for free with Coefficient so you can create a flexible sales commission calculator with fresh Salesforce data.