[AccessD] (no subject)

On almost every bonus or commission scheme I've seen there are "exceptions"
, "adjustments" etc. The way I handle our monthly bonus entry is to dump the
basic data to an Excel template and create an Excel function to handle the
bonus/commission calculation. Using a spreadsheet allows the use of ad hoc
columns or rows to deal with additional calculations. Supplementary data can
also be added. Admittedly our calc is probably more complicated then a basic
commission calc because it is based on a number of parameters including
salary, personal objectives, etc. some of which are entered directly into
the spreadsheet.
Here is an ex of such an excel function. The nice part is once built it can
be used like any other Excel function.
Jim Hale

Function BonusSMgrMoCalc(Salary As Double, ActualMargin As Double,
PlanMargin As Double, Optional Mo As Integer) As Currency
Dim dblMarginpct As Double, dblSalaryYTD As Double
If IsNull(Mo) = True Then
    Mo = 1
ElseIf Not (Mo >= 1 And Mo <= 12) Then
    Mo = 1
End If

If PlanMargin <= 0 Or ActualMargin <= 0 Or Salary <= 0 Then
    BonusSMgrMoCalc = 0
Else
    dblMarginpct = ActualMargin / PlanMargin
    dblSalaryYTD = Salary * (Mo / 12)
    Select Case dblMarginpct
    Case Is <= 0.9
        BonusSMgrMoCalc = 0
    Case 0.901 To 0.95
        BonusSMgrMoCalc = dblSalaryYTD * 0.0045 * (dblMarginpct - 0.9) * 100
    Case 0.951 To 1
        BonusSMgrMoCalc = (dblSalaryYTD * 0.0225) + (dblSalaryYTD * 0.0135 *
(dblMarginpct - 0.95) * 100)
    Case Is > 1.001
        BonusSMgrMoCalc = (dblSalaryYTD * 0.09) + (dblSalaryYTD * 0.005 *
(dblMarginpct - 1) * 100)
    Case Else
        BonusSMgrMoCalc = 0
    End Select
End If
BonusSMgrMoCalc = Round(BonusSMgrMoCalc, 2)
End Function

-----Original Message-----
From: Stuart McLachlan [mailto:stuart at lexacorp.com.pg]
Sent: Sunday, May 22, 2005 11:26 PM
To: Access Developers discussion and problem solving
Subject: RE: [AccessD] (no subject)


On 23 May 2005 at 0:00, John W. Colby wrote:

> Do NOT hard code the values in code.  Build a table of From/To/Commission.
> This will allow you to tweak your commissions as desired by changing
values
> in the table.
> 

Good point.

Then you can calculate commission using something like:
Dlookup("Commission","tblCommissions","FromSale <= " & Sales & " AND ToSale 
=> " & Sales)

(Note. Do NOT use From or To as the field names.  Using reserved words for 
field names can cause you all sorts of trouble)


-- 
Stuart


-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

***********************************************************************
The information transmitted is intended solely for the individual or
entity to which it is addressed and may contain confidential and/or
privileged material. Any review, retransmission, dissemination or
other use of or taking action in reliance upon this information by
persons or entities other than the intended recipient is prohibited.
If you have received this email in error please contact the sender and
delete the material from any computer. As a recipient of this email,
you are responsible for screening its contents and the contents of any
attachments for the presence of viruses. No liability is accepted for
any damages caused by any virus transmitted by this email.