Prof. Dr. Nick Gehrke

written by

Prof. Dr. Nick Gehrke

Collage of various business elements. Dollar, stocks, sky, building

In German, there is a saying that everybody is going to be familiar with when the holiday season comes to an end, and it goes something like this: When the money runs out, there is still so much of the month left to go [„Am Ende des Geldes ist noch so viel Monat übrig”]. What is often used as a phrase in private life can quickly become important in companies too. Because, in the end, it all comes down to liquidity. Or to put it another way: without money, you are nothing. An auditor should therefore always keep an eye on the bank accounts in financial accounting. So, in this article, I'll be showing you how you can analyze payouts in SAP.

 

Reconciling cash accounts is a well-known audit procedure. If a balance can be reconciled that of course indicates that the balance is correct, but ultimately it is also of interest to see what a closer analysis of disbursements can reveal.

 

Cash accounts in SAP

First of all, it is important to identify the money accounts in the chart of accounts. Of course, you can now go through the chart of accounts and identify the cash accounts using the account names. But isn't there an easier way? In fact, the financial accounting accounts in the chart of accounts that represent cash accounts should also be customized as such in the chart of accounts. You can therefore recognize cash accounts in company code-specific “Customizing” account in the table SKB1. The field XGKON='X' means that it is a cash account.

The following SQL query determines how many documents were posted to the respective cash account for a particular company code and fiscal year. This gives you an indication of the "importance" of a cash account. You can try out the query yourself in SAP by executing the "DBACOCKPIT" transaction and then navigating to "SQL Editor" via "Diagnostics":

 

SELECT HKONT, SKAT.TXT50, COUNT(DISTINCT BELNR) AS Anzahl_Belege
FROM BSEG
JOIN T001 ON (BSEG.MANDT = T001.MANDT AND BSEG.BUKRS = T001.BUKRS)
JOIN SKB1 ON (BSEG.MANDT = SKB1.MANDT AND BSEG.BUKRS = SKB1.BUKRS AND BSEG.HKONT = SKB1.SAKNR)
LEFT JOIN SKAT ON (SKAT.MANDT = BSEG.MANDT AND SKAT.KTOPL = T001.KTOPL AND SKAT.SAKNR = BSEG.HKONT AND SKAT.SPRAS = 'D')
WHERE SKB1.XGKON = 'X' AND BSEG.MANDT = '800' AND BSEG.BUKRS = '1000' AND GJAHR = 2007
GROUP BY HKONT, SKAT.TXT50
ORDER BY COUNT(DISTINCT BELNR) DESC

 

As always: remember to change the client (MANDT), company code (BUKRS) and fiscal year (GJAHR) to your object of investigation.

 

HKONT TXT50 Anzahl_Belege
113103 Deutsche Bank - foreign bank transfers 176
113102 Deutsche Bank - domestic bank transfers 18
113100 Deutsche Bank (domestic) 6
113130 Deutsche Bank – outgoing cash 2
119999 Derivative clearing account 2
113300 Commerzbank Frankfurt 2
113108 Deutsche Bank – checks received 1
113131 Deutsche Bank – incoming cash 1
... ... ...

 

As you can see, Deutsche Bank is the most widely used bank.

 

The largest disbursements

We now want to get an overview of the payouts posted to the cash accounts. Payouts can be distinguished by the fact that they are credited, i.e. passively "outgoing". Furthermore, we stratify all positions on the accounts into strata by thousands and thus obtain the number of documents per each stratum of € 1,000 and their total amount. The query for this is shown below and is an adaptation of the query above:

 

SELECT HKONT, SKAT.TXT50, ROUND(DMBTR/1000, 0, ROUND_DOWN) AS Betragsschichten, COUNT(DISTINCT BELNR) AS Anzahl_Belege, SUM(DMBTR) AS SUMME_IN_EUR FROM BSEG
JOIN T001 ON (BSEG.MANDT = T001.MANDT AND T001.BUKRS = BSEG.BUKRS)
JOIN SKB1 ON (BSEG.MANDT = SKB1.MANDT AND BSEG.BUKRS = SKB1.BUKRS AND BSEG.HKONT = SKB1.SAKNR)
LEFT JOIN SKAT ON (SKAT.MANDT = BSEG.MANDT AND SKAT.KTOPL = T001.KTOPL AND SKAT.SAKNR = BSEG.HKONT AND SKAT.SPRAS = 'D' )
WHERE SKB1.XGKON = 'X' AND BSEG.MANDT = '800' AND BSEG.BUKRS = '1000' AND GJAHR = 2007
AND SHKZG = 'H'
GROUP BY HKONT, SKAT.TXT50, ROUND(DMBTR/1000, 0, ROUND_DOWN)
ORDER BY HKONT, ROUND(DMBTR/1000, 0, ROUND_DOWN)

As always: remember to change the client (MANDT), company code (BUKRS) and fiscal year (GJAHR) to your object of investigation.

 

HKONT TXT50 Amount by strata Document_
Amounts
SUM_IN_
EUR

113100

Deutsche Bank (domestic)

1

1

1.551,03

113100

Deutsche Bank (domestic)

4

1

4.599,00

113100

Deutsche Bank (domestic)

127

1

127.917,30

113100

Deutsche Bank (domestic)

567

1

567.110,00

113100

Deutsche Bank (domestic)

890

1

890.900,00

113100

Deutsche Bank (domestic)

126753

1

126.753.714,81

113102

Deutsche Bank – domestic bank transfers

0

6

157,00

113102

Deutsche Bank – domestic bank transfers

2700

3

8.100.028,00

113102

Deutsche Bank – domestic bank transfers

4000

2

8.000.000,00

113102

Deutsche Bank – domestic bank transfers

4834

1

4.834.625,00 €

113102

Deutsche Bank – domestic bank transfers

5000

3

15.000.000,00

113102

Deutsche Bank – domestic bank transfers

19133

1

19.133.380,07

...

...

...

...

...

 

The amounts per strata (third column) are sorted in ascending order for each account. The auditor should pay particular attention to clear jumps between the total amounts of strata. With the money account "113100 Deutsche Bank Inland", for example, there is one stratum with only one payment of approx. € 890,900 and then the next stratum is € 126,753 thousand. This is a blatant leap and the auditor should take a closer look at such "discontinuities". Other clearly visible jumps are marked in yellow. Finally, we use the data as a basis to create a histogram with the query.

This article is a contribution from the series "Journal Entry Testing". For an overview of the topic of JET, you will find the corresponding article with all the references here:

Everybody is talking about it – but we are actually doing it: Journal Entry Testing in SAP (overview and document type statistics)

Do you have any questions or suggestions? Then leave a comment and we will get back to you as soon as possible.

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