Consulting

Results 1 to 2 of 2

Thread: Matching the data.

  1. #1

    Matching the data.

    Hi ALL.

    I donít know if this is possible but I will start by asking here. I work at a manufacturing plant. We sell items and also take returns from the stores. We have large chain stores that pay with one check for 100ís of invoices at once. Currently the accounts receivable dept manually matches up the invoice number and amount in our accounting system to the invoice number and dollar amount on the statement that is sent with the check.
    She has a printed .pdf that she goes line by line comparing the invoice the chain paid for then finds it in our accounting system to mark as paid. When the price paid does not match the price on the invoice, she needs to figure out why.
    I have asked the field rep to ask others in the same business how they do this process. He has not got any answer for me.
    My next step is trying to find a way to automate some of this since I canít find any other way to do it. One thought is to scan the pdf to convert to an excel file. Then see if I can get help to create an excel VB script that will compare the invoice number and dollar amount form the statement showing what it is paying to an excel file that has my invoices information then have it mark off any invoice numbers and dollar amount that match then flag any that do not match.
    Anywhere the amount if different than the invoice amount in our system needs to be addressed.

    Does anyone have any ideas how this can be automated? I welcome good ideas or anyone telling me why my idea wont work. I donít want to work on this and realize it does not help.
    Thanks ALL

  2. #2
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,704
    Location
    Currently the accounts receivable dept manually matches up the invoice number and amount in our accounting system to the invoice number and dollar amount on the statement that is sent with the check.
    She has a printed .pdf that she goes line by line comparing the invoice the chain paid for then finds it in our accounting system to mark as paid.
    That part can be automated

    When the price paid does not match the price on the invoice, she needs to figure out why.
    That part cannot be totally automated

    I will try to explain my last: The process from (them ordering items) to (them making payments) has too many people/depts involved, any of which/whom can/will make changes to the tracking paperwork

    Just on your end: Sales >. Shipping > Billing > Accounting
    On their end: Purchasing > Receiving > Sales > Returns > Shipping/disposal > Accounting > Payments
    Back on your end: Receiving/Returns > Accounting

    Some of those tracks are parallel or split and may not arrive at Accounting (yours or theirs) at the proper times

    Prices change.


    One thought is to scan the pdf to convert to an excel file. Then see if I can get help to create an excel VB script that will compare the invoice number and dollar amount form the statement showing what it is paying to an excel file that has my invoices information then have it mark off any invoice numbers and dollar amount that match then flag any that do not match.
    That is the best you will be able to do with Excel. Using a good relational Data Base will provide a littler better solution, but no truly complete automated solution is possible with so many people/depts/time involved


    Returns are the real bug-a-boo: The time frame on their payment and your accounting depends on many things, mostly governed by your business' policies.

    I suspect that your accountant is going to need a discrepancy account(s). Talk to a good Forensic Business CPA, 'cuz I dunno. I imagine that discrepancy accounts will mostly be reconciled in a reasonable time after Returns are completed. Note that the "Reasonable Time" discussion is way above our heads and involves Money, Guns, and Lawyers; (Accountants, Directors, and Legal.)
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •