9 Sales Data Analytics that Every Auditor should be Aware of
The auditing of order-to-cash and the order-to-cash process in SAP is one of the main topics in every audit department. In my two-part blog series, I would like to introduce the basics of performing an order-to-cash audit in SAP, starting with the necessary data structure and ending with some of the most interesting data indicators that every auditor should know about.
How do SAP processes in order-to-cash differ from purchase-to-pay?
If you compare the data structure in purchase processes to order-to-cash processes in SAP, you will notice the following: The data structure of order-to-cash (SAP module: Sales and Distribution SD) can be developed to be far more complex than that of purchase processes. While there are mainly purchase requisitions and purchase orders in purchase processes, you can find an order-to-cash flow within order-to-cash processes. This flow can be defined to be even more flexible and this can be done on an individual basis. In the simplest case, you will be dealing with the following “trio”: Order => Delivery => SD invoice. But it can be extended significantly in terms of requests and offers. The order-to-cash flow is taken into account by the Financial Process Mining Algorithm, meaning that it is, therefore, possible to fully reconstruct every step made within the data.
What are the important SAP tables in order-to-cash?
The most important SAP tables for the order-to-cash process for the auditor are:
- VBRK: Billing Document: Header Data (SD)
- VBRP: Billing Document: Item Data (SD)
- LIKP: SD Document: Delivery Header Data
- LIPS: SD document: Delivery: Item data
- VBAK: Sales Document: Header Data
- VBAP: Sales Document: Item Data
- BKPF: Accounting Document Header
- BSEG: Accounting Document Segment
- KNA1: General Data in Customer Master
- KNB1: Customer Master (Company Code)
- KNBK: Customer Master (Bank Details)
What are the relations to accounting?
Certain documents in accounting point to documents in order-to-cash. Such accounting documents are usually sales invoices and outgoing goods. The position of an accounting document (BSEG table) in the VBELN data field (billing document) refers to the header of an SD invoice (in the VBRK table).
Extracting data from your SAP system
Mining data from your SAP system is not always easy. There are various tools on the market which allow for data extraction from SAP systems. An ABAP program is usually installed for this purpose. This can however be a “show-stopper” because transactions into a SAP system often require a Change Management Process, and that can take a long time, due to the various authorization steps involved.
Another possibility is to manually download single tables from the SAP system. The transaction SE16 may be used to do this. This involves a lot of manual tasks however and can be a rather cumbersome process.
By using Remote Function Call (RFC), there is another way of extracting data from your SAP system. You only need a SAP user with corresponding user access rights to call the Remote Function Call component. One of the big advantages of this is that you do not need to install any ABAP programs in your SAP system. Compared to other techniques, RFC is pretty old and can sometimes be slow, though it is well-established and available in all SAP systems. RFC can thus be a convenient and effective way of optimizing data extraction and can be used to extract data for analysis from the SAP system with relative ease.
9 interesting data indicators for your next sales audit
In what follows, I will introduce 9 interesting data indicators for your next order-to-cash audit. We will start with data indicators based on the SAP SD (Sales and Distribution) module, which is upstream of the accounting system.
Data indicator 1: Sales delivery prior to sales order
The sales process should usually follow a normal process. The chronological sequence of activities should therefore be evaluated with data analysis. In the case of the “sales delivery prior to sales order” data indicator, there is the risk that an order for a delivery has been created subsequently. A delivery (or the delivery note) will be marked if an order was recorded in the activity sequence after delivery.
Data indicator 2: Revenue without an outgoing invoice
This data indicator is particularly popular with the annual auditor who wants to exclude fake sales, since there is the risk that an item of revenue was posted without an invoice. How can you evaluate this by data analysis? This is where things start to get a little more complicated, but it works nevertheless: A document will be marked by the data analysis when it posts a revenue / income item (P & L account on the credit side) under the following circumstances. In over 90 percent of cases, there will be a posting line on the credit side of the revenue account that corresponds to a debit item in the debit (that is, a debit invoice). For the document in question, however, the debit item is missing on the debit side (= invoice).
Data indicator 3: Intra-community supply or service with VAT
VAT is a complex issue. If you want to avoid getting into trouble with the tax authorities, you better make sure you do everything right when it comes to sales tax! With this data indicator, there is always the risk that an intra-Community supply has not been processed without VAT or that the reverse charge system has not been applied to other cross-border services within the EU. Both should lead to an initial invoice which invoices a net amount – exclusive of VAT. A data analysis can check the following: The document is marked if the referenced customer is a business (data field KNA1-STKZN) and is located in a foreign member state (EU field) (data field KNA1-LAND1) or the delivery country and destination country are within the EU (data fields BSEG-EGLLD and EGBLD) and VAT (data field BSEG-MWSTS) was invoiced.
Data indicator 4: Missing VAT IDs in customer master data
Issuing an invoice can pretty much be considered to be a “formal act”. If an invoice is not complete, this will result in problems with the tax office. Either for you or your customers. It is therefore important, for foreign customers within the EU, that you have the VAT ID of your customer. Otherwise, there is the risk that the value-added tax may not have been correctly recorded or that VAT declarations may be incorrect. A data analysis can detect such cases as follows: The accounting document is marked if it references a customer which is located in a EU country other than one’s own and which does not have a VAT ID in the master data. Natural persons as debtors must be excluded.
Data indicator 5: Ad-hoc address changes in customer master data
Customers that move often and therefore require a change of address in their master data should arouse your attention! There is the risk that frequent changes of address are being made for fraudulent or non-paying customers. A data analysis can be used to detect such cases as follows: The document is marked if it references a customer whose address has been changed at least twice within 180 days, and the entry date of the document is between the two change dates. Changes are only taken into account if the old and new value of the street name differ according to the SOUNDEX algorithm in order to exclude what are merely corrections of spelling errors in the master data.
Data indicator 6: Transactions with blocked customers
How is it possible to post to blocked accounts? Quite simple: by clearing the lock, posting an invoice, and then re-activating the lock. Here, there is the risk that customers that are actually blocked can be used to place an order despite being blocked. This presents a very clear pattern for unwanted events. With the help of data analysis, such cases can be detected as follows: The accounting document is marked if it references a customer for which a change to the revocation indicator has been made before and after this document in the sequence. The change also takes place on the same day.
Data indicator 7: Transfer of postings among customers
It is always interesting when postings are made to and from the subsidiary ledger, in our case for debtors. As an auditor, you should always be aware of whether this kind of thing is occurring and understand the reasons why something like this is going on in your accounts. There is the risk that balances may be shifted back and forth between customers without the original invoice. A data analysis can check this using the following criteria: The accounting document is marked if these account assignments refer to different debtors.
Data indicator 8: Segregation of Duties
The segregation of duties analysis is a good indicator to use on the sales process to assess the access security of your SAP system. The following examples of segregation of duties conflicts can be detected in this way:
- Maintain a sales doc and generate a billing doc for it
- Initiate a payment by creating fictitious credit notes
- Maintain customer master records and post fraudulent payments
- Create billing and inappropriately post payment
- Maintain sales docs and enter an incorrect invoice
- Maintain fictitious customer and initiate orders
Data indicator 9: Reversed incoming payments
Cancelled incoming payments are an indication that the process of clearing is suspicious. As an auditor, this is something that should arouse your interest. There is the risk that incoming payments have been incorrectly allocated. With a data analysis, you can determine whether this is the case using the following criteria: The accounting document is marked if it is a cancelled incoming payment. A payment is recognized when a payment has been booked to an accounting account which is linked to a bank account.
In addition to the 9 sales data indicators presented here, there are many other data indicators available! If you want to find out more about what you can analyze for the purposes of a sales audit, you can download a description of all the sales data indicators available in zapliance here:
In the next blog post, you will find out how to detect hidden funds in order-to-cash processes and how you can use targeted data analysis to uncover such cash savings.