Darkness favors secret dealings, or in other words: Let’s do Journal Entry Testing again!

If an auditor examines his or her accounting data, the keyword often turns out to be: Journal Entry Testing. In addition to the highest, most frequent and most problem-free amounts, one’s attention is most often drawn to: Weekend postings. Of course, you can also detect weekend postings immediately with zap Audit. But this is something we are seeing more and more: Working at the weekends is nothing special or unusual, especially when looking at other countries and cultures. The weekends as we would define them (Saturday and Sunday) are no longer really days on which nothing happens in terms of regular business operations. So what benefit is an evaluation of postings made at weekends and how can you discover days when there really wasn’t much going on and someone perhaps posted something unobserved? We’ll show you how to do this in the SAP system, with some tricks in SQL!

There is increasingly a lot which goes in the company at the weekend: Automatic processes are posted, or goods receipts keep on arriving at the warehouse on Saturday and / or Sunday. True to the motto:

The Company Never Sleeps!

But when was there really not very much going on in the company and when was there a corresponding opportunity to post something unobserved? We look into this question below and come up with an answer based on a few simple statistical considerations.

What usually happens on weekdays?

The fact that people always work weekends is certainly not true in most companies. Instead, it is worth looking more closely which activities and business transactions are carried out on weekends and which are not. It therefore would seem a good idea to distinguish business transactions by document type.

It is quite straightforward to do this type of evaluation with zap Audit. With the new version, some indicators have been made even “sharper” and new ones have been added. For example: “Postings on other days than normally posted”. Admittedly, we weren’t very creative when it came to choosing a name. If you have any good alternative ideas, let us know in the comments. The results in zap Audit then look like this:

To try out the analysis described below directly in SAP, simply call up the “DBACOCKPIT” transaction. In the left tree, navigate to the “SQL Editor” via “Diagnosis” and you are ready to go. We have already tested all of the following SQL queries on a SAP HANA database for you.

We first examine how high the average number of documents and the standard deviation of this number of documents per weekday are. And we do this separately for each document type:

SELECT BLART, WEEKDAY, COUNT(DISTINCT CPUDT) DAYS_POSTED, AVG(NUMBER_DOCS) AVG_DOCS, STDDEV(NUMBER_DOCS) STDDEV_DOCS FROM
(

• SELECT BLART, DAYNAME(CPUDT) WEEKDAY, CPUDT, COUNT(DISTINCT BELNR) NUMBER_DOCS FROM BKPF WHERE BUKRS='1000' AND GJAHR='2018' GROUP BY BLART, CPUDT

)
GROUP BY BLART, WEEKDAY
ORDER BY BLART, AVG_DOCS DESC

Note: Change the fields marked in red to the subject of your audit.

Let’s take a look at an extract from the set of results for document type RE (invoice receipt):

What do these results tell us?

For obvious reasons, incoming invoices are not processed very often at weekends. And especially not on Sundays. In the whole year, only two invoices were posted on one Sunday. They would definitely be worth looking at if you were looking for receipts posted when “darkness favors secret dealings”.

But now, let’s look at a simple statistical consideration. What was that rule with mean and standard deviation again? The rule of thumb is: If you assume a normal distribution of the document frequencies on the weekdays, then approx. 95% of all weekdays should have a document volume within the interval:

Mean value (AVG_DOCS) +/- 2 * Standard deviation (STDDEV_DOCS)

It’s called a confidence interval.

For document volumes greater than:

Mean value + 2 * Standard deviation

and less than:

Mean value – 2 * Standard deviation

these volumes should only occur on about 2.5% of these weekdays.

We are interested in days with particularly low volumes and use the SQL query employed above once again, only this time extending it with the lower confidence limit:

SELECT BLART, WEEKDAY, COUNT(DISTINCT CPUDT) DAYS_POSTED,AVG(NUMBER_DOCS) AVG_DOCS, STDDEV(NUMBER_DOCS) STDDEV_DOCS,AVG(NUMBER_DOCS)-2*STDDEV(NUMBER_DOCS) LOWER_LIMIT FROM
(

• SELECT BLART, DAYNAME(CPUDT) WEEKDAY, CPUDT, COUNT(DISTINCT BELNR) NUMBER_DOCS FROM BKPF WHERE BUKRS='1000' AND GJAHR='2018' GROUP BY BLART, CPUDT

)
GROUP BY BLART,WEEKDAY
ORDER BY BLART,AVG_DOCS DESC

Note: Change the fields marked in red to the subject of your audit.

Let’s look at document type RE again:

The LOWER_LIMIT is always greater than 0 documents. This means that we should now check whether there are days for document type RE with a number of postings below the LOWER_LIMIT. This could be considered a rare event. For example, it could have been a public holiday. And yet invoices were still posted!

We now examine Wednesdays to see if there was a Wednesday on which less than 36 invoices were posted:

SELECT BLART, DAYNAME(CPUDT) WEEKDAY, CPUDT, COUNT(DISTINCT BELNR) NUMBER_DOCS
FROM BKPF

• WHERE BLART='RE' AND DAYNAME(CPUDT)='WEDNESDAY' AND BUKRS='1000' AND GJAHR='2018'

GROUP BY BLART, CPUDT
HAVING COUNT(DISTINCT BELNR) < 36;

Note: Change the fields marked in red to the subject of your audit.

In my test dataset, this gives the following:

That’s kind of interesting. One Wednesday falls on Boxing Day. Someone on the staff probably forgot about something before the turn of the year. And the other Wednesday seems to be in the middle of the summer break.

Now we know what the days are for this type of document when, potentially at least, “darkness favors secret dealings”. As an auditor, we can now take a look at such documents. We no longer need to work simply based on the premise that days at the weekend are somehow particularly “suspicious”. The analysis is completely dynamic and based on empirical facts.