course outline using idea to audit revenue idea to audit revenue.pdf · current auditing standards...

COURSE OUTLINE

Using IDEA to Audit Revenue

Session 1: Setup 1 Create a folder and copy the data files

Check the IDEA version number Set the Working Folder Record Data File Control Totals

Session 2: Are Audit Trails Complete and Is Reporting Accurate? 5 Summarize by Invoice

Identifying Open Invoices Aging Analysis of Open Invoices Other Tests to Consider

Session 3: Are Revenue Transactions Accurate? 15Revenue Reversals

Identifying Possible Reversed Transactions Shipping and Billing Errors

Orders Not Shipped Over-billing of Orders Invoiced Prior to Shipment

Other Tests to Consider Session 4: Are Revenue Transactions Complete? 31

Sequential Gap Detection Identifying Gaps in Invoice Number Sequences

Sales Billed without a Sales Order Identifying Invoices without Matching Sales Orders

Other Tests to Consider Session 5: Are Revenue Balances Correct? 37 Customers with Numerous Invoices

Summarize Invoices by Customer Calculation of Open Invoice Ratio Other Tests to Consider

Using IDEA to Audit Revenues

Session 6: Are Revenue Transactions Authorized? 47Are Customer Credit Limits Authorized and Adhered to?

Extracting Customers with No Credit Limit Matching Customer Invoices to Credit Limits Extracting Credit Limit Exceptions

Is there Common Information in the Customer Master and Employee Master Data Files?

Cross-Matching Using the First 8 Characters Cross-Matching Addresses Using Just Numbers

Using Editable Fields as Tickmarks or Manually Tag Items Other Tests to Consider

Session 7: Are Activities Operating Effectively and Efficiently? 65Addressing the Quantity of Transactions by Size

Stratification by Size Extract High Volume Customers Other Tests to Consider

Conclusion 71 Complete Evaluation 73

Overview Overview Revenues generate the cash that is the life-blood of any organization and therefore, is a major area of concern of all auditors. The restatement of revenue reporting due to error or fraud has caused financial calamity for many organizations in recent years. In today’s business climate, improper revenue recognition attracts unflattering media attention. It can degrade customer confidence, impact company profitability and even jeopardize a company’s ability to sustain operations. Revenue analysis can accomplish many goals for an organization. These include avoiding financial misstatements, preventing uncollectible accounts by reviewing billing practices, assessing the effectiveness of the accounts receivable lifecycle and reducing fraud. Looking at revenue analysis from an audit perspective, the auditor’s goal is to address certain audit objectives to be able to express an opinion on the completeness, existence, accuracy, validity, valuation and reporting of revenue transactions. Current auditing standards require that we understand the organization’s business and for risk assessments and analytical procedures to be performed during the planning phase of the audit. As part of the risk assessment and as a good practice while planning any professional services, it is important to really understand WHY you have the engagement. Is the company publicly held so that an audit is a regulation the client must follow? Are there lenders or other stakeholders who require the audited financial statements? Who will be relying on the financial statements? What does management expect from your services? In this seminar, we will learn a variety of data analysis tests designed to provide:

• assurance as to the effectiveness of internal controls over the accounts receivable/revenue cycle

• assurance as to the accuracy and completeness of revenues • identification of risks that fraud or material misstatement might exist • identification of certain indicators that could improve operations or profitability

Session 1

©2007 Audimation Services, Inc. 1

Session 1: Setup

Insert the User Group Data Files CD into your CD-ROM

Browse to the CD and select all files and copy them into a folder titled IDEA Revenue Analysis. Note: If your user profile restricts your ability to create folders outside of your My Documents folder, simply override the folder location listed for the extraction of the data files and redirect it to your My Documents folder.

Launch IDEA If you need to borrow a key from the instructor, you will need your driver’s license for a deposit. If you are using a student version of IDEA, please note that you are limited to 10,000 records per file.

Check that the version number of IDEA is 7.1 or 7.0. Set the Working Folder IDEA uses the concept of a Working Folder to organize and store databases. It is recommended that a separate Working Folder be created for each audit, investigation, or analysis. This can be done using the IDEA File Explorer window or by using Windows Explorer. All files imported and created in the course of your work will be stored in the Working Folder you create. Once a Working Folder is set it remains as the active folder until changed. After launching IDEA, change the Working Folder.

1. Select Set Working Folder from the File menu options.


2. Set the Working Folder to C:\IDEA Revenue Analysis.

3. The Project Properties window will appear. Enter the name of the project or client in the Project name: “User Group” and the Period: “today’s date”.

4. In order to save you time, the IDEA databases that will be used in today’s session have already been imported into IDEA. Before we begin any testing, it is important to make certain that the files obtained are complete. Take a moment to open each database, and record the number of records, date range and control total for each file by clicking on FIELD STATISTICS in the properties window.

Database Name # Records Period Start

Period End

Control Total(s)

Approved Prices N/A N/A N/A

Cash Receipts

Customer Master N/A N/A N/A

Customer Master Old

N/A N/A N/A

Employee Master N/A N/A N/A

GL Transactions

Invoices

Order and Ship

Prior Invoices

2

Session 1


Session 1: Notes


4

Session 2


Session 2: Are Audit Trails Complete and Is Reporting Accurate? Audit trails allow anyone within an organization to retrieve essential facts about a transaction. Management uses reports from the organization’s accounting systems to monitor the progress of the organization; to evaluate current financial condition; and to monitor trends in operations. The accuracy of the reports that management relies on is critical.

The Invoices.IMD data represents line item detail information from the sales invoicing system. In order to focus on information at the invoice level, you will first need to summarize this detail data by invoice.

Summarize by Invoice

1. Open the Invoices.IMD file by double clicking on the file name in the File Explorer window.

2. From the Analysis menu, select Summarization.

3. Select the INV_NUM field as the field to summarize.

4. Select the INV_AMT field as the field to total.

5. Click on the Fields button and select the CUS_NUM field, the PD_DATE field and the INV_DATE field to be included in the summary file. Click OK to close this window.


6. Change the file name to Sum by Invoice and click OK to complete the summary.

Now that the invoice data has been summarized to the invoice level, you can analyze the dollar value size of the invoices by performing a stratification of the invoice amount field.

6

Session 2


Identifying Open Invoices

1. Open the Sum by Invoice.IMD file.

2. From the Data menu, select the Extractions sub-menu, then select Direct Extraction.

3. Change the file name to Open Invoices.

4. Click on the Equation Editor icon to open the Equation Editor window.


5. Enter the following equation: PD_DATE = “00000000”

6. Click the green checkmark icon to close the Equation Editor and store your equation.

7. Click on the Fields button and verify that the INV_DATE field will be included in the summary file (the default is for all fields to be included).

Click OK to close this window.

8. Click OK to complete the extraction.

8

Session 2


Customers with past due balances represent a credit risk to an organization. Past due balances must be evaluated for collectibility and may necessitate adjustments to the organizations financial statements for end of period reporting. One method for evaluating open invoices is to re-perform an aging analysis utilizing IDEA’s Aging tool.


Aging Analysis of Open Invoices

1. Open the Open Invoices.IMD file.

2. From the Analysis menu, select Aging.

3. For the Aging date, enter 2007/01/22.

Note: The aging date would usually be the latest invoice date in the original invoice file plus one day. To verify this date, you can open the Invoices.IMD file. Go to the Properties window and select Field Statistics. Look at the Date field statistics for the INV_DATE field. In the Latest Date statistic, you should see the date 2007/01/22.

4. For the Aging field to use, select INV_DATE as the date field.

5. For the Amount field to total, select INV_AMT_SUM as the numeric field.

6. Check the box for Generate detailed aging database and change the file name to Aging of Open Invoices.

7. Check the box for Generate key summary database and change the file name to Age Summary of Open Invoices.

10

Session 2


8. Click on the Key button and select CUS_NUM as the summary key. Click OK to close this window.

9. Check the box for Create Result and change the name to Aging of Open Invoices.

10. Click OK to complete the aging analysis process.

The aging result should be compared to system generated aging reports for the same time frame to assess the accuracy and reliability of the system reports. The Aging of Open Invoices and Age Summary of Open Invoices databases provide a means to quickly identify which customers and which particular invoices may be collection risks. You can easily identify any customers with net credit balances by indexing the Age Summary of Open Invoices database using the INV_AMT_SUM field in ascending order. This will bring customers with net credit balances to the top of the list.


Other Tests to Consider 1. You might want to run a Benford’s Law analysis on the line item detail of the invoices as

well as on the invoice total amounts to see how the distribution of values compares with Benford’s predictions.

2. If you have data from multiple time periods, you could run a Benford’s Law analysis for each period and then compare the distribution graphs. If there are no dramatic changes in business operations from period to period, you would expect the distribution of the Benford’s graphs to remain reasonably the same.

12

Session 2


Session 2: Notes


14

Session 3


Session 3: Are Revenue Transactions Accurate? The primary concern in this area is whether transactions in the sales processing system are accurate or free from errors. Issues in this category would include whether there are duplicate invoices detected, whether revenue reversal entries are appropriate, and whether the quantities shipped and prices listed on the sales order match the quantities billed and prices listed on the invoice.

Revenue Reversals Two common areas where fraud may occur in revenue are pre-billed revenues that are later reversed and re-billed; and fraudulent invoices created to meet goals in a particular period that are later reversed or written off as a bad debt. There are a number of ways that you could search for reversed transactions. This exercise focuses on a method that utilizes the absolute value of the transactions.

Identifying Possible Reversed Transactions

1. Open the Invoices.IMD file.

2. Select DATA>Field Manipulation>Append and append an absolute value field called Abs_Amt using the following equation: @abs(INV_AMT)


16

Session 3


Bring the Debits and Credits Together

3. From the Analysis menu, select the Duplicate Key sub-menu, then select Exclusion.

4. For the Fields to match, select the CUS_NUM and ABS_AMT fields.

5. For the Fields that must be different, select the INV_AMT field.

6. Change the file name to Possible Revenue Reversals and click OK to complete the analysis.

7. Indexing the resulting database by the ABS_AMT field in descending order and the INV_DATE field in ascending order will show you the largest transactions first and will put the transactions in their order of occurrence.


A total of 244 possible revenue reversals have been identified.

18

Session 3


Shipping and Billing Errors

Shipping and billing accuracy is directly related to the quality of customer service provided. Errors in shipping and billing can impair a customer’s trust in the organization. If possible errors are found, you should look for the gap in controls that allowed the error to occur and determine if it represents a control weakness that needs to be corrected.

Orders Not Shipped

1. Open the Order and Ship.IMD file.

2. Index the file using the SHIP_DATE field in ascending order. Orders that have no recorded ship date will be listed at the top of the indexed file.

3. Right click on the shipping date cell for one of the records with no ship date and select Display All Records Containing. This will apply a filter so only the unshipped records will be visible.


4. From the File menu, select Save As and enter the file name Unshipped Orders.

5. Click OK to complete the extraction process.

20

Session 3


6. Return to the Order and Ship database.

7. In the Properties window on the right hand side of your IDEA desktop, right click on Criteria and select Clear to return to your original unfiltered view of the data.

Depending on the time it takes to prepare and ship an order, most organizations would expect to have some orders that were not yet shipped. However, orders outstanding for more than a few days should be reviewed.


Over-billing of Orders

1. Open the Order and Ship.IMD file.

2. Open Data > Field Manipulation and change the field type for the ORD_NUM field from Numeric to Character so its field type will match the order number field in the invoice file.

3. From the File menu, select Join Databases.

22

Session 3


4. The Order and Ship should be the primary database.

5. The Invoices should be the secondary database.

6. Change the file name to Compare Ordered to Billed.

7. Click on the Match button to select match keys. Select the ORD_NUM field in the Primary file and the ORD_NUM field in the Secondary file as the match keys.


8. Select All records in primary file as the join method to be used and click OK to complete the join process.


10. Change the file name to Overbilled Orders.


11. Click on the Equation Editor icon to open the Equation Editor.

12. Enter the following equation: (ORD_QTY * INV_UNIT_PR) <> INV_AMT

24

Session 3





In the normal course of operations for most businesses, it would be highly unusual for a customer to be invoiced for an order prior to that order being shipped to the customer. This type of activity is usually seen when someone is trying to either manipulate sales data by pre-dating invoices or to manipulate inventory data by delaying the recognition of the inventory reduction. In either case, if the invoicing date and shipping date can be matched by order number, testing for such discrepancies can be an easy process.

26

Session 3


Invoiced Prior to Shipment

