Subscribe now – and receive valuable tips and tricks for your next audit!

Journal Entry Testing: The Best Queries about Money in SAP

journal-entry-testing-best-queries-about-money-in-sap

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.

HKONTTXT50Anzahl_Belege
113103Deutsche Bank – foreign bank transfers176
113102Deutsche Bank – domestic bank transfers18
113100Deutsche Bank (domestic)6
113130Deutsche Bank – outgoing cash2
119999Derivative clearing account2
113300Commerzbank Frankfurt2
113108Deutsche Bank – checks received1
113131Deutsche Bank – incoming cash1

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.

HKONTTXT50Amount by strataDocument_
Amounts
SUM_IN_
EUR
113100Deutsche Bank (domestic)111.551,03
113100Deutsche Bank (domestic)414.599,00
113100Deutsche Bank (domestic)1271127.917,30
113100Deutsche Bank (domestic)5671567.110,00
113100Deutsche Bank (domestic)8901890.900,00
113100Deutsche Bank (domestic)1267531126.753.714,81
113102Deutsche Bank – domestic bank transfers06157,00
113102Deutsche Bank – domestic bank transfers270038.100.028,00
113102Deutsche Bank – domestic bank transfers400028.000.000,00
113102Deutsche Bank – domestic bank transfers483414.834.625,00 €
113102Deutsche Bank – domestic bank transfers5000315.000.000,00
113102Deutsche Bank – domestic bank transfers19133119.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.

Subscribe now – and you will receive valuable tips and tricks for your next SAP audit.

Contact

We would be happy to hear from you.
You can reach us here: