datalink Posted April 2, 2012 Posted April 2, 2012 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 Posted April 3, 2012 Posted April 3, 2012 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 Posted April 3, 2012 Posted April 3, 2012 This is the way I would handle it. Take a look at this attached file. Commissions.zip
datalink Posted April 3, 2012 Author Posted April 3, 2012 Wow! Thanks to both of you for these approaches. I'm exploring them now. Thanks so much!!
datalink Posted April 4, 2012 Author Posted April 4, 2012 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 Posted April 4, 2012 Posted April 4, 2012 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 Posted April 4, 2012 Author Posted April 4, 2012 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 Posted April 4, 2012 Posted April 4, 2012 Like: clerk 1 ; Sales TOTAL : $190 ; Commission: 5%; Final Commision for clerk1 on sale id#019 : $9.50
datalink Posted April 4, 2012 Author Posted April 4, 2012 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 Posted April 4, 2012 Posted April 4, 2012 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 Posted April 4, 2012 Author Posted April 4, 2012 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 Posted April 4, 2012 Posted April 4, 2012 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 Posted April 4, 2012 Author Posted April 4, 2012 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 Posted April 4, 2012 Posted April 4, 2012 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 Posted April 4, 2012 Author Posted April 4, 2012 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 Posted April 5, 2012 Author Posted April 5, 2012 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 Posted April 5, 2012 Posted April 5, 2012 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 Posted April 5, 2012 Posted April 5, 2012 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
Recommended Posts
This topic is 4632 days old. Please don't post here. Open a new topic instead.
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now