An unsparing look behind the scenes: How to analyze one-time accounts in SAP

Posted by Dennis Jürgensen on Apr 5, 2017 4:45:00 PM

Numerous compliance guidelines exist in medium- to large-sized companies for the usage of one-time accounts in SAP. These may vary from the total prohibition of one-time payments to a limit placed on such payments, which may extend up to quite a significant amount of money, depending on the company. Based on the guidelines and using your professional judgment, we will analyze the outlying cases you need to discuss with the relevant specialist department.

 

Part 3 of the series: "One-Time Accounts"

1. One Time Accounts - The quick checklist for your accounting department
2. How to find one-time invoices in SAP in three steps
3. An unsparing look behind the scenes: How to analyze one-time accounts in SAP
4. Do you have power users with one-time accounts?

 

Analyzing one-time accounts in three steps - Man holding money

 

Content and basis for analysis of one-time documents

Having already taken a look at all one-time accounts in the last post in this series, we will now analyze extraordinary document types in this post. This analysis will be based on the document numbers (BELNR field) from the last series. If you missed that post, you can download the ePaper and get up to speed using your own data here:

 

Download now!

 

Analytics of one-time documents

With the help of the second SQL statement from the ePaper, you will obtain the following:

 

BUKRS

GJAHR

BELNR  

BUZEI  

LIFNR  

XCPDD  

DMBTR (Amount)

1000

2012

2500014049

1

1002720

X

9,164.80

1000

2012

2500019151

1

1002521

X

8,165.66

1000

2012

2500018179

1

1002720

X

6,716.88

1000

2012

2500014819

1

1002521

X

4,600.00

1000

2012

2500019011

1

1002720

X

3,867.50

1000

2012

2500021917

1

1002720

X

3,841.32

 

 

...

 

 

 

...

 

Step 1: Getting the document types and document numbers

However, in addition to identifying high amounts, you can also make other interesting discoveries. Other questions you might ask might be: What are the document types behind the one-time documents and could some of them be identified as outliers? Read on to discover the answers to these questions below.

Before we can start performing the analytics, we need to determine the document types. There are several possible ways of doing this:

  1. Using an adjusted SQL statement
  2. SAP transaction "S_ALR_87012291"

Unfortunately, the document type cannot be found in the “BSEG” table. That’s why we need to use the SQL “join” operator on the “BKPF” table.

But here we will be taking a closer look at option 2:

This second option is not quite so technical, and is certainly no less extensive in scope. Using SAP transaction S_ALR_87012291 (Line Item Journal), we can get nearly all the information we need. We just need to enter the company code, fiscal year and the corresponding vendor account (LIFNR field) from the second part of this series. In “Further selections” you can uncheck everything, except the vendor section, where you need to enter the vendor account. The result will look something like the following:

 

line-item-journal.jpg

 

When you have entered all the necessary data, you can click on “Execute” or press F8. To export the document types in the next screen, you need to “Change Layout…” or press “CTRL+F8”. In the “Hidden fields” area, you can see the document type item. Click on document type and add it to “Line 1”. That’s all we need for now, so click on “Copy”. The document type will now be shown in the Line Item Journal. To investigate further, we will now export the table to Excel by clicking on “Spreadsheet…” or pressing “CTRL+SHIFT+F7”. Save the file e.g. to the desktop. The resulting table then contains all the relevant information.

 

Step 2: Creating a PivotTable

Because the Excel spreadsheet might be long and big, we will use a PivotTable to analyze the document types further. First we need to get an overview of all the document types used, the corresponding frequencies and the amounts.

In Excel, you simply select “Insert” and “PivotTable”. In the window which is displayed, you only need to check to make sure that the “New Worksheet” option is selected, because Excel will mark all the content itself. In the new worksheet, you need to select the document types as columns; and the amount and the document numbers as values. This is where your professional judgment comes in. Check the list e.g. for rare document types, or combinations of rare and high amounts. My table looks like this:

 

Row Labels

Number of document numbers

Sum of amount

AB – Accounting document

1924

2152643.57

PH - Commission

2143

337850.96

KA – Vendor document

16

6752.39

KG – Vendor credit memo

3

3237.87

KR – Vendor invoice

643

187364.15

KS – Vendor invoice

19

12867.65

RE – Invoice - gross

175

57236.42

ZP – Payment posting

874

256622

ZD – Payment provider

64

32894.48

Grand total

5861

3047469.49

 

Step 3: Use your Professional Judgment

Taking a look at the table, the Commission item seemed suspicious to me, because normally you would have a contract with people you are paying commissions to. That’s why they shouldn’t be marked as one-time payments, so I decided to look into this in a bit further detail.

 

In zap Audit, we have integrated a much more efficient process for the analysis of one-time documents, so you don’t need to look up document types, or examine data on your own. In addition, we have added business intelligence methods and are constantly working to improve the algorithms used. That’s why you should automate everything that can be automated with zap Audit, and only audit, what needs to be audited.

 

What are your one-time account experiences? Share your thoughts below or write an email to: blog(at)zapliance.com

 

Topics: Purchase, SAP Audit, one-time accounts, one-time invoices

Blog Commentary