Complex Commission Calculation

[datalink]

Hi There,

I've been asked to construct a commission calculation that initially seems quite simple to me, but as I've gotten into it I'm find it pretty complex and its reached a point of painful.

At it core the concept is that the commissions are calculated on two tiers. There is a standard percentage paid on all sales that fall below quota. A a higher commission is paid on everything above that quota. It seemed to me that it would be a pretty straight forward case calculation. The tricky part are the sales that come in tipping the scale with part of the sale being below quota and part of it being above quota. In that case the portion of that sale that brings the sales person up to quota is paid at the lower percentage. The remaining part of that sale is paid at the higher percentage.

I've built a pretty rudimentary prototype that I have attached. For this prototype there are only two tables. Sales and Periods. Each sale is tied to a period and that's how progress over a quarter is tracked against quota.

I've been mucking around with this for days. My guess is that perhaps the commission amount needs to be stored statically at the Sales level and calculated by a set field step. If it isn't stored statically I'm unsure how to prevent all the sales from recalculating once the quota is met.

This must be something folks deal with all the time, but I'm really at a bit of a loss and any suggestions would be greatly appreciated.

ComissionPrototype.fp7.zip

[comment]

A a higher commission is paid on everything above that quota.

IIUC, the total commission depends on the the total amount of sales in a period. If so, the correct place to calculate the commission would be in the periods table (or at the period level of a summary report).

ComissionPrototype2.zip

[dwdata]

This is the way I would handle it. Take a look at this attached file.

Commissions.zip

[datalink]

Wow! Thanks to both of you for these approaches. I'm exploring them now. Thanks so much!!

[datalink]

Hey Comment,

I've been playing with your calculation:

