Discussions

Expand all | Collapse all

Running totals in a commission app

  • 1.  Running totals in a commission app

    Posted 01-14-2019 14:25
    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


  • 2.  RE: Running totals in a commission app

    Posted 01-14-2019 14:33
    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?



  • 3.  RE: Running totals in a commission app

    Posted 01-14-2019 14:35
    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.


  • 4.  RE: Running totals in a commission app

    Posted 01-14-2019 15:41
    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.