Preventing a $580K A/R write-off
Faced with a $580K write off of multiple A/R invoice balances, I developed and managed a Microsoft Access database to reconcile more than 15,000 individual invoices over a 6 month audit period. The results were outstanding. 99.7% of the outstanding A/R was collected. DSO dropped from 48 to 2. Average weekly time spent on managing invoicing and payments dropped from an average of 4 weeks to less than 30 minutes. All client KPI measurements were exceeded for accuracy, speed, and quality. The database has now grown to more than 60,000 individual invoice files, and just as many payment records. Total number of records in this database is more than 120,000. If you are facing a similar delima - how to you manage or reconcile large data files - I can create customized Access solutions for your team, including a complete GUI interface.
Published on: Mar 4, 2016
Transcripts - Preventing a $580K A/R write-off
Accounts Receivable Collection
Background• Client required invoices to be submitted according to strict data definitions using CSV file formatting.• Average number of invoice lines submitted each week was 350.• Each line of the file was considered to be a separate invoice and required a separate invoice number.• Entire files or specific invoice lines could fail electronic submission based on non-compliance to data definitions.• Client would inform when either occurrence took place. For entire files, the file would be corrected and resubmitted for processing. For line errors, the specific invoice(s) would need to be researched, corrected, and resubmitted.• Payment terms were net 60.
Situation• In September of 2011, the financial controller resigned his position with 6 weeks notice.• The controller was the only individual who understood the entire invoicing and payment process.• A new controller was hired from within and was transferred to the location. The new controller was highly recommended.• The new controller worked with the outgoing controller for a 4- week period. The training and knowledge transfer was being monitored by the General Manager.• Within 5 weeks, we started to receive comments from our client that our error rate for file submissions was escalating.• Within 12 weeks, the account was beginning to receive increased corporate scrutiny for outstanding accounts receivable balances.• An internal audit was conducted and it was determined that at least $580,000 of invoices were unaccounted for and had not been submitted to the client for payment.
Findings• The controller didn’t understand the billing process and did not seek assistance.• The controller was applying EFT payments to the oldest invoices versus the correct invoice.• A/R Aging reports were no longer valid.• It was impossible to determine which invoices had been billed and paid vs. what had not been invoiced.• Entire CSV invoice files had failed electronic submission and had not been resubmitted.• Multiple lines of CSV invoice files contained data that resulted in line submission failures. Failure rates had climbed to as high as 18% from an average of 3%. Invoice lines were not resubmitted.• The corporation was prepared to write-off outstanding invoice balances.• Client was not aware of the extent of the problem• DSO had risen to as high 48.
Reconciliation Process• 4 relational tables, multiple queries, and multiple report formats were created in a Microsoft Access database.• Invoice and payment files were exported from their native formats and were imported into the reconciliation database.• Files included client EFT detail payment files (PDF to Excel), client EFT summary files (PDF to Excel), corporate invoice detail files (Excel), and corporate invoice summary files (Hard Copy Originals).• Invoice detail files were imported and matched against corporate summary files. Variances were researched and reconciled.• Client EFT detail payment files, once in Excel file format, were imported into the database.• Invoice numbers were assigned Primary Keys. This allowed for invoices to be matched against payments.• All unmatched invoices were individually reviewed against 3M payments systems and resubmitted.
Reconciliation ProcessMultiple data fields created and linked to manage submission, payments,submission failures, aging, adjustments and bad debt.Allowed for complete reconciliation of invoicing submission and payments. Inthis sample, more than 55,000 invoices were being reconciled.
Reconciliation ProcessMultiple reports were developed to manage invoice payment detailsMultiple SQL queries created to mine and update dataNotice on the invoice below that it took 7 different payments over a 2 monthperiod to completely pay just one invoice balance. 7 different payments over 3 months to pay 1 invoice in total
Reconciliation ProcessReconciliation of import records from the EFT file. EFT File Database
Reconciliation ProcessInstructions were e-mailed to ARAMARK’s Cash & Banking team for how to applythe EFT payments
Reconciliation ProcessInvoices that failed billing submission were researched. A history was log wasmaintained for all invoices requiring multiple submissions.
Reconciliation ProcessAn invoice aging report was created to allow for individual invoice lines to beclosely monitored throughout the billing and payment cycle
Results• Of the original $580,000, 99.7% was collected• Re-engineered the process from an intensive manual billing and reconciliation process to a highly efficient electronic system• Weekly processing time dropped from an average of 4 hours to less than30 minutes• Duplicate invoicing dropped to 0• DSO fell from 48 to 2• Billing error rates dropped from 18% to 1.8%• 100% of invoices were submitted within the defined client billing requirements• No invoices were carried into FY 2012 or FY 2013