Running totals in a commission app
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 ...
Forum Discussion
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.
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.