26.07.2019

Data analysis – sorting the good from the bad

In this blog post I outline some important issues when analysing SAP® accounts payable postings. Specifically, I discuss which documents should be considered and which can be ignored – or the attributes according to which a compression may, where applicable, also make sense. As this depends on the individual (or on the analysis objective), this should be seen as a suggestion.

This topic is inspired by the blog post “Avoiding a data hotpot” where I explained how to differentiate order documents (e.g. based on document types, order history or the presence of supplier numbers/their absence in the case of stock transport orders).

But how can you better differentiate between documents within FI?

You may be familiar with the following scenario: You “just” want to analyse FI postings within accounts payable, such as the total invoice volume per supplier, the FI returns rate based on credit memos, the cancellation rate, manual payments, invoices without an order reference, potentially unused cash discounts, or duplicate payments. After an initial check, however, it becomes clear that as well as actual external suppliers, the documents also include, for example, employees who get their travel expenses refunded in this way, or intercompany suppliers for internal allocations, as well as CpD vendors used for making collective postings. Depending on the analysis objective, it may be useful to exclude or focus on certain document groups.

 

A few basics

Before we get into details, let’s start with a few basics and some definitions.

Here, we mainly consider the digital representation of a vendor document or an invoice posting. For the purposes of this blog post, I will refer only to the lines of a multiline general ledger document that is managed in the vendor subledger in parallel to the general ledger. From a data perspective, these are stored in SAP® in the table BSAK (for cleared vendor items) and in BSIK (for open vendor items).

