Results 1 to 3 of 3
  1. #1
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142

    How to model invoice payment discount?

    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.

    Click image for larger version. 

Name:	zz1.png 
Views:	20 
Size:	36.9 KB 
ID:	34717

    (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)

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    This should be like a shopping cart.
    along with the purchased items, The discount would be a line item: -$xx.00 , Early Discount,

  3. #3
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    I'm not sure I follow. How would I model it? Like a shopping cart in what way? Our contract progress billing procedures doesn't work like a shopping cart in the way I'm imagining it.

    I'm starting to think that rather than model it I'll have to come up with a way to manipulate the system as is... a procedure for issuing separate credit invoices along with negative payments applied in the event payment is received in time for the discount. This would require a deductive change order perhaps to settle up before closing the job. Does this sound reasonable? Otherwise I'm thinking that I would have to edit invoice values after they been issued/paid and I'm fairly certain that's a no-no.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 11
    Last Post: 12-04-2017, 07:18 AM
  2. Replies: 4
    Last Post: 10-01-2016, 09:19 AM
  3. Replies: 11
    Last Post: 11-28-2015, 10:14 PM
  4. Replies: 3
    Last Post: 10-19-2015, 11:05 PM
  5. Replies: 2
    Last Post: 09-14-2015, 09:01 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums