Auditing Program by using excel to answer the questions QuestionsYour accounting firm is auditor of VINCENTPRAN LTD. A consumable product company that deals in sale of traditional cloths on both whole

Using Microsoft Excel as Audit Software

1. Introduction

Auditors use audit software to perform different types of audit tests on the accounting records maintained by the client company. The client accounting records or data files are often text files. It is difficult for an auditor to work on text files unless they are imported into the audit software.

The audit software is used to:

1. define the content of data records and perform various operations to generate reports.

2. perform many different kinds of tests and other functions where the client’s data are in machine-readable form. Below is a list of examples of audit works that an auditor can perform using the audit software.

1. Testing accuracy of accounting records. Generalised Audit Software (GAS) can be used to:

1. verify the accuracy of the client’s calculations. For example, the auditor independently recalculating employees’ net pays, employees’ overtime, pension contribution, accounts receivable and accounts payables balances.

2. verify accuracy of figures on source documents to accounting records. For example, verifying if figures in sales invoices tie with records in the sales journal, figures in purchase invoices tie with records in purchase journal.

1. Testing completeness. The auditor’s software can:

1. check if all records in the journals have been completely transferred to the subsidiary ledger. For example, all records in sales journal have been transferred to the sales ledger or accounts receivable ledger.

3. Testing for consistency. The audit software can use dummy data to

(i) determine consistency of control procedures and report exceptions. For example, review if accounts receivable balances have exceeded the credit limit and identifying inventory items that may be obsolete.

4. Test for Duplication: The audit software can sort data or records by ranges, numbers or values. For example, sorting data by invoice numbers to identify duplication and by payment vouchers’ numbers to identify double payments.

5. Comparing data on separate files. Where records on separate files should contain compatible data, the software can be used to:

(i) determine whether the data are comparable on separable files or to make other comparisons. For instance, changes in payroll master records between two dates can be compared with details of appointments and terminations of employees, and changes in job classifications.

6. Performing Analytical Review: The auditor can use the software to:

(i) determine reasonableness of changes over years. For example, comparing inventory this year with last year, using horizontal analysis/vertical analysis/ratio analysis.

(ii) predict going concern ability of the business entity. For example, using forecasting techniques such as trends analysis/regression analysis.

1. predict inventory movement.

7. Summarising or resequencing data and performing analyses. The audit software can change the format and aggregate data in a variety of ways. For example,

(i) verifying accounts receivable ageing,

(ii) preparing general ledger trial balances,

(iii)summarising inventory turnover statistics for obsolescence analysis and

(iv) resequencing inventory items by location to facilitate physical observations.

8. Selecting audit samples. The auditor’s software can select samples from client data in several different ways, including using statistical sampling methods.

1. Microsoft Excel as an audit software

This study material is to introduce students to Microsoft Excel as audit software.

Using Excel for Data Analysis

A range of alternative software tools is available for data analysis - Excel, Access, dBASE, ACL, SAS, SPSS. In the case of difficult task such as joining files, stratified sampling and other more complex quantitative analysis, tools other than excel are used. ACL, SAS, SPSS and now TABLEAU are used especially when working on big data. Excel is a powerful solution for complex calculations, statistical analyses, database queries (where you want only data meeting specific criteria) and generating charts.

Excel's Database - the List

A list is a two-dimensional range of cells. There are rows ("records") and columns ("fields"). The first row of the list contains the column headings ("field names") representing the set of data available for each of the records. The field names are used to organise the data, find records meeting criteria and generate reports. No blank rows or underline should separate the field names from the rest of the data. Excel can handle only one list at a time.

The following topics are explained:

1. Importing accounting data into Microsoft Excel

2. Sorting and summarising data

3. Exception reporting using advanced filters

4. Pivot tables for data analysis

5. Charting the results

6. Handling report files

7. Selecting a random sample

8. Locating matching data or joining

Importing data into Microsoft excel

Excel can read 3 types of text files: Comma Separated Values (CSV), Text and Column-Delimited or Fixed Width. Fields in CSV files are separated by commas. Values are enclosed in quotation marks if they contain a comma. Text files have fields which are Tab delimited. Fixed Width files have fields of fixed width. Each field is assigned to specific character positions in a record. Unused positions are filled with spaces.

Accounting data may be passed across to Excel using a number of approaches:

• Accounting software may have an option to produce output in a worksheet (e.g. Excel) or Text (ASCII) format.

• If a database management system (DBMS) is used by the accounting application, Structured Query Language (SQL) commands may be available to extract a flat file (fixed record length, ASCII file).

• Accounting software may have an option to produce a report which is to be stored in a file (including headings, page numbers etc.).

