Looking for paradise (untaxed)
Actually "The Trip to Panama" would have been a better header, but the news desks of the broadcasters beat us to it. Although of course the children's book by Janosch, with the tiny tiger and little bear, has nothing to do with the affair revolving around the letterbox or shell companies of the Panamanian law firm. But the title is just so catchy.
In this blog post I start by briefly going back to an older, comprehensive posting based on the example of a data analytics approach posing the concrete question "Tracing payments/business partners in critical countries". The major part of today's post nevertheless focuses on pointing out some specifics to watch out for when dealing with data from SAP®. The examples chosen are fictitious (and – admittedly – bold and simple). They are based on a SAP® IDES system in the version SAP® mySAP ERP2005 ECC 6.0 (unicode).
As indicated, tracing tax havens, payments or business partners in such countries is not a new topic. Actually it should be a standard analysis when auditing enterprise data or, depending on how roles are split, also be worked regularly and automated from the point of view of compliance. You can also say it short and sweet as KYP – not "Keep You Posted" but "Know Your Partners". Even if motives or interest in delving into such facts may be slightly different in the present case, we see an overlap with auditing or compliance-driven questions when it comes to the topic.
The analysis "Business partners in critical countries" in the context of tax havens, by the way, was in the past a central example used in our series "CCM – 5 things you should know – and 2 things you better not forget". The focus there is on how to structure such data analytics in the form of automated or continuous controls monitoring / continuous auditing. The necessary steps named begin with definition of the question and identification of necessary data source(s), continue through examination of relevant data structures, understanding technical possibilities and constraints to matching results to the particular recipient. So take another look at this series of articles if you are interested in the basic methodology and project milestones.
Today however, taking concrete examples, I want to show that such simple analytics do not always come up with the familiar quick wins or low-hanging fruits. Instead that you often have to dig more deeply to really get to the bottom of even a relatively simple question.
I will look at the following points:
- Principal office as part of global master data in SAP® tables
- Role of bank country compared to country of domicile
- Non-transparency of manually made payments
- Risk of one-time master records
- Significance of partner functions
- Actual reporting date of master data compared to history
To start, let us take the country of domicile of the business partner:
1. Principal office as part of global master data in SAP®
Of course your business partner is usually held in the system as a master record, ie will be saved with name, address, contact data, tax number and other relevant details. This, by the way, not only means partners like customers and vendors but also affiliated companies. The latter can generally be identified separately by the label "Trading partner" and/or by their own account groups or an appropriate control account. The address details of the master record will also include the country of the partner or affiliate company.
Now to take a look at a typical vendor master record in our SAP® IDES system. This is the master record of a supplier numbered 1700, the SAP® transaction being XK03 (central display of vendor master data).
Fig. 1 - Supplier 1700 – country of domicile Panama
Most of the address information visible here in the screen shot is saved in table LFA1 for suppliers or KNA1 for customers. In both cases the field name for the country attribute is "LAND1", and the field often used to differentiate companies belonging to the group is "VBUND". (The latter is not completed in the case above, so it is presumably a supplier not part of the company group.)
"That's it" you might think – you only have to compare the countries of the business partners to a list of critical countries, output and trace the hits, and perhaps separate group companies from "third parties". But the more you get into the matter, the more aspects you encounter that are important in this context.
2. Bank country versus country of domicile
It is one thing in which country the creditor or debitor has their domicile, but it will possibly also play a role in which country (or countries) the business partner or affiliate company maintains bank connections. The bank country for customers and suppliers may be located in SAP® data structures in the tables LFBK (bank data creditors) and KNBK (bank data debitors). If you only analyze the countries in the global supplier or vendor master as explained in the example of the principal office, you would not consider this aspect. Anyway, bank data can often be analyzed in the context of suppliers especially if the usual payment channel is credit transfer, because no transfer can be made without a known bank account – at least by an automatic payment run. Customer bank connections on the other hand are often somewhat sketchily maintained in as much as they are not needed for direct debiting procedures. If the debitor can pay by credit transfer, in other words you wait for cash receipt without doing anything, well then your company does not need the bank data of the customer.
So let us again with XK03 take a look at a supplier, in this case creditor 3000 in our SAP® system. The country of domicile is US – so far so good.
Fig. 2 - Supplier 3000 – country of domicile US
But when you take a closer look at the bank connections created for this creditor, by paging through the different views of the vendor master data, you find after a few clicks that there are two bank accounts for supplier 3000 in the system: one with bank country USA, and another abbreviated VI for the American Virgin Islands.
Fig. 3 - Two different bank connections for supplier 3000
This shows that looking into the bank country can be very much of interest. (By the way, as a result of the change to IBAN it is also quite interesting how in SAP®, from the point of view of data, the relationship between these bank connections of the "old world" and the new IBAN system is implemented. But that is a subject worthy of a separate blog post. For our purposes LFBK and KNBK will do.)
3. Non-transparency of manually made payments
A classic question in auditing is the search for manual payments. But why should they be seen as critical in the first place? It becomes clear in the context of country analysis: An important aspect of minimizing risk is to make the payment process as stable and automatic as possible. That means, ideally an order goes to a supplier, they deliver, and place an invoice. The invoice is booked under their supplier number, the bank account on the invoice is identical to that held in master data and is used automatically. The payment program (in our case the SAP® payment run by transaction F110) schedules payment optimally according to terms of payment (period, discount), and creates a payment list that is sent to the bank automatically and without any possibility of manipulation.
So much for the ideal case. But if payment is made "outside" the system, no banking connection held in the SAP® system is needed; so payments could be made manually to partners (or, to put it another way, to countries) that are not to be found in connection with the target banking connection in master data tables. One solution would be to analyze electronic account statements in as much as the appropriate SAP® functionality is used. Often you may find third-party solutions here, which should also be considered. To conclude this point: Only by looking at bank account movements outside the financial accounting system, ie at the level of account statements, can you be sure to have grasped manual payments too.
4. Risk of one-time master records (conto pro diverse)
We have already spoken of CpD (conto pro diverse) elsewhere in the blog. In brief terms, the risk may be that no master record explicitly created for this business partner is used, and thus the information held in master data tables, like the bank account, is not used. In the case of invoices booked under a CpD supplier the bank connection can even be recorded ad hoc. In this case too, the recipient countries or recipient bank accounts are not to be found in the LFBK and KNBK master data tables. The reason is that there is usually only a general part for CpD master records (eg "Collected suppliers A-K"), but that they have no concrete bank connection because this can of course be different depending on the posted document. Still there is good news for data analysts, at least if the SAP® payment run was used to make payments, and no manual payment: The REGUH payment run table logs recipient bank data and thus the recipient country too for each payment run. The following screen shot shows not the table but the transaction F110 of a concrete payment run payment.
Fig. 4 - Detailed view of CpD payment by automatic payment run (REGUH table)
A one-time vendor was used and so the recipient bank data are not to be found in the LFBK table. The details of the payment run show the bank connection actually used including the country (JP = Japan).
5. Partner functions
The next aspect I want to look at is partner functions. These are often used in SAP® to map complex business relationships. In the simplest case you have the following scenario for suppliers: An inquiry goes to supplier 1000, they generate a quotation, receive our order, send us the invoice, and receive payment. Or, as customers see it: Customer 2000 places an order with us, we deliver to customer 2000, send the invoice to them, perhaps receive a return, and for that award them a credit memo. All these transactions can be found under the same partner number.
But there are other constructs that perhaps may even differ within different sales documents for the same customer: The order comes from customer 2000 in country A, but delivery is to customer 3000 in country B, and the invoice goes to customer 4000 in country C – of course these in turn can have their registered offices and their bank connection(s) in different countries. The good news is that master records used in partner functions are also to be found in the normal master record tables LFA1 and KNA1, and can be traced by the appropriate partner number.
Fig. 5 - Partner roles for customer 1000 – Payee is 1050
If we look at this in detail, we note that the payee in 1050 has its headquarters in a critical country:
Fig. 6 - Country for payee 1050
But what is important, when starting to analyze critical countries proceeding from business transactions (eg sales documents in the SAP® SD module), is to consider the presence of partner functions at the level of the sales document. You can find the partner functions at document level in the SAP® table VBPA.
6. Changes in course of time
Finally I want to make you aware that in most observations of master data you see the actual status, ie the status at the instant of data export or looking at the source system. But address data and bank details can of course have been changed in the course of time. To achieve some certainty for your data analytics, seen historically that is, it can be helpful to take a look at the SAP® change tables, in which you find logging that includes the attributes "old value", "new value" plus matching timestamp. A starting point for this would be the CDHDR and CDPOS tables for instance, often mentioned here in the blog, and for which you can find a whitepaper in the downloads. The change classes here would be KRED for creditors and DEBI for changes to debitor master data.
I hope I have managed to give you a little insight into points that can be worth watching if you – perhaps because of what is currently going on, ie the Panama Papers – are interested in analyzing business relationships in critical countries or tax havens.
Even if the examples were illustrated by SAP® screen shots, these kind of analytics naturally make more sense at the level of the database / raw data using your analytic software. Here we can offer you direct support, like with our dab:Exporter data extraction software, enabling you to simply extract the above-mentioned tables – some of them can be very large. What may also be relevant for you is ACL™ analytic software for performing manual analytics, or our ready defined dab:AnalyticSuite analyses in which we have already mapped these country comparisons in the form of standard reports at the push of a button. Just call us any time if you have any questions.