Make reversals, but do it promptly! Don’t let the mess get out of hand!

Posted by Prof. Dr. Nick Gehrke on Mar 8, 2018 4:00:00 PM

If you want your financial accounting in SAP to be nice and clean, you need to keep things tidy. Sometimes you have to make corrections, and in financial accounting, this means making reversals. How much there is to learn about "clean" accounting becomes especially noticeable when you take a closer look at the work involved in performing a proper "cleanup". So, without further ado, let's now turn our attention to your reversals in financial accounting. This may seem a pretty dull topic, but ultimately it is one which touches on fundamental topics such as the tidiness of your bookkeeping and you may even find some weird reversals that give the impression that your transactions have not been booked in a timely manner.

 timely-reversals-in-sap.jpeg

 

Why do I need to concern myself with reversals?

Clean accounting is something that can be said to be achieved by anyone who adheres to the Principles of proper accounting (Grundsätze ordnungsmäßiger Buchhaltung – GoB) or, more specifically, to the Principles for properly maintaining, keeping and storing books, records and documents in electronic form and for data access, as provided by the German tax authorities (Grundsätze zur ordnungsmäßigen Führung und Aufbewahrung von Büchern, Aufzeichnungen und Unterlagen in elektronischer Form sowie zum Datenzugriff – GoBD). Here you can find the following requirement, for example:

"Every business transaction must be recorded in a basic record or journal as soon as possible after it is created. According to the General Terms and Conditions, business transactions must always be posted on an ongoing basis (journal). It is contrary to the nature of commercial accounting to confine itself first of all to the collection of documents and then, after a long period of time has elapsed, to enter the transactions in basic records or journals on the basis of these documents" (paragraph 46).

Of course, this also means that errors should be detected promptly and then corrected. We can see if this is being done properly by analyzing the reversal behavior in your company.

In what follows, I would like to show you the various options at your disposal for carrying out such an analysis based on the SAP data model.

 

How to find reversals in the SAP database?

In SAP, all document headers in Accounting are stored in the BKPF table. The XREVERSAL data field indicates whether a document has been reversed or is a reversal document. XREVERSAL='1' means that the document has been reversed and XRESERVAL='2' means that it is a reversal document. The data field STBLG then contains the document number of the corresponding reversal document or the reversed document, depending on which of the two documents you are currently dealing with. Reversed documents and reversal documents are therefore mutually referenced. The data field STJAH contains the fiscal year of the corresponding reversal document or reversed document.

 

This information is sufficient for you to create a list of all the documents in which the reversed document and the reversal document were always listed together. I have written some SQL queries that you can try out yourself. If you want to test these in your SAP System, use the transaction "DBACOCKPIT - Diagnostics - SQL Editor".

 

The following query lists the reversed document (REVERSED_BELNR) and for this document the posting date (REVERSED_BUDAT), the document date (REVERSED_BLDAT) and the entry date (REVERSED_CPUDT). The system also displays the reversal document (REVERSAL_BELNR), its posting date (REVERSAL_BUDAT), the document date (REVERSAL_BLDAT) and the entry date (REVERSAL_CPUDT).

 

Query 1 (tested for HANA):

SELECT REVERSED.MANDT,REVERSED.BUKRS,REVERSED.GJAHR REVERSED_GJAHR,REVERSED.BELNR REVERSED_BELNR,REVERSED.BUDAT REVERSED_BUDAT,REVERSED.BLDAT REVERSED_BLDAT,REVERSED.CPUDT REVERSED_CPUDT,REVERSAL.GJAHR REVERSAL_GJAHR,REVERSAL.BELNR REVERSAL_BELNR,REVERSAL.BUDAT REVERSAL_BUDAT,REVERSAL.BLDAT REVERSAL_BLDAT,REVERSAL.CPUDT REVERSAL_CPUDT FROM BKPF REVERSED JOIN BKPF REVERSAL ON (REVERSED.MANDT=REVERSAL.MANDT AND REVERSED.BUKRS=REVERSAL.BUKRS AND REVERSED.STBLG=REVERSAL.BELNR AND REVERSED.STJAH=REVERSAL.GJAHR) WHERE REVERSED.XREVERSAL='1' AND REVERSAL.XREVERSAL='2'

The resulting data gives us a good basis to work from.

 

Strange inconsistencies or: back to the future

Now we should use our collection of documents to determine whether there is a lack of plausible associations between the reversed and reversal receipts. It would be very strange if the reversal document had an earlier posting date than the reversed document. Let us check this out for you. To do this, we only need to extend Query 1 as follows.

Query 2 (tested for HANA):

