more-arw search

Q&A Forum

Speadsheet or Software Options for Managing Accounts Receivable

accounts receivable spreadsheet vs softwareDoes anyone have a collections spreadsheet in excel - my collections team currently has a spreadsheet in excel but I need to fix it where we can upload new data into the spreadsheet each week (to account for payments received, new collections going over a time range: 60,90,120 days) but for the on-going collections, keep the notes from previous weeks.  We have a fairly sizable collections list across different entities but need to be able to update this from a download from our accounting software each week or every two weeks.

Or does anyone use a software where you can update each week/two weeks from a text file download from the accounting software package we use?



Topic Expert
Mike Caruana
Title: Director of Financial Services
Company: Diamond Resorts International
(Director of Financial Services, Diamond Resorts International) |

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.

Topic Expert
Bob Stenz
Title: Controller
Company: Silicon Valley start-up
(Controller, Silicon Valley start-up) |

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.

Len Green
Title: Performance Improvement Consultant and E..
Company: Haygarth Consulting LLC
LinkedIn Profile
(Performance Improvement Consultant and ERP Strategist, Haygarth Consulting LLC) |

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

Andrea Bornschlegel
Title: Fractional CFO/Controller
Company: Financial Consultant
(Fractional CFO/Controller, Financial Consultant) |

I interpreted your question much more simply than some of the above answers (great answers; perhaps not what you were looking for) -- so simply that you might have tried and discarded this, but here goes:

It sounds like you can easily download from your accounting software, but for process or system reasons your notes are in a prior excel spreadsheet. Try using vlookup on a unique identifier (e.g. invoice number) to pull the old notes/information onto the new spreadsheet. Instead of updating your ongoing spreadsheet with payments etc., you pull the information which was added manually into the new spreadsheet. [If you do not have a unique identifier, you can probably build one by concatenating two or more fields.]


Get Free Membership

By signing up, you will receive emails from Proformative regarding Proformative programs, events, community news and activity. You can withdraw your consent at any time. Contact Us.

Business Exchange

Browse the Business Exchange to find information, resources and peer reviews to help you select the right solution for your business.

Learn more

Contribute to Community

If you’re interested in learning more about contributing to your Proformative community, we have many ways for you to get involved. Please email [email protected] to learn more about becoming a speaker or contributing to the blogs/Q&A Forum.