Speadsheet or Software Options for Managing Accounts Receivable

I would recommend you at least begin using Microsoft Access (assuming you use Office). Access is Microsoft's database application. It supports very easy import of several data types. The design of the database and related tables is very flexible. You can basically do it however you'd like, including formatting, etc. Reports are pretty easy to create and generate, too. Lastly, there is a lot of support for the app so getting up and running is a breeze. It's a very quick, customizable and scalable solution for a relatively small collections dept/function.

We managed AR quite well just using QuickBooks (including the notes field). I suppose the issue relates more to consolidating AR collections across entities (i.e. multiple ERP systems). If I had to address this situation, I would first attempt to consolidate billing in a single ERP system.

Hi Anon
It's not evident how large your organization is (number of AR accounts to manage, staff size, nature of business, do you need to track agency collectors, etc.) or what your current Billing/AR software is. But let me offer some general hints to start:

1. what extra modules does your software vendor provide to enhance collections and payment tracking? Do they have 3rd party relationships that can be implemented alongside your current Billing/AR software?
2. can you tailor (not customize-which means changing code) your current Billing/AR software to allow you to add the following functionality:
2.1 assign collector names that you can use to sort and filter accounts by collector for monitoring and reporting
2.2 record all contact with AR customers-e.g. sent email Aug 1, made phone call Aug 3, got promise to pay $5k next Thursday, etc - this sometimes can be found in conjunction with CRM software, so that AR, Sales and Order Management all know what's going on with the account.
2.3 set triggers/alerts; e.g. next Friday remind me to check if the $5k was received and applied to the account
2.4 automate application of late fees, creation of dunning letters by degree of urgency....
2.5 do you need deduction/dispute/resolution tracking (e.g. where a write-off is agreed to, classify it by reason code...bad product, price dispute, delivered late...)

I'm curious why the Excel spreadsheets track payments made - can't that be done directly in the AR sub ledger and reported on?

And finally, what's the root cause behind the level of collections? Is there room for an improvement in upstream processes that could minimize collections effort?

In any event, before you start looking at solutions, look at your processes and figure out what they should be, how you need to operate in future; then document your key requirements and evaluate options accordingly.

Hope that helps
Len