What is a sales commission report?
A sales commission report is a document (generally on Excel or any reporting tool) that tracks and summarizes the earnings of sales professionals based on the commissions they generate.
It serves as a critical tool for sales teams and management to monitor performance, ensuring transparency in earnings and providing insights into sales trends.
How to create a sales commission report?
The most common tool to create sales commission report is via Excel or Google Sheets. It is free with good capabilities to create a report.
Using spreadsheet for commission calculation
If you are already using Google Sheet or Excel for commission calculation follow these steps:
In this case your Excel or Google Sheet will already have all the data. You only need to add filters and create charts to get reports (explained below).
Using any other platform for commission calculation
Check if your platform has in-built abilities to create report. If not you can create a report using the data from the third-party platform on spreadsheet.
1. Collect data or integrate with your data source
- Sales Data: Gather information on sales amounts, dates, and salesperson details. Integrate with your data source if the data is elsewhere.
- Commission Structure: Ensure you have the commission rates and any applicable adjustments or deductions.
2. Choose a reporting tool
- Spreadsheet: Google Sheets or Excel are commonly used.
- Reporting software: Consider using specialized software if available.
3. Create the report structure (object level)
Sales person might be in multiple plans and will close multiple deals. You have to add commission for every sale and assign it to a sales rep. Then you can check all the commissions earned by a sales rep.
- Header: Include the commission period (e.g., "Q2 2024"), and any relevant details.
- Columns: Typical columns include:
- Date: When the sale occurred.
- Deal value: How much revenue the deal brought
- Deal ID: Unique ID of the deal
- Salesperson Name: The person responsible for the sale.
- Sales Amount: Total value of the sales.
- Commission Rate (%): Percentage used to calculate the commission.
- Commission Earned: Calculated commission before adjustments.
- Adjustments/Deductions: Any changes to the commission amount.
- Total Commission: Final amount after adjustments.
- Manager: Add the reporting manager to the salesperson
- Other: Clawback/roll up/percentage split between two reps and other data that you want
4. Enter and format data
- Data entry: Input sales and commission data into the columns. You can also integrate to populate the data.
- Format: Keep all the necessary fields and adjust the column width and borders for a presentable appearance
5. Review and validate
- Accuracy Check: Verify calculations and data entry.
- Consistency: Ensure the report format is consistent throughout.
You can the same filter and types from the following section to create reports.
Filters to use for sales commission reports
Here are the common filters you can use. You can customize and use anything based on your data.
- Date range:
- Filter by date: To focus on specific periods, such as monthly or quarterly.
- Example: Filter to show only Q2 2024 data.
- Salesperson/manager
- Filter by name: To analyze individual performance or how much a team has informed.
- Example: View commissions for Alice Johnson only.
- Commission rate:
- Filter by rate: To review commissions based on different rates.
- Example: See commissions where the rate is greater than 10%.
- Sales amount:
- Filter by amount: To focus on high-value or low-value sales.
- Example: Filter to show only sales above $5,000.
- Adjustments/deductions:
- Filter by type: To examine the impact of adjustments or deductions.
- Example: Show entries with deductions greater than $20.
Types of reports you can create
Define the goal or problem statement before creating a report. This helps you in deciding what type of report you want. Here are some common ones:
- Commission earned per user: Total commission earned by each
- Total sales and commission: Sum of sales and total commission earned.
- Commission vs. target: Compare actual commission earned against sales targets.
- Top performers: Identify top salespeople based on total commission earned.
- Commission adjustments impact: Assess the effect of adjustments or deductions on total commissions.
- Sales by commission rate: Analyze sales and commission distribution by commission rate.
Automate commission calculation and also get highly customizable and in-depth reports in one platform
With ElevateHQ, the scope is vast with reports. With our platform, you can automate commission calculation. You can create plans, integrate with your CRM and other data sources, write the commission logic and formula to run calculation.
Since you will manage commission calculation completely on the platform, you can create in-depth reports with our built-in report feature. You just have to choose the parameters and report will get generated.
Here are some examples:
- You can create a report with the commission and deal amount of users by plan and teams
- Create report with 'Payouts' data such as 'Amount paid', 'Amount due' from the company
- Create report with 'Quotas' such as 'Target' and 'Achievement percentage'
- Create report for managers, based on currency, geography or any variable
If you are interesting in understanding more about automating commission calculation and reports, schedule a demo.