The entire posting consists of posting lines with debit or credit postings. By way of example, let’s consider the classical three-line posting: When a supplier sends an invoice it is posted as a liability on its supplier account. The remaining two posting lines represent the cost element and the tax (alternatively, the goods receipt/incoming invoice account is posted instead of the cost element and the offsetting entry there is assigned to the corresponding costs – although we won't consider this in more detail here).

Typical key attributes / fields we can access:

  • Company code
  • Financial year
  • Document number
  • Document line
  • Various amount fields in the local, document and foreign currency
  • Document type
  • Posting key
  • Posting texts
  • Payment term

 

Suitable attributes for separating data

Some attributes are directly suitable and others are indirectly suitable for separating data. In this blog post I will briefly discuss the following ways of separating data.

  • econciliation account
  • Document types
  • Posting key
  • CpD account indicator
  • Various blocking and deletion flags
  • Supplier group
  • Affiliated company ID

Some of the information can be found directly in the tables BSEG and/or BSAK / BSIK, while others can be added via joins or relations to the tables mentioned below.

 

Reconciliation account

Similar information can be obtained from the reconciliation account. This can be found directly in the table BSAK/BSIK with the field name AKONT. It is redundantly managed because it is assigned to the suppliers in the master data at company code level; this assignment can be found in table LFB1. This obviously makes sense in cases where a German supplier is reconciled via “domestic suppliers” within the German company code, but due to its German head office is seen as a foreign supplier within the American company code, etc. Differentiation works well using a company-code specific reconciliation account. The reconciliation account can be used both for analysis and filter purposes.

 

Reconciliation accountAccount textAmountCount
0000160000Trade Payables 182,944,610.2514,154
0000160000Verbindlichkeiten 8,181,588,468.9812,270
0000160010Trade Payables - dom10,600.002
0000161000AP-foreign 10,988,090.3888
0000164001Payables - CC 1000 1,586.002
0000164003Payables 3000 1,680,371.04124
0000164003Payables CC 3000 305,752.589
0000167000HR 3rd Party Payable 8,072.0111
0000176050Travel expenses paya 3,816.374
Totals8,377,531,367.6126,664

Picture 2 – Vendor transactions by reconciliation account

 

Document types

Document types are often used because they are generally familiar to SAP users and can be recognised if used in reporting. The document type can also be used to exclude unwanted postings. There are limitations, however:

One disadvantage may be that a document type “vendor invoice” does not say whether this relates to an internal or external supplier, travel expenses submitted, or a normal purchase transaction. Also, a document type “other posting” can include almost any transaction type and has limited information value. Moreover, the document type is managed at the level of the overall document and contains only very limited information on the individual posting lines. The next point offers somewhat better options if a more precise differentiation is required:

 

Doc TypeDocument type textAmountCount
ABAccounting document 6,045,471.56120
EUConversion diff.Euro 0.28195
KAVendor document 4,321,612.36294
KRVendor invoice 133,173,671.4521,905
KZVendor payment 43,274,294.9468
REGross inv. receipt 4,139,709,522.793,394
ZPPayment posting 4,051,006,794.23688
Total8,377,531,367.6126,664

Picture 3 – Vendor transactions by document type

 

Posting key

Unlike document types, posting keys enable a more precise differentiation as customizing for the posting keys records

  • Whether the posting line is posted as a debit or credit
  • The type of posting to which the respective line refers
  • Whether the line is relevant for vendors, customers, assets, materials or a neutral general ledger account posting
  • Whether the respective line pertains to costs, sales or payments within the context of transaction figure projections.
  • Whether it is a special G/L transaction that must be shown separately.

The posting key (field name BSCHL) can be found in each individual posting line; some of the specified attributes are also managed redundantly here and can be analysed directly in BSAK and BSIK. In contrast, other information can be viewed in the customising settings in the table TBSL, and the corresponding texts in TBSLT.

 

Posting KeyPosting Key TextAmountCount
21Credit memo 106,800,816.80107
22Reverse invoice 909,132.36285
25Outgoing payment 4,094,375,767.17757
27Clearing 0.10
31Invoice 4,172,000,849.0025,308
32Reverse credit memo 3,380,480.008
35Incoming payment 64,100.003
37Other clearing 0.1895
38Payment clearing 222.001
Totals8,377,531,367.6126,664

Picture 4 – Vendor transactions by posting key

 

CpD account indicator

CpD suppliers can be identified in various ways: both the reconciliation account and also the account group could refer to a one-time account; alternatively, you can view the XCPDK indicator, which is located at the level of the global master data in table LFA1 and is filled with an X in the case of a CpD account. Incidentally, my colleague Martin Riedl has written an interesting blog post on the finer points of CPD/the recording of bank data in the document, which is well worth a read.

 

Various blocking and deletion flags

The blog post “Avoiding a data hotpot” explained that orders marked as deleted should not be excluded from data analyses per se, and the same applies analogously for financial accounting analyses. Of course, postings made on the basis of documentation and retention obligations should not be deleted under any circumstances. However, the relevant business partner could have a deletion flag or a block in the master records.

Due to the mere fact that a supplier is (now) flagged as deleted, the history of its postings should be the subject of historically-based reports. Blocking and deletion flags can of course nevertheless at least be included in the analysis. A whole range of options exist here for deleting a supplier or blocking it for individual areas. A global deletion flag (field name LOEVM) can, for example be set. This is then used at the highest level of the master data, in table LFA1. It is also possible to delete or block a supplier for selected company codes; from a data perspective, this is then shown in table LFB1; purchasing blocks are shown in the purchasing master data in table LFM1, etc.

 

Supplier group

The supplier group is not located directly in the table BSAK but is stored in the supplier's master data. Unlike the reconciliation account, therefore, the supplier group is not company-code-specific/dependent on the assigned company code. The supplier group is located in table LFA1 (field name KTOKK). Depending on customizing, one grouping is more or less directly visible, e.g.

  • Employees
  • External suppliers
  • CpD suppliers
  • Intercompany suppliers

 

 

Vendor group Vendor group textAmountCount
0001 Vendors 8,189,960,908.4524,514
0020 Vendor-BP-Commission Recipient 220.014
0099 One-time vendors 10,600.002
CPDL One-time vend.(ext.no.assgnmt) 162,066.004
HRTP Employees 8,072.0111
KRED Vendors 11,830,255.95141
LIEF Vendors 175,556,649.191,983
PLNT Vendor belonging to group 1,586.002
VEND Vendors (external, alpha) 1,010.003
Totals 8,377,531,367.6126,664

Picture 5 – Vendor transactions by supplier group

 

Affiliated company ID

Although in practice the concrete form is to some extent dependent on customizing, the partner code (LFA1-VBUND) is a possible indicator of intragroup transactions/trading partners. Often, this contains a value that corresponds to the company code of the affiliated company. For example, company code 3000 – dab: Canada could be represented as a supplier with the number 0100000110 dab: Canada Ltd., and VBUND attribute 3000.

 

Summary

In this blog I have shown you some ways to differentiate your SAP® accounting data from various perspectives in analyses and reports. In practice, it does of course make sense to combine several of these aspects:

It makes sense, for example, to limit the data to invoices only in a first step, by means of the posting key, and at the same time to perform the analysis only for actual external suppliers (and completely exclude intercompany postings and HR-based travel-expense postings). The options you use for your filters and/or compressions will depend entirely on your specific use case scenarios.

I hope you have enjoyed this blog post. Please contact us if you have any questions or comments.


Comments (0)
Be the first who comments this blog entry.
Blog login

You are not logged in. Please log in to comment this blog entry.

go to Login