Letting the
Data tell the
Story
IIA Puget Sound Chapter
September 2015
David Coderre
[email protected]
1
Morning Topics
Analytics imperative
Obtaining data
Determining data requirements
Quantitative risk assessment for RBAP
update
Analytics in the Planning phase
2
Why Data Analytics?
The IIAs Global Technology Audit Guide (GTAG 16, Data Analysis
Technologies) states, data analytics can give auditors:
Greater productivity and cost savings
To increase quality, efficiency and value
More efficient data access
Reduced audit risk
ISACAs white paper on data analytics, points out that a data
analytics plan should focus on the end result by:
Performing ad-hoc analysis in support of targeted risk areas
Leveraging data analytics within many projects for greater
insight
Moving to repeatable analyses periodically on high-risk activities
Defining success measures along the way.
3
Why Use Electronic Data Analysis?
Electronic data is available
Powerful and Innovative
Accurate and efficient
Interactive and easy to use
Supports financial monitoring
The Department of Redundancy Department has 35,000
employees.
How long would it take you to manually search payroll
records to find duplicate payments and to identify the
control weaknesses?
4
Why Use Electronic Data Analysis?
Understand the business process
Assess integrity of info for decision making
Identify and assess risk
Improve consistency of review processes
Improve timeliness
Perform proactive tests
Look for indicators of problems
Review 100% of transactions
Do continuous auditing / monitoring
5
Focus of Data Analytics in Internal Auditing
Support for traditional audits, such as testing entire data populations as
opposed to samples alone
Routines designed to identify specific items, e.g. fraud, duplicate payments,
duplicate purchases, split transactions, credit cards, suspicious journal entries,
employee/vendor match, exceeding purchase limits
CAE thoughts on use of analytics by internal audit
(from 2013 State of the Internal Audit Profession Study)
81% 85%
71% 74%
31%
Data Analytics Plan to expand Data analytics are Data analytics are Data analytics are
are used use of data important to important to
analytics but do important to
regularly improving the strengthening audit gaining a better
not have a well- quantification of coverage
developed plan understand of risks
issues
6
Analytics Maturity Model
* PWC Internal audit analytics conundrum (Dec 2013) 7
Understanding and Assessing the
Business Process
Policies define Processes are Reports inform Controls are Automation
processes assigned to Decision-making defined by implements
people decisions controls
Business Goals Business People and Management Procedures Systems and
and Objectives Processes Organization Reports and Controls Data
Risks
Processes are not People lack Reports do not Controls do not Controls dont
aligned to goals knowledge and provide useful utilize support business
and objectives experience information information process
Data Analysis
Fraud and abuse; Develop under- Asses skills and Info sources; Assess Asses efficiency/
Assess fraud risk standing of competencies integrity and controls effectiveness of
non-compliance process inputs completeness info systems
outputs of the data support to
business process
8
Different Roles / Support by Data
Internal Audit Evaluation
Identify risk areas, risks not Assess efficiency and effectiveness
managed of program areas
Identify control weaknesses Identify areas for improvement
Improve efficiency and
effectiveness of business
operations
Monitor compliance Avoid & reduce errors
Assess adequacy of controls Detect & prevent fraud
Drive accountability and improved Improve operational efficiency
controls Responsibility for controls
Assess and mitigate risks
Internal Control Business Process Management
9
Obtaining the Data
Access methods:
Online access to application
Run standard reports
SQL or Ad hoc reports
Access to application data or extract
10
Data Analysis: a Generic Approach
1. Goals and Objectives of the Review
2. Identify all available data sources
Internal to the organization
External to the organization
Data dictionary list and description of fields
3. Obtain Access to the data and assess integrity
4. Formulate hypotheses about record / field relationships
5. Perform analytical tests for each hypothesis
Generic Approach contd
6. Evaluate results and refine the tests
7. Re-run refined test to produce shorter, more meaningful
results (repeat steps 4-6)
8. Evaluate (via analysis, interview, or other techniques)
every item on the refined list
9. Address every item on the list - either:
Valid explanation is found; or
Probable improper - full investigation is needed
Approaches to Defining your Data
Requirements
Control Person / Data
Weakness Fraudster Fields
Data
Analysis
Tests
Start by defining control weaknesses
Start by defining key data fields
13
Data Requirements Approach #1
Examine control from the persons perspective:
Who could benefit or take advantage of a control
weakness?
How could they be involved?
What can they effect, manipulate or control to
allow the fraud to happen?
Can they act alone or is collusion required?
If collusion is required, who else is involved?
What data analysis would identify control
exposures?
14
Control Weaknesses / Risk
Discussion:
Risk/Control weakness: there is no control over the
quantity received versus the quantity ordered.
1. Who might exploit the control weakness and
why?
2. What analysis should be performed to
determine if risk is being exploited?
3. What data would be required to determine if
the risk is being exploited?
15
Data Requirements Approach #2
Identify key data elements
Info?
Key data
fields Who?
Enter
Modify
Delete Why?
Motivation/
Objective
What?
Key
Controls Tests?
Source /
Integrity
16
Accounts Receivable - Example
Info/Data Who Why Control Test
Customer Salesman Bonus Customer creation / Duplicates
Fictitious modification
Customer Discount Customer creation / Duplicates
Credit rating modification
Credit Limit Salesman Kickbacks Customer creation / Change to credit
or Clerk modification limit info
Payment Clerk Kickbacks Customer creation / Change to
terms modification payment terms
17
Accounts Receivable - Example
Info/Data Who Why Control Test
Date Clerk Kickbacks Shipping Date Due Date > 30 days
after shipping date
Amount Clerk Kickbacks Contract info Amount due < sales
price
Fraud risk Test
Incompatible duties SOD analysis
Wrong customer Variance analysis
Wrong bank account Bank account info
18
Exercise complete table for A/P
Identify the data fields required by the A/P process and
complete table.
Info/Data Who Why Control Test
19
Accounts Payable - Example
Info/Data Who Why Control Test
Vendor Clerk Duplicates Vendor creation / Duplicates
Fictitious modification Blanks in key fields
Classify on Created by
Vendor Usage by clerk
Change address Log Modifications by clerk
or bank account
duplicates
Vendor Duplicates
Invoice Clerk Duplicates Duplicates Duplicates
number
Vendor Duplicates Duplicates Duplicates
Amount Clerk Over payment IR=GR=Contract IR<> GR or Contract
Payment terms Vendor table Xtab Vendor by
payment terms
Vendor Over charge IR = GR = Contract IR<> GR or Contract
20
Accounts Payable - Example
Info/Data Who Why Control Test
Date Clerk Early Payment Good Receipt Compare to GR/IR
(GR) and Invoice dates
Receipt (IR)
dates
GR and IR dates Compare to GR/IR
Vendor Early Payment
dates
Payment Clerk Early Payment Vendor table Check payment
Terms terms
Fraud risk Test
Incompatible duties SOD analysis
User making payments to self Compare user and vendor info
Wrong vendor Ratio analysis or invoicing sequence
21
Risk-based
Audit
Planning
IIA Puget Sound Chapter
September 2015
David Coderre
[email protected]
22
Agenda
Development of data-driven key risk indicators
Risk re-defined
Risk Assessment - Strategic Objectives:
1. Corporate Risk
Drilldown to determine which areas of the organization are
contributing to the corporate risk
2. Project or Activity Risk
Relative risk rating of organizational activities - on different
risk categories
23
Risk Management: dont worry I know
what I am doing
* No beavers were harmed in the making of this slide
Overview
Problem with traditional risk assessments:
Risk assessment relies primarily on qualitative and subjective
measures
Corporate risk profile is time consuming to prepare and is
typically only updated once a year
Corporate risk profile is an aggregate (top-down) view of risk
Corporate risk profile does not allow you to examine
organizational entity / activity impact on risk
Corporate risk profile does not allow you to examine the
different risk categories (e.g. financial, HR, operational,
strategic, legal, technological, etc.)
25
Emerging View of Analytics
Quantifying risks Monitor key
Using data by measuring Profiling where
control
analysis as a consequence and functions/risks
monitoring effectiveness/
strategic reside in your
likelihood improvements
planning tool organization
to business
Determining
response
Stakeholders
Audit
Ongoing assessment of risks and controls
Identification of specific audits; drilldown into risks; refine
audit objectives
Annual risk-based audit planning
Finance
Financial monitoring and control testing
Assessment of new opportunities
Statement of assurance
Corporate Risk Officer
Support for corporate risk profile
Assessment of mitigation efforts
Ongoing assessment of current and emerging risks
27
Benefits of Ongoing Risk Assessment
Auditors can be more proactive in assessing corporate risks and
emerging areas of risk
Predictive business performance measures will help drive
productivity by 20 percent by 2017
Managers that persist in using historical measures miss the
opportunity to capitalize on opportunities that would increase
profit or fail to intervene to prevent an unforeseen event, resulting
in a decrease in profit
ERM is more reliable and effective when ERM frameworks are
shown to produce credible and useful risk-adjusted performance
measures on an ongoing basis
28
Quantitative Risk Indicators (QRIs)
Pre-requisites for Effective QRIs
By-product of production systems
Easily updated and reduce reporting burden
Free from bias
React to changes in risk levels
Support the assessment risk at any organizational
level - drilldown to transactional level data
Support annual and ongoing risk assessment
process
29
Quantitative Risk Indicators
Subjective/qualitative assessment
Risk > Probability and Impact
Quantitative/data-driven
assessment
Risk -> Variability, Complexity and Volume
30
Ongoing Assessment of Risks
Objective:
The development and assessment of data-driven key risk
indicators for ongoing assessments:
For each corporate risk
Assessments of each organizational entity to
determine impact on corporate risk and to develop an
overall risk ranking (Low, Medium, High).
For each risk category
Assessments of each organizational entity to
determine impact on each risk category and to develop
an of overall risk ranking (Low, Medium, High).
31
Development of KRIs
Steps:
1. Ensure that your Audit Universe is aligned to Strategic
Initiatives that are tied to Corporate Objectives
2. Develop KRIs for each corporate risk and for all
corporate risk categories
3. Perform ongoing assessment of corporate risks and
risk categories by audit entity or any slice of the
organization
4. Select activities/entities to audit which have highest
corporate or risk category ratings.
32
Quantitative Key Risk Indicators
Corporate Risks - example:
1. Risk the management or loss of Intellectual
Property will damage ability to drive future
revenue
2. Risk .
X . Risk ..
33
Developing Data-Driven Risk Indicators
for Corporate Risk Intellectual Property
Risk Sub-category Risk Result / Impact Risk Indicator
R&D failure to manage research Project failure Success rate
and development projects. Escalation in costs Expenditures / budget
Project delays Project status / Plan
Safeguarding of IP - failure to Loss of IP Percentage turnover
implement safeguards to prevent ? Number of grievances
theft of IP by employees. Percentage use of outside /
non fulltime employee
Geographic location of
facilities
IT Controls failure to implement Loss of data Email attachments
IT controls to protect IP. System unavailable # of unauthorized access
or unreliable attempts
Control System availability / downtime
weaknesses
34
Overall Corporate Risk Rating
Combined assessment of risk across all corporate risks
for each organizational entity.
Org Entity Corp Risk 1 Corp Risk 2 Corp Risk n Rank
Entity 1 3.5 3.7 2.3 Low
Entity 2 3.5 4.5 4.6 High
Entity X 4.8 2.8 4.4 Medium
35
Risk Categories - example
External environment Information
Legal and regulatory Human resources
Strategy Technology
Governance Financial and
Operational administrative
36
Developing Data-Driven Risk Indicators
for HR Risk Category
Risk Sub-category Risk Result / Impact Risk Indicator
Recruiting failure to attract Lack of resources Vacancies
people with the right competencies. Lack of skills Acting appointments
? ?
Resource Allocation failure to Inappropriate Type of employee
allocate resources in an effective resources for tasks Employee classifications
manner to support the achievement ? Status of employee
of goals and objectives.
Unions
?
Retention failure to retain people Demographics Years of pensionable service
with the right competencies and Low experience Average age
match them to the right jobs. levels Average years in position
Turnover ?
Work environment failure to Unhappy workforce Average sick leave/vacations
treat people with value and respect. High sick leave Percentage departures
?
37
HR Risk Category Data-Driven Indicators
Volume / Size
# of employees
Payroll dollars
Variability/Change
Avg age; avg age of senior managers
Avg years of pensionable service; % who can retire < 2 years
Experience years in dept / position / classification
% Fulltime employees; % affected by org change
% acting ; % new hires
Leave: total leave taken; avg sick; avg vacation; avg unpaid
Complexity
# types of employee; # classifications; # locations; # unions
% employee with non-std hours
Other
% Sex (M/F); % FOL (Eng/Fr)
38
HR Risk Category Assessment
Objective: to support the assessment of HR
risk category for each audit entity.
Audit Entity Volume Variability/Change Complexity Rank
Entity 1 304 5 6 12% 1 12 4 28% Medium
Entity 2 281 13 2 13% 2 16 6 32% High
Entity X 463 28 6 21% 4 9 8 14% Low
39
Data-Driven Risk Indicators for Finance Risk
Exercise: for these and additional risk categories - develop risk indicators
Risk Sub-category Risk Results / Impact Risk Indicator
Failure to establish a Errors and corrections
proper financial Losses
framework Suspense transactions
Failure to establish Over expenditures
budgeting and Late expenditure decisions
forecasting processes Poor commitment
accounting
?
Failure to manage Complex financial
financial structure structure
Inadequate financial
structure
Discretionary expenses
40
Developing Data-Driven Risk Indicators
for Finance Risk Category
Risk Sub-category Risk Results / Impact Risk Indicator
Failure to establish a Errors and corrections % JV and reversals
proper financial Losses % Losses
framework Suspense transactions % Suspense account
Failure to establish Over expenditures Actual > Planned
budgeting and Late expenditure decisions % expenditures period 12+
forecasting processes Poor commitment % not referencing a
accounting commitment or PO
? ?
Failure to manage Complex financial # of funds / fund centres
financial structure structure Use of Internal orders / WBS
Inadequate financial # of currencies
structure
% discretionary expenses
Discretionary expenses
41
Finance Risk - Data-Driven Indicators
Volume
Total Expenses, Revenue, and Assets
Variability/Change
Percentage of discretionary spending
Percentage of expenditures in Period 12, 13+
Total and number of JVs / Suspense account transactions
Total and number of Reversal documents / Loss transactions
Complexity
Number of Funds / Fund centres / Cost centres
Number of Economic object categories / GLs
Number of Currencies / Document types
Use of Internal Orders / Purchase orders / Fund reservations
Use of Materiel and Asset numbers / Real estate blocks / WBS
Number of employees
Number of P-Cards
42
Risk Factor Weighting
43
Financial Risk Category Rating
Financial risk rating:
Overall rating;
By volume;
By variability; and
By complexity
Not only can you
assess which entity
has the highest
overall risk, but you
can also determine
whether it is because
of volume, variability
or complexity.
44
Quantitative Risk Indicators
ACL Demo
45
Other Risks - examples
Legal and Regulatory
Number of new regulations
Number of modified regulations
Regulatory fines within the industry
Frequency and extent of onsite visits by regulators
Frequency of media coverage of issues affecting
regulations
Cyber Security
Number of firewall attacks and breaches
Number of applications operating in the cloud
Amount of IT traffic
Assessment of the organizations cyber security maturity
Frequency of alerts from cyber security vendors
Source: 2014 Emerging Risks Teleconference and Panelist
Discussion, Corporate Executive Board
Other Risks - examples
Retail
Number of products
Number of regions
Sales / sq ft
???
Manufacturing
Number of production lines
Number of special runs
Percentage defects
Percentage downtime
???
Overall Risk Category Rating
Combined assessment of risk across all risk categories
for each organizational entity.
Org Entity Financial HR Operational Rank
Entity 1 3.2 3.2 4.3 Low
Entity 2 6.5 5.5 3.6 High
Entity X 4.3 1.8 4.4 Medium
48
Overall Risk Rating
A
1 Corp Risk 1
K 0.9 B
0.8 Corp Risk 2
0.7
0.6 Corp Risk 3
0.5
J C Corp Risk 4
0.4
0.3
0.2
0.1
0
I D
H E
G F
49
Corporate Risk rating by indicator
A
3 Gov
K B
2.5 Proj Mgt
IT Control
2
Safeguard
1.5
J C
1
0.5
I D
H E
G F
50
Overall Risk Rating by Audit Entity
A
3
Projects A - K K
2.5
B
1.5
J C
1
0.5
I D
H E
G F
51
Audit Entity - rating by risk category
A
1 HR
K 0.9 B Fin
0.8
Legal
0.7
Strat
0.6
0.5
J C
0.4
0.3
0.2
0.1
0
I D
H E
G F
52
HR Risk Rating by Region by Indicator
Volume
A
1 Variability
0.9
J B
0.8 Complexity
0.7
0.6
0.5
0.4
I 0.3 C
0.2
0.1
0
H D
G E
53
HR Risk Rating - Complexity by Entity
A Num_EmpTypeR
1
Num_ClassR
K 0.9 B
Num_RegionR
0.8
0.7 Num_UnionR
0.6 Pct_NoStdWWR
0.5
J C
0.4
0.3
0.2
0.1
0
I D
H E High-level of HR Risk
G F
54
Conclusions
Data-driven risk indicators:
Use operational system information
Quantitative (data-driven) and can easily be updated (e.g.
monthly/quarterly)
Provide relative risk ranking for each org entity
Support the risk identification and assessment process
Support ongoing assessment of risk management and mitigation
activities
Support the ongoing update of the risk profile
Support the ongoing assessment of risks for new initiatives
55
Risk
Assessment in
the Planning
Phase
IIA Puget Sound Chapter
September 2015
David Coderre
[email protected]
56
Support to Individual Audits or Reviews
Planning phase of an audit or review
Review operations / improve our understanding of business processes
Identify and assess risks
Assessment of control framework and testing of critical controls
Identify areas for improvement in operations and controls
Establish review objectives and scope
Conduct
Assess compliance
Test controls
Follow-up
Status and impact of recommendation
Fraud Risk
Identify and assess fraud risk
57
Support to Financial Officers
Financial monitoring
Trial balance
Multi-year trend analysis
Cost / Benefit analysis
Life cycle costing
Program and sub-program expenditures
Post payment verification
Internal control testing
ICFR analysis of risks and controls
Assess fraud risk
???
58
There are better ways to check for
weaknesses in the controls
59
Hazardous Materials Example
Objective:
Ensure that purchasing and disposal activities
respected environmental considerations
Ensure that hazardous materials were being
properly stored, used and disposed of
Background:
Numerous warehouses/depots across the country
Various types of hazardous materials with different
environmental impacts, storage and health and
safety requirements
60
Inventory
From To
Economic order quantities
Verifying inventory
(counts) Just-in-time Inventory
Three-way matching Obsolete inventory
(contract receipt Provisioning rates
invoice) Fraud risks
Management override
Items sent to scrap
Turnover rates
Unusual pricing
Attractive items
61
Financial Trends and Analysis
62
Financial Trends and Analysis
63
Multi-Year Summaries
64
Multi-Year Trend Analysis
Trends by Cost Centre by Fiscal Year
2007 2008 2009 2010 2011 2012
65
Analysis Examples
Inventory obsolete, sale of scrap, Risk identification and assessment
warehouse space, delivery time, EOQ Acquisition card monitoring
Succession planning Payroll rates and allowances
Hazardous Materials User access rights
Staffing Travel agency costs
Operational efficiency Overtime
Duplicate invoices Separation of duties
Reorganization Charter air routes
Control testing Telecom charges
Health claims System conversion
Inventory Management Management bonuses
Stale/backdated commitments Recruitment process
Trial balance and financial trends Program costing/tracking
66
Challenges and Opportunities
From To
Controls Efficiency and Effectiveness
Compliance Governance, Risk Mgt and
Control processes
Information Gatherer Knowledge Provider
Hindsight Foresight
Shop Floor C-suite
Tick and Bop Strategic Change Agent
Detection Provision of Assurance
67
Operational Readiness
Is Unit ready to be deployed?
Factors:
Equipment
Personnel
Support
?
68
Reference Materials
70