1. Open the Compare Ordered to Billed database created in the previous exercise.



3. Change the file name to Invoiced before Shipped. 4. Click on the Equation Editor icon to open the Equation Editor. 5. Enter the following equation: INV_DATE < SHIP_DATE



Extract identified 237 records.

28

Session 3


Other Tests to Consider

1. Calculate the average size of sales by customer and identify individual sales transactions to customers that exceed the average sale (or some multiple of the average sale) for a customer.

2. You can search for duplicate invoices using various combinations of fields as the duplicate keys.

3. You can search for re-issued invoices where the first invoice was issued incorrectly using the duplicate key exclusion tool.

4. If you have data on the quantity ordered and the quantity shipped, you can search for orders where the two quantities do not agree (over shipping or under shipping of ordered quantities).

5. You can search for orders where there is no corresponding invoice information to identify unrecorded sales.


Session 3: Notes

30

Session 4


Session 4: Are Revenue Transactions Complete?

It is generally more difficult to confirm that revenue transaction information is complete than it is to determine if it is accurate. This is because to ensure completeness you have to make sure that there are no missing transactions whereas to ensure accuracy you simply have to make sure there are no errors in the information that is present.

Sequential Gap Detection

Gap detection is one of the basic tests for completeness. There are several sequentially assigned numbers used in the sales process. These include the order numbers as well as the invoice numbers.

Identifying Gaps in Invoice Number Sequences


2. From the Analysis menu, select Gap Detection. The gap detection method used will adapt automatically depending upon the type of field you select for testing.

3. For Field to use, select INV_NUM.

4. Change the result name to Invoice Number Gap Detection.

5. Click OK to complete the gap detection test.


IDEA’s gap detection result will show the beginning and ending values for each gap detected along with the number of items in that range. Each range can be expanded to show the invoice numbers contained in that range.

32

Session 4


Sales Billed without a Sales Order The sales order process helps document the process by which sales orders are approved. Sales should not be invoiced without the authorization control process occurring. Invoice data can be matched to sales order data to determine if any invoices were processed that did not have a corresponding sales order.

Identifying Invoices without Matching Sales Orders

1. Open the Invoices.IMD file.


3. The Invoices file should be the primary database.

4. The Order and Ship file should be the secondary database.

5. Change the file name to Invoices without Sales Orders.


6. Click on the Match button to select match keys. Select the ORD_NUM field for the Primary (Invoices file) and the ORD_NUM field for the Secondary (Order and Ship file) as the match keys.


7. Select Records with no secondary match as the join method to be used and click OK to complete the join process.

As there is no sales order authorization control present for these invoices, you would need to investigate to determine what approval process was used for these invoices. The existence of appropriate authorization may be determined by reviewing hard copy documentation for the sale. If no such documentation is found, it is possible these invoices are fraudulent.

A total of 2,979 records were identified.

34

Session 4



1. A common practice to increase the appearance of sales dollars is to create and process fraudulent sales invoices at the end of a fiscal cut off period. One method to detect such tendencies would be to stratify the sales by date, using a separate strata for the last few days at the end of each fiscal period to determine the volume and size of transactions being processed at period end.

2. Another approach to detecting such sales padding would be to calculate the average sales by customer over an extended period of time. The compare that average to the sales processed during smaller periods of time while paying particular attention to any periods where the volume and size of sales processed exceeds the calculated average sales for a customer.


Session 4: Notes

36

Session 5


Session 5: Are Revenue Balances Correct? In order to assess whether the revenue balances are correctly reflected in an organization’s financial information, you have to step back from the microcosm of transactional processing to the macro-economic level that is management’s oversight and review procedures. Issues in this area include how overpayment and underpayment of accounts are handled; how are outstanding balances evaluated and do write-offs of uncollectible accounts occur in a timely manner, and is the summarized reporting information utilized for management decision making accurately reflective of the underlying accounts.

It is management’s responsibility to manage credit outstanding to customers. In order to do this, management must consider several aspects of the customer relationship including total volume of sales, current open invoices, and whether any of the open invoices are past due.

Customers with high open invoices to sales ratios should be closely monitored to ensure the balances are collectible. Organizations with seasonal businesses may have periods where high open invoice ratios are the norm. Customers with open invoices but no current sales may represent a collection risk for an organization and should be reviewed to make sure the open invoices are not fraudulent.

Customers with Numerous Invoices

It can be beneficial to identify customers with large volumes of transactions. This could identify erroneous or even fraudulent activity. If there is a large volume of low dollar activity, there may be issues of efficiency which could be addressed by reducing the frequency of invoicing.

Summarize Invoices by Customer




3. Select CUS_NUM as the Fields to summarize.

4. Select INV_AMT_SUM as the Numeric fields to total. 5. Change the file name to Sum Invoices by Customer and click OK to complete the

summary.

38

Session 5


In order to identify customers with high volumes of invoices, you will need to determine what level of invoicing represents a high volume. For this analysis, assume that customers who average more than 2 invoices per month are considered to be high volume customers.

Calculation of Open Invoice Ratio

1. Open the Sum by Invoices by Customer.IMD file.

2. Change the INV_AMT_SUM_SUM field name to Total Invoices and change the NO_OF_RECS1 field name to NO_OF_INV.

Click OK.


3. Open the Open Invoices.IMD file.


5. Select the CUS_NUM field as the Fields to summarize.

6. Select the INV_AMT_SUM field as the Numeric fields to total. 7. Change the file name to Sum Open Invoices by Customer

8. Click OK to complete the summary.

40

Session 5


9. Open the Field Manipulation screen by clicking on Data>Field Manipulation.

10. Change the INV_AMT_SUM field name to Open Invoices and change the No_of_Recs1 field name to No_Of_Open. Click OK.


11. Open the Sum Invoices by Customer.IMD file.


13. The Sum Invoices by Customer file should be the Primary database.

42

Session 5


14. The Sum Open Invoices by Customer file should be the Secondary database. Click on

the Fields button and deselect the CUS_NUM field in this database.

15. Click OK to close this window.

16. Change the file name to Compare Total to Open Invoices.

17. Click on the Match button to select match keys. Select the CUS_NUM field in both files as the match keys.


18. Select All records in primary file as the join method to be used and click OK to complete the join process.


19. Right click in the data area of Open Invoices field in the new database and select Insert Virtual Field from the short-cut menu to append a new virtual numeric field called Pct_Open using the following equation:

@if(Open_Invoices = 0, 0, (NO_OF_OPEN/NO_OF_INV) * 100).

44

Session 5


Click OK.


1. Extract and review invoices that were written-off and match them to current sales invoices to identify activity with the same customers.

2. If all invoice term information is available, extract and review accounts with excessive terms.

3. Look for re-aged account activity by matching current open invoices for a customer with previous credits and/or paid invoices for the same customer to look for exact matches. Re-aging outstanding invoices by offsetting a credit to an older invoice with the proceeds of a new invoice can be a deliberate attempt to change the valuation of accounts receivable. The original documentation should be reviewed on any matches identified to determine the reasonableness of the activity.


Session 5: Notes

46

Session 6


Session 6: Are Revenue Transactions Authorized?

In this section, we will focus on data analysis techniques that can be used to assess whether revenue transactions are properly authorized, accurate and complete. In general there are two ways in which unauthorized transactions can occur. The revenue processing system can be bypassed from inside the organization or it can be manipulated from outside the organization. Sometimes unauthorized transactions occur as a result of combined efforts from both inside and outside the organization (collusion between employees and customers). If system controls are continually circumvented, then errors and efficiencies will begin to occur.

The breadth of the data analysis test you will be able to perform on revenue transactions will be dependent on the types of information the organization can provide. Not every organization can provide a list of employee job responsibilities to identify which employees are authorized to create invoices and process cash receipts. In addition, not every accounting system records the identity of the employee entering the information. Many do not even track changes to master files. Additionally, many organizations do not have formal systems for establishing customer credit limits.

For this section, the data provided includes a customer master file with approved credit limit information as well as customer receipts data.

Are Customer Credit Limits Authorized and Adhered to? Customers with no set credit limit could accrue large receivable balances without being detected and, as a result, could present a collection risk to the organization. Additionally, the lack of credit limit could indicate that the customer master information was not completed properly either in error or by intention to hide fraudulent transactions.

Extracting Customers with No Credit Limit

1. Open the Customer Master.IMD file.



