Life Lessons

I often write about analytics and other aspects of data extraction, interpretation and the like.  Today I thought I would do something different.

I am teaching a 4th year university/college course.  The students are eager to get their CMA/CPA designation and go off to work for a big accounting firm.  I admire their zeal, but worry about the lack of practical “seat of the pants” knowledge.   I tell them that for 16 years their focus has been on “marks” and for the next 30 years marks mean nothing.  They need to focus on interpersonal, communication, analytical, strategic, how details support the big picture and other skills that schools tend to ignore.

What has experience taught me? Or “what I wish someone had told me”.  So here goes, a list of my life-lessons:

  1. I should have taken more chances. I was always worried about failure – which I didn’t try things.  What I realize now is that not trying is pretty much the same as failing.  Except for one thing, you can learn from failing – you can learn from not trying.  It just reinforces the notion of not trying.  I was fortunate to have mentors and managers who forced me to try new things and move beyond my comfort zone – things I never would have done on my own.  In every failure there is a lesson-learned that you would not have discovered without trying and failing.  These can motivate you to try again and ultimately succeed.
  2. There are many wrong and many more right paths. There is no single way to get to a destination.  Initially, I never even considered internal audit or data analysis, but the paths that I have taken have helped me to succeed in data analysis.  My years in guidance and counseling have helped me to relate to programmer, business process owners, and auditors.  This in turn has allowed me to better understand their needs and assist them.  My stint as a consultant has helped me to focus on critical issues and develop work plans to achieve results with time frames and constraints.
  3. Take on challenges that are outside your comfort zone. Many organizations have charitable campaigns, university recruitment programs, and other opportunities for you to try new things.  Typically, these are ideal learning opportunities because the other people involved are there because they want to be.  They are motivated, they are people –people, and they are great people to know (from a career perspective).
  4. Seek out mentors at various levels. These can be part of a formal mentoring program or simply people who have experience that they are willing to share.  On the same note, be a mentor.  Being a mentor will cause you to consider the “why” things are the way they are.
  5. Take what you like about your job and enlarge it. There are always parts of a job we hate, but we spend too much effort on avoiding these and not enough on developing and enhancing the aspects we love.  Almost every job I took on looked totally different after I had been there a few years.  I was willing to develop, enhance, expand the areas I enjoyed while completing the less interesting duties.  You will always find time to do things you love.
  6. Put yourself in other people’s shoes. What motivates them and what pisses them off.  Understand that they have priorities which will differ from yours and work with them to accomplish what needs to be done.
  7. Never bring your boss a problem. You can highlight issues, but always follow this by a suggestion for improving the situation.  I worked with a brilliant programmer who always pointed out the problems with any program development activity.  I had to constantly say. “OK it is a problem, but what should we do”.  He knew the answer, but it was frustrating to have to force the answer from him.  He became well known for being a program killer when in fact he was an incredible resource.
  8. Make the path straight for your subordinates. I always thought that my job was to make my employees’ job easier.  I would act as a buffer, protecting them from negative and non-constructive feedback.  I would fight on their behalf (without them knowing).  On the flip side – when they succeeded, I looked good so it was a win-win situation.  The better they did performing tasks for which I had responsibility, the better my manager thought I was doing.
  9. If you don’t know – say so. I was often the good-to person when it came to analytics and information systems.  Quite often I would get ask questions for which I did not have an answer and I never hesitated to say “I don’t know”.  Too many people see admitting a lack of knowledge as a weakness.  Counter-intuitive as it may sound, by saying “I don’t know”, my credibility and stature grew.  However, I was also quick to follow “I don’t know” with “but I will find out and get back to you”.
  10. Be respectful of everyone regardless of their level and position. One of my first jobs I had some photocopying to be done.  I left the originals and a short note “25 copies, double-sided, Dave” on the desk of the person responsible for making copies.  The person was not impressed with my lack of common courteously.  I should have said “please make 25 copies, double-sided.  Dave”.   Better yet, I should have included her named “Carole, please make 25 copies ….”  Since then, I have thanked the person who empties my garbage can and vacuums the floor.  I have tried to learn the names of the security guard, the janitor, etc. to the same extent that I want to know the CEO, CFO and other senior management’s names and faces.  This simple recognition of others and the work they do has made my job more pleasant and brought me as much benefits as it did those to whom I have treated appropriately.
  11. Be your own career manager. Twice I chose to take a position that offered less pay than my current position.  Why?  Because I thought it was better for me at the time and in the long run.  I doubt any career manager would suggest you take a 15% pay cut.  They don’t know your current situation, your aspiration, goals and time lines as well as you do.  So take charge of your own career.
  12. Make friends at work. You will be spending 8-10 hours a day, 5 days a week, with your co-workers, maybe more time than you spend with friends and family.  Be nice to them and give them every reason to be nice to you.  Join in office parties, social events and even coffee breaks.  Some of my best friends are former co-workers.
  13. Work to live; don’t live to work. While you are managing your career – manage your life.  You can’t wait until you retire to start enjoying yourself.  Discover new and interesting leisure activities.  Most of all – make time for you, and your family and friends.
  14. Never stop learning. Grow as an individual and as a professional.  Learning new things can stimulate and energize you when you need it most.  Sometimes a hobby can become a second career or simply a source of joy and amusement.
  15. Learn what is important to you and add it to this list.

The Data Analytics Conundrum

Studies after study have shown that data analytics is more effective and efficient at detecting risk, and identifying control weaknesses, non-compliance, and inefficient business processes.  Chief Audit Executives (CAEs) have repeated stated that data analysis expertise is a much needed skill in internal audit, and IIA surveys of software over the past 10-15 years have rated data extraction, data analysis and analytical software as critical tools for effective audit organizations.   Why then do more than half of the internal audit organizations still rate their analytic capability as poor or needing improvement?

I have been an internal auditor for 30 years and have been a user and advocate of analytics for 28 of those.  (It was during the first two years of auditing that I realized I could do a better job by analyzing data.)  I have been asked hundreds of times, “How can we develop and maintain an analytics capability?”  Too often CAEs give up without even trying (“we are doing good audits now, why change things?”; or only make a feeble attempt at it (“let’s get a programmer right out of college and have them develop analytics for us.”)

The reality is change is difficult.  As auditors we are constantly making recommendations to help others improve, change, do more, etc. but we ourselves stick with traditional auditing tools and techniques.  Perhaps what is needed is a taste of our own medicine.

In order to successfully implement analytics and integrate data analysis in the audit process you must have a formal development and implementation plan.  The plan must address the need for sufficient people (appropriate level and number), technology (primarily software) and processes (you need to change the way you currently perform audits).  It must also have a project manager who will be held accountable for delivery on the plan, clear objectives, milestones, and a reporting requirement – to the CAE and/or the audit committee (but only if they fully support the adoption of analytics).

Analytics also requires an understanding of the business processes, the data and supporting them, and a solid grasp of internal auditing processes and requirements (e.g. application of the IIA standards).  None of these will be provided by junior level audit or programming resources.  Rarely will all these skills exist within one individual; and they might not already exist in your audit organization.  Rather than being an impediment, this should be seen as an opportunity: an opportunity to obtain the right resources and task them with a clear objective.  If you are lucky and have the appropriate type of resources in your organization – this is ideal.  Existing resources should already know the business processes and have the internal audit skills, and perhaps have some analytical capabilities.  However, they will need to be supported by training and software, and given sufficient time to develop the skills and implement the functionality.  Most importantly, they will need to be dedicated to analytics.  Otherwise you end up pulling valuable resources away from other priorities and tasking them with something in addition to what they are already doing; or settle for a subset of the required skills.  In either case, it is a recipe for failure.

A statement I hear often is, “We are a small audit organization, and we can’t afford to dedicate a person to analytics”.  It is usually used as a rationale for not using data analytics.  My response is something along the lines of “Because you are small does that mean you can afford to be less efficient and effective?”  The reality is, unless you are using analytics, you are not addressing risk, testing controls, examining compliance and improving business operations to the extent that you could be.  If you are going to decide not to use data analytics, at least make it an informed decision.  Examine the costs and benefits and then decide.  Don’t simply look at your existing resources, which are most likely being used to the maximum, and decide that you can’t take on anything else.  It is not a question of doing more with the same resources.  Ask yourself if there are things that you don’t need to be doing or if they are better ways to do what you need to do.  Also look at what you are not doing and determine the value-added if you could do those things.  Then decide if you can afford not to be using data analytics.

I also get asked about audit software, “which package should I use?”  This is something that should be decided based on your requirements and your short- and long-term plans for analytics.  I encourage you to fully utilize the existing capabilities such as standard reports and you can definitely start with Excel, but don’t be limited by what you have – think about what you need.  Find out what other audit organizations are using.  For more than 10-15 years, the IIA magazine, Internal Auditor, conducted a survey of software usage and the results were printed in the August issue.  The results have consistently shown that ACL is the most used audit software for data extraction, data analysis, and fraud prevention and detection in the world and has been for almost 15 years.  It is the software I use, so I may be biased, but just because you are biased doesn’t mean you are wrong.

In conclusion, you should be using data analysis.  You will need to plan and manage your adoption of analytics.  It will take time, resources, and technology.  It has to be integrated in the audit process (planning, conduct and reporting) and developed with an understanding of the business processes and the underlying data.  It is easy to do wrong, but worth doing right.

Why did I title this “The Data Analysis Conundrum”?  Because I don’t understand why we are still talking about the “Why, How, and What” of data analytics and not simply getting on with the job.  Stop asking questions about analytics – get off the fence and actively pursue it.  The successful implementation of analytics will add significant value to the internal audit function and your ability to support the goals and objectives of senior management.

Adding Value to Compliance Audits – part2

The following posts is part 2 of “Adding Value to Compliance Audits”

Given a good understanding of the current level and sources of risk, the next step is to look at the requirement for, and the adequacy and effectiveness of, the control to mitigate the risk.  This requires an understanding of the cause and source of the risk and the operation of the control.  Is the control still required?  Does the current control address the root cause?   Are there better ways to mitigate the risk?  By answering these questions the audit may identify unnecessary controls, ineffective controls, or identify better controls to address the current risk.  All of which may reduce the cost of compliance, while improving risk mitigation.  Recommendations such as automating a control can save time and effort and been seen as a real value add.

The next step would be to verify that the control activities are being performed (i.e. compliance).  However, you are not done yet.  If you find non-compliance it is still not sufficient to recommend “Do A”.    Audit recommendations should address the root cause.  Identifying a lack of compliance is not the same as determining why management is not complying (i.e. determining the cause).  Was management aware of the requirement? Are they capable of complying? Are there compensating controls that have been implemented?

In order to determine the cause of non-compliance asking “Why” (usually several times) is often sufficient to determine the cause.  You should also determine the impact of non-compliance.  Then instead of “Do A” audit can provide a rationale and make a recommendation that actually assists management in complying.

The next step is to ensure that you are doing the audit right – this means maximizing your use of all your resources available to you, including analytics.   Data analytics can be defined as the application of analysis techniques to understand business processes, to identify and assess risks, to test controls, to assess efficiency and effectiveness, and to prevent, detect and investigate fraud.  Data analytics techniques, ranging from simple categorization and stratification to sophisticated predictive and prescriptive models, can assist organizations in focusing their risk responses on the areas in which there is a higher risk – including compliance risk.

Existing levels of risk can be assessed and trends identified to determine if the risk is increasing or decreasing.  For example, environmental compliance could examine spills (number and quantity), cleanup costs, and lawsuits (quantity and value); production compliance could examine material, personnel, maintenance and operational costs.  By examining measures over several months or years, a trend line can be produced to assess the effectiveness of mitigation efforts and identify emerging risks.

Rather than relying solely on substantive tests, the effectiveness of controls can also be tested with analytics.  In addition, you can look at trends that will have positive or negative effects on compliance.  For example, environmental compliance can examine the control over the purchasing of hazardous materials – ensuring that the purchase quantities match requirements – thereby avoiding environmental compliance issues around disposal.   Compliance with hiring practices could review staffing methods, staffing rates (by gender, by race, etc) to ensure proper procedures are being followed and address employment equity requirements before they become non-compliance issues.

Sometimes compliance with a poor control can increase risk and dysfunctional behaviour; and cultural issues can make enterprise-wide compliance difficult for global companies and increase risk.  Doing the right compliance audit – not simply “did we do A” and doing it efficiently and effectively can result in significant value to the organization and remove the “got ya” stigma of compliance audits.  However, it requires auditors to re-look at the compliance-related risk and controls and use analytics.

Richard Chambers, President of the IIA, identified his top ten imperatives of the decade which highlight the challenges that auditors must face to provide value to management.  These include enhancing proficiency with data mining and analytics; providing assurance on risk management effectiveness; and enhancing and leveraging a continuous focus on risk.   These challenges can be applied to all types of audits from compliance to operational.  He encouraged auditors to look at business objectives, risks to the achievement of objectives and design audits that provide assurance over the governance, risk management and control/compliance frameworks put in place by management.  A compliance audit should not be any different: it should identify and assess risk; and examine the effectiveness and efficiency on the controls to mitigate the risk.   By doing so, it will add-value to the company as well as provide assurance to senior management.

Accounts Payable Example

In an Accounts Payable audit there was a requirement to formally authorize invoices for payment by stamping and signing the original invoices.  The stamp and approval verified that goods/services had been received in accordance with the contract and that the invoice could be paid.  Falsifying this approval had serious legal repercussions – including up to 5 years imprisonment.

The audit covered numerous accounts payable offices spread across the globe.  As a part of the audit we verified that invoices had been properly approved i.e. stamped and signed by the authorized approval authority.  At several locations we noted that the invoices were not being properly authorized (stamped and signed).  But the reasons for non-compliance differed.  In one small office (AP1) they were unaware of the requirement.  We identified an underlying problem with corporate communication of financial regulations, including a lack of translated procedures.  In another office (AP2), they had been told by the legal department that the stamp that was being used did not contain the appropriate wording and they should immediately stop using the stamp and obtain the official corporate stamp with the correct wording.  The local A/P manager had been trying for months to obtain an official corporate stamp – he even showed us numerous emails – to no avail.  At another location (AP3) they had converted to electronic invoices and authorization – so they were no longer stamping and signing invoices.

A compliance audit that did not ask “why” might easily have issued the simple recommendation “stamp and sign all invoices” – adding zero value to the A/P process.  Adding value to this compliance audit would have had very different recommendations.

Starting with the risk: The control was put in place to ensure that we were not paying for goods/services we did not receive; and that goods/services were of the quality, quantity, and price agreed to in the contract.  Given the nature of decentralized contracting, the risk still existed and a control was required.

The second step would have been to determine if the control was effective and efficient.  At AP1, the control was not working because of a problem in the corporate communication area – we had acquired a new overseas operation and regulations had not been translated.  This required a different recommendation; one that would address the root cause – corporate communication – and did not penalize the local A/P manager.

At AP2 non-compliance was attributed to a breakdown between the legal and finance departments.  Legal was reviewing all official stamps and finance was responsible for updating, revising and supplying them.  Unfortunately, the two departments were not coordinating their work and finance was unaware of the problem with the invoice authorization stamp.  This recommendation addressed the communication between departments.

At AP3, the physical stamping and signature of the invoice had been replaced by an automated approval.  Recommending compliance with the current regulation would be ludicrous.  However, the automated controls needed improvement to verify the authority of the person providing the electronic approval.  As a result, a recommendation was made to address the weakness in the automated control.

The result of the compliance audit improved the corporate communication processes, interdepartmental activities, and IT controls.  The recommendations were seen as having value – much more than “Stamp and sign all invoices” would have received.

In addition, the audit of the efficiency and effectiveness of the A/P process can benefit from the use of analytics.  The controls over approval can easily be done by matching the electronic approval with a database of approvers.  Examining actions by users can identify instances where separation of duties was not achieved.  Totals by payment terms or payment method can quickly highlight inefficient practices or even fraud.  The resulting recommendations can improve compliance and reduce business risks while adding value.

Adding Value to Compliance Audits – part 1

I have often been critical of compliance audits, but I recently realized that it is not the ‘compliance audit’ that bothers me, but the way it is done.  This led me to write the following thoughts.

It is difficult to argue that compliance audits are not an important internal audit product.  Done properly, they can protect a company from fines, penalties and even criminal charges.  For example, non- compliance with anti-money laundering legislation have recently had serious consequences.  A financial institution was fined $1.93B for failure to conduct basic money laundering due diligence in its operations in Mexico.  But it doesn’t stop there the institution is also being sued by families of murdered by Mexican drug cartels.  Non-compliance with environmental regulation has had significant monetary and non-monetary impacts on companies.  The Environmental Protection Agency’s enforcement actions include administrative, civil and criminal penalties.  And SEC has civil and criminal penalties for insider trading and other non-compliant activities.  Despite the seriousness of non-compliance, compliance audits are often not seen to be of value by many managers.  Possibly because these audits often look something like this:

  • Objective: Verify compliance with “A”
  • Criterion – you are supposed to do “A”
  • Condition – the audit found you were not doing “A”
  • Recommendation – Do “A”

And some auditors wonder why the client does not see any value in a compliance audit – particularly if they already knew that they were not complying with the requirements to do “A”.  These audits fail to identify the cause, and the impact, of the non-compliance.  Auditors need to do more – not only to ensure that compliance audits are providing real assurance to senior management – but also to be seen to be adding value.

There are two basic things you can do to add value to compliance audits: do the right audit; and do it right.  Doing the right audit mean examining why there is a compliance requirement in the first place.  Typical it is for legal, regulatory or operational reasons.  But behind the simple compliance “you must do A” there is a risk that was deemed serious enough for management or regulatory/legal authorities to put in a compliance requirement.  Now you are auditing compliance with that requirement – perhaps because there is a mandatory requirement to verify compliance on a regular basis.  However, risk shifts quickly in an economy where “speed of change” is a critical success factor of business, and it morphs rapidly in a world where globalization and automation results in shifts in strategic and operational initiatives of global enterprises.  Yesterday’s risk and compliance requirements are not always the same as those of today.  Changing risks and compliance requirements can affect not only the need for the compliance controls but also their adequacy.

The Institute of Internal Auditors’ (IIA) “Three Lines of Defense in Effective Risk Management and Control” model specifically addresses the “who and what” of risk management and control. The overarching context of the model acknowledges the broader role of organizational governance and governing bodies.  The model encourages auditors to expand their role to include risk and compliance.   In addition, it is not enough that the various risk and control functions exist — each must have a well-defined role and their efforts should be coordinated to avoid duplication and gaps in controls.  As a result, it is not uncommon to find teams of internal auditors, enterprise risk management specialists, compliance officers, internal control specialists, quality inspectors, fraud investigators, and other risk and control professionals working in concert to help their organizations manage risk.

Senior management and governing bodies collectively have responsibility and accountability for setting the organization’s objectives, defining strategies to achieve those objectives, and establishing governance structures and processes to best manage the risks in accomplishing those objectives.  The second line includes risk, compliance, financial controls, IT functions that oversee risk.  While the compliance function monitors various specific risks such as noncompliance with applicable laws and regulations; internal audit provides the independent assessment over risk – the third line of defense.

If you are a manufacturing plant, there are probably numerous environmental regulations that you must comply with; and publicly traded companies you will have SOX and other financial and legal rules and regulations.  Virtually every company will have a set of policies and procedures that must be followed to protect it from lawsuits, prosecution, reputational and other risks.  These are the areas where compliance requirements will be established and where audit will perform compliance audits.

Internal audit provides assurance on the effectiveness of governance, risk management, and internal controls (GRC), including the manner in which the first and second lines of defense achieve risk management and control objectives.  The scope of this assurance covers a broad range of objectives, including compliance with laws, regulations, policies, procedures, and contracts. (IIA Position Paper: Three Lines of Defense in Effective Risk Management and Control (June 2013)).  But it should not be “compliance simply for compliance sake”.  Audit should be mindful of the overarching business objective and the controls that are put in place to help mitigate risk to the achievement of the objective – even when examining controls with compliance requirements.

Deconstructing the top level strategy into key goals/objectives will help you to identify the enterprise-level risks that threaten the achievement of those goals; the process-level control objectives that mitigate those enterprise risks; the process-level risks; and the controls that mitigate the process level risks.  The compliance activities will likely be closely related to these process-level risks and controls and these are the risks and controls that should be assessed.

The risk tolerance around an activity subject to compliance requirements may be closer to zero than other activities of the organization.  However, transforming a compliance audit into a value added activity still starts with the determination of the audit objective.  This sets out in clear terms, what the audit seeks to accomplish and drives the scope, criteria, work plan and final results.  If the audit objective is simply to verify compliance with “A”, then you will fall into the trap of concluding “You are not doing A” and recommending “Do A”.  However if the audit considers the compliance–related business objective and the associated risk; and has as an objective such as: to verify the need for, existence and adequacy of compliance with “A”, it will be better positioned to address the governance, risk management and compliance issues as well as to add value.

Given this type of audit objective, one of the first steps would be to perform a risk assessment to determine if the original risks and compliance requirements still exist.  They may have been eliminated by a change in operations e.g. we are no longer making that product; or we are no longer using that manufacturing process.  It may have been transferred to someone else – we subcontracted out the operation – or business process re-engineering, changes in location, retooling, are just a few of many possible reasons why the original risk and associated need for compliance may have been eliminated, transferred or lessen.  In these cases, the value-add might be the elimination of the requirement to comply:  no risk – no compliance requirement.

****** more next week *******

Year 23 – 2010 – HR Data

Following on the success achieved by the development of the standard extract of financial data from SAP, I decided to design and develop a standard extract from PeopleSoft – our company used SAP for finances and PeopleSoft for HR and another package for Pay (don’t ask why).  For HR data, we really only needed three files to address most of the audit requirements:

  • Person – providing information on the employee such as gender, date of birth, start date, job classification, position number, etc.)
  • Position – providing information on the position such as position number, title, security clearance, level, etc.
  • Dept – providing information of the department such as title.

