Project billing periods

[Guest]

Trying to build a database to track Project Costs and Billing. Having
trouble with billing aspect. Would appreciate help.

Each project has several project-specific "Activities", in turn each
"Activity" has several typical tasks... eg. "Drafting", "Engineering",
"Fabrication". Each Project may have several billing periods based on
monthly billings independent of other projects. Each of the tasks would have
a "percent complete" assigned to each billing period upon which the value of
work would be calculated.

My problem is in building in the billing period to each project that will
have a global effect on all the tasks for that project but where I can adjust
the percentage for each task independently. I am stck. Please help.

 

[Allen Browne]

Presumably you have tables set up something like this:

Client table (one record for each client), with fields:
ClientID AutoNum primary key (pk)
Client Text company name
...
Project table (one record for each project), with fields:
ProjectID AutoNum pk
ProjectName Text
ClientID Number Foreign key (fk) to Client.ClientID
ProjectStart Date/Time when the project was started
ProjectDue Date/Time when the project is due to be completed.

Activity table (one record for each activity in a project), fields:
ActivityID AutoNum pk
ProjectID Number fk to Project.ProjectID. Required.
ActivityCharge Currency how much this is will cost when
completed.
...

Task table:
TaskID AutoNum pk
ActivityID Number fk to Activity.ActivityID.
Required
TaskDate Date/Time Required
PercentComplete Number Double. Required.
InvoiceID Number Initially blank. (See below.)
ChargeAmount Currency ditto.

Invoice table:
InvoiceID Number pk
ClientID Number fk to Client.ClientID. Required.
InvoiceDate Date/Time Required.
BatchID Number fk. to Batch.BatchID if you invoice
in batches.
...

The basic idea is that Tasks are children of Activities (i.e. one to many
between Activity and Task.) The tasks also become the line items of the
invoice. The ones where the foreign key field (Task.InvoiceID) is null are
the ones that have not yet been invoiced.

Now you will need to write some code to generate the invoices. For each
project you need to invoice, the code will OpenRecordset on the Tasks that
have not been billed (i.e. InvoiceID is null), finding the highest
PercentComplete for each one (since there might be more than one.) It will
also get the highest PercentComplete that has been billed for that activity,
the sum of charges so far, and the quoted total for the activity. Subtract
the previous PercentComplete from the current PercentComplete, and multiply
by the ChargeAmount to get the charge for this period. If the
PercentComplete >= 100%, or the sum of previous charges + current calculated
charge exceeds the ActivityCharge (e.g. due to rounding errors or altered
records), use the difference instead of the percentage calculation. Store
the new Invoice number in Task.InvoiceID, and the new ChargeAmount.

There are several things to lock down to make this works correctly. Your
interface needs to prevent edits or deletions to tasks once InvoiceID has
been filled in. It also needs to block edits or deletions to the projects
and activities once there are invoices for them.

You may like to use a cascade-to-null relation between the Invoice and Task
tables, so that if you delete an invoice, Task.InvoiceID is automatically
set to Null. If that's a new concept, see:
Cascade-to-Null Relations
at:
http://allenbrowne.com/ser-64.html

 

[Guest]

Thank you for your help. I did not anticipate such a detailed explanation
but if possible, would you be able to tell me how I could set up a table such
as:

BillingPeriod table with fields:
BillingID AutoNum pk
TaskID num fk to Task.TaskID
BillingPeriod num {somehow drawn on a global period number set for
the project)
%Complete num Double

Ultimately, I'd like to be able to be able to produce a Progress Draw
summarized to Activity level for use by accounting such as:

Peformed to Date Previously Current
Balance to
Activity TotalValue %'age $ Certified $ Period $
Complete

.... where each subsequent month's value to date becomes the value previously
certified. Hence, i would like to be able to store the %'age complete
corresponding to the billing period number but with the period number
automatically populated to the corresponding subsequent billing period for
each task before update of the newest %'age (%'age could also be less than
previous if "accidently" overbilled). Would I include the period number in
the Projects Table and link it to Billing Period datail table?

 

[Allen Browne]

Hmm. Not sure about that BillingPeriod table.

The BillingPeriod is meant to cover a week or a month or something? So,
after you have created the entry for the BillingPeriod, if the user enters
another Task and dates it back to that period, you're then in trouble? There
could be multiple entries for a TaskID in the billing period too?

I'm not sure I would know how to do it reliably that way.

 

[Guest]

Actually, the Tasks and Activities are set at the beginning of the project
and are based on the project budget derived from the estimated costs. Once
the project begins, any changes to the budget are handled through Change
Orders that would be additional Activities and Tasks categorized as such.
Ultimately, the final value of the project will not be the budgeted value but
rather will show a Variance (either positive or negative). Primavera's
Expedition program is set up that way but it is much more complex yet not
specific enough in other areas that I am trying to achieve.

A billing period is typically a month. Currenlty, the way I had it set up I
was getting multiple entries in the BillingPeriod table but what I am tring
to get is to avoid having to input the actual billing period number
corresponding to the current month for every task. Is there another way?

 

[Allen Browne]

The way I prefer to handle this is to create an invoice for the period for
each customer who has any unhandled "tasks" before the end of the billing
period. The invoice then has one or more line items, which are identical to
the "tasks."

A customer could (theoretically) have multiple projects on the go, so this
invoice to the customer could cover tasks from multiple projects. The
invoice-generation code picks up covers all uninvoiced tasks up to the end
of the billing period (i.e. there is no starting date, so an tasks entered
late do get picked up.)

There are other solutions, but that's my usual approach.