3. Change the File Name to No Credit Limit Customers. 4. Click on the Equation Editor icon to open the Equation Editor. 5. Enter the following equation: Credit_Limit = 0

This equation assumes that the credit limit field is a numeric field.



You would normally follow-up any findings by researching the customers without credit limits. Because there is always the possibility of finding fraud based on such results, the analyst/auditor should normally perform this research personally.

Customers whose balances exceed their approved credit limit represent potential credit risks to the organization. In order to perform this type of analysis, you will need to have a paid date field in the sales invoice data or to combine the cash receipts and sales invoice data in order to determine those invoices that have not yet been paid.

Once the open invoices have been identified, they can be matched to the customer credit limit information in the customer master data in order to determine if any of the customers with open invoices have exceeded their credit limit. This can be done in more than one way. For this exercise, we will be using the File Compare function.

48

Session 6


Matching Customer Invoices to Credit Limits

1. Open the Customer Master.IMD file. 2. From the File menu, select Compare Databases.

3. Select Credit_Limit as the Total field: for the Primary database.

4. Select Open Invoices.IMD as the Secondary database.


5. Select INV_AMT_SUM as the Total field for the Secondary database. 6. Click on the Match button and select CUS_NUM as the match key field for Primary

database and CUS_NUM as the match key field for the Secondary database. Click OK to close the Match Key Fields.

7. Change the File Name to Compare Credit Limits to Customer Balances and click OK to complete the comparison.

50

Session 6


The comparison matches all customers with open invoices to all of the customers listed in the customer master file. To complete our analysis, you will need to extract those customer records that represent credit limit exceptions.

In line 1, we have CUS_NUM 5000000 with a record in the primary file Customer Master with a primary total of 100. We do not have a record in the secondary file, Open Invoices. In line 2, we have a customer without a customer master file record but an open invoice of $55.00.

Extracting Credit Limit Exceptions

1. Open the Compare Credit Limits to Customer Balances.IMD file.



3. Change the File Name to Credit Limit Exceptions. 4. Click on the Equation Editor icon to open the Equation Editor. 5. Enter the following equation: S_NRECS > 1 .AND. Difference < 0



52

Session 6


This analysis identified 50 customers with open balances in excess of their credit limits. In these cases, the customers had no established credit limit. Although the balances for these customers were nominal, the appropriate next step would be further research to determine the viability of the open invoices and to try and determine the reason for the lack of credit limit.

Is There Common Information in the Customer Master and Employee Master Data Files? Most auditors and fraud examiners recommend cross-matching information from the customer master data to the employee master data. Depending on the nature of the organization’s business and its operating procedures, there can be several legitimate reasons for employees to be listed in the customer master files. However, there are also several potential fraud scenarios that have been associated with this phenomena as well including redirection of product shipments and false refunds for voided sales.

Cross-matching street address information can be very problematic due to variations in punctuation and spelling as well as the use of abbreviations for parts of the street address information. At a minimum, you should consider scrubbing the address field information to eliminate as many of these issues as possible. In the first address matching exercise, you will use IDEA’s @upper function to convert all alphabetical letters to upper case. You will use IDEA’s @strip function to remove all punctuation and spaces. Then, you will truncate the result to 8 characters to minimize the number of characters used in the matching process.


Cross-Matching Using the First 8 Characters Only


2. Append a new address field called First_8_Chars using the following equation: @left(@strip(@upper(Cus_Address)), 8)

54

Session 6


3. Open the Employee Master.IMD file.

4. Append a new address field called First_8_Chars using the following equation: @left(@strip(@upper(Emp_Address)), 8)



6. The Customer Master file should be the Primary database.

7. The Employee Master file should be the Secondary database.

8. Change the File Name to Match First 8 Characters.

9. Click on the Match button to select match keys. Select the First_8_Chars field from both databases as the match keys. Click OK to close this window.

10. Select Matches only as the join method to be used.

Several years ago, Mark Nigrini suggested an alternative approach to address cross-matching that would avoid the problems of variances in punctuation, spelling and abbreviations and return a higher portion of true address matches. Dr. Nigrini’s approached was inspired by his work in the area of digital analysis. He suggested distilling address information down to just the numbers contained in the address fields and then using only the numbers as the basis for cross-matching. This works best with a combination of street address information and zip code information. While it does achieve a higher rate of true address matches, it also returns a higher occurrence of false positives.

