in session two we discussed evidence gathering techniques evidence analysis techniques
TRANSCRIPT
Session III Coverage-Performance Auditing in IT Environment
Computer Assisted Auditing Techniques Specilised and support audit
techniques/software
In this session we are going to discuss Computer Assisted Auditing Techniques
• Types, Benefits, CAATs adopted by SAI Computer Assisted Auditing Techniques
– Excel :-• Statistical functions• What if analysis• Filter
– ACCESS • Query• Filter• Join
– SQL• Simple Query• Complex Query• Join
Specialised & support Audit techniques/ software– COBIT-
• Meaning• Need for Control • Framework Principle • Critical Success Factor S• Key Performance Indicator• Maturity Model
– CRYSTALLBALL– TEAMMATE– EARNED VALUE ANALYSIS
Computer Assisted Auditing Computer Assisted Auditing Techniques- UsageTechniques- Usage
Used to obtain sufficient, relevant and useful evidence to Used to obtain sufficient, relevant and useful evidence to achieve audit objectiveachieve audit objective
To support audit findings and conclusionsTo support audit findings and conclusions To overcome stiff challenge posed by information To overcome stiff challenge posed by information
processing environmentprocessing environment To overcome difficulty in evidence gathering on account To overcome difficulty in evidence gathering on account
of different hardware and software environment, different of different hardware and software environment, different data structure, record formats, processing functions etc.data structure, record formats, processing functions etc.
To enable auditor in performing audits to gather To enable auditor in performing audits to gather information independentlyinformation independently
To provide means to gain access and analyze data for a To provide means to gain access and analyze data for a predetermined audit objectivepredetermined audit objective
To report audit findings with emphasis on the reliability To report audit findings with emphasis on the reliability of records produced and maintained in the system.of records produced and maintained in the system.
Types of CAAT Tools & TechniquesTypes of CAAT Tools & Techniques
Generalised Audit softwareGeneralised Audit software- IDEA, ACL Utility SoftwareUtility Software- database management systems report
generators that provides evidence on effectiveness of controls
Test DataTest Data- helps auditor to check for programme errors & whether programme meets its objective
Application software tracingApplication software tracing and mappingand mapping-gives information about internal controls built in the system
Expert systemsExpert systems- gives direction & valuable information at all levels of auditors while carrying out audit because the query based system is built on the knowledge base of the senior auditors or managers.
CAATs- Generalised Audit software
Standard software with capability to directly read and access data from various database platforms, flat-file systems and ASCII formats
Enable auditor to have independent access to data for analysis and the ability to use high level problem solving software to invoke functions to be performed on data files
Features- Mathematical computations, stratification, statistical analysis, sequence checking, duplicate checking, recomputations
Functions :-• File Access :- reading of different record formats
& file structure
• File reorganization :- indexing, sorting, merging, linking
• Data Selection :- global filtration condition & selection criteria
• Arithmetical function :- arithmetic operators & functions
Generalised Audit software
Reduce level of Audit Risk Greater independence from the auditee Broader and more consistent audit coverage Faster availability of information Improved exception identification Greater flexibility of run times Greater opportunity to quantify internal control
weakness Enhanced sampling Cost saving over times
CAATs - Benefits
SAI & CATTs SAI-adopted MS Office (Excel,Access), IDEA,
SQL Excel- what if analysis & statistical analysis Access-handling large volumes of data and
running queries SQL- used mainly in RDBMS environment to
query data directly which can be stored on CD or other portable media without bothering too much of data conversion before interrogation of data
IDEA- used for data analysis, data conversion, sampling etc.
Excel In Excel we are going to have brief idea
about various powerful utilities offered in Excel which an Auditor can explore during performance auditing. They are
• Statistical (built in ) functions
• What if Analysis
• Filter Utility
Statistical Analysis Functions- Excel Statistical Analysis Functions- Excel
Analysis ToolPak add-inAnalysis ToolPak add-in Microsoft Excel provides a set of Microsoft Excel provides a set of data analysis tools — called the Analysis ToolPak — that you data analysis tools — called the Analysis ToolPak — that you can use to save steps when you develop complex statistical or can use to save steps when you develop complex statistical or engineering analyses. You provide the data and parameters for engineering analyses. You provide the data and parameters for each analysis; the tool uses the appropriate statistical or each analysis; the tool uses the appropriate statistical or engineering macro functions and then displays the results in engineering macro functions and then displays the results in an output table. Some tools generate charts in addition to an output table. Some tools generate charts in addition to output tables.output tables.
Prerequisite knowledgePrerequisite knowledge To use these tools, you need to be To use these tools, you need to be familiar with the specific area of statistics or engineering that familiar with the specific area of statistics or engineering that you want to develop analyses for.you want to develop analyses for.
Related worksheet functionsRelated worksheet functions Excel provides many other Excel provides many other statistical, financial, and engineering worksheet functions. statistical, financial, and engineering worksheet functions. Some of the statistical functions (80) are built-in and others Some of the statistical functions (80) are built-in and others become available when you install the Analysis ToolPak. become available when you install the Analysis ToolPak.
Some of these statistical functions are as shown in Some of these statistical functions are as shown in next two screensnext two screens
This screen also shows the general formula syntax This screen also shows the general formula syntax and its interpretationand its interpretation
Excel
We have discussed about statistical functions now we will move to
• Statistical (built in ) functions
• What if Analysis
• Filter Utility
What if analysis- ExcelWhat if analysis- Excel
What if analysis talks about what will be the outcome of a formula in different scenarios.
Ex. If I am a shopkeeper and normally I took holiday on Sunday to what extent there will be increase in profit if I keep my shop open on Sunday also.
Ex. – When DA is changed from 59% of pay to 63% what will be the burden on exchequer.
What if analysis-tablesWhat if analysis-tables One-variable data tables - Use a one-variable data table if you want to
see how Change in amount affects the interest earned. This example calculates the interest amount for one year on the
investment range of Rs.1000-Rs. 5500 in a single step
What if analysis- One Variable Table What if analysis- One Variable Table
Select Table Range & relevant menu option as shown below
Table Range
Menu option
What if analysis-One variable TableWhat if analysis-One variable TableAfter clicking menu you get a dialogue box prompting to
Enter column input cell. Click Column Input cell and click B1 cell. You will get absolute address as shown in
dialogue box-column input cell.
Resultant Table- What if analysisResultant Table- What if analysis
After clicking OK button as shown in previous dialogue you will get result as shown below
Excel
We have discussed about what if analysis functions now we will move to
• Statistical (built in ) functions
• What if Analysis
• Filter Utility
Excel also can be used to extract the Excel also can be used to extract the information like IDEAinformation like IDEA
Open database and click Data Menu – Auto filter/ Advanced filter
Filtering in ExcelFiltering in Excel
The Data filter Menu shows two options• AutoFilter
• Advanced Filter
Auto filter allows one to select data based on some criteria from one or more than one field.
Advanced filter is used to prescribe complex queries for filtering.
Auto filter Example showing drop Auto filter Example showing drop down arrows to select criteria down arrows to select criteria
Filtering- on Designation field for Filtering- on Designation field for Designation “Auditor” and Pay Designation “Auditor” and Pay
>2400>2400
SAI & CATTs After discussing about Excel as a CAAts let us
know some of the powerful features offered by MS Access
Excel- what if analysis & statistical analysis Access-handling large volumes of data and
running queries, Joining database, filtering information like excel or IDEA
SQL- used mainly in RDBMS environment to query data directly which can be stored on CD or other portable media without bothering too much of data conversion before interrogation of data
IDEA- used for data analysis, data conversion, sampling etc.
MS Access Database QueriesThe diagram shows an Access Query Design whereinWe have mentioned two criteria on a table “Customer”Country ARGENTINA And Status “A” Or Country NIGERIA
Access- FilterAccess- FilterTo select the suppliers from USA I just have to
click value USA from Country column and filter by selection button on standard toolbar for
removing filter just click remove filter button
RelationsRelations Often data given to an Auditor is spread across various tables. But
Auditor is required to combine these tables to see the entire set of data. Relations is the utility which can help Auditor to combine many tables and view the resultant database as a single database for auditing purpose. One can set such relationship among tables and view it by viewing a toolbar button “Relationship”
Relations- Several Tables are related on Relations- Several Tables are related on some common field to view large set of some common field to view large set of
information togetherinformation together
Screen showing how one can view information relating to Screen showing how one can view information relating to suppliers, product & orders together from three distinct suppliers, product & orders together from three distinct tables because of such relationship- A single supplier tables because of such relationship- A single supplier having multiple orders for an unique product. If this having multiple orders for an unique product. If this information would have been stored in one table it would information would have been stored in one table it would have not satisfied information criteria avoidance of data have not satisfied information criteria avoidance of data redundancy.redundancy.
SAI & CATTs SAI-adopted MS Office (Excel,Access),
IDEA, SQL Excel- what if analysis & statistical analysis Access-handling large volumes of data and
running queries SQL- used mainly in RDBMS environment
to query data directly which can be stored on CD or other portable media without bothering too much of data conversion before interrogation of data
SQL
SQL stands for structured query language One can write complex SQL query to
extract the records as well as for joining the tables.
SQL - QuerySQL - Query This query intends to fetch such records from emp table where
deptno(department no.) is the department number of an employee called ‘KING’ which is not known to user also. The query statement will be as shown below.
Query statement
SQL Query fetching information from two tables
Two sql statements were fired to fetch the records from two tables – 1)emp 2)dept
First query statement
Second query statement
Joining two tables with a SQL statement In this query two tables are joined by a where clause equating deptno
field from emp table with deptno of dept table. So there exist a common column to combine these tables
Single query statementCombining two tables
Session Coverage-Performance Auditing in IT Environment
Computer Assisted Auditing Techniques Specilised and support audit
techniques/software
Need for control In recent years, it has become increasingly evident that
there is a need for a reference framework for security and control in IT. Successful organisations require an appreciation for and a basic understanding of the risks and constraints of IT at all levels within the enterprise in order to achieve effective direction and adequate controls.
To achieve success in the information economy, enterprise governance and IT governance can no longer be considered separate and distinct disciplines. Effective enterprise governance focuses individual and group expertise and experience where it can be most productive, monitors and measures performance and provides assurance to critical issues. IT, long considered solely an enabler of an enterprise's strategy, must now be regarded as an integral part of that strategy.
COBIT framework serves this need
COBIT CCOBIT ( Control Objectives for Information and
Related Technology)- generic standard for good information technology security and control practices
IIt provides a reference framework for management, users, auditors, control & security practitioners So while conducting Performance Audit with reference to this framework one will come to know what is expectation from Management, Auditor, Users and Control & Security professionals by business/ Government.
COBIT IInternational acceptance of COBIT as good practices for control
over information, IT & related risks. It can be tailored to the individual need. Auditor can refer to it as a best practice while conducting performance Audit in IT Environment
CCOBIT enables an enterprise to implement effective governance over the IT that is pervasive and intrinsic throughout the enterprise
CCOBIT- management guideline – addresses the management need to control & measure IT by providing tools to assess and measure the enterprise IT capability for 34 COBIT IT process
COBIT Domains These domains are applicable to very wide area not
only IT they can be applied to manual processes also. COBIT Controls are planned in 4 Domains
– Planning and Organization- IT long range & short range plan, Roles & responsibilities, Segregation of duties, IT Budgets, Return on Investment, Information Criteria, Recruitment, Promotion, Risk assessment, Compliance with external requirement, Manage Project, Manage Quality
– Acquisition and implementation- acquisition of hardware & software- Identify automated solution, Acquire & Maintain application software, Acquire & maintain Technology Infrastructure, Develop & maintain procedure, Install & Accredit system
COBIT Domains
– Delivery & Support- Define service levels- Service Level Agreements, Manage third party products (Escrow Agreements), Manage continuous service, Manage Data (preparation, authentication, output distribution etc.), System security, Identify & allocate cost, Training to users.
– Monitoring- Monitor the Process, Collecting & monitoring Data, Independent assurance before implementing new services, Effective evaluation of third party service providers.
COBIT Tools
Performance measurement elements (outcome measures & performance drivers for all IT process). These tools will tell whether IT is doing the job that business expect from it.
A list of critical success factors that provides succinct, non-technical best practices for each IT process.
Maturity models to assist in benchmarking and decision-making for capability improvement
• KPI for performance measurable indicators of performance of the enabling factors
• f(Control Statement and Considerations in ‘Waterfall’)
• How well they leverage/manage the resources needed
Control Statements
Control Practices
is enabled by
and considers
IT Processes
The control of
Business Requirements
which satisfy
Key Performance Indicators
• Management oriented IT control implementation guidance
• Most important things that contribute to the IT process achieving its goal
• Strategically
• Technically
• Organisationally
• Process or Procedure
• Control Statement and Considerations of the ‘Waterfall’
• Visible and measurable signs of success
• Short, focussed and action oriented
• Leveraging the resources of primary importance in this process
Critical Success Factors
Control Statements
Control Practices
is enabled by
and considers
IT Processes
The control of
Business Requirements
which satisfy
Specilised & support audit techniques/ software
COBIT Following software's assist performance
Audits– TeamMate– Crystall Ball– Earned Value Analysis– IDEA
TeamMate
Audit Management software developed by Pricewaterhouse Cooper
It is an electronic work paper package that simplifies and enhance effectiveness of audit documentation process
TeamMate Components
Teamrisk :-risk assessment tool for preparing risk based audit plan
Teamschedule :- a tool for scheduling staff resources to audit
Teammate (EWP) :- a electronic work paper system; and
Teamcentral :- a web-based issue tracking system
A Database Driven System
All important information such as audit steps, audit findings, review notes, sign-offs and edit histories are contained in database tables.
This type of system allows real-time team based use as well as filtering and sorting for key information.
Real-time team based use:
In a document based system (vs. TeamMate) there is one Word document that contains the audit exceptions and if this were in use by one auditor, other auditors would have to "wait their turn" until the document freed up.
This example can be extended to included documenting review notes and working on audit programs.
Continued…
In TeamMate auditors can document new exceptions while a review of previous exceptions is underway -- multiple users can access the TeamMate database information on a concurrent basis.
For example in TeamMate, four audit team members could each access the same audit concurrently and simultaneously document exceptions or work on the same audit section.
Filtering and Sorting tools:
As the key information is contained in database tables (displayed in a user-friendly graphical interface) you can filter and sort for key information instantly.
For example, an audit might contain 8 audit programs - each with 10 steps.
Continued…
In a Word based program system (unlike TeamMate) you would have 8 separate Microsoft Word programs.
A user could not readily determine key information such as outstanding steps, individual steps ready for review, all high risk steps, etc. The contents of the 8 programs are not viewable without launching all eight documents and manually scrolling through each document.
The Auditor Tool-set
TeamMate allows auditors to perform a wide range of audit "annotations in other computer applications such as Word or Excel.
With TeamMate, auditors can cross reference KDs, document objections, create notes reviewing earlier work, etc. while still working in their familiar applications such as Word and Excel.
The Art of Imaging KDs!
TeamMate allows easy incorporation of critical scanned images & photographs into the workpapers.
Once scanned, an image cannot be altered by anyone – the reader or the auditor - preserving the integrity of the document (i.e. no changes made to the Purchase Order that was scanned.)
Continued…
This helps to preserve key audit evidence in its exact form.
It also helps because we can link our audit observations digitally with the key evidence.
With TeamMate, scanned images are added at the click of a button -- directly into the workpapers!
File Compression / Encryption / Back-up
All workpapers are automatically compressed (to 1/7th -- 1/3rd of their native size) when closed within TeamMate. -
In addition, this document will be encrypted. The result is smaller audit file sizes and files
that cannot be accessed through the back door "File Manager or Windows Explorer.“
All audit information is encrypted: during e-mail transport, archival and current use.
Advanced Replication
TeamMate allows team-members located in other places the ability to access the entire audit without disrupting current use.
In TeamMate, a replica (exact copy) of the section is taken for remote review and work can continue on the master copy. Changes are merged after review.
Change Tracking
TeamMate will identify and highlight an individual audit step within an audit program that has been edited after review.
TeamMate allows senior level officials to "freeze" workpapers and programs -- thereby preventing unauthorized changes.
Other features…
TeamMate can support the audit workflow process at each :
Project identification can be documented through TeamRisk.
Scheduling, documentation of audit implementation can be done through TeamMate and
Follow-up can be performed through TeamCentral.
Key features
For using TeamMate to document our audit, we divide the entire audit project/topic into “programmes”. These “programmes” are nothing but areas to be audited.
The “programmes” are then divided into “audit steps”. These “steps” are the test of evidence based on audit criteria developed and in line with our audit objectives.
Key features continued…
TeamMate also helps by storing the steps & decisions, so that we need not prepare the same when we go for audit again.
It integrates with the structure of audit implementation guidelines envisaged in the Performance Auditing Guidelines.
Conclusion
TeamMate will not audit papers/data on your behalf. What it does is to make you document each step of your audit.
It also helps to record every decision taken while planning and reviewing audit.
It is useful for storing for future use.
Specilised & support audit techniques/ software
COBIT TeamMate Crystall Ball Earned Value Analysis IDEA
CrystallBall
Software developed by the General Accounting Office (US)
It analyses the effect of varying inputs to a modeled system on the outputs
Useful for performing cost & uncertainty analysis
Earned Value Analysis (EVA)
A quantitative tool of measuring the value of work actually done on a particular date during the lifecycle of the project and based on that, to forecast the project cost & scheduled performance
It measure progress of the project,provide schedule & budget variances on a particular date and to forecast the completion date and the cost of completion of the project
In this session we discussedIn this session we discussed
Computer Assisted Auditing Techniques– Excel
• Statistical sampling, What if analysis, Filtering– ACCESS
• Query
• Filter
• Join
– SQL• Simple Query
• Complex Query
• Join