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 account | Account text | Amount | Count |
|---|---|---|---|
| 0000160000 | Trade Payables | 182,944,610.25 | 14,154 |
| 0000160000 | Verbindlichkeiten | 8,181,588,468.98 | 12,270 |
| 0000160010 | Trade Payables - dom | 10,600.00 | 2 |
| 0000161000 | AP-foreign | 10,988,090.38 | 88 |
| 0000164001 | Payables - CC 1000 | 1,586.00 | 2 |
| 0000164003 | Payables 3000 | 1,680,371.04 | 124 |
| 0000164003 | Payables CC 3000 | 305,752.58 | 9 |
| 0000167000 | HR 3rd Party Payable | 8,072.01 | 11 |
| 0000176050 | Travel expenses paya | 3,816.37 | 4 |
| Totals | 8,377,531,367.61 | 26,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 Type | Document type text | Amount | Count |
|---|---|---|---|
| AB | Accounting document | 6,045,471.56 | 120 |
| EU | Conversion diff.Euro | 0.28 | 195 |
| KA | Vendor document | 4,321,612.36 | 294 |
| KR | Vendor invoice | 133,173,671.45 | 21,905 |
| KZ | Vendor payment | 43,274,294.94 | 68 |
| RE | Gross inv. receipt | 4,139,709,522.79 | 3,394 |
| ZP | Payment posting | 4,051,006,794.23 | 688 |
| Total | 8,377,531,367.61 | 26,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 Key | Posting Key Text | Amount | Count |
|---|---|---|---|
| 21 | Credit memo | 106,800,816.80 | 107 |
| 22 | Reverse invoice | 909,132.36 | 285 |
| 25 | Outgoing payment | 4,094,375,767.17 | 757 |
| 27 | Clearing | 0.10 | |
| 31 | Invoice | 4,172,000,849.00 | 25,308 |
| 32 | Reverse credit memo | 3,380,480.00 | 8 |
| 35 | Incoming payment | 64,100.00 | 3 |
| 37 | Other clearing | 0.18 | 95 |
| 38 | Payment clearing | 222.00 | 1 |
| Totals | 8,377,531,367.61 | 26,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 text | Amount | Count |
|---|---|---|---|
| 0001 | Vendors | 8,189,960,908.45 | 24,514 |
| 0020 | Vendor-BP-Commission Recipient | 220.01 | 4 |
| 0099 | One-time vendors | 10,600.00 | 2 |
| CPDL | One-time vend.(ext.no.assgnmt) | 162,066.00 | 4 |
| HRTP | Employees | 8,072.01 | 11 |
| KRED | Vendors | 11,830,255.95 | 141 |
| LIEF | Vendors | 175,556,649.19 | 1,983 |
| PLNT | Vendor belonging to group | 1,586.00 | 2 |
| VEND | Vendors (external, alpha) | 1,010.00 | 3 |
| Totals | 8,377,531,367.61 | 26,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.