56

Session 6


Cross-Matching Addresses Using Just Numbers


2. Append a new address field called Just_Numbers using the following equation: @justnumbers(Cus_Address).

3. Open the Employee Master.IMD file.

4. Append a new address field called Just_Numbers using the following equation: @justnumbers(Emp_Address).



6. The Customer Master file should be the Primary database.

7. The Employee Master file should be the Secondary database.

8. Change the File Name to Match Just Numbers.

58

Session 6


9. Click the Match button to select match keys. Select the Just_Numbers field from both

databases as the match keys.


10. Select Matches only as the join method to be used.

Depending on the information available in the customer master data as well as the employee master data, analysts should consider other cross-matching opportunities. Analysts have had success cross-matching other information including tax ID numbers, customer phone numbers to employee primary and secondary phone numbers as well as cross-matching addresses to other addresses in the employee data such as the addresses of emergency contact persons.


Using Editable Fields as Tick Marks or Manually Tag Items You can insert manual fields to be used as a tick mark for manual audit activities or as a tag to extract just the records you need for further analysis.

1. Open the file Match Just Numbers.IMD.

2. Select Data>Field Manipulation from the menu, then select Append.

3. Append an Editable Fields with the following definition:

Name TICKMARKType Multistate

Parameter -1

60

Session 6


4. Click OK.

5. This creates a new field, TICKMARK. This field will contain a blank since the parameter value was set at -1.

6. To tag selected items, click in the center of the field to change the value to true (check

mark) or 1. If you click a second time, the value will change to X or 0. If you click a third time the value will change to a question mark ? or 3, and if you click a fourth time the value will change to a blank or -1 again.

7. If you wish to extract the X items to a new database, select Data>Extraction>Direct Extraction from the menu, enter a new file name, and enter the formula TICKMARK=0.

8. To determine how many items have been selected via the X Tickmark, click on Properties>Database>Control Total. Then select Tickmark to set the control total.

9. Click OK. 10. The default system setting is for the Editable Field definition or formula and any

entries\changes to the data to be recorded in the History. However, you can elect to not record changes made to Editable Fields in History by selecting View>Options>System from the menu and selecting the option to not record them (Do not record field changes made through editable fields).

11. Insert a Boolean Editable Field.


12. Select Data>Field Manipulation from the menu, then select Append.

13. Append an Editable Fields with the following definition:

Name TICKMARKType Boolean

Parameter 0

14. Click OK. You are able to create the following additional editable field types:

a. Editable numeric b. Editable character c. Editable date

62

Session 6



1. One area that can be reviewed is the prices utilized in the invoices. If there is an approved price list, invoice prices can be compared to approved prices to identify variances. The degree of variance from listed prices could also be calculated. If the sales structure allows for some leeway on pricing, it would be beneficial to identify the outliers in price variances.

2. Matching cash receipts after period end to the open invoices at period end would let you assess the collection process and viability of the outstanding receivables reported at period end.

3. If data is available for multiple periods, summarizing the data by customer, salesperson, inventory item, etc. and comparing summarized balances from one period to the next would enable you to do some trend analysis.

4. If customer master data is available for multiple periods, the data could be matched by customer to identify additions, deletions, and changes from period to period. Customer master data can also be quickly reviewed for missing or incomplete information.

5. Searching order date information for orders received on weekends or holidays would be prudent if such events do not normally occur.


Session 6: Notes

64

Session 7


Session 7: Are Activities Operating Effectively and Efficiently?

Effectiveness concerns how well a process runs and whether it was performed consistently with the desired results achieved. Efficiency centers more on factors of cost and time and what is the least cost and fastest way to achieve the desired results. Obviously, you can have an effective process that is not very efficient and vice versa. Well run operations find a balance between these two goals.

Not every aspect of effective and efficient operations can be assessed using data analysis techniques. Some things like whether written policies and procedures exist, whether exception reports are produced and reviewed regularly, and how customer complaints are handled will have to be evaluated though other means. We will focus on those aspects that can be tested using the information contained in the data.

Assessing the Quantity of Transactions by Size Mark Nigrini, the author of Digital Analysis Using Benford’s Law, recommends the profiling of financial transactions by size as the starting point for analyzing transactional data. At a minimum, he recommends stratifying the data for credit transactions (values less than $0), $0 value transactions, low dollar transactions (values greater than $0 but less than $50), and high dollar transactions (values over $50). Each of these categories should be reviewed.

