formula calculations

[ckloch]

I need to create a database that will calculate customer fees. These
fees are based on two variables. Participants in the plan(which is a
per person fee) and the amount of assets... Right now I am doing
the fee calculations in Excel and have lookup formulas for
everything. However I think this can be more easily managed and
executed through access with the relationships. Right now things can
happed to throw off the formulas and it ends up a mess......

I am looking for ideas/suggestions on how to design this database.
Input on how to handle the formulas. Should the formulas be set up
in a query... If in a query suggestions of how to set the query up.

Thanks

 

[ckloch]

PS the asset charges are not you basic easy formulas. It is a tiered
calculation that takes up many rows in the excel spreadsheet.

This is an example of a formula......

=IF(C48>VLOOKUP(Plan_Data_Table!$D53,'Fee Schedules'!$A$4:$M
$21,2,FALSE)+VLOOKUP(Plan_Data_Table!$D53,'Fee Schedules'!$A$4:$M
$21,4,FALSE)+VLOOKUP(Plan_Data_Table!$D53,'Fee Schedules'!$A$4:$M
$21,6,FALSE)+VLOOKUP(Plan_Data_Table!$D53,'Fee Schedules'!$A$4:$M
$21,8,FALSE),VLOOKUP(Plan_Data_Table!$D53,'Fee Schedules'!$A$4:$M
$21,8,FALSE),IF(C48>VLOOKUP(Plan_Data_Table!$D53,'Fee Schedules'!$A
$4:$M$21,2,FALSE)+VLOOKUP(Plan_Data_Table!$D53,'Fee Schedules'!$A$4:$M
$21,4,FALSE)+VLOOKUP(Plan_Data_Table!$D53,'Fee Schedules'!$A$4:$M
$21,6,FALSE),C48-(VLOOKUP(Plan_Data_Table!$D53,'Fee Schedules'!$A$4:$M
$21,2,FALSE)+VLOOKUP(Plan_Data_Table!$D53,'Fee Schedules'!$A$4:$M
$21,4,FALSE)+VLOOKUP(Plan_Data_Table!$D53,'Fee Schedules'!$A$4:$M
$21,6,FALSE)),0))

 

[BruceM]

It will help if you describe just a bit more about the real-world scenario.
Are there a series of fees for each customer (such as annually or monthly or
per transaction)? Are the fees for a variety of services, or is there just
one type of fee? Does each customer have a record of assets? If so, is the
fee based simply on the total value of the assets, or is there more to it
than that?

 

[ckloch]

Each customer has atleast two different charges and the fees are
charged quarterly. The first charge is a per participant charge and
this is a basic calculations of variable a multiplied by the per
particpant rate. The second charge is an asset charge that is
sometimes tiered and sometime a flat charge. Each client has
schedule they are assigned to them that includes both charges. for
instance the per participant fee may be $35 per year and the asset fee
may be .75% for all assets. Or the asset fee maybe .75% for the
first 1 million and .50% for the next 1 million, etc.... Some
clients also have a minimum so if the described fees above don't meet
a certain amount then they are charged a set amount. For example if
the participant fee is $50 and the minimum is $125 then they are
charged $125.

There maybe be one time charges for certain clients to for special
services performed during the quarter or a base fee that is charged
quarterly.

Does that help?

 

[BruceM]

It sounds to me as if you will need a Customer table and a related Charge
table:

tblCustomer
CustID (primary key)
CustName, etc.

tblCharge
ChargeID (primary key)
CustID (foreign key)
ChargeTypeID
ChargeAmount

You can also make use of a lookup table for charges:

tblChargeType
ChargeTypeID (primary key)
ChargeType
ChargeAmount


Base a form on tblCustomer, with a subform based on tblCharge. Bind a combo
box (cboCharge) to ChargeTypeID (from tblCharge). Use tblChargeType as its
row source (with the fields in the order listed above). ChargeTypeID is the
bound column, the column widths are something like 0";1.5";0". The Column
Count is 3. It's AfterUpdate event could be something like:

Select Case Me.cboCharge
Case "Quarterly"
Me.ChargeAmount = Me.cboCharge.Column(2)
Case "Asset Fee"
Me.ChargeAmount = (a calculation result)
Case "Minimum Charge"
Me.ChargeAmount = (another calculation)
End Select

If the calculation is based on the customer's assets you would probably have
another subform to list the assets, along with a sum of the assets. You
could use that sum in the calculations, as needed. I can't really provide
details about how to go about the individual calculations. If a calculation
is based on total assets, you need a way of determining that number and
making it available to the calculation. If you are having a specific
problem with the calculation, post details. Once you see how it is done you
may be able to figure out how to approach the other calculations.

 

[ckloch]

The calculation of the asset fees is my problem.... the formula is
very long and complex. Actually the formula is part of the original
post and I posted it below too...

I am looking for suggestions on how to handle these formulas...
Should it be done in a query in a report? I don't think a form is
the appropriate place since I don't understand the need for a form?
All the asset amounts and participant counts will be a paste into a
table.....



=IF(C48>VLOOKUP(Plan_Data_Table!$D53,'Fee Schedules'!$A$4:$M
$21,2,FALSE)+VLOOKUP(Plan_Data_Table!$D53,'Fee Schedules'!$A$4:$M
$21,4,FALSE)+VLOOKUP(Plan_Data_Table!$D53,'Fee Schedules'!$A$4:$M
$21,6,FALSE)+VLOOKUP(Plan_Data_Table!$D53,'Fee Schedules'!$A$4:$M
$21,8,FALSE),VLOOKUP(Plan_Data_Table!$D53,'Fee Schedules'!$A$4:$M
$21,8,FALSE),IF(C48>VLOOKUP(Plan_Data_Table!$D53,'Fee Schedules'!$A
$4:$M$21,2,FALSE)+VLOOKUP(Plan_Data_Table!$D53,'Fee Schedules'!$A
$4:$M
$21,4,FALSE)+VLOOKUP(Plan_Data_Table!$D53,'Fee Schedules'!$A$4:$M
$21,6,FALSE),C48-(VLOOKUP(Plan_Data_Table!$D53,'Fee Schedules'!$A
$4:$M
$21,2,FALSE)+VLOOKUP(Plan_Data_Table!$D53,'Fee Schedules'!$A$4:$M
$21,4,FALSE)+VLOOKUP(Plan_Data_Table!$D53,'Fee Schedules'!$A$4:$M
$21,6,FALSE)),0))

 

[BruceM]

I cannot offer any suggestions based on the Excel formula. Cell references
have no meaning outside of that particular worksheet. You will need to
substitute numbers or explanations. What is in C48? What is
Plan_Data_Table!$D53? What do the Vlookup values such as $A$4:$M$21
represent?
The formula tells me: "If C48 > the sum of values in a lookup table, return
the last item in the list of numbers that were added together. If C48 > the
sum of a slightly different set of numbers, return the result of subtracting
the sum of those values from the value in C48, otherwise return a 0."
You can do the calculations in a query, or in an unbound text box on a
report or form, or using VBA code. I assumed the data is getting into the
tables by way of a form. If not, how does it get there?