Year 22 – 2009 – SAP Extract

Imagine my excitement when I had 7 responses to my previous post on Payroll and then my utter disappointment when I found out that all we in Russian and had nothing to do with the content of my blog.  This continued for several days and suddenly switched to English posts about Credit Unions.  In total I had over 65 spam bot posts including two that wanted to help me monetarize my site.

On the positive side, I was talking to Franco who said that he reads my blogs every Monday and the most recent post gave him some ideas of a payroll analysis he wanted to perform.

Audit example – Standard SAP extract:  By now, I had been extracting SAP data for 10 years and had developed a “Standard SAP Extract”.  While SAP has more than 70,000 tables, I was using 2 main tables and 9 master tables.  Using this set of data, I had supported hundreds of audits.  In addition, I had changed companies twice and was able to use the exact same extract (and all of my ACL scripts) at the new companies.  Unfortunately, it took between 6-12 months to get the extract built at the new company.

I had recently changed job again and was in a position where I could support many different internal audit shops.  I was working for a central agency government department that provided functional leadership and direction to the internal audit community in the federal government.  More than 100 government agencies had independent internal audit functions – each with their own CAE and each reporting to the head of the government agency.  About 40 of these had SAP as their main system.  I convinced my manager that we could develop a standard extract for SAP and develop ACL scripts to support audits in these 40+ agencies – increasing the efficiency and effectiveness of the internal audit function across the entire federal government.  He agreed.

The first step was to establish a CAATTs working group which was open to all federal government auditors.  We quickly realized that others – such as internal control, financial monitoring, and risk officers – were interested in joining the group.  So it went from CAATTs (computer assisted audit tools and techniques) to DAWG (Data analytics working group).  One of the first tasks of this group was to identify the type of information they needed from their SAP system.  Over 20 agencies provided me with a list of audits and the types of information and reports they obtained from SAP.  I compared this list with the extract I had been using for 10 years and developed a slightly larger field list.  While much of the information could be obtained from different tables, all of the detailed transactional information was also in the BSEG and BKPF tables in the FI modules.  So an extract of two tables (quite large for some agencies) was all that was required to support hundreds of audits in 40 different audit shops.

In SAP, pretty much all transactions affecting the financial statements flow into BSEG (detail) and BKPF (Header).  Regardless of your installation, there are standard fields – and this is what I extracted.   We hired a firm with SAP experts to build the ABAP (SAP Programs) and extracted: BSEG and BKPF (year to date) as well as master tables (GL, Vendor, Customer, Cost Center, Internal Order, Employee, Commitment Item, and User).

The data in BSEG/BKPF allows you to examine: accounts payable; accounts receivable; payroll; GL and journal entries; asset loading and depreciation; expenses related to projects, internal order, and cost centers.

I built ACL script to read the SAP extracts (extracted as flat files since the number of records could easily reach over 100M for a fiscal year).  The script also combined BSEG and BKPF so the header information was available for each detailed record.  In addition, the script took the Vendor/Customer line – which contains the vendor or customer number – information and copied it to the other lines in the document.  This meant that I could look at Vendor/Cost center or Vendor/GL combinations easily.  The script also produces summaries – by GL; by Cost Center; By Customer and by Vendor.

Lastly, the script performs trending analysis for things like: number of cost centers, vendors, GLs, etc.; an analysis of invoices (percent less than $500 and percent paid immediately), journal vouchers (number over $50K); etc.  This allows you to compare period by period totals to identify unusual trends.

The SAP Extract and ACL scripts won several awards including the Public Service award for Technological Innovation and the ACL Impact Award.  In addition, in 2016 I convinced ACL that the concept of a standard SAP extract employing DirectLink would be worth pursuing.   They improved the user interface and released a set of SAP plug-and-play scripts for Accounts Payable based on my work.  Recently, they announced an expended series of ACL scripts to examine other areas of SAP (inventory, payroll, journal entries, etc.).  So my work will help even more auditors – worldwide.

I was also asked by the IIA to be the principal author of the Global Audit Technology Guide (GTAG #3) on Continuous Auditing: Implications for Assurance, Monitoring and Risk Assessment.  The recently updated version is much improved, but at the time, this was important guidance on the development and use of continuous auditing and monitoring applications.

I also changed publishers (John Wiley and Sons) and update three books: Internal Audit – Efficiency through Automation; Computer Assisted Fraud Prevention and Detection; and Fraud Prevention and Detection using ACL.


Lessons-Learned: the biggest lesson was that even though I had been performing analytics for 20 years, there was more to learn and accomplish. Early in my career I attended an audit conference and one of the keynote speakers said, “If you only retain the skills you have now – you will be obsolete in 5 years.”  At the time I remember dismissing this as ridiculous, but within a year or two I realized that it was true.  If it was true in 1992, how much more is continually maintain and improving your skill set important today.  Within the last five years e-commerce and e-business have been common; cloud computing has become a major option for companies; big data has been successfully analyzed and used to improve operations; the internet of things has become more than a concept.  Each of these requires auditors to address risks that did not exist before and to have new areas of expertise.

It was an exciting and busy year.  I had the opportunity to provide guidance and direction on a global basis; to support hundreds of auditors seeking to perform analysis of SAP data; and to expand the reach of my instructional materials.  The icing on the cake was to receive awards for doing something that I loved.

Leave a comment