The norm for credit transactions is about 3%. Higher percentages could indicate an excessive amount of corrections being made after processing. The percentage and purpose of $0 value transactions should be reviewed closely. Although there is not monetary value to the transactions, there is still a processing cost that impacts operating effectiveness. Likewise large numbers of low dollar invoices could impact operating efficiency as well.

Stratification by Size


2. From the Analysis menu, select Stratification.


3. Select INV_AMT_SUM as the Fields to stratify.

4. Select INV_AMT_SUM as the Fields to total on.

5. The lower limit of the first strata will automatically adjust to the lowest dollar amount for all of the values in this field. Set the upper limits of the stratum to: 0, .01, 50, and 9,999,999,999.00 respectively to create the 4 desired stratum.

6. Change the name of the stratification result to Stratification by Size and click OK to complete the stratification.

66

Session 7


Notice that only 45% of the invoices, representing 70% of the invoice dollars, fall into the large dollar transaction category. Over 32% of the invoices fall into the low dollar transaction category yet they represent only 6% of the invoice dollars. Additionally, 3.5% of the invoices have a net invoice value of $0 and 18.5% have net credit values. These results may warrant further investigation.

Extract High Volume Customers

1. Open the Sum Invoices by Customer.IMD file.



3. Change the File Name to Numerous Inv Customers. 4. Click on the Equation Editor icon to open the Equation Editor window.

5. Enter the following equation: No_of_Inv > 12



68

Session 7


Frequent invoicing can have some associated inefficiencies but these costs may be offset by improved cash flow depending on the size of the invoices and the timeliness of payment. In instances like this, you will need to assess whether the invoicing patterns appear reasonable.


There are other aspects of operating effectiveness and efficiency that can be evaluated using data analysis techniques. These include:

1. Improved cash flow and investment opportunities may be gained by receiving customer payments on a timelier basis. This can be assessed by calculating the interest cost based on the days sales are outstanding.

2. The efficiency of the order processing system can be assessed by analyzing the number of days between the date the order is received and the date the order is shipped. Delays in shipping can result in delays in receiving revenue from orders.

3. The efficiency of the invoicing system can be assessed by analyzing the number of days between the date the order is shipped and the date the order is invoiced. Invoicing delays directly impact revenue receipts. In addition, invoicing dates that precede shipping dates may indicate manipulation of the financial statements by improperly recognizing income.

4. Failure to properly complete the order process can be analyzed by looking for orders that were never shipped (lost opportunities) as well as orders that were never invoices (lost revenues).


Session 7: Notes

70

Conclusion


CONCLUSION 1. Complete Evaluation Form

2. Thanks to our host for providing the facilities and opportunity to meet.

3. In the future, if your organization would like to be a host, please drop us a note.

Next action steps Remember:

1. WHY you are doing the work in the first place.

2. Look for continuous improvement.

3. Build new skills into the rest of your work.

Conclusion


0

Using IDEA to Audit Revenue Date: Instructor:

Your Name: Your Company:

Course Location: Please take a moment to fill out this evaluation form.

Poor Excellent 1. Overall rating for this course 1 2 3 4 5 2. Were objectives met 1 2 3 4 5 3. Were the prerequisite requirements appropriate 1 2 3 4 5 4. Instructor’s presentation 1 2 3 4 5 5. Instructor’s understanding of subject 1 2 3 4 5 6. Was the program content timely and relevant 1 2 3 4 5 7. Were the time allocations appropriate 1 2 3 4 5 8. Effectiveness of audiovisual materials 1 2 3 4 5

9. This course was beneficial to me in the following ways:

10. This course could be improved by:

11. What other training would you be interested in?

Acquiring & Importing Data Automating & Implementing Your Analysis 1 (IDEAScript) Analyzing & Interrogating Data Lev. 1 Automating & Implementing Your Analysis 2 (IDEAScript)

Analyzing & Interrogating Data Lev. 2 Fighting Fraud with Data Mining Techniques In-house Training Other Topic: 12. Additional Comments:

Please sign here if we can use your name and comments in our marketing materials.

Signature: _______________

Printed Name: _________