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.
