My sales reps get paid different comission brackets based on their overall profit margin for the month, each month.
I am trying to make a report that shows what overall profit margin they sold at per month, and ideally i would like the report to be live, and on their dashboard so they can see what profit margin they are selling at, at any any time.
When i say profit margin, i mean
Profit Margin % = (GROSS SALES - COST OF GOODS SOLD)/GROSS SALES)*100
I have been able to accomplish something like this in accrual basis, but what if you only wanted to pay commission on sales that money has been collected on? This provides some complications.
1.) What if payment is collected this month on an invoice from last month?
2.) What if an invoice is only partially paid for?
3.) What if half the invoice was paid last month, and half the invoice was paid this month?
My end goal would look like
Sales Rep A - 22%
Sales Rep B - 23%
etc
how do other people handle knowing the profit margin of each sales rep?