June 30, 2011 at 11:53 am
I am trying to make a formula as follows via a few input values. This is to be a cross reference sales margin and salesperson payout percent
starting at
margin | paypercent
15 | 5
35 | 20
If the margin is 15%, then pay 5%, if the margin is 35 then pay 10%. If I were to put margin of 25 in here I need it to be roughly
15 | 5%
25 | 8%
35 | 20%
which essentially puts a weight on the higher margins creating a something like a bell curve.
I want to be able to produce a formula that I can apply to any number between the high and low margins entered that will apply this weight and output the rate to pay.
Any help would be very much appreciated! Thanks
July 1, 2011 at 12:17 pm
I believe what you are looking for is called linear regressions.
You have a X and Y axis and a line insects at 15:5 and 35:20 so then the question is whats the solution to 20:?
If you search 'SQL server linear regression' you may find something useful.
July 1, 2011 at 1:24 pm
Actually non-linear regression. You have a curve of some type. You need a function to plot you curve based on your two points. You then need to find the intersection of the curve. I am clearly overstepping my bounds and I hope that something I said here can somehow be of value to you. Perhaps a statistical expert will step in here.
July 1, 2011 at 10:12 pm
Just a suggestion, but rather than create the formula, let your users define the marginvalues and the accompanying pay percentages. Store them in a table just like you are using for your illustration above. Then a simple join will give you the percentage you want. It may even outperform doing the calculations row by row.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
July 1, 2011 at 10:25 pm
Thanks for the suggestions.
I am actually using a table right now. A weekness is that I get many requests for 'test' plans with different scales that are derived from pure guesswork and require a lot of input on my part. If I were to plot these plans on a chart, it would have a very squiggly line instead of a non-linear curve.
I will do some googling, I think the suggestions will clear up the vocabulary I am searching for.
I do think that I will need 4 values, the start and finnish, as well as two values in the middle in determine the curve. A single mid-point would not provide enough data to compute all other points on the curve.
Also, I plan on using this formula to produce or update lines in a table as a trigger in SQL whenever a table with 'plans' and the values. Basically, I can put together a .net interface so that these users can plot their own curve on a chart and I can produce a reference chart as precise as I need from this formula.
July 1, 2011 at 10:55 pm
I have attached a really rudimentary chart I made in excel to visually demonstrate. The 'K's are know values and the 'x's are a few examples of the numbers I am trying to compute (they are supposed to be on the line but using excel to build a chart isnt the best way) . the Vertical axis is the margin percentage that I know, the horizontal is the 'return' which is the percentage to pay out that I need to know.
The ideas is that I always know the low and the high value, the 3rd value determines how the scale flows, how it deviates from being perfectly linear.
The purpose is to put incentive on getting more margin. under 15% margin and the payout is zero, over 35% and the payout is flat, but that is easily handled seperately instead of trying to complicate the curve.
google has shown me a lot of non-linear curves but I cant come up with a solution yet.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply