In session two we discussed

1. In session two we discussed • Evidence Gathering techniques • Evidence Analysis Techniques

2. Session III Coverage-Performance Auditing in IT Environment • Computer Assisted Auditing Techniques • Specilised and support audit techniques/software

3. 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

4. Computer Assisted Auditing Techniques- Usage • Used to obtain sufficient, relevant and useful evidence to achieve audit objective • To support audit findings and conclusions • To overcome stiff challenge posed by information processing environment • To overcome difficulty in evidence gathering on account of different hardware and software environment, different data structure, record formats, processing functions etc. • To enable auditor in performing audits to gather information independently • To provide means to gain access and analyze data for a predetermined audit objective • To report audit findings with emphasis on the reliability of records produced and maintained in the system.

5. Types of CAAT Tools & Techniques • Generalised Audit software- IDEA, ACL • Utility Software- database management systems report generators that provides evidence on effectiveness of controls • Test Data- helps auditor to check for programme errors & whether programme meets its objective • Application software tracingand mapping-gives information about internal controls built in the system • Expert 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.

6. 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

7. Generalised Audit software • 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

8. CAATs - Benefits • 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

9. 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.

10. 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

11. Statistical Analysis Functions- Excel • Analysis ToolPak add-in   Microsoft Excel provides a set of data analysis tools — called the Analysis ToolPak — that you can use to save steps when you develop complex statistical or engineering analyses. You provide the data and parameters for each analysis; the tool uses the appropriate statistical or engineering macro functions and then displays the results in an output table. Some tools generate charts in addition to output tables. • Prerequisite knowledge   To use these tools, you need to be familiar with the specific area of statistics or engineering that you want to develop analyses for. • Related worksheet functions   Excel provides many other statistical, financial, and engineering worksheet functions. Some of the statistical functions (80) are built-in and others become available when you install the Analysis ToolPak.

12. Some of these statistical functions are as shown in next two screensThis screen also shows the general formula syntax and its interpretation

13. Some more Statistical Analysis functions

14. Excel • We have discussed about statistical functions now we will move to • Statistical (built in ) functions • What if Analysis • Filter Utility

15. What 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.

16. What 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

17. What if analysis- One Variable Table Select Table Range & relevant menu option as shown below Menu option Table Range

18. What if analysis-One variable Table After 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.

19. Resultant Table- What if analysis After clicking OK button as shown in previous dialogue you will get result as shown below

20. Excel • We have discussed about what if analysis functions now we will move to • Statistical (built in ) functions • What if Analysis • Filter Utility

21. Excel also can be used to extract the information like IDEA Open database and click Data Menu – Auto filter/ Advanced filter

22. Filtering 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.

23. Auto filter Example showing drop down arrows to select criteria

24. Filtering- on Designation field for Designation “Auditor” and Pay >2400

25. 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.

26. Access • Query • Filter • Join

27. Access Database table

28. MS Access Database Queries The diagram shows an Access Query Design wherein We have mentioned two criteria on a table “Customer” Country ARGENTINA And Status “A” Or Country NIGERIA

29. Query result

30. Access • Query • Filter • Join

31. Access- 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

32. Access • Query • Filter • Join

33. Relations • 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”

34. Relations- Several Tables are related on some common field to view large set of information together

35. Screen showing how one can view information relating to suppliers, product & orders together from three distinct tables because of such relationship- A single supplier having multiple orders for an unique product. If this information would have been stored in one table it would have not satisfied information criteria avoidance of data redundancy.

36. 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

37. SQL • SQL stands for structured query language • One can write complex SQL query to extract the records as well as for joining the tables.

38. Query statement SQL - 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.

39. First query statement Second 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

40. Single query statement Combining two tables 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

41. Session Coverage-Performance Auditing in IT Environment • Computer Assisted Auditing Techniques • Specilised and support audit techniques/software

42. Specilised & support audit techniques/ software • COBIT • TeamMate • Crystall Ball • Earned Value Analysis

43. 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

44. COBIT IT Governance Institutes Product

45. 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.

46. 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

47. The Framework’s Principles

48. 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

49. 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.

50. 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