To open such files:

1. Choose the File > Open command. Excel displays the standard File Open dialog box.

1. Select the file format for the type of file you want to import in the List Files of Type drop-down list box.

1. Navigate to the drive and folder containing the file to be imported. Select the file in the File Name list box.

1. Choose the OK button.

When reading a text file, Excel automatically opens the Text Import Wizard to import the file:

1. Select the Delimited or Fixed Width option button.

1. Select the type of application or system from where the data is coming in the File Origin list box - Windows ANSI.

1. Select the starting row for importing data. You may wish to skip initial rows, e.g. headings in a report.

Press NEXT to continue.

If you selected Delimited, you are then required to specify what delimiters (normally Tab) are used by your file and the Text Qualifier (normally "). Press NEXT to continue.

If you select Fixed Width, you indicate where the location of each column break by clicking (remove by double-clicking) the ruler at the top of the Data Preview window. Press NEXT to continue.

1. Select the data format for each column. Options:

• General - Converts numeric values to numbers, data values to dates and all other values to text.

• Text - Formats all data as text.

• Date - Formats data to a date using the specified format.

• Do Not Import - Skips the column

1. Press FINISH to import the data.

1. Having checked the data, you should insert a row at the top of the list and give each column its heading. You should then save the file as an Excel workbook using the File > Save As command (specify the Workbook as its type).

Sorting and Summarising Data

Sorting a List

In the first section, we learned how to store data (Master) in a list. Remember, this is a two-dimensional range of cells, with a heading row containing the field names. Once data has been stored in a list, it can be sorted in various ways. Records may be sorted to facilitate reporting with subtotals or to arrange data in ascending/ descending amount sequence to focus attention on more significant items.

To sort a list, use the Sort command on the Data menu to arrange either the records or fields in ascending or descending order using the field(s) of your choice:

1. Select the range of the data list to be sorted. Clicking on any cell in the list will do. Select Data > Sort. Note the "My Data has Headers" button in the Sort dialog box must be checked to exclude the field name row from the sort.

1. Choose the fields to be used for sorting. Use the drop down list for selection and indicate ascending or descending sequence. Multiple levels of sorting are permitted. Select the primary sort column first, then add a level for the secondary sort column.

1. Choose OK to begin the sorting.

Producing Subtotals

Subtotals may be generated using the Subtotals command in the Data menu:

1. Select the "At each change in" (sorting) field for which subtotals will be generated.

1. Select the Function e.g. SUM, COUNT, AVERAGE, MAX, MIN, etc.

1. Select the field to be subtotalled.

1. Check whether Page Breaks and a Summary are required.

1. Choose OK.

To remove subtotals, select the Subtotals command again and select Remove All.

Detecting Duplicates

A common analysis performed on a file (master or transaction) is to check for duplicate records. For example, a transaction could be recorded twice or an account number may have been assigned to more than one account.

Sorting and subtotalling on the relevant transaction/document number or account number would highlight accounts with duplicates. In subtotalling, the relevant function could be COUNT. Another alternative is to introduce an extra field in the sorted list that would produce a value of 1 for any record where the account number was the same as the prior record. A value of zero would indicate no duplicate. The value of the field would be:

=IF(EXACT(A1,A2), 1, 0))

Duplicates could be extracted, using this field to a separate report.

Summary Statistics

Excel provides a range of statistical functions suitable for providing an overview of data:

• MAX Maximum value among a range of cells (e.g. =max(d2:d1000)

• MIN Minimum value among a range of cells

• COUNT Number of numeric values in a range of cells

• COUNTA Number of values in a range of cells, including non-numeric values

• AVERAGE Mean value of numeric values in a range of cells

• SUM Sum of numeric values in a range of cells

These functions are useful for summarising the entire data list.

Stratification

The purpose of stratification is to understand the distribution of values within a set of data. For example, we may wish to determine how many products have book values greater than some threshold or within particular ranges of values.

Having determined the MIN and MAX values for a given field, we need to set intervals for the stratification. For example, we may decide to use 5 strata and accordingly set a maximum value for each stratum:

Stratum Maximum Value Range of Values

1 0 Less than or equal to 0

2 1000 .01 to 1000

3 5000 1000.01 to 5000

4 10000 5000.01 to 10000

5 Greater than 10000

The simplest way of assigning a stratum number to each record is to define a further field - STRATUM. Its value is:

=IF(L2 <= 0, 1, IF(L2 <= 1000, 2, IF(L2 <= 5000, 3, IF(L2 <= 10000, 4, 5))))

Summary data can be extracted for each stratum using advanced filters and statistical functions, or pivot tables (covered later).

Ageing of Records

Ageing of records can be useful for determining how many days that an account is past due or how many days since a selling price was last revised. In such an application, the relevant date field can be compared with a cut-off date to determine the age in days. The age field would have the value:

=DAYS360(START_DATE,END_DATE)

E.G. =DAYS360(H2,"31/12/12")

Accounts in different age categories can be summarised in similar ways to the above.

Database Functions for Summarisation

Excel provides a set of specialised database functions for analysing lists. These functions can perform such operations as counting, averaging or totalling the values in any numeric field for only those records that meet specified criteria. The form of these functions is:

=DFUNCTION(database,field,criteria)

For example, to count the number of records in stratum 3, the following function could be used:

=DCOUNT(A1:N153,"STRATUM",P1:P2)

where range P1:P2 has the value:

P

1 STRATUM

2 3

Database functions include: DAVERAGE, DCOUNT, DCOUNTA, DMAX, DMIN, and DSUM.

Excel also provides 2 additional database functions - SUMIF and COUNTIF. Their syntax is:

=SUMIF(range, criteria, sum_range)

=COUNTIF(range,criteria)

To perform the above count, we could use:

=COUNTIF(N1:N153,3) assuming the STRATUM field is in column N.

Sorting & Summarising

Open MASTER (FORLUCK). Sort your list in ascending DOCNO sequence. Add an extra field DUP to each record. The value of this field for the first record (cell N2) is:

=IF(EXACT(A1,A2),1,0)

Copy this cell to the rest of the records.

Now use COUNTIF to count the number of duplicate records – cell N155:

=COUNTIF(N1:N153,1)

Open MASTER FILE . Add an extra field STRATUM to each record. Stratify each record on the basis of the value of the AMTPAID field or transaction value field. Assuming AMTis column G, the value of this field for the first record is:

=IF(G3 <= 0, 1, IF(G3 <= 1000, 2, IF(G3 <= 5000, 3, IF(G3 <= 10000, 4, 5))))

Copy this field to the rest of the records.

Now produce a summary table showing the count, average and sum of the AMTPAID field for each stratum. Use the functions - DCOUNT, DAVERAGE and DSUM. Use COUNT, AVERAGE and SUM for the total line. Organise the table as:

Exception Reporting Using Filters

Filtering your Data

The selection of records in a list that meet specified criteria is called ‘filtering’. Basic filtering can be performed using Data > Filter. This provides the ability to select records from a pull-down list for each column in the list. Records with specific field values, e.g. location 01, and 06, can be selected. Further selections can then be made from within those already selected using different field, e.g. product status A. Working papers can then be printed containing the filtered records.

Advanced Filters

Advanced filter provides a powerful tool for selecting and extracting a subset of the records in a list for further analysis and reporting. Criteria for selection are specified which can include calculated fields or complex conditions involving more than one field.

Advanced filter requires setting up a "Criteria range" including at least 2 rows - a row of field names and at least one row of criteria (though you can have more than one). You can have the option of copying the extracted records to a "Copy to" range (which is a good idea) where you could then set up a report with totals etc. for printing. The "Copy to" range should be set up with identical field names to the list and should be large enough to accommodate the entire list. However, only the fields to be copied need to be specified. The ordering of fields can also be changed.

An example of a criteria range to select records falling into stratum 3 is:

STRATUM

3

To select records with AMTPAID greater 1000 use:

BOOK

>1000 (enter this as '>10000)

An "AND" condition uses at least 2 fields:

STRATUM BOOK

3 >1000

or

BOOK BOOK

>=0 <1000

An "OR" condition uses at least 2 rows:

STRATUM

2

3

Pivot Tables for Data Analysis

Pivot Tables are interactive tables containing summarised data. To produce a pivot table from a list, just click on any cell in the list and then:

1. Select Insert > Pivot Table. The Pivot Table Wizard is invoked to guide you through construction of the table. It assumes the source of the data is an Excel list. The source however could be an external data source, multiple consolidation ranges or another pivot table. Press NEXT.

1. Check that the data range specification matches the location of the list.

1. Select New Worksheet for output. This is stored in a new sheet in the same workbook. Press OK.

1. You then drag the field names into the ROW, COLUMN, DATA and PAGE positions. The DATA is the field(s) to appear in the body of the table which will be summed, counted, averaged, etc.

1. Format the data as required.

1. Rename the current sheet (Sheet 1) to Pivot Table.

1. File > Save.

Once the table has been generated, you can pivot by dragging fields around to see the results.

Click on the pivot table then click on Design to select subtotals and grand totals, report layout, and row/column headers.

The pivot table display does not change when the underlying data changes. The table can be updated or refreshed by selecting any cell in the table and clicking the Refresh Data button on the Query and Pivot toolbar.

Charting the Results

A useful way of presenting your results is to use a chart. A chart can be created from a two-dimensional table of data, such as a pivot table. Excel provides a Chart Wizard to allow you to create charts quickly.

To create a chart:

1. Select the data to be charted. If the data is in a pivot table, avoid any columns containing totals.

1. Select Chart from the Insert menu, e.g. Column.

1. With the chart selected, you can use Layout to set chart and axis titles, legend, rotation, etc.

1. Click on Insert > Header/Footer to set chart header and footer.

1. Print your chart.

Handling Report Files

A report file (or spooled file) is a file containing a copy of a report. Report files are generated by an accounting package by outputing to a file rather than the printer. Accordingly, this file will contain headings, page numbers, totals and blank lines for spacing.

Such a file can be imported into Excel as a text file. However, we want to ignore all the records with headings, page numbers, totals and blanks. We may use the Advanced Filter to extract only the data records from the report. To do so, we need to identify a unique characteristic that only the data records have.

Random Sampling

Excel has a function RAND() that generates random numbers between 0 and 1. This can be used to generate a series of n random numbers, given a range of relevant document numbers, A1 to B1:

=INT($A$1 + ($B$1-$A$1)*RAND() )

Random samples can be selected from a list. Given n (sample size) and N (population size), we can calculate a probability of selection for each record of n/N. Each record can have an extra field - SAMPLE. Its value can be:

=IF(RAND() <= n/N,1,0)

Records to be included in the sample will have a value of 1 in this field. This generates a sample size close to n. These records can be extracted using an advanced filter.

Locating Matching Data or Joining

Often, we wish to locate matching data from another source to provide more information in a report. For example, we might have transaction data that includes the customer number, but not the corresponding customer name. The latter may be stored in a separate file. We may want to include the customer name against each transaction in a report. We need to ‘join’ the data from the two sources – locate the matching customer name for the customer number in the transaction, and display it along with the transaction.

We will assume we have two worksheets – MASTER and DEPT. DEPT has a list of department names that correspond to the values of PRODCLS (product class) in MASTER. We plan to bring the DEPT data into MASTER, so the department name can be displayed for each product. MASTER is referred to as the primary data list, because we will look up values from MASTER in the DEPT data list (referred to as the secondary data list). Both worksheets have to be open at the same time.

Firstly, add another sheet to the MASTER workbook. Change its name to DEPT. Copy and paste the department data list from the DEPT workbook to the DEPT sheet in MASTER.

Return to the MASTER sheet. Add a new column to the inventory data called DEPT. The value of the first cell in this column should be:

=IFERROR(VLOOKUP(M2,DEPT!$A$1:$B$11,2,FALSE), "No Match")

The VLOOKUP function is used to take the value in column M (M2 is the first cell in the PRODCLS column, e.g. “01”) and refer to the data list DEPT!$A$1:$B$11 (in the DEPT sheet) to search vertically down column 1 (assumed to hold matching values of column M in MASTER) to locate the matching row for a given product class. The parameter 2 indicates that the second column in the row should be taken as the value of the function (e.g. ‘Housewares’). FALSE requires the function to locate only perfect matches, not near matches. The IFERROR function addresses the possibility that a product class may have no matching department name in DEPT. In this case, the value to be displayed is “No Match”.

Working papers and reports can then be produced using the joined data lists.

The two related data lists must have a common text column. The secondary data list must be in ascending sorted order on this column.

Joining data lists can have many applications. Joining allows the generation of reports incorporating data from both sources. For example, as explained above, we may wish to include department names in an inventory report. Alternatively, we may wish to report matches and mismatches between the data in two tables. For example, by joining an inventory master data with stocktake data, we can identify items not counted and review the magnitude of differences between recorded quantities and count quantities.

Exercises 1: Audit of Accounts Receivable

Using the accounts receivable master file (AREMAF), you are required to

1. Import the text file into excel.

2. Identify duplication of sales and or payments by customers.

3. Identify all negative values.

4. Separate negative values from invoice and credit note values.

5. Recalculate the proper accounts receivable balance.

6. Identify how many customers are taking longer than 30 days to pay.

7. Identify accounts receivable balances with values between greater than $1000.

8. Assuming the company credit limit for a customer is $1200, identify customers who were granted credit over the limit Is this a test of control or substantive test?

9. Prepare an age analysis assuming credit limit period is 45 days.

10. Calculate provision for bad debts assuming the rate is 3% using the clients’ accounts receivable balance and then using the audited accounts receivable balance.

11. Write your audit conclusions.

12. Save your work in pdf format.