Is someone giving your sales revenues a “haircut”?

Giving sales revenues a “haircut” is often a way of helping managing directors and sales managers when their targets are slipping away from them. We look at this specific pattern of manipulation of sales revenues and how you can identify it in your SAP data.

The sales revenue “haircut” with no effect on net income

The fraud pattern which I am going to present here is what I will refer to “manipulation of sales revenue not affecting net income”. Postings are made which lead to higher sales, but which are offset by postings of expenses of a different nature, i.e. figures are being “pushed around” within the profit & loss (P&L) statement. Overall, the net income for the year remains the same; there are just figures that have been reallocated within the profit & loss statement. An example would be a posting of the following type:

Personnel expenses (debit) of €1,000 booked against €1,000 of sales revenue (credit)

The next step is to find out whether such postings exist in the SAP system.

Criteria for data delimitation

The question now arises as to which criteria should be used in order to query the financial accounting data in this respect. The criteria that match the type of scenario we are looking for can be formulated as follows:

  1. All postings that have only been posted to the profit & loss statement in debit and credit (i.e. not affecting net income).
  2. Different financial accounting accounts were used for debit and credit.

To test the SQL queries described in your SAP system, use the “DBACOCKPIT” transaction as usual and navigate to “SQL Editor” via “Diagnostics”.

The following query lists all the relevant documents:

1SELECT DISTINCT BKPF.MANDT||'-'||BKPF.GJAHR||'-'||BKPF.BUKRS||'-'||BKPF.BELNR DOCID FROM BKPF
JOIN BSEG ON (BKPF.MANDT=BSEG.MANDT AND BKPF.BUKRS=BSEG.BUKRS AND BKPF.GJAHR=BSEG.GJAHR AND BKPF.BELNR=BSEG.BELNR)
WHERE
BSEG.SHKZG='H' AND (BSEG.XBILK IS NULL OR BSEG.XBILK='')
AND (BKPF.STBLG IS NULL OR BKPF.STBLG='')
AND EXISTS (SELECT * FROM BSEG B WHERE B.MANDT=BSEG.MANDT AND B.BUKRS=BSEG.BUKRS AND B.GJAHR=BSEG.GJAHR AND B.BELNR=BSEG.BELNR AND B.SHKZG='S' AND B.HKONT!=BSEG.HKONT AND (B.XBILK IS NULL OR B.XBILK='') )
AND NOT EXISTS (SELECT * FROM BSEG B WHERE B.MANDT=BSEG.MANDT AND B.BUKRS=BSEG.BUKRS AND B.GJAHR=BSEG.GJAHR AND B.BELNR=BSEG.BELNR AND B.SHKZG='S' AND B.HKONT=BSEG.HKONT)
AND NOT EXISTS (SELECT * FROM BSEG B WHERE B.MANDT=BSEG.MANDT AND B.BUKRS=BSEG.BUKRS AND B.GJAHR=BSEG.GJAHR AND B.BELNR=BSEG.BELNR AND B.SHKZG='S' AND B.XBILK='X')
AND NOT EXISTS (SELECT * FROM BSEG B WHERE B.MANDT=BSEG.MANDT AND B.BUKRS=BSEG.BUKRS AND B.GJAHR=BSEG.GJAHR AND B.BELNR=BSEG.BELNR AND B.SHKZG='H' AND B.XBILK='X')

However, this is “only” a list of documents. Here it is worth making a few remarks which will help you to understand the query better. The places marked in red are explained in the last column:

1aSELECT DISTINCT BKPF.MANDT||'-'||BKPF.GJAHR||'-'||BKPF.BUKRS||'-'||BKPF.BELNR DOCID FROM BKPFRemoval of duplicate documents
1bJOIN BSEG ON (BKPF.MANDT=BSEG.MANDT AND BKPF.BUKRS=BSEG.BUKRS AND BKPF.GJAHR=BSEG.GJAHR AND BKPF.BELNR=BSEG.BELNR) Classic “Join” between BKPF and BSEG
1cWHERE 
BSEG.SHKZG=’H’ AND (BSEG.XBILK IS NULL OR BSEG.XBILK=”) 
Restricts to items that have been posted to “credit” in the profit & loss statement…
1dAND (BKPF.STBLG IS NULL OR BKPF.STBLG=”) … and which are not a reversal…
1eAND EXISTS (SELECT * FROM BSEG B WHERE B.MANDT=BSEG.MANDT AND B.BUKRS=BSEG.BUKRS AND B.GJAHR=BSEG.GJAHR AND B.BELNR=BSEG.BELNR AND B.SHKZG=’S’ AND B.HKONT!=BSEG.HKONT AND (B.XBILK IS NULL OR B.XBILK=”) ) … for which there is an item on the “debit side” in the document that was posted to another account and belongs in the profit & loss statement…
1fAND NOT EXISTS (SELECT * FROM BSEG B WHERE B.MANDT=BSEG.MANDT AND B.BUKRS=BSEG.BUKRS AND B.GJAHR=BSEG.GJAHR AND B.BELNR=BSEG.BELNR AND B.SHKZG=’S’ AND B.HKONT=BSEG.HKONT)  … and there is an item on the “debit side” that was posted to the same P&L account as in “credit”…
1gAND NOT EXISTS (SELECT * FROM BSEG B WHERE B.MANDT=BSEG.MANDT AND B.BUKRS=BSEG.BUKRS AND B.GJAHR=BSEG.GJAHR AND B.BELNR=BSEG.BELNR AND B.SHKZG=’S’ AND B.XBILK=’X’) … and there is an item on the “debit side” that was posted to the balance sheet…
1hAND NOT EXISTS (SELECT * FROM BSEG B WHERE B.MANDT=BSEG.MANDT AND B.BUKRS=BSEG.BUKRS AND B.GJAHR=BSEG.GJAHR AND B.BELNR=BSEG.BELNR AND B.SHKZG=’H’ AND B.XBILK=’X’)…, but also no “credit” item that was posted to the balance sheet.

We now want to determine which financial accounting accounts were used in debit and credit for each of these documents. The above query is reused with a WHERE condition:

2SELECT BSEG.MANDT,BSEG.BUKRS,BSEG.GJAHR,BSEG.BELNR,STRING_AGG(CASE WHEN BSEG.SHKZG='S' THEN SKAT.TXT50 ELSE null END,', ') DEBIT, STRING_AGG(CASE WHEN BSEG.SHKZG='H' THEN SKAT.TXT50 ELSE null END,', ') CREDIT
FROM (SELECT DISTINCT MANDT,BUKRS,GJAHR,BELNR,HKONT,SHKZG,XBILK FROM BSEG) BSEG
LEFT JOIN T001 ON (T001.MANDT=BSEG.MANDT AND T001.BUKRS=BSEG.BUKRS)
LEFT JOIN SKAT ON (SKAT.MANDT=BSEG.MANDT AND SKAT.KTOPL=T001.KTOPL AND SKAT.SAKNR=BSEG.HKONT AND SKAT.SPRAS='D')
WHERE
BSEG.MANDT||'-'||BSEG.GJAHR||'-'||BSEG.BUKRS||'-'||BSEG.BELNR IN
(
SELECT DISTINCT BKPF.MANDT||'-'||BKPF.GJAHR||'-'||BKPF.BUKRS||'-'||BKPF.BELNR DOCID FROM BKPF
JOIN BSEG ON (BKPF.MANDT=BSEG.MANDT AND BKPF.BUKRS=BSEG.BUKRS AND BKPF.GJAHR=BSEG.GJAHR AND BKPF.BELNR=BSEG.BELNR)
WHERE
BSEG.SHKZG='H' AND (BSEG.XBILK IS NULL OR BSEG.XBILK='')
AND (BKPF.STBLG IS NULL OR BKPF.STBLG='')
AND EXISTS (SELECT * FROM BSEG B WHERE B.MANDT=BSEG.MANDT AND B.BUKRS=BSEG.BUKRS AND B.GJAHR=BSEG.GJAHR AND B.BELNR=BSEG.BELNR AND B.SHKZG='S' AND B.HKONT!=BSEG.HKONT AND (B.XBILK IS NULL OR B.XBILK=''))
AND NOT EXISTS (SELECT * FROM BSEG B WHERE B.MANDT=BSEG.MANDT AND B.BUKRS=BSEG.BUKRS AND B.GJAHR=BSEG.GJAHR AND B.BELNR=BSEG.BELNR AND B.SHKZG='S' AND B.HKONT=BSEG.HKONT)
AND NOT EXISTS (SELECT * FROM BSEG B WHERE B.MANDT=BSEG.MANDT AND B.BUKRS=BSEG.BUKRS AND B.GJAHR=BSEG.GJAHR AND B.BELNR=BSEG.BELNR AND B.SHKZG='S' AND B.XBILK='X')
AND NOT EXISTS (SELECT * FROM BSEG B WHERE B.MANDT=BSEG.MANDT AND B.BUKRS=BSEG.BUKRS AND B.GJAHR=BSEG.GJAHR AND B.BELNR=BSEG.BELNR AND B.SHKZG='H' AND B.XBILK='X')
)
GROUP BY BSEG.MANDT,BSEG.BUKRS,BSEG.GJAHR,BSEG.BELNR

The result of this query shows which documents used which accounts.

