Prof. Dr. Nick Gehrke

written by

Prof. Dr. Nick Gehrke

business , e-commerce concept illustration with graphs and charts from computer

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:

 

MANDT BUKRS GJAHR BELNR HKONT SHKZG

800

1000

2018

100005794

140000

S

800

1000

2018

100005794

230051

S

800

1000

2018

100005794

175000

H

800

1000

2018

100005794

800000

H

 

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:

 

MANDT BUKRS GJAHR BELNR DEBIT CREDIT

800

1000

2018

1900002600

0000154000, 0000230051, 0000476900

160000

800

1000

2018

1900002601

0000154000, 0000230051, 0000403000

160000

800

1000

2018

1900002602

0000154000, 0000230051, 0000471000

160000

 

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:

 

DEBIT COUNT(*)

0000154000; 0000191100

279

0000154000; 0000476900

166

0000154000; 0000476100

151

0000154000; 0000403000

149

0000154000; 0000471000

121

0000154000; 0000191100; 0000230051

117

0000154000; 0000476000

93

 

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.

 

zapliance Newsletter label

Sign up to receive
our latest news and special offers direct to your inbox!

zapliance customer brands

Our recommendations to you

Comments