b. How to Prepare your Books and Reconcile your Bank Statement with the TUIO Export
Your TUIO data export contains 2 tabs:
- Your invoices
- The transaction details for any given invoice
This second tab comes in handy when several transactions have taken place on an invoice (for instance, a payment followed by a refund) since it will allow you to audit the sequence of events that took place.
The first step is to apply a filter on the column headers of the export - this will let you filter the data by whatever parameter you want: program, student, payment method, invoice status, and so on.
Helpful Things You Can Do with the Export
Determine your Revenue, Transaction Fees Expense, and Revenue Net of Fees (Net Payouts):
As an example, to determine your cash revenue and transaction expenses for a given time period, follow these steps:
- Download your TUIO Invoice Export (see how here) by applying a filter for 'Paid Date' for a given period (e.g. Sept. 1 - Sept. 30th).
- Once you have the spreadsheet, sum up the 'Transaction Fee' column.
- If you are located in Canada, this column includes sales tax, the detail of which is given in the 'Transaction Fee Tax' column. To be clear, the amounts in the 'Transaction Fee' column are inclusive of sales tax. The column showing the transaction fees before tax is the 'Transaction Fee Subtotal' column. Your accountant will know what figure to use depending on whether you are eligible for a nil, partial, or full refund of the sales tax. US-based users can ignore this comment altogether as they are not subject to Canadian sales tax.
- Next, to compute your revenue, put a filter on the 'Invoice Status' column in order to exclude any Refunded invoices. Once that is done, you can sum up the 'Invoice Total' column, which will give you your revenue.
- Finally, to compute cash flow/payouts, sum up the 'Net Payout' column for a given period. This column simply calculates your Revenue minus Transaction Fees. By summing it up, you can therefore easily figure out how much revenue you earned net of fees (gross profit/cash flow).
Note that the 'Invoice Total' column includes any Parent Service Fee you may charge to parents using a given payment method (e.g. credit cards). Similarly, the 'Transaction Fee' column includes the service fee paid by the parent. Altogether, the consequence is that both your revenue and expenses will be a bit higher than what they would be if you did not charge any service fee. From a books perspective, this is a wash (no impact to bottom line), but it is important for you as an operator to understand that a portion of the Transaction Fees shown in that column are not actually paid by you but by the parents. To determine the true cost to your organization, simply sum up the Transaction Fees column and deduct from it the sum of the Parent Service Fee column.
Example: an administrator determines that over the last 12 months, the sum of the Transaction Fees column is $1,000. However, the school charges parents a service fee to pay by credit card, and the sum of the Parent Service Fee column is $700 for the year. Therefore, the actual true cost to the school for the year is $1,000 - $700 = $300.
Determine if an Invoice Was Deposited to your Account and When:
The last 4 columns of the TUIO Export contain information that will help you determine if and when a given invoice was paid out to your account, which helps greatly with any reconciliation you might want to do with your bank account. These columns indicate whether the payout for the invoice was already deposited, the deposit reference number, the deposit amount and the date the deposit was sent. This data updates automatically from the payment processor, and allows you to easily identify and recognize the flow of funds to your account.
Key things to know and understand when it comes to deposit:
- If the Deposit Reference, Deposit Amount and Deposit Sent Date columns are empty, it means that as far as our system knows, the funds have not yet been sent - there could be a brief delay for this data to flow to us from the processor. Typically, in those cases, the Deposit column will say 'No', meaning the funds are not deposited.
- When the funds are deposited, all columns will be filled with data. The 'Deposit Reference' column can be used to group invoices that were paid in the same deposit batch.
- For example, if 10 invoices with net payouts of $1,000 each show the same Deposit Reference, they will have been deposited in the same batch. Because they will be part of the same deposit, the Deposit Amount will be the same for all of them (in this case, the deposit amount column will show $10,000 since the deposit is a batch of 10 invoices with a Net Payout of $1,000 each). Similarly, the Deposit Sent Date will also be the same for all these 10 invoices. Therefore, to wrap up with this example, if the Deposit Sent Date was Sept. 1, all you would need to do from here would be to review your bank account statement and look for a deposit of $10,000 on or shortly after Sept. 1. You would know then that this is the deposit containing the payouts for the 10 invoices you had been looking to reconcile.
Audit the Transactions of a Given Invoice:
One of the columns of your export is the Transaction IDs column. More often than not, this column will only show one ID because most invoices are paid with a single transaction. But in other instances, you may see several IDs in this column, meaning that several transactions happened to get the invoice to the state where it currently is (i.e. Paid, Refunded, etc.).
In order to see what these transactions were, you can navigate to the Transaction Details tab of the spreadsheet, and look up the invoice concerned in order to see what transactions happened and how it impacted the final payout.