Now, to put it all together, we want to find out how often the various account assignments occur in accounting documents. To do this, we aggregate the query that we just used above to count the number of occurrences accordingly:

3SELECT DEBIT,CREDIT,COUNT(DISTINCT BELNR) AMOUNT_BELNR FROM
(
SELECT BSEG.MANDT,BSEG.BUKRS,BSEG.GJAHR,BSEG.BELNR,STRING_AGG(CASE WHEN BSEG.SHKZG='S' THEN SKAT.TXT50 ELSE null END,', ') DEBIT, STRING_AGG(CASE WHEN BSEG.SHKZG='H' THEN SKAT.TXT50 ELSE null END,', ') CREDIT
FROM (SELECT DISTINCT MANDT,BUKRS,GJAHR,BELNR,HKONT,SHKZG,XBILK FROM BSEG) BSEG
LEFT JOIN T001 ON (T001.MANDT=BSEG.MANDT AND T001.BUKRS=BSEG.BUKRS)
LEFT JOIN SKAT ON (SKAT.MANDT=BSEG.MANDT AND SKAT.KTOPL=T001.KTOPL AND SKAT.SAKNR=BSEG.HKONT AND SKAT.SPRAS='D')
WHERE
BSEG.MANDT||'-'||BSEG.GJAHR||'-'||BSEG.BUKRS||'-'||BSEG.BELNR IN
(
SELECT DISTINCT BKPF.MANDT||'-'||BKPF.GJAHR||'-'||BKPF.BUKRS||'-'||BKPF.BELNR DOCID FROM BKPF
JOIN BSEG ON (BKPF.MANDT=BSEG.MANDT AND BKPF.BUKRS=BSEG.BUKRS AND BKPF.GJAHR=BSEG.GJAHR AND BKPF.BELNR=BSEG.BELNR)
WHERE
BSEG.SHKZG='H' AND (BSEG.XBILK IS NULL OR BSEG.XBILK='')
AND (BKPF.STBLG IS NULL OR BKPF.STBLG='')
AND EXISTS (SELECT * FROM BSEG B WHERE B.MANDT=BSEG.MANDT AND B.BUKRS=BSEG.BUKRS AND B.GJAHR=BSEG.GJAHR AND B.BELNR=BSEG.BELNR AND B.SHKZG='S' AND B.HKONT!=BSEG.HKONT AND (B.XBILK IS NULL OR B.XBILK=''))
AND NOT EXISTS (SELECT * FROM BSEG B WHERE B.MANDT=BSEG.MANDT AND B.BUKRS=BSEG.BUKRS AND B.GJAHR=BSEG.GJAHR AND B.BELNR=BSEG.BELNR AND B.SHKZG='S' AND B.HKONT=BSEG.HKONT)
AND NOT EXISTS (SELECT * FROM BSEG B WHERE B.MANDT=BSEG.MANDT AND B.BUKRS=BSEG.BUKRS AND B.GJAHR=BSEG.GJAHR AND B.BELNR=BSEG.BELNR AND B.SHKZG='S' AND B.XBILK='X')
AND NOT EXISTS (SELECT * FROM BSEG B WHERE B.MANDT=BSEG.MANDT AND B.BUKRS=BSEG.BUKRS AND B.GJAHR=BSEG.GJAHR AND B.BELNR=BSEG.BELNR AND B.SHKZG='H' AND B.XBILK='X')
)
GROUP BY BSEG.MANDT,BSEG.BUKRS,BSEG.GJAHR,BSEG.BELNR
) DOCS
GROUP BY DEBIT,CREDIT
ORDER BY COUNT(BELNR) DESC

The most frequently used account assignments are at the top. An example of a result would look something like this:

DebitCreditAMOUNT_BELNR
RentRE Revenue from rent – own use243
RE Revenue from operating costs flat rates, RE Rental – sales deduction (third party/own)RE Revenue from operating costs flat rates, RE Revenue from third-party usage rent236
RE Imputed expense vacancy rentRE Imputed revenue from vacancy rent217
Occupancy costsRE Revenue from rent – own use205
Personnel expendituresSales102

Not all rows are necessarily critical. There can be perfectly legitimate account assignments where only expenses have been simply transferred.

You as the auditor must now go through all the lines and consider whether the “sales hairdresser” has been at work. To do this, you need to look for account assignments to revenue accounts in credit (CREDIT). If such lines exist, look at the offsetting account on the debit side and critically ask yourself the question how such postings could have been made.

Sounds too complicated?

Is all this technically too complicated for you? If so, then no problem – that after all is exactly why we have developed zap Audit. With zap Audit, data analysis becomes straightforward, leaving you to concentrate on evaluating the results. So feel free to contact us.

Artikel teilen

Facebook
Twitter
XING
LinkedIn