Problem:
In Part 1 of this article, we automated the process of booking credit card commissions and fees and we generated a report that shows some insights about the fees that payment processing providers charge. The next step is to allocate the gross amount paid out by the credit card company and booked on the transfer account to the corresponding income accounts.
This step is not trivial since - depending on your business - booking correctly the income can have VAT implications. In addition, allocating the payouts accurately allows companies to better understand their performance and ultimately make better business decisions.
Solution:
Usually, companies record their sales on a booking software or a till (cash register). The software or till has the different products the company sells and stores each of the transactions they make.
The first step is to download the transactional data from the booking software or the till. You can do this step manually (every week or month) exporting and saving the transactional documents (CSV, excel, pdf, etc.) in a folder. A more efficient solution would be to automate this process so that at the end of each day, you get a file (CSV, excel, pdf, etc.) with all the transactions sent to your inbox. With a tool called Zapier (www.zapier.com) you can automatically take the document from the email and save it in a folder.
Once we have all the transactional files saved in a folder, we can use PowerQuery in Excel (or directly in PowerBI) to access the data, clean it up, and combine the file so that we get at the end of the process a nice table showing the date, the product sold, the quantity, the price, the payment method, etc.
As shown in the table above, we have sold different food and beverage items. For reporting purposes, in our accounting system we would like to book all the food items into one account and all the beverage items into another account. Hence, we need to find a way to automatically aggregate these items and allocate them into the right account.
To do that, we create an excel table with all the products and assign them a category and a booking account.
In PowerBI, we will create a data model and link the above tables. We will also add a dates table to be able to filter the dates by week, month, year, etc. The final step is to link the gross credit card payouts to our data model and allocate the payouts to the different accounts. To do that, we will also import the data from our credit card company into our data model. The data model in PowerBI looks as follows.
We can now check that for the selected period the total amount paid out by the credit card company matches the amount recorded on the booking software or till. We can then allocate the sales to the different products, categories, and accounts. Finally, we can show the bookings entries needed to book the income into the accounting software. We can achieve this with some basic DAX measures in PowerBI and the report would look like this:
The report shows clearly:
1) The amount paid out by the credit card company (for the selected period), the transactions recorded in the booking software or till for the same period, and a check to easily identify if there is a difference between both.
2) The sales amount split by Product, Category and Account, as well as some insights (for example, the percentage of total sales)
3) The bookings that are needed on the accounting software to record the income to the respective accounts.
Each new week, month, or quarter, we can just
download a new file (or get it automatically per email), hit refresh (or schedule
an automatic refresh), and our report will be updated.