Matching the data.

[joeny0706]

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

[SamT]

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