In the Report Center, you can build a Custom Analysis that will tell you how much commission your Sales Reps have earned based on the gross profit amount from won Estimates. For this analysis to work, you will need to be using Estimates in Service Autopilot, have a Sales Rep assigned to the Estimates on which you'd like to track commission, and a consistent use of statuses on your Estimates. Please note that this basic Formula will apply the same commission rate to each Sales Rep in the table. For additional options on how to handle multiple commission rates, see the end of this article.
Create the Data Set
Before you can begin using Formulas, you will first need to build a data set for your new analysis. For the purpose are this commission analysis, here is the minimum recommended data points:
The data points selected are Estimate Date, Estimate Description, Estimate Stage, Gross Profit, Sales Rep, and Client Name. Additionally, you may want to re-order the columns, filter by Estimate Stage, and Group the Sales Rep column as shown below:
Add the Commission Calculation
To add the commission calculation, follow these steps:
- Click the Formula tab.
- Enter a Name for the Formula, such as "Commission Amount".
- From the Insert a Column dropdown list, select "Gross Profit". This will add it to the Formula field in the proper code necessary for the Formula to work.
- From the Operator dropdown list, select "*" for multiplication or type an asterisk in the Formula field directly after the close bracket.
- In the Formula field after the asterisk, type the commission rate as a decimal. If you give 3% commission, you would type .03.
- For Data Type, select "Number".
- For Display Format, you will want to select a dollar value such as "$#,##0.00".
- Click Add. This will add a new column to your table with the name of the Formula as the column header.
Once you have added the Formula, you can Aggregate the new column for a sum to tell you the total commission amount. If your table is grouped by Sales Rep there will be a total for each Sales Rep as well if you aggregate the new column.
This analysis will show you the commission rate for all Sales Reps at the same rate. If your Sales Reps are paid different commission rates, you have two options. You can either build multiple analyses and filter each one for the Sales Reps who are paid the same rate or create a Case Function Formula that will allow you more flexibility.