This is why a charity shouldn’t use Excel for their bookkeeping:

I was asked by a charity to assist in getting their accounts up to date and get accurate reports for their trustees, which they have failed to do for some years. The charity also had a membership database which was managed in a google spreadsheet!

The problems they were facing was that they used to be on a desktop accounting system, which got corrupt and their backups were not checked and had failed for many months. So their accountants suggested they move to Xero, which they did do.

However, the person they had doing the bookkeeping had no idea about finance systems and how to set them up correctly to ensure the end result (management reporting) could be achieved. I talk about the right staff for the job in my book.

When they engaged with me, I soon realised their bookkeeping was at least six months behind. The banks (they had three different banks) had not been reconciled for three months, and much of what was reconciled was incorrect.

As you can imagine the membership payments are all the same amount and with over two thousand members, this could be very difficult to manage using google sheets.

The google sheets were being manually populated when the member renewed, however sometimes it was not easy to see who the money was from, so these were just ignored. Many members were moved to a cancelled sheet as they were assumed not paid, but in actual fact had paid. Because this process was so manual, the sheet was not kept current and of course many mistakes were made.

My first mission was to get their members into a CRM system that talks (integrates) to Xero. I chose Capsule for this for a number of reasons. Mainly because of its ease of use, price and it integrates with Xero, so anyone can see the current position of a member.

Capsule tracks emails and you can create notes about all communication or information about the member. The charity, as is the case with many charities, was strapped for cash, so Capsule and Xero were a good fit, affordable and workable.

Ideally you want a system that has little to no manual entering required, but again, these cost a lot more, so finding a solution that was affordable and workable was the important thing.

It took me a good three months, to work through this mess, get it cleaned up and current. For the first time in years, they had accurate financial reports for the trustees and were able to make informative decisions about how to spend the cash.

Another case of a charity that I helped convert to Xero from Excel. They came to me via a referral. The best way to get new business is via a referral, as no ‘hard sell’ is required!

They had a lady doing their books on Excel. The problem was, the bank was never reconciled, not all expenses were picked up, membership renewals were sent out months after the due date and the CEO was never able to report accurately to the board of trustees.

What I did, was set them up on Xero with bank feeds. They converted at their financial year end, so once the accountants had done the final accounts, I was able to bring on the opening balances. But they were already 8 months into the new year!

I had to manually import all the bank transactions for the backlog and started the feed from the current month. I then had to try and work out what was on the excel spreadsheet and what was in the bank and allocate it correctly according to their nominal code structure.

Sounds simple enough, which it would be if the Excel sheets were accurate! To cut a long story short, I got them up and running and current in less than 4 weeks and in time for their next trustee meeting.

For the first time in the CEO’s two years at the charity, he was able to clearly see which members had not paid, which ones had paid, and what their financial position was. Talk about running blind for years and now able to see!

I now act as their virtual bookkeeper and provide accurate monthly reports for them. At all times they know exactly what their restricted and unrestricted fund position is. They know exactly what members are paid up and who needs encouraging to pay up. Chasing membership is slightly different to chasing conventional debt as most of the time it is voluntary.

This again proved to me that Excel or Google Docs is not a good option for managing your bookkeeping and membership, no matter what size of business you are!