Return cost per date range

[Akastan]

Hi,

My company has various contracts that contain pricing matrixes. Some of
these matrixes have escalating cost structures based on a specific time
period. For example in the table below every 365 days the monthly cost
increases but escalation periods can differ from contract to contract. The
contracts are for services at individual sites and each site has a different
service start date.

My question is if I have a site that begins service in April 2008 and we
receive an invoice for Feb 2010 how can I reconcile the billed charge with
the correct Cost Per Year from the pricing matrix? Is there a query I can
run to determine the correct Cost Per Year?

Currently I have a form where the user enters a beginning (2/1/2010) and
ending (2/28/2010) date for the service charge period, in this case it is Feb
2010.

Contract # VENDOR NAME COST PER YEAR Service Period
BHN01 ABC COMPANY $877.00 1st year
BHN01 ABC COMPANY $960.00 2nd year
BHN01 ABC COMPANY $1,043.00 3rd Year
BHN01 ABC COMPANY $1,126.00 4th Year
BHN01 ABC COMPANY $1,209.00 5th Year
BHN01 ABC COMPANY $1,220.00 6th Year
BHN01 ABC COMPANY $1,295.00 7th Year

Thanks for your help,
Adam

 

[Allen Browne]

Consider replacing the ending date field with two fields like this:
Freq Number how many of the period
PeriodTypeID Text a value that works with DateAdd()

The records in your table could look like this in this pair of fields:
30 d means every 30 days
1 m means every calendar month
90 d means every 90 days
1 q means every quarter
1 yyyy means every year

Now you will have some way to identify the rates you charge per period, so
you can figure out:
- what to charge (based on the period type and number of periods),
- when it expires: DateAdd([PeriodTypeID], [Freq], [StartingDate]

If you are actually billing periodically within the contract period, that's
another issue. You might want to create all the invoices for the period at
the time when the contract is signed.

In terms of reconciling payments and invoices, IME there is no direct
relation between these two things. For example:
- A payment covers multiple invoices
- A payment covers part of an invoice
- A payment covers parts of multiple invoices
- A payment covers something that has not been invoiced yet.
A full-blown double-entry accounting system therefore calculates the
invoices and payments for a period, closes the period, and brings the
balance forward into the next period. if you don't want to program that,
perhaps you could just use sum-of-all-invoices less
sum-of-all-payments-received for the cleint to arrive at the balance due.