Accounts receivable reporting in the BI system
Controlling: Accounts Receivable Reporting in Tableau
Accounts Receivable reporting is an essential part of controlling cash flow in a business. We present an example of a management dashboard in Tableau that allows you to control these receivables at various levels.
The dashboard includes information on the volume and ageing of receivables. It provides the ability to filter by department, customer or salesperson responsible for sales to the customer. It also provides summary information on amounts, average overdue, interest volume or average forward payment volume in relation to monthly sales.
Integration of Tableau with the ERP system
The data source is the Comarch ERP system running on an MS SQL database. Most ERP systems have similar data structures, so it should be easy to modify and connect to any other system. The report is based on a ready-made view PreliminarzView, which contains information about outstanding receivables/payables. We include the following in this view:
- transaction header table (information on issued order/invoice documents)
- counterparty card table (information about the counterparty)
- employee card table (information about the employee responsible for the order/invoice)
The fields that link the various tables can be found in the documentation for the ERP system’s database. In some cases, the keys can be defined in the database, in which case Tableau will suggest the selection of the relevant fields.
The final definition (in the case of Comarch ERP) is shown in the following figure.
For detailed instructions on how to connect Tableau to Comarch ERP, please watch our webinar.
Control of overdue and ageing of receivables
The primary measure in the report is the outstanding amount. In order to obtain information on the overdue and specific ageing ranges, we need to create two additional measures. Overdue receivables measured in days are calculated based on the difference in the payment date and the current date of report generation.
The age ranges are defined using an ‘if then else’ expression and a previously created past due measure. By changing the conditions, you can choose any values for the intervals.
Individual customer payment timeliness
The main element of the dashboard is a view of customers, sorted by amount of debt, with the ageing of receivables for each customer listed. The view provides a quick overview of what the payment situation is for a particular customer. In the example below, you can clearly see that there is a problem with the largest debtor, whose payments are the most overdue, while the next customer on the list pays on time (the white items are payments that are on time).
Selecting a particular client (or clients) allows us to view individual documents in detail.
Ageing ranges for receivables
The dashboard also includes a treemap view of all payments with ageing ranges. This type of visualisation allows a very large amount of information to be displayed, e.g. 30,000 documents. At the same time, we have the ability to track the largest payments and compare them to the total amount owed. This is a sensible alternative to a pie chart, which is very difficult to read. Selecting a customer on the chart highlights their documents.
Thanks to the visual presentation of payment information, we are able to quickly assess the situation and payment structure for the entire company or for a single customer. Such a solution allows us to identify in time the elements we need to pay attention to (e.g. customers who are always late with their payments), and we can also move from general information to the details of a single document. For more information on the application of Business Intelligence solutions in finance and controlling, see the recording of our webinar.
The construction of the presented dashboard took about 2 hours, most of which was spent on data preparation, especially in finding the right tables and boxes. At the top of the page, you will find a fully functional report on Tableau Public.