An unsparing look behind the scenes: How to analyze one-time accounts in SAP
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?
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:
Analytics of one-time documents
With the help of the second SQL statement from the ePaper, you will obtain the following:
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:
- Using an adjusted SQL statement
- 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:
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|
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 with us here.