Formulas: best way to allow end user to create?

[Keetso]

Hi.

Here's one for the pros!

I have a database that tracks sales commissions for our team of independent salespeople. The commission calculations are based on forumulas dependent on the individual sales person and the company that the sale was made on behalf of. For example, Salesperson Jane Doe sold a $1000 worth of widgets for Widgets-R-Us. Her commission would be calculated as follows:

Company sold for: Widgets-R-Us
Amount of sale: $1000.00
Jane Doe's Commission base: 30%
Our company's "cut": 10%

Jane Doe's commission formula:
(Amount of sale) * (Jane Doe's commission base) = $300
Our company's "cut" formula
(Amount of sale)* (our "cut&quot = $100
The rest goes to Widgets-R-Us

There are currently 16 different companys we do business with and each company may have a different commission formula. Also, each salesperson may have different commission rates.

I used a SELECT statement to create the different formulas and all works great.

The problem I've identified with this is if (when) we add a new company (maybe 1/year), it's impossible to "add" the formula unless someone modifies the SELECT statement manually which I definately DON'T want to have happen.

I'm now trying to figure out a way to allow an end user to be able to add a new / or modify an existing formula by using a form and data input.

I thought about creating a table with all variables that exists in the current formulas and allow someone to use a form with combo boxes to "create" the formula (including combo boxes for "*","+","-", etc. Once complete, the new "formula" would be converted to a single string and stored in the table. The SELECT statement would reference the fields based on the company selected on the main data entry form and use the corresponding formula.

Does this sound feasible or would anyone have a better idea on how to handle this?

Many thanks in advance!

 

[CajunCenturion]

That sounds quite feasible, but "all variable that exist int he current formulas" should probably by "all variables that might exist in any formula".

Since you're going to allow the users to create the formulae, then you will need to be able confirm that any formula is syntactically correct, and have the ability to evaluate a user-define formula. Both quite feasible, but may require a little work.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[rdodge]

Provided that the formula is kept simple, you could create a 4 column table for both the customer and sales person. Here's how

CustomerSalesReturnTable

CustID(Unique and Long)
BeforeRate
Rate
AfterRate

SalesPersonReturnTable

SalePersonID(Unique and Long)
BeforeRate
Rate
AfterRate

I have used a similar type function for converting between Celcius and Fahrenheit as well as dealing with tax tables for income tax withholdings.

Let's say the sales person doesn't get commission pay for the first $15,000 sold over the course of the month, but then gets 30% of any amount remaining, then it's base gross pay for the month is $4,000

Now, the formula would be as follows:

(GrossSaleAmount+BeforeRate)*Rate-AfterRate

Let's say Gross Sale Amount was $45,000

(45000-15000)*.3+4000
30000*.3+4000
9000+4000
13000

Now, of course, this could produce a negative number such as if the sales person only brought in $1000 for the month, so to over come this issue, you may need to create a unique ID and another field for either minimal or maximal amount of gross sales amount.

SalesPersonReturn Table
ID(Unique)
SalesPersonID
GrossSaleMax
Rate
Base

Note in this example, we will first need to find which record has the lowest number that DOES NOT go below the GrossSale amount, which then once we return all records that meets that requirement, then return the record that has the lowest amount.

Set RST = <DBObject>.OpenRecordset(&quot;Query1&quot;,dbOpenDynaset,dbSeeChanges,dbReadOnly)

Where Query1 has the conditions, GrossSale>=GrossSaleMax And SalesPersonId = SalesPersonID, it has the Sort order by GrossSalesAmount, then you use the figures in the first record to do your calculations.

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

 

[Keetso]

Hi Cajun.

Thanks for the response!

I knew it was going to take a bit of work to accomplish this for sure. I think I can get past the syntax part by &quot;canning&quot; the majority of the formulae. This would allow the end user to change such things as the commission percentage, etc. The testing part is the kicker. I think I could create a &quot;TEST&quot; button or something that would generate some dummy data.

Any suggestions?

One other question, if I may.

Is it possible to have the selections in a combo box (staff names in my case) to automatically alphabetize itself, and especially after a name has bee updated?

 

[CajunCenturion]

I think the first thing you might want to do is to get a hold of the various forumlae that you need to use for the sixteen companies that you currently support, and look at the formulas to see if they can be canned, in addition, to determining what fields may be in play during the calculations. Even if you are able to &quot;can&quot; the majority of them, it's the ones that cannot be &quot;canned&quot; that will require the most attention.

How flexible does this need to be? Are sliding scales involved? How generalized do you want or need the solution to be?

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[rdodge]

for random percentages, you can use the following:

For I = 1 to 51 Step 1
RST.Add
RST.Fields(&quot;MinGrossSalesAmt&quot.Value = (I-1)*10000
RST.Fields(&quot;CommisionRate&quot.Value = (I-1)/100 'return a whole percentage rate between 0 and 50
RST.Update
Next I

Of course, this is only doing the percentages and minimal gross sales amount, fill in the blanks for the base and sales person id.

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

 

[Keetso]

Canjun and rdodge.

The majority of the formulae will be able to be canned. The only difference with these is the commission percentage rate. Thank goodness there are no sliding scales, etc. The formula entry should be as simple as possible. I think the toughest part will be creating a way to allow the user to &quot;test&quot; the formula before saving and incorporating it.

Another thought that just arose. If the user is creating a new company/formula, how would it be incorporated into the existing SELECT statement? I'm thinking that I could create a &quot;Forulae&quot; table with a formula field and ID field joined to the existing &quot;Company&quot; table and do a

FOR companyID = 1 to <max number of companies in the table>
SELECT companyID
use the corresponding formula
END SELECT
next companyID

Am I thinking correctly?

Again, many thanks!

 

[CajunCenturion]

Not sure, I think you need to take a step back and design the commission calculation tables. I think it's premature to be thinking about Select statements just yet. Let's focus on the requirements, and to do that, you need to look at all of the formulae.

Start by listing the existing formulas, paying attention to two things - what are the variables, and the calculation methods.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[MichaelRed]

I would at least think about setting up the 'most complex' calc using parameters. Use combo boxes for the parameters. Some parameters may be multiply dependent on a single parameter, but that is just a amall wrinkle. Each &quot;N/A&quot; parameter for a specific company / rep could be set to a generic value which did not affect the remainder of the calc. I would certainly avoid individual formulas for each case or circumstance, otherwise it seems pointless to use a 'database' or SQL in any form to just do calcs.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[rdodge]

As I was thinking of earlier and Michael basically said it, there should be some sort of standardization in place, of which that should be done in the planning stage before anything else is done. Go back to the different formulae and find out what is it that determines the formulae. I understand you are saying it's the customer, but there's also a business cost associated with customizing the different formulae, so with that said, that's why I'm saying there should be some sort of standardization taken place based on the various requirements that determines the formula(e).

Looking back at my example, it's coming from this point of view why I mentioned such type table

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000