Reconciling bank statements with club sales

Background

This would be simple if it were the case that every individual sale the club makes were recorded as a specific credit transfer to the bank account. For example, a member could transfer funds for the purchase of a subscription. Or they could pay by cheque. Or they could pay by cash and you could prepare a pay-in slip which you could present to the bank with the cash. Labour intensive, but workable.

But this model would not work so well for court fees for example. It is impractical (of course) for both the club and member to pay every court fee individually and separately into the bank so that you can add up the court fees from the bank statement.

To counter this, court fees are typically taken from a "pot" that is kept for each member whereby each credit to the pot would be a larger amount and it is these credits that would be reflected in the bank account that could be totted up to get the total court fees. Even this totting up is no longer accurate at this point - a member may have paid for lots of courts this month because they had credited their balance last month. Or they may credit their balance this month and not spend it until next month.

MMM environment

MMM takes this concept much further. It partners with ePosNow to maintain a credit account for all members that can be spent for any purpose. Indeed, for maximum efficiency and convenience, it would be best for members to pay for all their services from the club using their credit balance. Subscriptions, events, coaching programmes, courts, bar sales, replacement fobs etc.

So while it could be the case that a member credits their account with, say, £50 and this is reflected as a credit item in the clubs bank statement we can't be sure how that £50 will be spent. It's just £50 on account - money yet to be spent.

But with modern payment providers like Stripe and GoCardless we lose even more transparency on what each credit in the bank statement consists of sales-wise. They take every £50 (or £25 or £100) that all members have paid in for a day, add them up and transfer the total to the bank account. They also introduce a delay; £50 today won't be batched up into today's bank transfer - it'll be batched up into a transfer that will be made in a few days time. Indeed, the transfers may not even be made daily. In fact, at this point, you are better off batching bank payments up at least weekly or even monthly to minimise the clutter on your bank statement.

The end result is that it is practically impossible to break down your bank transactions into their constituent parts in terms of what was bought.

So far as MMM is concerned, everything gets paid out of a members account. ePosNow provides a far better vehicle to answering questions like "How much was spent on court fees last month?" than a bank statement could ever do anyway. So sales analysis is all performed in ePosNow; We use ePosNow to report on what sales the club has made during any particular period. We cannot use the bank statement.

But we still have to reconcile the bank statement. And this can be done on a per-payment-provider basis quite easily. This process consists of two stages:

1. We need to be satisfied that payments made via, say, Stripe are accurately reflected as credits in ePosNow
2. We need to be satisfied that payments made into the bank account accurately reflect those recorded in Stripe

Once we are happy that what's in the bank account reflects what has been added into ePosNow, we can focus on ePosNow to provide the goodness of sales management reporting.

The mechanics of achieving this depends on the payment provider so we shall outline the process for each that MMM employs:

Stripe

To perform this reconciliation you will require access to:

• ePosNow back office
• Your Stripe dashboard
• Your bank account

ePosNow vs Stripe

We can see a total of all Stripe payments made by members via MMM in ePosNow by selecting the Tenders sub-menu of the Banking main menu option in the ePosNow back office dashboard. There is a tender type called Stripe and, on selecting your period of choice on the same screen, you can see the total payments made by members via Stripe.

This total can be reconciled to Stripe by logging into their dashboard and selecting the Payments tab and exporting a report with the relevant date range. The total of the Amount column should match the total found in ePosNow with the following caveats:

• When totalling the Stripe amounts in their export file, be sure to filter only "Paid" items - otherwise the report includes failed payments that never made it into Stripe
• Any further difference is a net of fees that ManageMyMatch has had to apply in its "balancing out" process. This is more fully explained here, but essentially, due to the ability for members to move their club balance funds between clubs, or even extract funds back to their bank account, ManageMyMatch has to make adjusting entries to ensure club funds are properly reflected which includes adjusting for any fees that should or should not have been paid.

We now know that the money paid by members via Stripe has been correctly reflected in the account balances of those members.

Stripe vs Bank account

In one sense this reconciliation is a little harder. But in another sense it is all within the confines of the multi-billion dollar operation that is Stripe and if they get this wrong then what hope is there? The good news is they produce a report that specifically addresses this very issue of understanding how customer payments end up as bank payouts. This report can be found in the Business settings area of their dashboard under sub-menu Data. It is called Monthly report and you'll need to click that to get the downloaded report. It is beyond the scope of this article to fully explain this report and more detail can be found here (TBC).

This report totals both payments (from members) and payouts (to your bank account). Itemised reports for both can also be displayed and/or downloaded in the Payments and Balance areas of their dashboard. The report also details the fees that have been deducted from the payments prior to the payouts to your bank. The fees aggregate both the MMM application fee and the Stripe fee. However, MMM provide a monthly fee statement so that subtracting this from the fees in Stripe will give the Stripe processing fee total alone.

GoCardless

Coming soon ...