SELECT REVERSED.MANDT,REVERSED.BUKRS,REVERSED.GJAHR REVERSED_GJAHR,REVERSED.BELNR REVERSED_BELNR,REVERSED.BUDAT REVERSED_BUDAT,REVERSED.BLDAT REVERSED_BLDAT,REVERSED.CPUDT REVERSED_CPUDT,REVERSAL.GJAHR REVERSAL_GJAHR,REVERSAL.BELNR REVERSAL_BELNR,REVERSAL.BUDAT REVERSAL_BUDAT,REVERSAL.BLDAT REVERSAL_BLDAT,REVERSAL.CPUDT REVERSAL_CPUDT FROM BKPF REVERSED JOIN BKPF REVERSAL ON (REVERSED.MANDT=REVERSAL.MANDT AND REVERSED.BUKRS=REVERSAL.BUKRS AND REVERSED.STBLG=REVERSAL.BELNR AND REVERSED.STJAH=REVERSAL.GJAHR) WHERE REVERSED.XREVERSAL='1' AND REVERSAL.XREVERSAL='2' AND REVERSED.BUDAT>REVERSAL.BUDAT

The result set should be empty!

The same applies to the document date, of course. You can check this by simply changing the last two conditions in WHERE to:

AND REVERSED.BLDAT>REVERSAL.BLDAT

 

Problems with cut-offs

It is particularly critical if reversals occur where the reversed document is in a different fiscal year to the reversal document. This is important because the reversal makes a change to a previous (completed) annual financial statement. The auditor does not like this at all, because it interferes with the periodic recording of business transactions. It can also be referred to as a "cut-off problem". We can now easily check if there are any of instances of this with the following query:

Query 3 (tested for HANA):

SELECT REVERSED.MANDT,REVERSED.BUKRS,REVERSED.GJAHR REVERSED_GJAHR,REVERSED.BELNR REVERSED_BELNR,REVERSED.BUDAT REVERSED_BUDAT,REVERSED.BLDAT REVERSED_BLDAT,REVERSED.CPUDT REVERSED_CPUDT,REVERSAL.GJAHR REVERSAL_GJAHR,REVERSAL.BELNR REVERSAL_BELNR,REVERSAL.BUDAT REVERSAL_BUDAT,REVERSAL.BLDAT REVERSAL_BLDAT,REVERSAL.CPUDT REVERSAL_CPUDT FROM BKPF REVERSED JOIN BKPF REVERSAL ON (REVERSED.MANDT=REVERSAL.MANDT AND REVERSED.BUKRS=REVERSAL.BUKRS AND REVERSED.STBLG=REVERSAL.BELNR AND REVERSED.STJAH=REVERSAL.GJAHR) WHERE REVERSED.XREVERSAL='1' AND REVERSAL.XREVERSAL='2' AND REVERSED.GJAHR!=REVERSAL.GJAHR

Good for you if your result set for this query is empty!

Everyone else should check the hits carefully to see if there are just reversals in January on December of the previous year, or if there are longer periods in between!

 

Cleaning on a timely basis avoids getting into discussions with auditors

Last but not least, we will now gain an overview of the extent to which errors in financial accounting have been detected and reversed in a timely manner. To do this, it makes sense to display statistics that calculate how many documents were reversed after how many days. This shows that things have been tidied up promptly and gives a good overview of whether your accounting is well organized.

Query 4 (tested for HANA):

SELECT REVERSAL.MANDT, REVERSAL.BUKRS, REVERSAL.GJAHR, DAYS_BETWEEN (REVERSED.CPUDT, REVERSAL.CPUDT) "DAYS_BETWEEN", COUNT(*) NUMBER_DOCS

FROM BKPF REVERSED

JOIN BKPF REVERSAL ON (REVERSED.MANDT=REVERSAL.MANDT AND REVERSED.BUKRS=REVERSAL.BUKRS AND REVERSED.STBLG=REVERSAL.BELNR AND REVERSED.STJAH=REVERSAL.GJAHR)

WHERE REVERSED.XREVERSAL='1' AND REVERSAL.XREVERSAL='2'

GROUP BY REVERSAL.MANDT, REVERSAL.BUKRS, REVERSAL.GJAHR, DAYS_BETWEEN (REVERSED.CPUDT, REVERSAL.CPUDT)

ORDER BY REVERSAL.MANDT, REVERSAL.BUKRS, REVERSAL.GJAHR, DAYS_BETWEEN (REVERSED.CPUDT, REVERSAL.CPUDT) DESC

Query 4 shows how many documents (NUMBER_DOCS) were reversed after how many days (DAYS_BETWEEN) for each company code and fiscal year of the reversal. Here, of course, we should give priority to the longer periods of time.

 

The statistics from query 4 will then look something like this:

 

timely-posting-of-reversals-in-SAP.jpg

Too much trouble?

It is not always easy to access SAP data via SQL. Also, not every auditor is a keen fan of data analysis.

That's why you should give zap Audit a try. Here, all the data analyses are taken care of for you, allowing you to concentrate on the technical conclusions that need to be drawn.

We offer you the opportunity to try out a free zap Audit Proof-of-Concept with a small company code of your choice. Please contact us if you are interested in giving this a try:

 

Contact

 

Topics: Data Analytics, GoBD, SAP Audit, cross process, Postings in a timely manner, reversal

Blog Commentary