I'm working on system to track, among other things, construction progress billing and payments.
I'm having trouble figuring out how to tackle interest payment, or more specifically negative interest in payments.
I have a customer whom we've negotiated terms with that if the pay within a short window of being billed they get a 1% discount. I'm not sure how to represent this scenario in my data model. IE I sent them a bill for $50k, if they pay write me check tomorrow they only have to pay $49.5k and the invoice is to be considered paid in full.
My problem is that in invoice is only "closed" after it's been paid in full. A job is only closed after all of it's "Values" have been paid in full. So if a customer only pays 99% of the values in a job (as agreed) then my jobs still look open like there is money owed. How would I/you model the discount? I've posted a simplified version of my current model below.
Typical workflow:
At the beginning of a job we establish a list of billable items for that job, this list is called a "Schedule of Values". A job is billed once a month, and a job can last many months or even years. Each month during billing we go through the list of items in the "Schedule of Values" and determine what % of that item was completed during the month and bill for a proportional amount that month. IE if the job has $24k worth of widgets total, and we finished building 20% of them in March, we bill for $4.8k in march. Progress billing.
We may have many concurrent jobs with a single customer, they may write one check to be applied to many invoices. Sometimes they make partial payments for certain items within the schedule of values. This is why I have need to track payment of each individual Schedule of Value item as you'll see in the model below.
(Before you spank me, code is in place to ensure that an [InvoiceValues] associated [ScheduleOfValue] and associated [Invoice] records have the same jobID. Code is also in place to ensure payments from a customer cannot be applied to invoice values from jobs to which the customer does not belong)