Problem:
If you have a business where people pay with credit card, you will need a payment processing provider. One of these providers is Worldline (https://worldline.com/). Each week (or month - depending on the setting) Worldline will send you the money for all the sales paid with credit card after deducting their fees (commission, transaction, terminal rental fee, etc.).
When the money arrives to your bank account, you usually book the net payment into a transfer or temporary account against your bank account. You should not book the paid-out amount directly into an income account since the paid amount is net of fees. You would need to add the fees to the transfer account and then allocated the “grossed up” balance to the different income accounts. This is important not only to reflect properly the income and expenses but also since you might be calculating incorrectly the amount of VAT you owed to the government. VAT is applied to the income and not to the income net of fees.
We need an efficient and automated (as much as possible) way to do that.
Solution:
As a Worldline customer you have access to a portal with all the transactions they process for you. If you do not have access, you would need to ask them for your login details. On the Worldline portal, go to Payouts (All Payouts), select the time period you would like to download, select your prefer file format (CSV or Excel) and download the file. Save the file on a folder specifically created to save these files. You will save future files on the same folder.
Using PowerBI (or PowerQuery in Excel) we convert the raw file from Worldline into a report where we can interact more easily with the data, get some insights, and helps us to do the bookkeeping more efficiently.
We start with this format:
We end up with this report after automating the process:
With the above report we achieve the following advantages:
1) We can easily select the period
2) We get a better overview of the data
3) We generate some insights (Commission % per month, quarter, or year)
4) We help the bookkeeping process by automating the bookings that need to be done
Each new month or quarter, we can just download a new file, save it on the same folder, hit refresh, and our report is updated.
We can achieve the above result either using PowerBI or Excel. I personally prefer to use PowerBI since it allows me to combine different data sources and create a data model. Being able to create a data model becomes relevant when we move to the next automation step: allocating the sales to the different income accounts.