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: =(Cell above)+[@In]-[@Out]

enter image description here

To clarify, I think we should have £768.22 in our accounts, but we actually have £820.68.

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?

  • As has been suggested, we don't have enough info to completely solve (eg formulas, complete data) - but I note that the txn list you show has 10.50 IN as non-cash (presumably paypal?) and 0.21 OUT the same, but the Paypal column in the summary has 10.21, which isn't 10.50 - 0.21...
    – AakashM
    Jan 12 '18 at 9:26
  • Thanks, yeah that was mostly my hasty copying. I've fixed that mistake. Jan 12 '18 at 9:47
  • please provide the few lines before the one for 10.50, that way we can see that you added the $10.50 into the total column. Jan 12 '18 at 11:16
  • I've added that @mhoran_psprep Jan 12 '18 at 11:32
  • Are all the cells entered in the same way, eg 0.24 meaning £0.24 and then formatted as currency, or is it possible you have a mixture of things like £0.24 entered as text etc.? It's a little hard to tell from this data but Excel can do hidden weird things if you rely too much on formatting. What happens if you make all the cells just be "general" format and make sure they are all truly numbers not text etc.?
    – Vicky
    Jan 12 '18 at 12:01

I don't see how we can possibly answer this with the information you have given. However, in your shoes, I would start a new ledger sheet, using all the same formulas etc. Open the sheet with a consistent amount, in other words ignore the old discrepancy and pretend it didn't happen for the moment.

In a couple of weeks you will hopefully have a small-ish number of transactions, and either more discrepancy (and a limited scope in which to search for it) or no discrepancy (in which case you don't really have an issue other than forgetting to record transactions.)

In the meantime, do the bank account, Paypal or Stripe provide you with a statement of transactions? Can you match those up against your ledger?

  • This is what I did.. I temporarily ignored the discrepancy, and during yesterday's transactions (the ones I posted), the discrepancy grew £10.50, giving me just 9 transactions to investigate. The accounts do have a statement of transactions I can view; this alongside my own register, is how I created the ledger initially. Jan 12 '18 at 9:16
  • I realise there is probably some information missing.. it's where that information might be that I can't deduce. Jan 12 '18 at 9:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.