3 steps for auditing offsetting accounts in SAP

It’s once again time for us to continue our series on Journal Entry Testing (JET)! This time, we will be focusing on the well-known topic of offsetting account analysis in SAP.

What is the purpose of an offsetting account analysis?

Since the very beginnings of accounting, all business transactions have been recorded using double-entry accounting. An offsetting account analysis shows which offsetting accounts have been used for a particular account. Are they always the same? What variants are there? Have any “exotic” offsetting accounts been used which you ought to take a closer look at? Or perhaps there are some offsetting accounts which it is not appropriate to use for that account at all? These are all questions that an offsetting account analysis should help us to answer. Ultimately, an offsetting account analysis means that we want to learn something about the “posting structures” used.

Step 1: Which document is based on which financial accounting accounts?

First, the posting material should be filtered in such a way that all financial accounting accounts used are listed only once for all documents. You can try out the following SQL query if you call the “DBACOCKPIT” transaction in SAP and navigate to the “SQL Editor” via “Diagnostics”:

SELECT DISTINCT
MANDT, BUKRS, GJAHR, BELNR, HKONT, SHKZG
FROM BSEG
WHERE
MANDT='800' AND BUKRS='1000' AND GJAHR=2018

In the WHERE condition, replace the client (MANDT), company code (BUKRS), and fiscal year (GJAHR).

You will obtain a list, from which I provide an excerpt for only one document here:

Step 2: Summarize debit and credit accounts

In the next step, all the rows in a document are summarized so that all the accounts for the debit and credit sides of each document are displayed in a single row. To do this, we will parenthesize the query from step 1 as follows:

SELECT
MANDT, BUKRS, GJAHR, BELNR, STRING_AGG(CASE WHEN SHKZG='S' THEN HKONT ELSE null END, ', '
ORDER BY HKONT) DEBIT, STRING_AGG(CASE WHEN SHKZG='H' THEN HKONT ELSE null END, ', ' ORDER BY HKONT) CREDIT
FROM
(
SELECT DISTINCT
MANDT, BUKRS, GJAHR, BELNR, HKONT, SHKZG
FROM BSEG
WHERE MANDT='800' AND BUKRS='1000' AND GJAHR=2018
)
GROUP BY MANDT, BUKRS, GJAHR, BELNR

Here is a selection of three different documents to illustrate the result set:

Step 3: Generate statistics on offsetting accounts

Now we again bracket the query from step 2 (and thus also from step 1) and count how many documents each have the same structure in the offsetting accounts.

The following example calculates which offsetting accounts are used when a credit posting (CREDIT) has been posted to account 160000:

SELECT DEBIT,COUNT(*) FROM
(
SELECT
MANDT, BUKRS, GJAHR, BELNR,
STRING_AGG(CASE WHEN SHKZG='S' THEN HKONT ELSE null END, ', ' ORDER BY HKONT) DEBIT,
STRING_AGG(CASE WHEN SHKZG='H' THEN HKONT ELSE null END, ', ' ORDER BY HKONT) CREDIT
FROM
(
SELECT DISTINCT
MANDT, BUKRS, GJAHR, BELNR, HKONT, SHKZG
FROM BSEG
WHERE MANDT='800' AND BUKRS='1000' AND GJAHR=2018
)
GROUP BY MANDT, BUKRS, GJAHR, BELNR
)
WHERE CREDIT LIKE '%160000%'
GROUP BY DEBIT
ORDER BY COUNT(*) DESC

This gives the following result, for example:

You can thus see that there are many different posting structures for the offsetting accounts, which also occur relatively frequently. Of course, the auditor must now get down to business and assess whether these offsetting accounts are plausible.