I run a sports club where I accept payments via three methods: in cash, via a PayPal card reader and online via my website.
I'd been keeping track of the attendees and how much they paid in a register, but thought it best to transfer this to a spreadsheet and keep a ledger of every transaction. When I did this however, comparing the ledger to the total amount we actually have, there is a discrepancy. Yesterday, I discovered the discrepancy grew by about £10 which prompted me to investigate more.
I discovered that the total discrepancy is a little too close to the value of non-cash transactions I've handled to be coincidence so I believe I'm counting them twice (or zero times - cancelling out somewhere, I can't figure it out).
There are four places that money can be stored: in the bank, cash on hand, PayPal and Stripe. Occasionally the money is transferred from PayPal and Stripe to the bank account, however I don't record anything in the ledger for this since it doesn't represent money in or out, just money moved. I also don't record when I put cash in the bank as again it's just money moved.
As an example, here is an extract of the ledger from yesterday and the summary of accounts as they stand.
Date In Out Reference Non-cash Total 09/01/2018 £6.00 `name removed` £749.46 09/01/2018 £6.00 `name removed` £755.46 09/01/2018 £25.00 `name` Membership £780.46 09/01/2018 £25.00 `name` Membership £805.46 10/01/2018 £97.50 Competition Entry £707.96 11/01/2018 £10.50 `name removed` TRUE £718.46 11/01/2018 £6.00 `name removed` £724.46 11/01/2018 £9.00 `name removed` £733.46 11/01/2018 £8.50 `name removed` £741.96 11/01/2018 £8.50 `name removed` £750.46 11/01/2018 £6.00 `name removed` £756.46 11/01/2018 £6.00 `name removed` £762.46 11/01/2018 £6.00 `name removed` £768.46 11/01/2018 £0.24 Transaction Fees TRUE £768.22
The formula in the total column is:
To clarify, I think we should have
£768.22 in our accounts, but we actually have
I can't find where I might be miscounting these transactions. Can anybody see it please? What information might be missing that could help me find the underlying mistake?