Calculating Sales Commission

Your sales analysis reports in Brightpearl calculates sale margins as the difference between the actual sell price used on your sales orders and the cost currently recorded on your price list. If your cost price rarely changes you can calculate your sales commission by using these reports. If your cost prices change frequently you will probably want to caluclate your sales margins using the actual cost per sale. To do this you will need to export your data and calculate it in Excel using the how to guides below.

To get sales margins per sales person you will need to have assigned customers and orders to your staff members. You will then be able to use the filters to view figures per staff member.

Related how to guides - Calculating Sales Commission

How to export sales revenue per invoice and cost of sales per invoice

  1. Go to  Reports > General Ledger .
  2. Apply filters to view entries for your Sales  account codes and journal type of  SI: Sales invoice  for the relevant dates and sales person.
  3. Export this list to Excel.
  4. Now re-filter your General Ledger report by your  Current Assets  account codes and  JJ: Journal entry .
  5. Export this list to Excel too.

Note: Orders must be invoiced to show sales revenue and shipped to show a cost of sales value.

How to match the relevant sales revenue to cost of sales

Now you have the sales revenue and actual costs per sale you need to match up the relevant figures. This can be done with the Excel LOOKUP formula.

  1. Apply filters to both the spreadsheets then sort by the  Order ID  column, smallest to largest.
  2. Now you need to use a  LOOKUP  formula to match the relevant revenue and costs. In a new column on the sales figures spreadsheet enter your lookup.
    Here's an example... =LOOKUP(N:N,'[GENERAL_LEDGER-1.xls]Sheet 1'!$N:$N,'[GENERAL_LEDGER-1.xls]Sheet 1'!$S:$S)
    (where the cost spreadsheet is named "GENERAL_LEDGER-1")

    This formula will take the value column N (Order ID) in the sales sheet, look for the matching value in column S (Order ID) of the cost spreadsheet and enter the value in column S (cost of sale) into the cell.
    Using the Microsoft Excel formula wizard it will look like this:
  3. You will need to copy this cell and paste it down the full length of the report.
  4. Now you have the associated costs with each sale you can calculate the difference in the next column and then your sales commission from that figure.
Have more questions? Submit a request