Analyzing the receivables accounts in SAP gives you an initial feel for how receivables are distributed. But it does not let you say anything in detail about the payment behavior of individual customers. For this reason, in this article, we will take a more in-depth look at individual customers and compare them with each other.
In our last blog post, we showed how it is possible to obtain an overview of all receivables accounts in SAP. If you missed this article entitled “Do customers always pay on time?“, we recommend that you read it first before going any further. It will give you a clear overview of all receivables accounts and the differences between the baseline date for due date calculation and the clearing date. In this post, the focus is directly on the customer. In addition to investigating the payment behavior of individual customers, we will create a graph in Excel that shows customers according to their average payment behavior.
Evaluation of payment behavior
Without data, it is not possible to visually represent or evaluate anything in graph form. We have therefore adjusted the SQL statements from the last blog post so that they can be used directly for customer evaluation. Since our aim is to make your life easier when you come to perform your next SAP audit, we are making the instructions on how to do this, including all the necessary SQL statements, available for download below:
First, we will take a closer look at some key statistics for your customers. Using the first SQL statement, we obtain the following table:
| BLART | KUNNR | HKONT | DOCUMENTS | AVG(DATEDIFF(‘DAY’, ZFBDT, AUGDT)) | MIN(DATEDIFF(‘DAY’, ZFBDT, AUGDT)) | MAX(DATEDIFF(‘DAY’, ZFBDT, AUGDT)) |
| RV | Billing doc.transfer | 140000 | 215 | 22 | 6 | 39 |
| AB | Accounting document | 140000 | 24 | 48 | 32 | 70 |
| AB | Accounting document | 196900 | 103 | 23 | -16 | 87 |
| AB | Accounting document | 196900 | 135 | 21 | 7 | 39 |
| … | … | … | … | … | … | … |
Table 1: Overview of payment behavior of your customers
To simplify the table, I have omitted the client, company code and fiscal year. The 7 columns can be explained as follows:
BLART: Document type
KUNNR: Customer Number
HKONT: General Ledger Account
DOCUMENTS: Amount of Documents
AVG: Average days between the baseline date for due date calculation and the clearing date
MIN: Minimum days between the baseline date for due date calculation and the clearing date
MAX: Maximum days between the baseline date for due date calculation and the clearing date
As already mentioned in the last article, special attention should be paid to customers with exceptional data. An average duration between the baseline date for due date calculation and clearing above a defined company limit of, for example, 30 days is already very suspicious and should definitely be questioned. The findings may give rise to the need for a liquidity audit, because no company wants to sit on its receivables, right? Examine why individual customers take so long to clear a receivable. Keep an eye on the number of documents at the same time, so as not to get lost in this analysis.
Depending on how many customers you have in the SAP system, the list may become long and confusing. In many cases, it makes sense to create an (XY) scatter chart in Excel and draw in the defined company limit. To do this, I use the first SQL statement and export the results to an Excel file. Then I use the column of average duration between the baseline date for due date calculation and the clearing as the Y-axis and the customer number as the X-axis. In general, an (XY) scatter chart is normally not used to illustrate this kind of data, but it clearly shows all the customers above a defined company limit. Looking for a way to draw the company’s 30-day limit onto the chart, I was a little surprised at what I found in Excel. After some research, it turned out that it is apparently not possible to draw a simple straight line in an Excel chart without creating a new column with the same constant value. In the end, I simply opted to draw in a line manually using Insert – Shapes – Lines. By clicking on the green “+” in the upper right-hand corner of the diagram, I have also added a data caption so that you can immediately see which customer numbers are over the 30-day limit on average.
Figure 1: Excel scatter chart for evaluating the payment behavior of customers in SAP
For a breakdown of the data into different clusters, we have also added a second SQL statement to the whitepaper. This divides the individual customers into the following clusters:
Clearing before baseline date for due date calculation (column LESS_THAN_0)
Clearing within 30 days of the due date calculation (column BETWEEN_0_30)
Clearing between 31 and 60 days after the baseline date for due date calculation (column BETWEEN_31_60)
Clearing between 61 and 90 days after the baseline date for due date calculation (column BETWEEN_61_90)
Clearing between 91 and 180 days after the baseline date for due date calculation (column BETWEEN_91_180)
Clearing after at least 181 days after the baseline date for due date calculation (column MORE_THAN_180)
Now it’s over to you again! We hope you enjoy the tutorial:
