Training Information Analysis

Training Session Information Analysis for Auditors Audit Information and its analysis for forming opinions through spreadsheets

Session 1

Presenter: Altaf Noor Ali Chartered Accountant

1

1 Session 9.30 -11.30 = 120 min Information Analysis: Basic st

1. Session Objective 2. Computer based accounting systems 3. Excel Application in audit performance 4. Excel menu: A run down 5. Must-know Excel features: Range Format Charts Functions 6. Preparing spreadsheets: Common errors 7. Documentation of Software Files prepared during audit: Uses and challenges

2

Session Objective 1. Describe the importance of data in electronic form from accounting and audit: writing without pen 2. How electronic capture of data has influenced the accounting and audit practices? 3. Make participants familiar with Excel, and its practical application in audits 3

Computer-based Accounting Systems 1. How is it different from manual accounting? 2. What are the threats and opportunities? 3. Are computer-based accounting systems being utilised optimally by users? How it affects the work of auditors? 4. What is the relationship between the quality of accounting and the quality of audits? 4

Limitations of Computer-based Accounting Systems 1. Client-specific, not standard 2. Learning requires time and effort 3. Flexibility like charting is not available 4. Client reluctant to provide access 5. Heavy files in terms of storage

5

Audit evidence 500 1. The auditor should obtain sufficient appropriate audit evidence to be able to draw reasonable conclusions on which to base the audit opinion. 2. When information produced by the entity is used by the auditor to perform audit procedures, the auditor should obtain audit evidence about the accuracy and completeness of the information.

6

Audit Evidence: Principles p.9 Following generalizations about the reliability of 1. 2. 3.

4.

5.

audit evidence may be useful: Audit evidence is more reliable when it is obtained from independent sources outside the entity. Audit evidence that is generated internally is more reliable when the related controls imposed by the entity are effective. Audit evidence obtained directly by the auditor (for example, observation of the application of a control) is more reliable than audit evidence obtained indirectly or by inference (for example, inquiry about the application of a control). Audit evidence is more reliable when it exists in documentary form, whether paper, electronic, or other medium (for example, a contemporaneously written record of a meeting is more reliable than a subsequent oral representation of the matters discussed). Audit evidence provided by original documents is more reliable than audit evidence provided by photocopies or facsimiles. 7

Core Audit Techniques for Obtaining Audit Evidence 1. 2. 3. 4. 5. 6. 7. 8.

Inspection Observation Inquiry and Confirmation Recalculation Reperformance Analytical Procedures Reconciliation Intuition

8

Core Audit Technique: Inspection [examination, checking] 1. Inspection consists of examining records or documents, whether internal or external, in paper form, electronic form or other media. 2. Inspection provides audit evidence of varying degrees of reliability, depending on their nature and source. 3. Inspection of internal records and documents is critically dependant on the effectiveness of the controls over their production.

9

Core Audit Technique: Observation [witnessing] 1. Looking at a process or procedure being performed by others. 2. Example: observation of the counting of inventories by the entity’s personnel, disbursement of payroll, preparing financial statements from internal reports. 3. Provides real time insights into issues. 10

Core Audit Technique: Inquiry [seeking information] 1. Inquiries may range from formal written inquiries to informal oral inquiries. Evaluating responses in inquiries is an integral part of the inquiry process. 2. Responses to inquiries may provide a basis for the auditor to modify or perform additional audit procedures. 3. Inquiry alone would not provide sufficient audit evidence to detect material misstatement at the assertion level.

11

Core Audit Technique: Confirmation [seeking info directly] 1. Confirmation is the process of obtaining a representation directly from a third party. 2. For example, the auditor may seek confirmation of receivables by communication with debtors. 3. Confirmations can also be about terms of contract or any modifications have been made to the agreements or transactions and entity has with third parties or absence of side agreement.

12

Core Audit Technique: Recalculation [accuracy] and Recompute 1. This consists of checking the mathematical accuracy of documents or records. 2. Actually, it should also be about its completeness.

13

Core Audit Technique: Reperformance [re-do] 1. Independent execution of procedures or controls that were originally performed as part of the entity’s internal control. 2. Preparing bank reconciliation, not simply checking the one provided to you. 3. Constructing a bill based on the contract and then checking it with the actual one. 14

Core Audit Technique: Analytical Procedures [understand the big picture] 1. Study of data and inter-relating its components in an effort to understand the whole picture. 2. Evaluating financial information made by a study of plausible relationships among both financial and non-financial data. 3. Investigation of identified fluctuations and relationships that are inconsistent with other relevant information or deviate significantly from predicted amounts. 15

What is a ‘Reconciliation’?  Bank reconciliation  Account Reconciliation with debtors and creditors on receiving confirmation  Payroll reconciliation Its main strength is that it highlights the changes and links it mathematically. Its disadvantage is the tremendous effort. 16

Intuition 1. Not listed as an audit procedure, however, a trained judgement results in intuition 2. Intuition is not a systematic process 3. It draws inspiration from varied sources

17

Excel Application in Audits: Which one? Core Audit Procedures for Obtaining Audit Evidence

1. 2. 3. 4. 5. 6. 7. 8.

Inspection Observation Inquiry and Confirmation Recalculation Reperformance Analytical Procedures Reconciliation Intuition

18

Excel Application in Audits: Which one? ALL. Key = Evidence = 8 Core Audit Procedures for Obtaining Audit Evidence

1. 2. 3. 4. 5. 6. 7. 8.

Inspection Observation Inquiry and Confirmation Recalculation Reperformance Analytical Procedures Reconciliation Intuition

19

Excel Application in Audits 1. Checking calculations [any stage of audit, most common] 2. Preparing schedules 3. Recording Conduct of Test 4. Generating random numbers for audit sampling 5. Analysing information 6. Importing/exporting data to other applications [final audit]

20

Excel Application in Audits Conclusion 1. What are the limitations in using excel? 1. Excel is application-specific rather than needsspecific. Example: a user would pick up a feature of excel and apply it to get a specific task done say a chart 2. The users’ limitation of knowledge about its application becomes its limitation

2. A lot of accounting related work happens outside the computer based accounting system for which excel is used extensively 3. Using excel is easier than manual writing as you use a keyboard and much legible.

21

Excel menu: A run down Key: Spreadsheet = 9 1. 2. 3. 4. 5. 6. 7. 8. 9.

File Edit View Insert Format Tools Data Window Help

22

Must-know Excel features: Range Format Charts Functions TTS 1. Range: Name 2. Format: Numbers after decimal, percentage, date, customise 3. Find and Replace: Using alphabets and replacing them 4. Chart: Displaying graphs visually 5. Functions: Sumif, lower, 6. Linking: For easy navigation 7. Text to Speech: The computer speaks 8. Changing global settings of worksheets

23

Preparing spreadsheets: Common errors 1. Directory: Storing in the wrong place 2. Filename: Using different conventions, incorrect names 3. Leaving lines between data: As on paper 4. Not defining ranges and working with them in formulas: making formulas easy 5. Not defining data and formulas columnsl 6. Not using tts 7. Not reviewing work In short, not optimising the use of features already provided in the excel

24

Documentation of Software Files prepared during audit: Uses and challenges 1. 2. 3. 4.

Constant changes – final file? Separate client directory Standard file name standard No documentation on working paper file 5. Remedy: print directory, file name and time)

25

End of Session 1 Thank you

26

Training Session Information Analysis for Auditors: Database Having a dialogue with the data, analysing data, drawing conclusions and having error-free data

Session 2 Presenter: Altaf Noor Ali Chartered Accountant

27

2nd Session 11.30 -1.30 = 120 min Information Analysis: Converting Data into a Database 1. Session Objective 2. Questions about a set of data 3. Example: Data to Database 4. Using ‘AutoFilter’ feature 5. Interrogating data 6. Learning from data 7. Transforming data 8. Conclusion

28

Session Objective 1. The difference between data and database 2. Converting data into a database 3. Illustrate use of autofilter tool for interrogating data 4. Drawing proper conclusions

29

Questions about a set of data 1. 2. 3. 4. 5. 6. 7.

Maximum value Minimum value Values falling within a range Locating a particular record Aggregating data Organising and editing data How accurate is the data?

30

Example: Data to Database 1. A set of data when organised in a certain form is called database. In other words, database is data laid out in a systematic manner 2. Database allows new insights into data, it allows interrogating of data which is the most powerful tool in a spreadsheet

31

Using ‘AutoFilter’ feature 1. A data needs to be bought in a certain format to enable it to be converted into a database. For example, for each column, the title of top row is expressed as a single name and there should be no blank lines between the rows 2. The use of auto filter enables data to be seen and understood in a new light

32

Interrogating data 1. Likeness: Star Trek type computer, ask any question and it responds 2. Converting data into a database allows data to be interrogated and understood well by the user 3. This happens because a user is able to put questions about the database and consider responses 4. Quality of data is enhanced while reviewing and errors corrected

33

Learning from data 1. Asking the right questions 2. Having the competence of designing right questions 3. Above Below 4. Top ten 5. Customise your queries 6. Collecting responses

34

Transforming data 1. The computer based accounting system should be capable of exporting data to the excel 2. The data may not appear in a readily useable position 3. You should be mentally prepared to convert data into a database. This may involve deleting lines between data, deleting blank columns, bringing text into lower case for easy reading, finding replacing certain text, increasing row width, reformatting date, converting numbers into formulas, etc.

35

Conclusion 1. Every auditor should know how to convert data into a database 2. Auditor should make the best use of features offered by excel to reach to audit conclusion, primarily through understanding the relevant data

36

End of Session 2 Thank you

37

Training Session Information Compiling for Auditors Developing a model accounting system on excel

Session 3

Presenter: Altaf Noor Ali Chartered Accountant

38

3rd Session 2.30 - 4 = 90 min Information Analysis: Using excel for information compiling [an excel based model stand alone accounting system]

1. Session Objective 2. Skills you require 3. Practical Example: From setup to financial statements 4. Common errors 5. Conclusion

39

Session Objective 1. If all computer based accounting systems are databases, whynot use excel features to develop a reasonable accounting system? 2. Sharing the experience of developing a stand alone accounting system on excel 3. Can you do it?

40

Skills you require 1. Some basic accounting concepts 2. Basic spreadsheet skills, as described ahead 3. Learning capacity [investment in terms of time] 4. Practice [hardware and software] 5. Ability to find and correct your own mistakes [undertaking painstaking checking]

41

Practical Example: From set-up to financial statements

42

Basic Set-up 1. Separate worksheet for each bank account 1. Restricting chart of accounts to about 50 accounts

2. One worksheet for passing journal entries 3. One worksheet for summarising balances 4. One worksheet for balance sheet 5. One worksheet for income and expenditure 6. One worksheet for notes to the accounts 43

Limitations 1. Restricted chart of accounts 2. No real accounts [debtors and creditors] 3. No fixed assets register 4. No separate bank book to save on bank reconciliation statement 5. No other subsidiary vouchers like debit and credit note recording

44

Strengths 1. Acceptable legally 2. Small memory size 3. Easily edited, better quality, error fixing is easier. 4. Analysis is easier through charts 5. Data can be easily exported to word etc.

45

Model Sequence 1. 2. 3. 4. 5. 6.

Enter bank transactions Verify Voucher Code transactions Adjusting entries worksheet Consolidation Find errors, making corrections 7. Analytical review, editing text 8. Compile financial statements

46

Common Errors 1. Inserting lines below the range 2. Redefining codes 3. Sub-group codes 4. Editing initial text inputs 5. Verification and reviews 6. Self-review threat

47

Conclusion 1. It is possible to build a reasonable accounting system on excel 2. This capability may be used for limited verification of processing of main computer based accounting system

48

49

End of Session 3 Thank you

50