In today’s blog post, we are going to take a look at how to identify “dubious” posting structures within SAP’s financial accounting. I will show you how to quickly find posting structures in SAP and how to know which ones you should perhaps take a closer look at, because they do not meet the usual expectations!
What is a “dubious” posting structure?
Okay, I admit, the word “dubious” sounds somewhat casual and may seem a bit too vague at first to be a notion that you can really work with properly. But let me start by explaining the approach.
Since the days of Luca Pacioli, it has been well known to humanity that making a posting in financial accounting consists of posting lines to accounts (for example, in debit) and offsetting accounts (for example, in credit), whereby the posted amounts must always correspond in debit and credit. From an audit perspective, this is probably one of the first and perhaps the most important internal controls when it comes to accounting.
As a rule, the auditor often is sufficiently familiar with accounting that he can always imagine what usual offsetting accounts will be when he is looking at a particular financial accounting account in which he is particularly interested. This means that there are typical offsetting accounts for each account. If an untypical offsetting account has been posted, then we are going to refer to this as “dubious” in what follows and it should then serve as reason for us to drill down deeper.
So ultimately this is about an analysis of offsetting accounts.
First form an expectation
We will first illustrate the formation of expectations using two concrete examples.
In our test data set, there are two accounts in which we are particularly interested:
- 0000800000 Sales revenue
- 0000113100 Deutsche Bank
Sales revenues are mainly posted to credit, as these are revenues. As offsetting accounts on the debit side, of course, we would expect to have receivables, although we might still have to contend with sales deductions. We would not expect any offsetting accounts of a different nature at first.
With the Deutsche Bank account, we want to look at withdrawals, i.e. we are interested in credit postings. Offsetting accounts that we would expect on the debit side are, for example, liabilities, or taxes paid perhaps, and transfers from other bank accounts.
Revenue analysis
This is where things start to get really interesting!
Once we have selected the two accounts, we want to find out what the offsetting accounts are on the debit side for the sales revenue on account “0000800000”. The following SQL Query is used to do this. To execute the query, use transaction “DBACOCKPIT” in SAP and navigate in the lefthand directory to “Diagnostics – SQL Editor” (tested on a HANA DB):
| 1 | SELECT CONTRA.HKONT ACCOUNT, SKAT.TXT50 ACCOUNT_NAME, CONTRA.SHKZG DEBIT_OR_CREDIT, COUNT(DISTINCT CONTRA.BELNR) NUMBER_OF_DOCUMENTS FROM BSEG | The number and name of the offsetting accounts, debit or credit indicator (SHKZG) and the amount of documents the offsetting account is related in. |
JOIN BKPF ON (BSEG.MANDT=BKPF.MANDT AND BSEG.BUKRS=BKPF.BUKRS AND BSEG.GJAHR=BKPF.GJAHR AND BSEG.BELNR=BKPF.BELNR) | Join on table accounting document header | |
JOIN BSEG CONTRA ON (BSEG.MANDT=CONTRA.MANDT AND BSEG.BUKRS=CONTRA.BUKRS AND BSEG.GJAHR=CONTRA.GJAHR AND BSEG.BELNR=CONTRA.BELNR AND BSEG.SHKZG!=CONTRA.SHKZG) | Join on corresponding line items If revenue is in credit, the corresponding line item is in debit. “!= means unlike” | |
LEFT JOIN T001 ON (T001.MANDT=CONTRA.MANDT AND T001.BUKRS=CONTRA.BUKRS) | Join on table account master record and company code details | |
LEFT JOIN SKAT ON (T001.MANDT=SKAT.MANDT AND T001.KTOPL=SKAT.KTOPL AND SKAT.SAKNR=CONTRA.HKONT AND SKAT.SPRAS='E') | ||
WHERE BSEG.HKONT='0000800000' AND BSEG.SHKZG='H' AND BKPF.STBLG = '' AND BSEG.GJAHR=1997 AND BSEG.BUKRS='1000' | The account of interest: Sales revenues in debit; Only none reversed documents; company code and year of choice | |
GROUP BY CONTRA.HKONT, CONTRA.HKONT, CONTRA.SHKZG, SKAT.TXT50; | Aggregation by Account |
(Further below you can easily copy the query at the Deutsche Bank analysis)
If you want to try this out for yourself, then you must of course insert your own financial accounting account and your own side of the accounts (S = debit or H = credit) in the “WHERE” condition accordingly:
WHERE BSEG.HKONT='0000113100' AND BSEG.SHKZG='H'
In addition, you must adapt the query with both your own company code and the fiscal year you wish to consider:
BSEG.GJAHR=1997 AND BSEG.BUKRS='1000'
In our test data set, the following offsetting accounts for the sales revenue come up:
| ACCOUNT | ACCOUNT_NAME | DEBIT_OR_ CREDIT | NUMBER_OF_ DOCUMENTS |
|---|---|---|---|
| 113102 | Deutsche Bank – domestic bank tranfers | S | 1 |
| 154000 | Input tax | S | 13 |
| 400010 | Raw materials consumed | S | 1 |
| 417000 | Purchased services | S | 13 |
| 230051 | Gain/loss Euro conversion / document difference | S | 3 |
| 400000 | Consumption, raw material | S | 6 |
| 410000 | Usage: trading goods | S | 1 |
| 113101 | Deutsche Bank – checks payable | S | 1 |
When reviewing the results, we discover the receivables and sales deductions as previously expected. We also find a few currency gains or losses that were not part of our initial our expectations, but which are not exactly surprising. The whole thing therefore does not really seem very unusual, which is why we are not going analyze this account in any more detail.
Investigation of the bank account
Having not noticed any unusual changes in revenues, we will now take a look at the payments with respect to Deutsche Bank. To do this, we can use the same SQL query and simply swap out the account for sales revenue “0000800000” with the account for Deutsche Bank “0000113100” in the WHERE condition (tested on a HANA DB):
| 2 | SELECT CONTRA.HKONT ACCOUNT ,SKAT.TXT50 ACCOUNT_NAME, CONTRA.SHKZG DEBIT_OR_CREDIT,COUNT(DISTINCT CONTRA.BELNR) NUMBER_OF_DOCUMENTS FROM BSEG |
After executing the query, our test data set gives us the following result:
| ACCOUNT | ACCOUNT_NAME | DEBIT_OR_ CREDIT | NUMBER_OF_ DOCUMENTS |
|---|---|---|---|
| 113102 | Deutsche Bank – domestic bank tranfers | S | 1 |
| 154000 | Input tax | S | 13 |
| 400010 | Raw materials consumed | S | 1 |
| 417000 | Purchased services | S | 13 |
| 230051 | Gain/loss Euro conversion / document difference | S | 3 |
| 400000 | Consumption, raw material | S | 6 |
| 410000 | Usage: trading goods | S | 1 |
| 113101 | Deutsche Bank – checks payable | S | 1 |
Now let’s take a look through the accounts again. What is striking here is that we cannot find any liabilities on an offsetting account at all.
That’s strange!
Apparently, no suppliers are paid from this bank account. The sales taxes paid are not surprising as an offsetting account. But it is certainly strange that the expense accounts “Raw Material consumed” were posted and especially that this has been done directly against Cash! And apparently services were also posted directly against the bank (“Purchased services”). Normally there should be a liability for such services first, which is then paid. This was probably not the case here and this has also occurred with 13 different documents. This does not really correspond to our expectations and therefore the next step should be to examine which individual documents are behind this more closely. We will provide an example for the relevant documents relating to the “Purchased services” offsetting account.
It couldn’t be more simple:
| 3 | SELECT DISTINCT BSEG.BELNR FROM BSEG | Only showing document numbers once (distinct) |
JOIN BKPF ON (BSEG.MANDT=BKPF.MANDT AND BSEG.BUKRS=BKPF.BUKRS AND BSEG.GJAHR=BKPF.GJAHR AND BSEG.BELNR=BKPF.BELNR) | Join on table accounting document header | |
WHERE BSEG.SHKZG='H' AND BSEG.HKONT='0000113100' AND EXISTS | Account of interest is the Deutsche Bank debit (only the outgoing payments) | |
(SELECT * FROM BSEG CONTRA WHERE BSEG.MANDT=CONTRA.MANDT AND BSEG.BUKRS=CONTRA.BUKRS AND BSEG.GJAHR=CONTRA.GJAHR AND BSEG.BELNR=CONTRA.BELNR AND BSEG.SHKZG!=CONTRA.SHKZG AND CONTRA.HKONT='0000417000') | Documents are of interest only if the offsetting account ‘0000417000’ = ‘Purchased services’ is related. | |
AND BKPF.STBLG = ''; | Reversed documents excluded |
When executing the query, you get the relevant document numbers, for example:
| BELNR | ||
|---|---|---|
| 100007425 | 100007426 | 100007427 |
| 100007428 | 100007605 | 100007606 |
| 100007607 | 100007608 | 100007609 |
| 100007342 | 100007343 | 100007610 |
| 100007424 |
One should now take a closer look at these documents in order to track down this “dubious” posting structure.
And what now?
Data analysis is a powerful tool. Those who can carry out the right analysis exactly when they need it and are in a position to question the data accordingly are the lucky ones!
But SQL is not everyone’s cup of tea and even today it is till hard to find colleagues in auditing who are both adept in commercial matters and in data science.
This is where our offer comes in: Put zapliance to the test and leave the data science tasks completely to the software. For small company codes, you can even do this completely free of charge. The trial includes more than 150 automated audit questions / data indicators, integrated process mining and there are no functional restrictions. So you can be sure to find at least one or two of the data analyses you need. Make a free appointment with us without obligation and we will show you how to make the most of our advanced audit and data analysis software in one quick walkthrough.
