How to Reconcile Payment Processor Backend to TUIO
[note: as of September 30th, 2020, the content of this article are not up to date as we have released new functionalities in our reporting eliminating the need for making adjustments or reconciling with payment processor backend altogether. This article will be updated or replaced as required once we finalize the new functionalities some time in October 2020]
If you are looking to reconcile your TUIO Invoice Data Export to the data on your Payment Backend Account Statement, there are a few things you should know in order to eliminate and understand any potential discrepancy.
Before we start, it is important to set the record straight: any reconciliation process for bookkeeping purposes involves deciding which source will prevail in case of a mismatch.
In our case, it's important to understand that the source of 'truth' should be the payment processor backend. Sure, TUIO issues and time stamps invoices for both issuance and transaction reconciliation purposes; however, the payment processor backend is the entity processing payments, applying transaction fees, paying out deposits and transmitting in turn that data to TUIO. It is therefore the source of last resort if one must be chosen.
Let's now turn to the 2 kinds of potential discrepancies and how to address them.
1. The 2 Kinds of Potential Discrepancies
- The 1st kind of potential discrepancy is due to dates differences between TUIO and the payment processor backend in recognizing a transaction confirmed or an invoice paid (more on this below).
- The 2nd kind of potential discrepancy arises when returns (for bank account transactions) or chargebacks (for credit cards) take place and the invoice is subsequently reprocessed (as is often the case in order for the parents to correct the situation). The discrepancy specifically occurs because the transaction fee is charged by the processor every time the transaction is re-processed, while the TUIO Invoice Export Data only shows the latest fee charged on the invoice (and not the cumulative fees if applicable). These discrepancies are usually minimal but it is important to understand them if one wants to reconcile the data to the nearest cent.
2. How to Adjust for the Discrepancies
a. For Credit Cards Transactions
Let's say that you are pulling the payment processor backend Account Statement for the month of January (Jan 1st to Jan 31st) for your credit card transactions. These dates correspond to what the payment processor backend considers 'settlement' (confirmed) dates, which are usually 1 day after the transactions have been processed.
However, on TUIO, the 'Paid Date' is the date when the transaction was processed (so 1 day PRIOR to the settlement date).
Therefore, the way to align the data from both systems is as follows:
- Pull the payment processor backend Account Statement for dates Jan 1st to Jan 31st (make sure to check the box 'show summary' as it will come in handy later)
- Pull your TUIO Invoice Data Export with the following filters: 'Paid Date': December 31st to January 30th (therefore offsetting the date discrepancy); Payment method: Credit card.
From here, the reconciliation becomes fairly straightforward:
- On the payment processor backend (Summary section at the top), the 'Settlements' figure minus the 'Credits' figure (if any), minus the 'Chargebacks' figure (if any), should equal the sum of the 'Invoice Total' column on TUIO. Therefore, Settlements (PPB) - Credits (PPB) - Chargebacks (PPB) = Invoice Total (TUIO).
- On the payment processor backend (Summary section at the top), the 'Splitpay Out' figure minus the 'Splitcredit Out' should equal the sum of the 'Transaction Fees' column on TUIO, unless you have had Chargebacks, in which case the figure on the payment processor backend will be higher (and is the correct one).
- On the payment processor backend (Summary section at the top), the 'EFT Sent' figure minus the 'Overdraft EFT Funding' figure (if any), should equal the sum of the 'Paid to You' column on TUIO, unless you have had Chargebacks, in which case there will be a delta equal to the difference in transaction fees between TUIO and the payment processor backend. Therefore, EFT Sent (PPB) - Overdraft EFT Funding (PPB) + Delta in transaction fee (if any) + Balance in PPB (if any) = Paid to You (TUIO). The balance held in PPB are the funds that were not yet set to your account at the date of the report, and which therefore would not be included in 'EFT Sent'. It is the 2nd column from the right on the Account Statement PPB screen.
b. For Direct Debit (Bank Account) Transactions
Let's say that you are pulling the payment processor backend Account Statement for the month of January (Jan 1st to Jan 31st) for your bank account transactions. These dates correspond to what the payment processor backend considers 'presentment' (processed) dates, which are the dates the transactions are initiated. For bank account transactions, the payment processor backend usually assumes that the transaction is confirmed when processed, unless it is 'returned' for any reason over the next 3-5 days after that (at which points, it is declared returned/failed).
For that reason, and to avoid any confusion with parents due to invoices switching statuses, TUIO waits 3 days to confirm as paid an invoice processed by bank account, assuming that no return notification has been received in the meantime. The 'Paid Date' on TUIO is thus 3 days AFTER the date when the transaction was processed on the payment processor backend.
Therefore, the way to align the data from both systems is as follows:
- Pull the payment processor backend Account Statement for dates Jan 1st to Jan 31st (make sure to check the box 'show summary' as it will come in handy later)
- Pull your TUIO Invoice Data Export with the following filters: 'Paid Date': January 4th to February 3th (therefore offsetting the date discrepancy); Payment method: Bank account.
From here, the reconciliation becomes fairly straightforward:
- On the payment processor backend (Summary section at the top), the 'Presentments' figure minus the 'Returns' figure (if any), minus the 'Credits' figure (if any) should equal the sum of the 'Invoice Total' column on TUIO. Therfore, Presentments (PPB) - Returns (PPB) - Credits (PPB) = Invoice Total (TUIO).
- On the payment processor backend (Summary section at the top), the 'Splitpay Out' figure minus the 'Splitcredit Out' should equal the sum of the 'Transaction Fees' column on TUIO, unless you have had Returns, in which case the figure on the payment processor backend will be higher (and is the correct one).
- On the payment processor backend (Summary section at the top), the 'EFT Sent' figure minus the 'Overdraft EFT Funding' figure (if any), should equal the sum of the 'Paid to You' column on TUIO, unless you have had Returns, in which case there will be a small delta equal to the difference in transaction fees between TUIO and the payment processor backend. Therefore, EFT Sent (PPB) - Overdraft EFT Funding (PPB) + Delta in transaction fee (if any) + Balance in PPB (if any) = Paid to You (TUIO). The balance held in PPB are the funds that were not yet set to your account at the date of the report, and which therefore would not be included in 'EFT Sent'. It is the 2nd column from the right on the Account Statement PPB screen.
Main Takeaways
The good news is that, regardless of whether you decide to follow TUIO or the payment processor backend for your bookkeeping reconciliation, any dates discrepancy will eliminate itself in the long run and the only (usually small) discrepancy you may want to adjust (if applicable) for in your bookkeeping is the transaction fee figure, which you can match to the payment processor backend's Splitpay Out figure (net of any Splitcredit Out as applicable - Splitcredit Out are simply refunds of transaction fees).
And as always, if there is something you cannot make sense of, feel free to reach out to our support team and we will help you figure things out.