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
- Go to Reports > General Ledger .
- Apply filters to view entries for your Sales account codes and journal type of SI: Sales invoice for the relevant dates and sales person.
- Export this list to Excel.
- Now re-filter your General Ledger report by your Current Assets account codes and JJ: Journal entry .
- 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.
- Apply filters to both the spreadsheets then sort by the Order ID column, smallest to largest.
Now you need to use a
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:
- You will need to copy this cell and paste it down the full length of the report.
- 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.