The analytics team was now at three people – so I could build some overlap in our knowledge of the application systems that were we accessing on a regular basis. For example, this meant that I was no longer the only person who understood the financial system. Now we had at least two people for each of the 10-12 systems we were accessing on a regular basis. For all but the inventory system (an IMS database we accessed with ACL for MVS IMS interface) we were getting monthly extracts – either by running the extract jobs ourselves, or as a standard production job.
The first question I had to address in building a team was the level and experience of the people that should be part of the analytics function. A related question was: Should an auditor be taught programming (data extraction and analysis) or should a programmer be taught to conduct audits? Failures in implementing analytics have one thing in common — management did not assign the right person or people to the task. Too often, a junior programmer with limited or no audit experience — addressing only the IT aspects of the job — is assigned to develop the analytics function. Given the nature of the task — dealing with business process owners, system programmers, and review team leaders — the analytics function must be staffed at the appropriate level and with the necessary experience. The biggest hurdle is having the business process knowledge to identify the types of analytics to run. Because of management’s support, I was able to hire people at senior auditor or team leader levels. One was a programmer with IT audit experience, the other a programmer willing to learn about audit.
The size of the analytics function is the next question to be addressed. This will depend on the size of the audit function overall, as well as the analyses to be performed and the types of technical expertise and experience that are available in the audit organization. If responsibility is assigned to a single person, he or she must be, at a minimum, the equivalent of a team leader level and must have data extraction and analysis and evaluation or audit experience. This will mean hiring someone with the required skills if they do not exist in-house. As the use of data analytics increases, the analytics function can grow, adding junior levels, a career path, and mobility to the function.
Finally, the analytics function must be visible and knowledgeable of, and responsive to, the needs of the review teams. At the same time, it must also be proactive in recognizing opportunities for the application of data analysis and in marketing existing and new applications of technology. I tried to make sure the team was doing all of these things.
I was auditing a $4B equipment project with the objective of designing, building, and maintaining a specialized piece of equipment; and the project office was expected to be around for 7-8 years. The project’s Finance Manager purchased a system that supported work breakdown structures, contracting and financial reporting and identified the costs and parts needed for ongoing equipment maintenance. I was leading an audit of the project management office and one of the objectives was to ensure that the project management system’s information was accurate and complete; the controls were adequate and effective; and that the interface between the project management system and the corporate financial and maintenance systems was working properly.
For a portion of the audit, I extracted information from the project management system and compared it with data extracted from the finance and maintenance systems. This allowed me to assess the accuracy and completeness of the interface between the systems as well as the accuracy of the project management system’s reporting to the steering committee.
The first thing I noticed was that the project system – while it had a detailed work breakdown structure, sufficient financial information at the general ledger account level to support the estimation of the ongoing maintenance costs was not be recorded in the corporate financial system. A simple classify by GL Acct, totaling the Amount, showed that the project system was only uploading four GL accounts: regular salary, overtime, travel and total project costs. The information was not sufficient as the use of only four GL accounts made the ongoing management of the equipment – after the closure of the project office – impossible to forecast with any accuracy. Typically ongoing maintenance would be estimated as a percentage of the cost of the equipment – not including professional services and other costs.
The second thing I noticed was that the interface to the financial system was manual and only summary information was being uploaded (totals by GL account by period). When comparing the project system’s details, I also noted that overtime was much higher than was being coded in the financial system; as was the amount for contracted professional services and IT development and maintenance. Amounts were being shifted from overtime and professional services to regular salary by journal vouchers each month. The adjusted amounts were uploaded to the financial system. Also, the project reports presented to the steering committee – which supposedly came from the project system – had been altered to agree with the totals in the financial system.
I asked the deputy contracting manager why professional services and IT development and maintenance were being recorded as regular salary. He said that there was extra budget in salary and they had max’ed out the budget for professional services. I had seen this before – but knew that it was easy to properly adjust budget from one account to another – particularly for a large project like this. When I challenged him on this – he pulled me into an empty meeting room and quietly explained that the project Finance Manager was deliberately hiding contracting and IT expenditures because he was using earmarked project funds to support the development of a corporate project management system without approval from senior management.
I met the project’s Finance Manager and he explained that the original company who provided the project management software had gone broke and we now “owned” the software and were legally allowed to modify it. He also stated that since the company had many large projects, we would benefit from having a customized project management software package. However, when I talked to the Senior Project Manager, she clearly stated that she did not have the authority to spend earmarked project funding to develop a project management software application and was unaware of the efforts of the Finance Manager.
I reviewed the contracts related to IT services and the project had spent over $12M for the modification of the project management software without a solid system development plan, change modification process or other sound programming practices in place.
I continued my review of the contract details analyzing them by contracting option: sole source, non-competitive and competitive. This clearly showed that all of the contracts were sole-sourced to a single firm and that the contract amounts were just under the competitive bid threshold. When I questioned the Contracting Manager about this, he said that the Finance Manager had instructed him to split the contracts so they could be sole-sourced. I asked him why he had signed all of the contracts except for the 14 contracts I had in my hand – which had been signed by the Finance Manager. I wondered why the Finance Manager was signing contract – this seemed like a conflict of interest. He said that those contracts were for the services of the Finance Manager’s daughter (under her married name) and he refused to sign them. He told me that she was being paid at a senior programmer level but in fact had no programming skills and was only performing clerical duties. Suddenly I was immersed in a possible fraud.
I performed additional tests: checking contractor addresses against employee addresses; calculating maximum prices paid for items; verify overtime hours and pay rates against the time reporting and HR systems; and verify travel expenses. In the end, the Finance Manager was investigated for possible fraud, misuse of project funds and mismanagement. What started as an IT audit to examine the completeness, accuracy and system interface, became three projects: IT audit; Contracting for IT services; and a fraud investigation.
Through data analysis I was able to show that the interface was not adequate; the information being transferred to the financial system did not have sufficient detail (summarized and too few GL accounts); and did not agree with the project management system. I was also looked at ranges of the contract amount (Stratify) to identify contract splitting- just below the competitive bid amount; matched the project and finance systems to identify misuse of project funds ($12.2M); and several other contracting irregularities. The fraud investigation resulted in the Finance Manager being fired, the Deputy Project Manager being demoted for favouritism and nepotism; and the Project Manager being removed.
Another audit that year was a basic inventory audit at a large warehouse. The objective was simply to determine if the controls over the safeguarding of materials were adequate. Because of the size of the warehouse and nature of items – hi-tech, portable, attractive items – the warehouse had been audited every few years. Typically the audits compared what was on the shelf with what was recorded in the system and while there were always minor variances, no significant issues were identified.
The audit program called for a sample of items to be generated from the inventory system showing the item number and quantity. The auditors then went to the warehouse and counted the number of items on the shelf. The team leader was aware of several fraud techniques and had the auditors open shrink wrapped boxes to ensure the boxes actually contained the items. She had heard about sand or rocks being placed in boxes when the items had been stolen.
For the first time in this audit – we had data analysis software to perform the sample. Previously we simply taken every 100 items or generated a list of random numbers and matched this to a hardcopy list of items in inventory. Now we could look at quantities and develop a stratified sample. In order to produce a stratified sample, I used statistics and stratify to determine the number of items and quantities in various ranges of the data. This allowed us to select certain items that had more quantity or value (top strata) or were considered to be attractive items (e.g. computers and hi-tech equipment). This made for a more efficient sampling method and a better representation of the sample.
I wanted to supplement that audit by not only comparing the system total with the count of items on the shelf, but also by comparing the receipts and issues of goods to generate my own totals for each item. We obtained a transaction file with all “receipts” and “issues”. Receipts were recorded at the receiving dock and detailed the quantity received, by item, by contract, by shipment. Issues were more complex and could be: goods ordered by a project; returns; scrapped items; etc. For the purpose of the audit objective a computed field (Total = Receipts – Issues) was calculated and the sum by stock number compared to the system totals by stock number. As in previous years, there was very little discrepancy.
I also performed statistics on the Receipt quantity and the Issue quantity – just to get a feel for the data. The results showed the number of positive, negatives, top 5, lowest 5, etc. When reviewing the results, I noticed that there were negative Receipts. I showed the results to the team leader and we thought about what a negative Receipt could mean. These could not be returns, short orders, or issues to projects because the system only allowed the receipt clerk to process receipt transactions. I also produced a Classify by clerk on the negative receipt records – only two of the 15 clerks had ever entered negative receipts and one was no longer with the company. The team leader decided to ask the receipt clerk in question about these entries.
When she approached the clerk and said, “We want to talk to you about these negative receipt entries” the clerk immediate broke down and said “I knew you would catch me eventually.” Despite being somewhat shaken by the sudden confession, the team leader quickly recovered and said “yes, but we wanted to give you a chance to explain how and why you did it.” The clerk proceeded to tell us that controls prevented him from entering anything by a Receipt transaction; however, the controls did not prevent negative receipts. So he would enter a receipt of 10 laptops and reference the proper invoice number. This was followed by a receipt of -2 laptops (without an invoice reference) and he would steal two laptops. The inventory system total would show 8 laptops which agreed with the physical inventory. The invoice quantity would match to the receipt quantity of 10 and the vendor would be paid. While the transaction with the negative receipt did not have an invoice number it did not show up on the report of unmatched receipts because the receipt quantity was less than zero because the programming logic only looked for receipt quantities greater than 0. He had learned of the control weakness from the other clerk.
The total of the negative receipts was $375K over the past 5 years. Three audits had been performed during this time and had not identified a serious problem with the controls over the safeguarding of assets.
Analysis – ACL Commands: FILTER; EXPRESSION; STATISTICS; STRATIFY; CLASSIFY; JOIN; and SAMPLE
Lessons-learned: it always pays to ask “Why”. “Why were there negative receipt quantities? Why didn’t you sign theses contracts? Why are all the amounts for just under the contract competitive bid limit?” I could ignore the items or simply reported the non-compliance, but by asking why, we were able to find the source of the problem – the root cause.
More and more business process were supported by integrated applications and automated controls (e.g. 3-way matching of quantity and unit price) but if the data can be manipulated, these controls are ineffective. Controls over safeguarding of assets include the accurate entry of the information and the controls over the unauthorized entry or manipulation of the data – not simply comparing the system total with the count of what is on the shelf. SAS #99 states that substantive testing is not sufficient and auditors must test IT controls. It also requires auditors to use a technology focus (data analytics) for fraud-auditing and IT auditing procedures.
Always maintain a healthy level of skepticism – about the adequacy of the controls and management’s monitoring processes. Even major projects with project management offices, oversight committees and strict reporting requirements rely on the proper functioning of controls and supervision. The Senior Project Manager has abdicated all responsibility for finance and contracting to the Finance Manager. Allowing the Finance Manager to perform both financial and contracting duties was already a serious separation of duties issues, but this was compounded by the fact that there was no oversight. He was even able to deceive the Project Steering and Management committees by producing false reports and altering the data before uploading it to the corporate financial system.