(Min ( Quota ; cSumAmount ) * PercentageStandard

+

Max ( 0 ; cSumAmount - Quota ) * PercentageBonus

Its simple and elegant (and I'm not sure I know how it works.) Its creating a running total. Because commissions are paid out based on payment of invoice, rather than on a period total, I need to calculate the commission amount on each order as they are entered. I wonder how to change it so that it would be a bit more static.

I was thinking of using a script that set cSumAmount onto an order at the time the order is entered and then based on that set the Commission Amount on that order, but of course it still provides the running total rather than the total for that order.

Could you help me out a little further? I appreciate it so much.

[imoree]

I am not comment but

(Min ( Quota ; cSumAmount ) * PercentageStandard

// this returns the Minimum value in Quota & cSumAmount ; then * by PercentageStandard

+

Max ( 0 ; cSumAmount - Quota ) * PercentageBonus

//this function returns the highest in cSumAmount - Quota then * percentageBonus

I was thinking of using a script that set cSumAmount onto an order at the time the order is entered and then based on that set the Commission Amount on that order, but of course it still provides the running total rather than the total for that order.

That won't work as you can't edit a calculated field unless you decide to do some massive scripting!

I need to calculate the commission amount on each order as they are entered. I wonder how to change it so that it would be a bit more static.

If you have FMP advanced you can create a custom function but you can also just write a script like

set var $Min = Min ( QUota; cSumAmount) * Percentage Standard

set var $Max = Max ( 0; cSumAmount - QUota) * Percentage Standard

set $$result = $Min + $Max

you can then

Set field = Commission::FIELD = $$result

[datalink]

Hey Imoree,

Thanks for the tutorial there.

What I meant was to have a couple of standard number fields on the order record and then to use a set field step in a script which would set the calculated commission amount on the order so that they commission amount would be the for only that order, at the time that the order was entered. This way, once an order is paid they know what the commission on that order that needs to be paid is. Does that make sense?

[imoree]

Like:

clerk 1 ; Sales TOTAL : $190 ; Commission: 5%; Final Commision for clerk1 on sale id#019 : $9.50

[datalink]

Thanks to both of you for your help. Its nearly there.

I'm attaching a revised version of the file. It shows two windows. The first window shows sales period. The second window shows sales with the right most field highlighted in green showing what the commission amount on that sale should be. I manually entered that amount in the commission field at the order level, but I need a way to calculate it either live (which would be preferable and require changing the field to a calculation field) or to use a set field step in a script to set that commission amount in a static field such as it is now.

I'm hoping some one can help me get this the rest of the way. Its so close. (I'm also running out of time. Yikes!)

Thanks in advance.

ComissionPrototype3.fp7.zip

[imoree]

Looks like 10% commisssion! is that all?

these numbers you havE! are they correct as the calc by Comment & your input amounts are off by quite a bit!

[datalink]

Hey Imoree,

If you are on the sales table view and sort by period (I should have put that in the script. Sorry.) you will see how the lines break over periods.

If you look at period one you'll see the first line is commissioned at 10% because it is below quota. The second line is commissioned partially at 10% to get up to quota and partially at 20% as its beyond quota and so the bonus commission amount is paid on part of the sale. The third line in period one is entirely at 20% because quota is entire met.

Comment's calc on the periods table is accurately calculating the period's commission total, but I input the sales commission totals manually. The sum of the sales line does match the sum on the periods table using Comment's calc.

I am really grateful for the help you're providing with this.

[imoree]

Based on what you said i get this:

new field is right of yours..

Period Table

cSumAmount - Sum(OrderLine::Amount) == This is total sum of all amounts

cCommission - CommentsCalc; this is calculating total commission NOT INDIVIDUAL

Try thisComissionPrototypeFIXED.fp7.zip

[datalink]

Hmm...I think I'm doing a poor job explaining what I'm trying to do.

The calculation that I need would give the same result as what is in the green field on the sales layout, which I calculated manually and just entered in that field. That green number is the number that I'm trying to get to.

If you look at Sales layout and sort if by PeriodID you will have the first three lines in Sales Period 1 grouped together. The first line (line 14) is entirely below quota, so it's commission is calculated at 10%. The second line (line 15) brings the period total up to quota (so its calculated partially at 10% and partially at 20% which is where the amount of $7,250 is coming from. The third line (line 16) is calculated at 20% because by the time that line is added to the system quota has been complete met.

On the sales layout Period 2 all of the line items are still below quota for period 2, so those are each calculated at 10%.

Hopefully this will make more sense. I need a calculation that will give the results that are in the green field.

Thanks so much for your help! Please don't give up on me!

Sales.tiff

[dwdata]

I am not quite following your logic:

Let say the the Periods details are as follows:

Period 1, Quota = 50,000, Standard % = 10, Bonus % = 20

The your transactions are:

Trans1 - PeriodID = 1, Amount = 25,000, Running Total = 25,000

Trans2 - PeriodID = 1, Amount = 50,000, Running Total = 75,000

Trans3 - PeriodID = 1, Amount = 25,000, Running Total = 100,000

It seems it should break down as follows:

Trans1 - Reg_Comminsion = 2,500, Bonus_Commission = 0 (Quota Not Met)

Trans2 - Reg_Comminsion = 2,500, Bonus_Commission = 5,000 (Quota Met - partial bonus)

Trans3 - Reg_Comminsion = 0, Bonus_Commission = 5,000 (Quota Exceeded - All Bonus)

-------

Total Commissions - Regular = 5,000, Bonus = 10,000, Grand Total = 15,000

This calculates the same as:

Regular Commission = if(sum(ALL_SALES) > Periods_Quota, Periods_Quota, sum(ALL_SALES)) * (Periods_Reg% / 100)

Bonus Commission = if(sum(ALL_SALES) <= Periods_Quota, 0, sum(ALL_SALES) - Periods_Quota) * (Periods_Bonus% / 100)

Total Commission = Regular Commission + Bonus Commission

-----------------

Am I missing something? If so, please explain.

[datalink]

Hi DWData,

Yes...your calculation works great and thank you so much for your prototype. I might be missing a major part of what you're saying, but it seems to me that you're calculating total commission at the period level, which is great. I need to be able to show the commission on each sale line as a total for each sale. I could easily be missing something but it seems like your method will display commission total for the period but not for the individual order.

I attached another prototype version with your calculation incorporated at the sale level.

I must be missing something. Thanks again for your help.

ComissionPrototype4.fp7.zip

[datalink]

Hey Folks....just bumping this back up again. Hoping someone has some ideas about being able to show the calculated commission on each sale the sale level.

Thanks again for your thoughts.

[imoree]

cCommission:

Min ( Quota ; cSumAmount ) * PercentageStandard

+

Max ( 0 ; cSumAmount - Quota ) * PercentageBonus

z_CommissionStandard_c =

Case( /* if the sum of the ENTIRE above amount is greater than the QUota for the period; then Give me the QUota; Else give me

the Sum of the ENTIRE AMOUNT above (times) the PercentageStandard of that period!.

*/

Sum(Period::cSumAmount) > Period::Quota; Period::Quota;

Sum(Period::cSumAmount)) * (Period::PercentageStandard)

z_CommissionBonus_c =

Case(Sum(Period::cSumAmount)  ≤ Period::Quota; 0 ;

Sum (Period::cSumAmount) - Period::Quota) * (Period::PercentageBonus)

/* if the Sum of the Entire cSumAmount is lessthan or equal to the Period Quota; return nothing

else Give the the sum of the Etnure AMOUNT {minus} tje Period QUota then Times that by the period Bonu

z_CommissionTotal_c =

z_CommissionStandard_c + z_CommissionBonus_c

? are you trying to put all of this in one ( 1 } calculated field or what? or is your math wrong here?

Problem here is cSumAmount for period 1 = $100,000

Period 2 = $ 60,000

So i dont know how you would calculate per line entry when you are calculating the ENTIRE amount

in the Period table!

[dwdata]

If you want an itemized report of how the commission is allocated (including split commissions), it is very difficult to do because of how summary fields behave in FMP. It is possible to use brute force and script the process, but on larger sets of records, it might not be optimal. Check out the file I I am attaching.

Commissions_DW2.zip