One of the features that customers often look for in an accounting or sales software package is the ability to calculate commission. Generally, sales reps earn a percentage income for any sales they produce in a given period, this income is referred to as commission. Not many accounting software packages built for small businesses calculate commission.
Sage Intelligence Reporting offers you the Sales report which you can fine-tune and tailor according to your commission needs. You can add custom formulae, charts and graphs, and PivotTables to easily calculate sales rep commission, track sales rep performance and trends. The Sales report is saved for future use, so you don’t have recreate the report every time you need to calculate commissions.
Using the Sales Analysis report in Sage 50 US Intelligence Reporting, we’ll demonstrate how you can use it to create a sales commission calculator.
Note that this report may have a different name depending on your integration, but is typically is typically found in your Sales or Dashboard folder in the Report Manager.
Follow these steps to create a sales commission calculator:
- Run the report
a) From the Report Manager, run the Sales report.
b) Choose a date range which suits you. For this exercise we will only be editing the template.
- Choose your preferences
a) Once the report has loaded in Excel, go ahead and open up the Sales Analysis
b) To access the field list, click anywhere on the Pivot Table, then under the Analyze tab on the Ribbon, click Field List. Depending on the data you want to see, you can customize your report by checking/unchecking the boxes in the field list to add and remove columns.
Note: Ensure you have a column displaying the total sales amounts. We’ll use this to calculate the sales commission.
- Set the filters
a) In the filter options of the PivotTable, ensure that you have Sales Rep and Transaction type. This will help you generate commission payouts for individual sales reps by clicking on their names.
b) Once you have created your filters set the transaction type to Invoice; this ensures that the commission calculation is based on sales only (not receipts, sales orders or credit memos etc.).
Note that if you can’t find the sales rep or transaction type fields, you may need to add the expressions to the container in the Connector and/or bring them through in your report in the Report Manager.
- Enter formulas and Cell References
a) Choose any set of cells (outside the PivotTable) and enter the following “commission labels”: Sales Rep, Total Sales, Commission Percentage and Commission Payout.
Note: It is recommended to place the commission labels in the same column. To the right of these we will enter formulas for the corresponding data, i.e. the calculated fields.
b) To have a sales rep automatically show up in the Sales Rep field, select the cell and type “=” (equals) and simply click in the sales rep filter field in the Pivot Table. The Sales Rep field will now mirror whichever sales rep is selected in the Pivot Table filter.
c) To get the total sales amount from the Pivot Table, click in the Total Sales field and enter “=” (equals), then scroll down and select the Total Sales Amount Grand Total in the Pivot Table and press enter. The formula used is a GETPIVOTABLTEDATA formula which means it will always adjust to provide the right amount depending on how the Pivot Table changes.
d) Now, enter the desired commission percentage in its respective field, say 10%, but make sure you enter the value as a fraction (0.1) and then set the cell format to percent.
e) Lastly, we calculate the commission payout by entering a formula in the Commission Payout field. My Total Sales amount field is in cell F6 and the Commission Percent is in cell F7. So in this case, the formula to be entered is “=F7*F6” (or Total Sales multiplied by the Commission Percentage).
The PivotTable will adjust the figures any time a specific sales rep is selected and the amounts will be automatically calculated.
Some additional functionality you can add to enhance your report includes:
- Add Timelines to the Pivot Table to view commissions over a time range.
- Add Slicers to select more than one sales rep, getting a combined amount
- Add a graph to visually represent the commissions versus total sales for each sales rep
With these brief steps you can create your own commission’s calculator. Remember you will need to save the template back to the report in the Report Manager in order for your changes to persist.