Since it was a snapshot of the data base and not transactions, I extract the information of a quarterly basis.  Also, if current data was required, I could take a snapshot on any day.

The data was meant to support various audits that required information on personnel as well as payroll audits (e.g. compare actual pay to the HR salary levels).  I also produced summaries to provide trend analysis and identify risks.  This include: employment equity percentages; percentage that could retire within two years; percentage of employees in acting positions; percentage of vacant position; average turnover, etc.

Once again I ran afoul with lawyers and my authority to access personal information.  This despite the fact our audit charter clearly stated that we had “unfettered access to any and all information required”.  This issue was exacerbated by the fact that my HR extracts from PeopleSoft were not linked to a specific audit.  The issue was compounded by the government requirement that personal information only be used for the purposes for which it was collected.  Apparently, the PeopleSoft data – when collected – did not indicate that it would be used for “audit purposes”.  When confronted with this fact, I realized that for pay, travel and entertainment, health claims – basically all personal data being collected by numerous systems – “audit” was never identified as a “purpose”.  It seemed ridiculous, but the law meant that audit couldn’t have personal information, such as the employee name and number, in order to perform a payroll audit because the pay system stated that personal information was collected “to pay employees and produce income tax reports”.  The system did not state that the personal information could be used for “audit purposes”.

I was confused as I had never heard of this “consistent use” requirement before – turns out it was a new law to protect personal information.  At first I thought that I would have to go through each system and request a change – a lengthy process involving the system owner, lawyers and who knows how many other people – to each and every system we used that contained personal information.  But, while my manager thought about the effort involved in even trying to do this, I had an epiphany – we could write an overarching statement that would say that all personal information collected by the company could be used for audit purposes.  (I was careful not to say “used for audits” since I wanted to identify and assess risks by looking at data on an ongoing basis (i.e. not tied to a specific audit).   The main system owners, such as PeopleSoft, had no problem – it was less work for them – and even the lawyers presented very little argument.  As a result, internal audit now have a clear statement in place that supported their authority to access and use personal information.

Audits: in addition to obtaining access to PeopleSoft and developing a mechanism to support audit’s access to personal information, I also supported several audits with analytics.

For an audit of fleet cars, we accessed the credit card data.  Each car had its own credit card to be used for gas and automotive repairs.  The transactional data included the number of gallons of gas, the fuel type and octane level, the price per gallon, date, etc.  It was easy to identify credit cards that had been used several times in a day (some were being used 3-4 times within 5 minutes).  This lead to a fraud investigation which included a stake-out and resulted in several employees being charged with fraud when they used the company credit card to fill not only the company car, but also their spouse’s and children’s cars.  We also identified cars with n abnormal number of repairs and replacement parts (e.g. three mufflers within 6 months; two sets of tires within 3 months).  A fraud investigation determined that the parts had been used to fix employee cars.

For an IT audit, we took a snapshot of the main tables of the current system and compared these to the new system’s tables.  Both were relational data bases.  In the new system, one of the table had one less record that the old system.   Turns out the first record was treated as the field names which meant that the index keys were off by 1 so every record in the parent was matched to the wrong record in the child table.

On a personal note, I won the IIA for “Contribution to the Profession” for my many years of encouraging auditors to embrace analytics.


Lesson-learned – you have to constantly be looking at things that can affect your ability to access data.  This could be a system conversion; a merger/acquisition; changes to federal laws; etc.   I have been impacted by each of these on more than one occasion.  The result can mean many months of limited or no access to data if you do not know far enough in advance of the change to plan for it.

Analytics is only limited by your imagination.  It can be used for more than financial data.  It can be used it to compare the source code in production with the ‘approved’ source code; to looking at staffing and succession planning for HR; to perform employee health and welfare audits; and even to determine if a military unit was ready to go to war.

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.

Continue reading Year 22 – 2009 – SAP Extract

Year 21 – 2008 – part 1 – Ensuring Integrity

 Accessing different systems, trying to address auditor requirements, and performing complex analysis – they all present risks.  And while I have had a great deal of success, there also have been many mistakes.  I once heard it said, “learn from others mistakes – you don’t have enough time to make them all yourself” or something similar.  This is why I always try to post a lesson-learned and this post is no different.

Here are three audits where my analysis was less than perfect – but where I learned valuable lessons.

  1. Expense advances – I was supporting an audit of advances and extracted all transactions related to travel expenses from the SAP system. The filter was a combination of a document type and a GL code.  When I presented the auditor with the extracted data, I told her “Here are all of the travel advance transactions; the total is $23M – be sure to verify this with the client.  Six months later after additional analysis and other audit procedures – the draft report was given to senior management.  They replied, “$23M, it should be much closer to $61M.”  Turns out there were two types of advances (excluding salary advances), travel advances and sensitive expenditures.  We had only extracted the travel advances.  Now I could (and probably did) argue that this was not my fault – I had told the auditor to verify the data.  But as the SAP and data expert – I should have done more to ensure that I was providing a complete set of data to support the audit.  Part of doing this would have been to ask the auditor to supply the “audit objective”.  In this case, the objective was not “to verify the controls over travel advances”; but “to verify controls over expense advances”.  After this mistake, I was also sure to get the audit objectives and to ensure that my understanding – and the data that I would be extracting – agreed with the auditor’s understanding.  From then on, I also checked to see if the auditor had verified the accuracy and completeness of the data.

Continue reading Year 21 – 2008 – part 1 – Ensuring Integrity

Year 20 – 2007 – Inventory

It was hard to believe, but I had now been at this (data analytics to support audit) for 20 years.  And I still found it interesting, challenging, frustrating, rewarding and aggravating – all at once.

I was constantly being asked to access new systems and perform analysis for different types of audits.  At the same time, I had my regular monthly routine tasks of extracting, downloading and cleansing data we used on a regular basis.  For example, the SAP extract – full year-to-date extracted and download every period – would take most of the day to perform by the time I got to period 8.  I could only download one period at a time because of CPU limitations – so I would start a background extract of period 1 and work on other things.  When it finished, I would extract period 2 and download period 1; and so on until I reached the current period (AX and DirectLink would have made things much simpler).  In addition, I had to extract and download the 12 master tables (vendor, customer, cost centre, GL, etc) that I needed every quarter.

Once all year-to-date extracts had been performed, I had a script that combined the periods and transformed the detailed transaction (BSEG table) and the header (BKPF Table) into a more useful data set where the customer and vendor information was on every line of a document.  The script also produced a snapshot of the controls and summary files (by GL; by Cost Centre; by Vendor; etc.).  Next I would combine data from the previous “X” years to produce multi-year summaries (by GL by year; by Cost Centre by year; etc.).

Continue reading Year 20 – 2007 – Inventory

Year 16 – 2003 – Recruitment Process

People, even those that perform analytics, often think that data analysis can only be applied to financial-type audits.  I have tried to highlight other types of audits where analytics played a significant role including transportation, inventory, and hazardous materials (environmental).   In that vein, I offer you analysis that was part of an HR recruitment audit.

he organization was an international/national police force.  Like many police forces, it needed a fairly continuous flow of recruits.  The problem with this agency was that the recruitment process – which leads to a six month training program – was overly long. In fact it was 18-22 months from the time a potential recruit entered the process until they were offered begin the training program.  During this time, they were not paid, and, as a result, many suitable recruits exited the recruitment process because they found other jobs.

Working with the HR section, the auditors determined that they were 36 separate steps in the recruitment process.  Some were fair minor – like completing an application form – while other were more time consuming – like the security clearance process.  I was able to obtain the recruitment data for the past 3 years.  The data contained the start and end date for each step for each recruit.  In reviewing the recruitment data I was able to determine that the steps were done in series – not in parallel.  This meant that before a recruit could enter step “n”, step “n-1” had to be completed.  Our first recommendation was to change the process to permit steps to be done in parallel.  For example, rather than waiting for the results of the written test (which could take up to two weeks), recruits could start on the physical test phase.

Continue reading Year 16 – 2003 – Recruitment Process

Year 11 – 1998 – ERP Systems Arrive

Disaster!  After 10 years of hard work to develop a decent understanding of the company’s finance, inventory, two HR and three pay systems, we switched to SAP (for finance and payroll) and PeopleSoft for HR) and another ERP system for inventory.  It was bad enough that we were changing systems, but to implement separate systems for the major functions seemed idiotic and created much more work for me and the rest of the CAATTs team.  In addition, we had roadblocks, particularly from IT who were busy trying to implement the new systems.  I was hard to argue with them.

However, it did reinforce the notion that auditors need to be flexible, nimble, and willing to accept change – all things we expect of our clients when we make recommendations.  It also reminded me of the importance of personal relationships and multiple methods of accessing data.

I spent a good portion of a year reviewing previous requests for data and analysis support (to determine what was required by auditors); working with auditors of different stripes (financial, operational, compliance, HR, etc.) to find out what data they required; and the technical folks (programmers, analysts, business owners, etc.) to re-negotiate access to the various systems and data.  I also mapped our current data to the new applications (e.g. in the previous system we had access to the responsibility centre which was the Cost centre in SAP; record number/document number, invoice date/document date, etc.).  For SAP this involved obtaining read access to the system and bring up an invoice – pressing F4 on each field and then selecting “technical data” to get the German field names and table names.

Continue reading Year 11 – 1998 – ERP Systems Arrive