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:
- All postings that have only been posted to the profit & loss statement in debit and credit (i.e. not affecting net income).
- 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:
| 1 | SELECT DISTINCT BKPF.MANDT||'-'||BKPF.GJAHR||'-'||BKPF.BUKRS||'-'||BKPF.BELNR DOCID FROM BKPF |
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:
| 1a | SELECT DISTINCT BKPF.MANDT||'-'||BKPF.GJAHR||'-'||BKPF.BUKRS||'-'||BKPF.BELNR DOCID FROM BKPF | Removal of duplicate documents |
| 1b | JOIN 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 |
| 1c | WHERE 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… |
| 1d | AND (BKPF.STBLG IS NULL OR BKPF.STBLG=”) | … and which are not a reversal… |
| 1e | 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=”) ) | … 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… |
| 1f | 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 there is an item on the “debit side” that was posted to the same P&L account as in “credit”… |
| 1g | 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 there is an item on the “debit side” that was posted to the balance sheet… |
| 1h | 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’) | …, 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:
| 2 | 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 |
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:
| 3 | SELECT DEBIT,CREDIT,COUNT(DISTINCT BELNR) AMOUNT_BELNR FROM |
The most frequently used account assignments are at the top. An example of a result would look something like this:
| Debit | Credit | AMOUNT_BELNR |
|---|---|---|
| Rent | RE Revenue from rent – own use | 243 |
| 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 rent | 236 |
| RE Imputed expense vacancy rent | RE Imputed revenue from vacancy rent | 217 |
| Occupancy costs | RE Revenue from rent – own use | 205 |
| Personnel expenditures | Sales | 102 |
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.
