Running totals in a commission app

  • 0
  • 1
  • Question
  • Updated 5 months ago
  • In Progress
I'm trying to help my Finance Manager automate our commission process which today is purely manual and takes him 5-7 hours per month. 

We have 3 tiered commission plan where the commission goes up once the Sales Rep achieves a certain revenue clip level...example:

$0.00 - $100,000 - 0.3% of revenue
$100,000 - $250,000 - 1.7% of revenue
$250,000 - 2.3 % of revenue

What I've done is set-up a table called Quotas that defines the quotas per rep and built a relationship between the details table where the billable records go. What I'm trying to achieve is to have a running total where I flag each record below 100K at 0.3% and in accordance with the 3 revenue tiers above. I'll program this using a Summary table but I'd like to do in a standard table format as well.

I know there are previous tickets on the subject of running totals but I'm hoping to enlist the help of the QB network to speed things up hence the ticket.

Thanks,

Chris
Photo of Chris Collins

Chris Collins

  • 70 Points

Posted 5 months ago

  • 0
  • 1
Can you explain how the running total is meant to be determined.  Is it from the hire date of the rep or for a calendar year or what?

Photo of Chris Collins

Chris Collins

  • 70 Points
Great question and apologies for not specifying that upfront. This would be done quarterly by Rep and that's the way my Quotas table is set-up.
I think that the only way to do this is to load up a table with all the res for every future quarter and set that as the key field.

You would use Excel to initially populate this.

I'm not sure if you have rep numbers or territory number or you need to identify the rep by name, but suppose that you use Territory numbers.

The Summary table would be in the format of REP#-YYYYMM of each quarter.

Like
123-201801
123-201804
123-201807
123-201810

Then you would calculate a similar field by formula on the sales table  and make a relationship.  Then that summary table would have the information to pay the commissions.

You may be able to get that summary table Key fields populated for new sales automatically with an Automation but if not, I'm sure its less work for the Finance dept to maintain that summary table than they do now.