Bank Reconciliation & Accounting Program

[DickyP]

Thank you DickyP your comments and your link which I will discuss with my boss.

Whilst I recommend the MM Ex application don't forget in any evaluation the elephant in the room - data transfer from old to new.. Not knowing the data structure of your current system I wouldn't know, but it is easy to manipulate SQLite which MM Ex uses and import from most SQL Databases. MM Ex itself has a number of data import tools MM Ex Import / Export

 

[gemma-the-husky]

"A bank rec is the last thing I would bother about" was purely in terms of adding it as database functionality.

Manual records.

I would go further. Too often, I have seen a lack of control with figures recorded in a database, or a proprietary accounting system such as Sage. Users think a computer system generates the right answer, but sometimes don't have external procedures to "prove" that all of the data is in the computer. So i think there should be appropriate manual records, paper or more likely spreadsheet, but outside, and additional to the computer records.

Take donations for instance, possibly cash, possibly standing orders or BACS receipts, maybe even Direct Debits. . Not only would I record the receipts in a general ledger (Dr Bank, Cr Sales/Donations, or a more detailed variant thereof), I would also more than likely keep a manual record outside the accounts system (again, paper, or spreadsheet) summarising those donations. For instance if you want to recover Gift Aid on the donations, it's vital that you can demonstrate that the records you use to claim the Gift aid can be reconciled with the accounts system, and show that the donations recorded in the accounts match the donations on which you recovered Gift Aid. This is for your own benefit so that you know you have actually claimed the correct amount of Gift Aid. It's not a given, as the accounts, and the Gif Aid claims may represent different periods, and not all donations can be Gift Aoded.

I do have a database that stores donations, and automatically fills the HMRC Gift Aid claim spreadsheet to claim the Gift Aid, splitting the claim by tax year, and limiting each claim to 1000 donors - so a single process does the entire claim. It's certainly far easier when all the donations are in a database. But I still like to see a cross check of some sort to verify that the donations stored in the database agree with the cash books/accounts.

I would want to do the same sort of exercise with sales receipts, with a charity that operates their own charity shops.

 

[AngelSpeaks]

Has your charity considered using QuickBooks? Two non profits that have worked with my special needs child used QuickBooks Accounting. Each bank account has it's own Bank Reconciliation.

 

[Pat Hartman]

An automatic reconciliation starts with your bank statement. How are you importing that now? are you getting a .cvs or .xlsx file from the bank or are you using a OCR product to scan a pdf and convert it into an .xlsx?

Also, why would you be doing this in Access rather than your accounting program? Does your accounting program not have the facility to import a file from your bank?

 

[access2010]

I obviously misunderstood your comment about BS reconciliation being unnecessary: and by definition I don't expect to do a BSR in code. The whole point is that it is an exercise to be aware of your situation.



As to my dislike of keeping accounts on a spreadsheet it comes from regularly taking over accounts run on a spreadsheet and without exception the spreadsheets have been wrong, and taken unnecessary time to get the accounts they purport to represent straight.

Thank you for your comments, gemma-the-husky which which is another reason we want a Bank REc Program.

 

[access2010]

If you want a bank rec module, you would need a table with all the items from the bank statement, and another table with all the items in your cash book. You then cross reference all the items on the bank statement to the cash book, inserting any items on the bank statement into the cash book where they don't exist, such as bank charges. You should then be able to identify reconciling items in the CB - lodgements not credited and unpresented cheques. However matching bank statements to cash book records is most likely non trivial for a computer programme, as many of the references are unlikely to correspond exactly, and it will take longer to do it in a database than it would just putting a "cleared" indicator in a CB (paper or spreadsheet).

You are also creating both a bank statement table that you don't really need, and a cash book table that you may not need either, as you can probably work from an analysed cash book or spreadsheet (which is why I do like to use a spreadsheet). It's probably easier to tick off the items in the CB/spreadsheet against the BS, and then anything unticked is a reconciling item. (Or maybe a CB error) If references don't match exactly you can generally see which item jn the CB corresponds to the BS entry. If you can't download statements from the bank you would have to key them into the database manually. If you can download them, you still have to write an import process, and make sure that you don't duplicate/omit any.

You could do the CB in an access table, but it's extra work. If @DickyP isn't keen on seeing a CB spreadsheet I imagine he would be even less keen on doing it in a database. I've never seen the need personally. This is one area where I do find it useful to see a running total in a table.

@access2010 . It all goes back to the size of your charity, the records you need to keep, and the accounts you need to produce. In general terms I would strongly be advising you against trying to do automatic bank recs, unless you really had to. I genuinely just don't see the need or the value. I think it really starts from considering your cash book though, and how large/complex a document that is.

Thank you gemma-the-husky for your comments. We fo need a separate bank account for each of the Endowment funds, and with a Database we will be able to print a report for any request made to us.

 

[access2010]

As I've agreed all along this is not a task for an Access process - my initial input came from gemma's initial assertion- "A bank rec is the last thing I would bother about" - that any such reconciliation wasn't worthwhile.

Thank you DickyP for your comments and we will have losts to discuss this weekend.

 

[Pat Hartman]

@access2010 how do you import your bank statement now? Does your accounting package really not have a reconciliation function?

 

[gemma-the-husky]

Thank you gemma-the-husky for your comments. We fo need a separate bank account for each of the Endowment funds, and with a Database we will be able to print a report for any request made to us.

I am not disputing that. I am just surprised that you see a bank rec module as the first priority. How many transactions do you have per account in an average month?