Caltory Management Solutions




Using QuickBooks to Track and Calculate Sales Rep Commissions

Step 7: Calculating Sales Rep Commissions

To calculate the sales commissions due each sales rep, run the report you created and select the time period for which commissions are payable. Here is a report for January 2011.

Select Export and

The report  will appear in Excel:

To complete the process, you’ll need to change the format of column S to numeric values, add a column T for the product of Sales Price times Commission Rate, and total the results for each Sales Rep.

To format the cells for Commission Rate, place cursor at the top of Column S and right click to open the Format Cells window.

Select Number. Here, to handle percentages more than two digits, the Decimal places option has been set to 4.

Click OK.

Next, format the column that will receive the dollar value of Sales Commissions. Caption the first row of column T, right click the top of the column, and format the cells appropriate for currency.

Click OK.

The Commission Amt. column is now ready for the formula to calculate Sales Commission amounts.

Next, place the cursor in the first commission amount to be entered (in this example, the third row), enter the “=” sign, and click on the corresponding rows for Sales Price and Commission rate. The product of cell Q3 times S3 gives the Commission amount for a $205.99 sale at a commission rate of 12.5%.

The result of multiplying cell Q3 times cell S3 will replace the formula after you press the enter key.

Next, place the cursor in the lower right corner of the cell containing the commission amount. A plus sign will appear. Drag the plus sign to the end of the column for which rows containing sales prices and commission rates exist.

You now have the commissions for each sale for each sales rep. The final step is to add each sale for each sales rep. To do this, Place the cursor in the first empty cell after the last entry of each sales rep, select AutoSum at the top of the spreadsheet, and hit enter.  

Repeat for each Sales Rep.

You now have the totals for the period identified in the Sales Rep Commissions report exported to Excel. The process can be expedited by using a macro in Excel to automatically format and calculate totals.



 Prevous 1 2 3 4 5 6 7