The world’s leading publication for data science, AI, and ML professionals.

Detect Fraud and Abnormalities in Financial Data

How to do Data Analytics in Areas like Internal Audit, Financial & Accounting or Controlling

Photo by Patrick Hendry on Unsplash
Photo by Patrick Hendry on Unsplash

Software like SAP handle all business processes of a company such as bookkeeping, controlling, sales, etc. It also hosts a lot of data, especially financial data that could lead to significant insights and need to be controlled by departments like business intelligence, accounting or internal audit. The following overview of common checks should provide a useful list of hands on analytic use cases. In this article, it is illustrated with the example of SAP FiCo, but other systems tick similarly.

Suspicious Changes

With the help of the tables CDHDR and CDPOS you can analyze changes in tables and also identify suspicious processes like different values in an order which changed from 20.000 € to 19.999 € just below an existing limit. What you need is an access (at least reading) for the mentioned tables, an understanding how these tables work and some SQL.

Example: SQL Result – Changes of Values:

UDATE       |CHANGENR |VALUE_NEW |VALUE_OLD                 01/01/2020  |1234     |20.000    | 
02/03/2020  |1234     |19.999    |20.000

Another example could be to view how often a credit limit of a customer has changed [1]. Many changes or an update right before a certain order could be also worth to take a look at.

Example: SQL Result – Count Changes :

OBJECTCLAS  |OBJECTID  |FNAME    |Count_Changes                   KLIM        |543       |KLIMK    |6

Check for Duplicates

To monitor the quality of master data but also to prevent incorrect bookings or even Fraud it is always a good idea to check for duplicates – one famous example is the customer data within your SAP data. For deeper analysis like this you might need some other approaches than SQL and you would probably prefer a python notebook.

Example: String Similarity Check with Python [2]:

import distance
distance.levenshtein("customer_abc", "customer_abcd")

Double Payments

Double payments mean losing money, therefore you can check if financial records in the BSEG table have duplicates. The following SQL join BSEG on BSEG to identify records with the same company code, amount, etc. but with different document number.

Example: SQL check duplicates [3]:

SELECT B1.MANDT,B1.BUKRS,B1.GJAHR,B1.BELNR BELNR1,B1.BUZEI BUZEI1,B2.BELNR BELNR2,B2.BUZEI BUZEI2,B1.DMBTR FROM BSEG BSEG_1 JOIN BSEG BSEG_2 ON (BSEG_1.MANDT=BSEG_2.MANDT AND BSEG_1.BUKRS=BSEG_2.BUKRS AND BSEG_1.DMBTR=BSEG_2.DMBTR AND BSEG_1.SHKZG=BSEG_2.SHKZG) WHERE B1.BELNR!=B2.BELNR

Transaction on Weekends and Holidays

Postings on unusual dates can be risky due to accrual accounting (annual accounts, advance return for turnover tax, etc.) or fraud. To get records with dates as created or changed date you could need holiday dates from an open API like getfestivo [4].

Unusual Booking Texts

To identify expenses that should not exist, you can search in the BKPF table for unusual booking texts with:

Example SQL text search:

... FROM BKPF
WHERE UPPER(BKTXT) LIKE = "Cancellation"
OR UPPER(BKTXT)= "Credit note"
UPPER(BKTXT)= "fee"
UPPER(BKTXT)= "Switzerland"
.....

Conclusion

These are just a small number of possible analytics questions within an Sap system, but due to the fact that we are talking about financial data, it is an important topic and you and your company should be aware of these questions and be eager to build up analytic competencies. In this article, examples were related to SAP but other ERP systems with finance modules like Oracle or DATEV will work the same way and the cases will be similar. How to combine SAP with the powerful Google Cloud Analytics Platform – Provider of data analytics tools such as BigQuery, Data Studio or recently looker – to gain a powerful data analytics platform and valuable insights. If you are interested in practical data analytics approaches for SAP data, this article could be also interesting for you.

Further Sources and Readings

[1]Shi-Ming Huang, Tung-Hsien Wu, Auditing SAP Using CAATs for Order to Cash (2015).

[2]pypi.org, https://pypi.org/project/Distance/ (2021)

[3]DAB-GmbH, https://www.dab-europe.com/fileadmin/PublicData/Downloads/2016_04_dab_Gesamtkatalog_ES.pdf (2016)

[4]festivo, https://getfestivo.com/documentation (2021)


Related Articles