Tip sheet from fellow PTA treasurers on reconciling WePay deposits

With the help of some local unit treasurers, we've provided this tip sheet for treasurers using MemberHub and our payment processor, WePay.

How PTA Treasurers can reconcile WePay deposits for certain versions of QuickBooks

If you use some versions of QuickBooks, the process is simple:

First, set up a WePay Receivables account in your Chart of Accounts. Categorize it as Income during setup.

Determine your batch close for your WePay configuration. For instance, we selected to receive weekly deposits on Mondays. We’ve found that payments processed through Saturday should be included in the batch. Verify the dates within your account and match to your settings.

Run your order report from your MemberHub store (Export CSV). Open in Excel and enable filters for all columns. 

1. Filter by category – column N. Categories should match your budget line items. If not, filter by item – column J. If you prefer not to use filters, you can sort by category/item.
2. Sum by selected category/item (column M) and document.
3. Repeat steps 1 & 2 until all categories/items in the batch are accounted for.

Access Settlement Reports from your WePay account. The settlement reports will give you the actual deposit amounts that will be transmitted to your bank account. Retrieve report for associated batch. 

Your deposits will then be set up as split deposits.

1. Click on Record Deposits.
2. Select the appropriate bank account in the Deposit To field.
3. Select the appropriate batch date or date of deposit.
4. In the From Account field, select the first deposit category (i.e., PTA Dues)
5. In the Amount field, input the expected amount (the amount before any reserves are withheld).
6. Repeat steps 4 & 5 for any other categories for which funds were received in this deposit.
7. As your last line item, use the WePay Receivables account in the From Account field.
8. In the Amount field, input a negative number equal to the shortfall of your deposit or a credit amount if your deposit amount exceeds your expected batch amount.
9. Verify that the Deposit Subtotal matches the amount you actually received (WePay settlement report).
10. Click Save & Close or Save & New.

The reserves being held and released each week can result in either a shortfall or surplus on any given week. When you receive deposits from WePay, any surplus will be entered as a positive against the WePay Receivables account, and any shortfalls will be entered as a negative against the WePay Receivables account. Ultimately, this account should be a net zero account once all reserves are released.

Shortfall example:  Your payers have purchased $1,242 of various items in a given batch. Your payers have paid the associated fees. Your PTA is expected to receive $1,242, but because of reserves being held and released, your bank statement says you’ve received $962.  You export your order report to Excel and get category/item totals for your batch period.

In Quickbooks, your deposit should look something like this:

Deposit To:  PTA Checking               Date:  August 31, 2018      Memo: Deposit  

                                                               Deposit Subtotal:                        $962.00       

Surplus example: Your payers have purchased $700 of various items in a given batch. Your payers have paid the associated fees. Your PTA is expecting to receive $700, but because of reserves being held and released, your PTA receives $980.  You export your order report to Excel and get category/item totals for your batch period.

In Quickbooks, your deposit should look something like this:

Deposit To:  PTA Checking               Date:  September 7, 2018  Memo: Deposit  

                                                                Deposit Subtotal:                        $980.00       

By following this method, the line items in your budget will accurately reflect what your payers have paid, and your bank balance will be accurate as you’ve accounted for the shortfall or surplus due to WePay reserves being held and released in the WePay Receivables account.