NIST Special Publication 500-233

Computer

Systems

Technology

U.S. DEPARTMENT OF

COMMERCE

Technology Administration

National Institute of Standards and Technology

Nisr

A Manager's Guide for

Monitoring Data Integrity

in Financial Systems

Roger Sies

ll*illlllli'^ll/HN'i!'. * TECH R.I.C.
m he National Institute of Standards and Technology was established in 1988 by Congress to "assist industry

M- in the development of technology . . . needed to improve product quality, to modernize manufacturing processes,

to ensure product reliability . . . and to facilitate rapid commercialization ... of products based on new scientific discoveries." NIST, originally founded as the National Bureau of Standards in 1901, works to strengthen U.S. industry's

competitiveness; advance science and engineering; and improve public health, safety, and the environment. One of the agency's basic functions is to develop, maintain, and retain custody of the national standards of measurement, and

provide the means and methods for comparing standards used in science, engineering, manufacturing, commerce,

industry, and education with the standards adopted or recognized by the Federal Government.

As an agency of the U.S. Commerce Department's Technology Administration, NIST conducts basic and applied research in the physical sciences and engineering, and develops measurement techniques, test methods, standards, and

related services. The Institute does generic and precompetitive work on new and advanced technologies. NIST's

research facilities are located at Gaithersburg, MD 20899, and at Boulder, CO 80303. Major technical operating units and their principal activities are listed below. For more information contact the Public Inquiries Desk, 301-975-3058.

Office of the Director

• Advanced Technology Program

• Quality Programs

• International and Academic Affairs

Technology Services

• Manufacturing Extension Partnership

• Standards Services

- • Technology Commercialization

• Measurement Services

• Technology Evaluation and Assessment

• Information Services

Materials Science and Engineering

Laboratory

• Intelligent Processing of Materials

• Ceramics

• Materials Reliability'

• Polymers

• Metallurgy

• Reactor Radiation

Chemical Science and Technology

Laboratory

• Biotechnology

• Chemical Kinetics and Thermodynamics

• Analytical Chemical Research

• Process Measurements

• Surface and Microanalysis Science

• Thermophysics"

Physics Laboratory

• Electron and Optical Physics

• Atomic Physics

• Molecular Physics

• Radiometric Physics

• Quantum Metrology

• Ionizing Radiation

• Time and Frequency'

• Quantum Physics'

Manufacturing Engineering Laboratory

• Precision Engineering

• Automated Production Technology

• Intelligent Systems

• Manufacturing Systems Integration

• Fabrication Technology

Electronics and Electrical Engineering

Laboratory

• Microelectronics

• Law Enforcement Standards

• Electricity

• Semiconductor Electronics

• Electromagnetic Fields'

• Electromagnetic Technology'

• Optoelectronics'

Building and Fire Research Laboratory

• Structures

• Building Materials

• Building Environment

• Fire Safety

• Fire Science

Computer Systems Laboratory

• Office of Enterprise Integration

• Information Systems Engineering

• Systems and Software Technology

• Computer Security

• Systems and Network Architecture

• Advanced Systems

Computing and Applied Mathematics

Laboratory

• Applied and Computational Mathematics"

• Statistical Engineering^

• Scientific Computing Environments'^

• Computer Services

• Computer Systems and Communications^

• Information Systems

'At Boulder, CO 80303. ^Some elements at Boulder, CO 80303.
NIST Special Publication 500-233

A Manager's Guide

for Monitoring Data

Integrity in Financial

Systems

Roger F. Sies, CIA, CGFM

Computer Systems Laboratory

National Institute of Standards and Technology

Gaithersburg, MD 20899-0001

February 1996

U. S. Department of Commerce

Ronald H. Brown, Secretary

Technology Administration

Mary L. Good, Under Secretary for Technology

National Institute of Standards and Technology

Arati Prabhakar, Director
National Institute of Standards

and Technology

Special Publication 500-233

Natl. Inst. Stand. Technol.

Spec. Publ. 500-233

73 pages (Feb. 1996)

Natl. Inst. Stand. Technol. CODEN: NSPUE2

U.S. Government Printing Office

Washington: 1996

For sale by the Superintendent of Documents, U.S. Government Printing Office,

Washington, DC 20402-9325
Introduction 1 Tflbl© Of Need for Monitoring Financial Systems 2 Monitoring Module Overview 5 OOniGntS

Monitoring Module Tools 6

Implementation 7 Data Warehousing 9

Monitoring Tools Must Change

as Technology Changes 14

Secondary Uses for Monitoring Tools 15

Documenting the Monitoring Module 16

Appendix A. Description of Monitoring Tools 18 Exception Reporting 20

Data Retrieval and Management Analysis 21

Selecting, Sorting, and Summarizing Data 21 Analysis 22

Field Statistics 22

Frequency Distribution Reporting 22

File or Table Stratification 22

Aging Data 23

Gap Detection and Duplicate Detection 23

Calculated Fields 23

Data Manipulation 23

Join Fields Between Files and Tables 23

Compare/Match Two Files or Tables 24

Exporting Data 24

Monitoring Module Guidelines for Functions/Processes .... 25

Appendix B. Preventive Controls 44

Edit and Limit Checks 44

Digital Signatures 45

Appendix C. Integrated Test Facility 46

Appendix D. Monitoring Module Tools - Examples and Samples 49

Appendix E. Contributors to the Report 59

Glossary 61

References 64

iii

Introduction 1

The concept of putting a set of automated monitoring tools into

accounting systems was first described in NIST Special Publication

(SP) 500-226, SelfMonitoring Accounting System^ 1 ] . This report,

written with the assistance of a work group of financial and system

experts (see Appendix E), provides an expanded list of suggested

functionality, a more specific description of the individual tools than

the original publication, and a guide to implement and use those tools.

This guide will enable managers to identify and justify software tools

and techniques to include in new or existing financial systems.

Adopting these guidelines would result in a Monitoring Module

consisting of software that includes an easy-to-use report generator.

Financial and accounting personnel, as well as other system users, could use the Monitoring Module to help ensure input accuracy,

completeness, and timeliness. The Monitoring Module would help ensure data consistency across the organization and increase managers'

ability to test and report the level of success with which their

management goals are being met. If possible, the Monitoring Module

discussed in this report should be implemented as a suite of tools built

into financial systems and should be considered an integral internal

control for financial systems.

It is important to note that most, if not all, of the Monitoring Module

discussed in this report can be used in mixed systems as well. A

mixed system is an information system that supports both financial

and nonfinancial functions. To be effective in improving an

organization's data integrity, the module should be included in all financial and mixed systems. The next section describes the need for

the Monitoring Module within the Federal government; however, it is important to note that this Module will serve the needs of private

industry equally well.

Introduction 1
Need for The public is demanding increased accountability for the use of funds

Monitoring by responsible managers. With the increased emphasis being placed on Financial improving government operations and providing meaningfiil

Systems infomiation to multiple levels of users, program and financial managers

are expected to make and implement accurate and timely financial

decisions. The CFO Act of 1990 (as amended) along with other recent

legislation covering the financial accountability of the Federal

government, has significantly increased Federal agency requirements

for collecting, maintaining, and reporting accurate and comprehensive

financial data on agency programs, operations, and resources. Requirements for system internal controls - both general and specific

to the application - have increased in importance as well. These trends

mirror similar developments in the private sector and at the state and

local levels.

In the Federal sector, the Joint Financial Management Improvement

Program (JFMIP) has developed thQ Frameworkfor Federal Financial

Management Systems [2], with subsidiary sets of requirements for

selected financial system modules (core, travel, payroll, budget, etc.).

These requirements are meant to help program and financial managers

establish and maintain financial management systems to support

managers and Federal government delivery programs (see Figure 1).

Figure 1. Agency Systems

Architecture, which

provides a logical

perspective

identifying the

relationships of

various agency

system types. From

JFMIP 's Framework

for Federal Financial

Management

stems ' jy°rl(st3tion Support Tools

Recognizing the need for good financial management information and

supporting systems, the CFO Council adopted the following vision for

financial management in the spring of 1994:

2 A Manager's Guide for Monitoring Data Integrity in Financial Systems
Enabling government to work better and cost less requires

program and financial managers, working in partnership

using modern management techniques and integrated

financial management systems, to ensure the integrity of

information, make decisions, and measure performance to achieve desirable outcomes and real cost effectiveness.

Both the JFMIP framework and the private-sector-based Committee

of Sponsoring Organizations of the Treadway Commission (COSO)

Model Frameworkfor Internal Controls present monitoring

capabihties as integral to financial management systems frameworks

(see Figure 2).

Objectives Figure 2. Relationship of

Internal Control

Objectives and

Components.

Source: Adapted from Internal Control—Intesrated Framework

In an environment where most financial transactions are automated,

there may be no "paper trail." Furthermore, implementation of highly

automated financial systems may expose organizations to increased

risks of waste, fraud, and abuse if system controls fail to be effective.

It is imperative, therefore, that automated monitoring capabilities as sophisticated as the automated financial operations themselves be

available to financial managers.

Introduction 3
With the use of effective techniques and user-friendly tools, an automated monitoring module for automated financial management

systems can be designed, implemented, and operated within the

constraints of budget austerity and downsizing. The techniques

described in this guide focus on establishing an "expected" experience

in financial operations and on analyzing those circumstances that

deviate from these expectations. These techniques also permit the

financial manager to readily identify exceptional transactions, spot

trends, and identify problem areas early enough for effective

preventive action.

With the automated monitoring tools described in this guide, the

financial manager can improve data integrity by evaluating the

effectiveness of existing controls and by continually testing processing

accuracy and validity. It is hoped that financial managers will pursue

the implementation of such a monitoring module as part of an overall

effort to improve financial management in the public and private

sectors (see Figure 3).

Figure 3. The

unshaded blocks

represent the use of

generalized audit

software with a

financial system. The

shaded areas show the

Monitoring Module

added.

Financial Database

Agency Reports

Monitoring Module

Monitoring

Module Reporting

Corrective Action

if Needed

Generalized

Audit Software

Selected Data

Analysis

Audit Reports

4 A Manager's Guide for Monitoring Data Integrity in Financial Systems
Rather than waiting until auditors tell them where problems exist,

managers should have the ability to find and correct problems as they

occur. New accounting systems can provide preventive controls, such

as data verification and edit checking as data are entered (see Appendix

B). These tests greatly improve the quality of information and should

be considered the first level of defense against errors in any good

financial system. Documentation should be available that describes in detail the tests performed on data being entered into the system.

These tests, along with all other internal controls, should be evaluated

periodically. One method of evaluating these tests is an Integrated

Test Facility (see Appendix C).

The most desirable point to detect and correct errors is before the data

are entered into the system. As this is not always possible, the

Monitoring Module tools are a good next line of defense. After

clearing the preventive controls, data involved in multiple transactions,

such as incurring and paying a liability, must be compared for

consistency. In addition, there are transactions falling within an

acceptable range that still must be reviewed by management after they

are in the system; for example, valid transactions over a designated

dollar threshold.

Such capabilities should be included in the Monitoring Module as a set of reports generated by queries to the data in the system. These

reports should give the manager some of the capability that auditors

have to analyze data through the use of Generalized Audit Software.

(A detailed discussion of the use of Generalized Audit Software to perform data integrity testing is included in the Information Systems

Audit and Control Foundation's Computerized Information Systems

(CIS) Audit Manual [3]) These kinds of capabilities should be

included in accounting systems, allowing management to perform

analysis that will be described in detail below. Such analysis would

enable managers to find discrepancies in accounts and transactions soon after they occur.

Monitoring

Module

Overview

Introduction 5
The Monitoring Module could include functionality that allows for an

infonnation store of applicable rules, regulations, accounting policies,

and laws that can be compared to transactions. This infonnation store would be used for edit and limit checks and for reports to management

on discrepancies found in the records. The store could also contain

information regarding management's goals. The Monitoring Module

could then test and report to management the level of success with

which these goals are being met. Expert systems, as well as other

software solutions, could provide this functionality.

The set of suggested Monitoring Module automated tools is listed

below and discussed in Appendix A. The software tools from the list will change over time as computer technology evolves.

• Exception Reporting

• Data Retrieval and Management Analysis

Selecting, Sorting, and Summarizing Data

Analysis

Field Statistics

Frequency Distribution Reporting

File or Table Stratification

Aging Data

Gap Detection and Duplicate Detection

Calculated Fields

Data Manipulation

Join Fields Between Files and Tables

Compare/Match Two Files or Tables

Exporting Data

A Manager's Guide for Monitoring Data Integrity in Financial Systems
Implementation 2

All Monitoring Module tools should be as automated as possible.

Exception reporting, including choosing proper criteria and report

design, should be set up by the manager or a vendor as part of the

implementation process. Management would then need to review the

criteria periodically and make required changes. Manual intervention

would be needed to select the tool for use and, possibly, to enter

infonnation needed to perform the function. These tools should be as easy to use as possible. For example, there are two review methods

available to the user:

• The system prompts the user for the statistical precision, prompts

for the reliability desired, and then calculates the sample size and

selects the records after user approval

• The system prompts the user for the criteria to use in direct record

selection

This section is intended as guidance for management decisions made

within the framework of individual accounting systems.

Implementation decisions should be made by management with the

help of its internal audit organization. The cost of accounting errors within the organization and the cost of implementing these monitoring

tools should be considered in making these decisions. It should be

noted that the costs of errors are long term and are often difficult to quantify. Incorrect accounting inforaiation can lead to loss of assets and faulty management decisions. The consequences of faulty

decisions may not immediately be apparent, and the resulting problems

and their attendant costs may not be traced to the incorrect accounting

information that was used to make the decisions. System vendors

should be able to provide cost estimates for implementation, and

internal audit organizations should be able to estimate the costs of

incorrect financial information.

Implementation 7
General systems categories include off-the-shelf systems, modified offthe-shelf systems, and custom-built systems. Within these categories

are systems built with relational database technology and systems built

with more traditional technology such as COBOL, C, or some other

programming language. Systems are currently being implemented in all of these categories. Implementation of the Monitoring Module tools

should be straightforward and inexpensive. For instance, it should be

easy to add many of these tools to the systems currently being

implemented with relational database technology. Older COBOL

systems may be more difficult; the best approach may be to take

generalized audit software from a commercial vendor and add it to the

system in the same way an auditor would.

It is important that the tools be integrated into the system as a module.

A facile user interface should present the Module to the user and the user should be able to choose the desired tool from a list. There should

be on-line descriptions and examples of the tools. For new systems

built using relational database technology, implementing most of these

tools is a matter of creating new reports and fill-in screens that allow

the user to enter information used to automatically create queries.

Additional programming will be needed for some capabilities such as

statistical sampling. This programming should be minor and should

not add greatly to the cost of the system.

The best approach for existing systems that are not implemented with

relational database technology may be an off-the-shelf generalized audit

software package. A system designer may be able to add menus to make this software appear more integrated with the financial system.

Existing systems that use relational database technology allow easier

development of a standardized interface to the Monitoring Module.

This idea is emphasized in Federal Information Processing Standards

Publication 193, SQL Environments [4], which recommends that

legacy and special purpose databases could present a simple relational

interface to tools such as the Monitoring Module. Such interfaces

could be procured separately and added to legacy systems.

Management must understand and be involved in the implementation

of these tools.

A Manager's Guide for Monitoring Data Integrity in Financial Systems
The key to effective monitoring tools is to include their implementation

as a stated goal in the financial system implementation plan. If this

goal is not included, there may be resistance to completing what others

may see as a luxury. If you are adding software monitoring tools to an

existing system, define a project and set of tasks that will also lead to successful implementation. Make your implementation goals concrete,

discrete, and achievable within a short time frame. This approach will

increase the probability that your organization can start to evaluate the

validity of system data before staff or other resources are re-allocated

to another project.

Your organization may decide to load the data on a computer other than Data

the one on which the financial system resides. This approach is often Warehousing

chosen when performance (throughput or response time) or security concerns mandate choosing another repository for the data. Data

warehousing, whereby data are placed on a computer distinct from the

computer being used to process the data, is coming into increasing use. These data are then "mined" to select and report information deemed

significant. Although creating a separate system for data processing

presents its own set of challenges, e.g., coordinating the timing of

copying data to the remote system, a data warehouse often provides

advantages that cannot be achieved through use of the production

financial system computer platfonn.

Regardless of the implementation approach elected, the Monitoring

Module should be considered integral to the organization's overall

financial system architecture. The Monitoring Module should fit between the high level executive information system and day-to-day

control activities, as shown in Figure 1.

Implementation 9
Figure 1. The

JFMIP Agency

Systems Architecture

with the Monitoring

Module, Control

Activities, and

Executive Information

System (EIS).

The financial systems architecture illustrated in Figure 1 represents a blending of the JFMIP framework model for financial systems with the

COSO model for internal control as a suggested approach for

implementing the Monitoring Module.

Existing computer standards should be used when possible. Two such

standards that can be useful in implementing the Monitoring Module

are the Federal Infomiation Processing Standards Publication \93,SQL

Environments, and the Database Language (SQL) [5] standards.

Every item in the list on page 6 should have a menu selection

associated with it in the overall Monitoring Module menu. Associated

with each of these menu selections should be the software

programming for perforaiing the function. The following set of figures

(2-6) demonstrate how a user interface for the Monitoring Module

might look. This set is neither intended nor detailed enough to be an

actual user interface.

10 A Manager's Guide for Monitoring Data Integrity in Financial Systems
File Edit

Enter Transactions

Generate Reports

Other Clioices

Figure 2 shows the

Monitoring Module

being selected from

the available

accounting system

functions.

File

Exception Reports

Field Statistics

Frequency Distribution

File/Table Stratification

Age Data

Ga^ & Duplicate Detection

Calculated Fields

Join Fields/Tables

Compare/Match

Export Data

Figure 3 shows the

Selecting, Sorting, and

Summary Tool being

selected.

Implementation
Figure 4 shows the

Payment Management

cycle being selected to use in conjunction

with the Selecting,

Sorting and Summary

Tool.

File

Funds Management

General Ledger

Cost Management

Seized Forfeited Asset System

Travel

P.ayroll

Direct Loan System

Guaranteed Loan System

inventory System

Figure 5 shows the

desired payment

management file fields

being selected for

inclusion in the

analysis.

File

Highlight Desired Fields

Cancel

12 A Manager's Guide for Monitoring Data Integrity in Financial Systems
File

Choose Field Name

Vendor Name

Transaction Date

Vendor Name = Smith Office Supply

Transaction Date >= 2/1/95 AND <= 2/8/95

Amount >= 50000

# Sort Records

O Create Summary Fields

CZJ

Cancel

Figure 6

demonstrates how the

query would be

developed. If "Sort

Records" or "Create

Summary Fields" is selected, additional screens are provided

to allow the user to provide the additional

information needed.

Implementation 13
Monitoring Tools Must Change

as Technology Changes 3

Accounting systems and the underlying technology will change over

time as advances occur, enabling corresponding changes in the

Monitoring Module. Accounting systems are likely to become more

distributed, and technology such as Electronic Commerce and

Electronic Data Interchange, more prevalent. Data will be entered

from both across and outside the organization. Technology such as

digital signatures (see Appendix B) will be needed in financial systems

to allow authentication of transactions that come into the accounting

system from an outside source. Digital signatures can also be used to determine that proper segregation of duties is in place. Tools must be

added to the Monitoring Module to verify that new technology is perfomiing as expected in preventing unauthorized entry or incorrect

data into the financial system.

The set of tools recommended for inclusion in financial systems will

change as the financial system technology changes. New or soon to be

available tools must be considered for inclusion in a set of monitoring

tools. An example of such a tool is profiling, the detection of

deviations in behavior. An example of a profiling application is monitoring and verifying phone calls made on a phone charge card.

Patterns of charged calls are fonned and stored in a user profile. This

profile is then used to match against subsequent calls made by the

user. If any unusual patterns are detected, such as calling long

distance more frequently than usual or calling overseas, the user is notified to verify authorization. Profiling is also being used in the

health care industry. Profiles compiled for patients contain such

infomiation as frequency of visits, types of treatments, insurance

information, and payment information. These profiles could be used

to identify such unusual patterns as more than the usual treatments,

treatments for conditions that have already been treated, and

unusually high billings.

14 A Manager's Guide for Monitoring Data Integrity in Financial Systems
Secondary Uses for

Monitoring Tools 4

Both government and private sector managers are often asked to put

financial infomiation together quickly for concise presentations.

Congress frequently requests infonnation from Federal agencies. New

results-oriented initiatives such as the Government Perfonnance and

Results Act (GPRA) will increase these demands, and the information

required will be more complex and difficult to obtain. GPRA shifts

the focus from program inputs to results and to how well the

programs are meeting their intended objectives.

Organizations today need infomiation reported quickly. They must

draw fi-om more complex and elusive data stored at more than one

location in the organization. In addition, they must develop more

flexible reporting capabilides. The tools discussed in this report will

provide the user with powerful, flexible ad hoc reporting capabilities

useful in responding to these infomiation requirements.

Secondary Uses for Monitoring Tools 15
Documenting the

Monitoring Module 5

Documentation for the entire accounting system should be consistent

with computer standards and good software engineering practices. The

Monitoring Module should be included in such documentation. The

Institute of Electrical and Electronics Engineers, Inc., list the following

minimum quality assurance requirements for documentation in their

Software Engineering Standards [6]:

• Software Requirements Specification,

• Software Design Description,

Software Verification and Validation Plan,

• Software Verification and Validation Report, and

User Documentation.

The National Bureau of Standards (now National Institute of

Standards and Technology) Special Publication, Gw/i/e to Auditingfor

Controls and Security: A System Development Life Cycle Approach[7],

is another good source for documentation guidelines.

Good documentation is essential for safeguarding system investments.

Good documentation allows for easier system maintenance and

facilitates system modification. User documentation helps ensure proper system use and lower personnel training costs.

In addition to the uses listed above, a complete set of documentation

allows auditors to make an initial assessment of the internal controls

provided by the Monitoring Module. This may serve to lower both

costs and time spent on auditing the system.

16 A Manager's Guide for Monitoring Data Integrity in Financial Systems
Monitoring Module user documentation should be one cohesive

section of the system documentation for easy access. This

documentation should also be built into the system in the form of online help. It should include complete infonnation about the tools,

including criteria for selecting data and exception reporting. The

documentation should have enough information regarding each tool to inform the user of what information must be entered to produce the

desired effect. Tool descriptions should include the purpose of each

tool and should outline the tests and analyses management might wish

to perform. Finally, each report created by the Module should

contain the parameters used in creating the report.

Documenting the Monitoring Module 17
Appendix A. Description of

Monitoring Tools 6

This section suggests tools that might be included in the Monitoring

Module. These tools can be generalized for use in mixed systems as well as pure financial systems. Although there are a few examples for

mixed systems, most of the examples in this report are found in pure

financial systems. Management must make the final decision about

which tools to include in its systems. This decision should be made

with advice from the internal auditors who perform system audits, and

the manager should take implementation costs into account. However,

any consideration of implementation costs should include the potential

for long-term, substantial cost savings to be derived from better control over assets and more accurate provision of data.

None of the capabilities discussed in the section are new, nor is the

idea of giving this functionality to accountants and managers. What is new is the approach: Putting organized sets of software routines into

accounting systems to perform these functions. The Systems

Auditability and Control Report from the Institute of Internal

Auditors Research Foundation points out that preventive, detective,

and corrective controls tend to operate in combination, either with or without dependencies. Cost-effectiveness is a key element in the use of preventive, detective, and corrective controls. Some errors may

result in grave consequences and must be prevented if at all possible.

Other errors may be costly to prevent, but easy to detect and correct. Other errors or problems may be impossible to predict or prevent, but

must be acknowledged so that appropriate recovery or corrective

controls can be employed.

Some control techniques may be classified as preventive, detective, or

corrective depending on where or how they are used in a process or system. Some considerations in the classification and combinations of

these control types follow.

Preventive Controls keep undesirable events from occurring. In an

infonnation systems (IS) environment, preventive controls are accomplished by implementing automated procedures to prohibit

18 A Manager's Guide for Monitoring Data Integrity in Financial Systems
unauthorized system access and to force appropriate and consistent

action by users. Because preventive controls are unlikely to be 100

percent effective, they should operate in combination with detective

controls, which identify errors or events that were not prevented. It may not be cost-effective to prevent all errors at the source, and

complementary detective controls may be used as a measure to prevent the error from being carried through the system.

Detective controls identify undesirable events after they have

occurred. Detective controls have the following two components:

The system should identify and record user activity, key

transactions, and unusual conditions in exception and summary

reports to ensure a thorough and accurate audit trail.

A management hierarchy should be in place to review the

exception and summary reports. Identify any abnormalities, and

take appropriate action.

Detective controls should identify expected error types, as well as those that are not expected to occur.

Corrective controls cause or encourage a desirable event or corrective

action to occur after an undesirable event has been detected. This type

of control takes effect after the undesirable event has occurred and

attempts to reverse the error or correct the mistake. [8]

The tools suggested for the Monitoring Module fit into the detective

control category. Other technology, such as edit and limit checks and

digital signatures, fit into the preventive control category. Preventive

controls are discussed in Appendix B.

Appendix A. Description of Monitoring Tools 19
Catching errors before the data are entered into the system is the most

desirable method for discovering and correcting problems, but this is not always possible. The use of exception reporting can be the next

line of defense against erroneous data.

Criteria used to identify exceptions are designed to address specific areas with predictable problems. These criteria are more or less stable

but should be reviewed to reflect trends uncovered by other monitoring

tools. Criteria will often need to be tailored to specific parts of the

organization. The user interface should allow authorized personnel to change these criteria with relative ease.

There may be more than one way to handle the data that do not pass

the pre-defined criteria: suspend the data (transaction) from further

processing and post the data to a suspense file; forward the data to another system user for resolution; ask the person entering the data to modify the data to make it acceptable; or print the data onto an

exception report for distribution within the organization. One or all of

these methods can be used in a financial management system.

Whatever the method used, it is important to identify the exception,

and to report that exception to appropriate authorities, and to ensure

that the exception is appropriately and efficiently resolved. Some

systems will accept a transaction (data) and send a notification to another system user, such as a manager or director, notifying that user

that an exception has occurred that requires follow-up. Others will

send electronic mail messages to authorized users notifying them of the

exception and requesting exception resolution. Others still will send

the data or transaction to authorized users and request that user's

approval on the data (transaction) to allow for further processing to take place.

The essential components of a well-designed monitoring capability

include the ability to identify data that do not meet pre-defined

criteria, report the situation of possible non-compliance, and have

another individual with authority approve or reject the data for further

system processing.

A Manager's Guide for Monitoring Data Integrity in Financial Systems
Some of the functions perfonned by data retrieval and management Data Retrieval

analysis are as follows: and Management

Analysis

Selecting, sorting, and summarizing data

Field statistics and frequency distribution

File or table stratification, and aging data

Gap and duplicate record detection

Calculated fields

Joining and comparing fields between files or tables

Exporting data

This tool should be used to generate ad hoc reports to supplement Selecting, Sorting,

predesigned exception reporting, allowing management to select Summarizing

groups of records from files or tables based on various sets of criteria. Data

All records from a file or table can be selected, or a subset of the

records can be selected based on specific criteria or on a statistical

sample. For example, all accounts payable transactions for a

particular vendor could be selected by performing a query of all the

records whose vendor field is equal to the name of that particular

vendor. This selection could be made more selective, and perhaps

more useful, by asking for only those vendor records for a specific

time (e.g., January 1 through January 31).

Selected records can appear in several different sequences. They can be in the order the computer system normally stores data. They can be reordered or sorted alphabetically or numerically within one or more fields in ascending or descending order. They could also be

presented randomly. A sort on one field might be the accounts

payable transaction file by vendor name. A two field sort, by vendor

and then by date, might be more useful. It might be even more useful

to have the file sorted by date first and then by vendor name within

each date. All records could be selected for a particular vendor, sorted

by date, and then sorted by dollar value within each date.

Appendix A. Description of Monitoring Tools 21
Once records are selected and sorted, they can be summarized. For

example, the accounts payable transactions can be sorted by vendor

and totaled or summarized for each vendor and for the entire file or

table. The records could also be selected for one vendor, sorted by

date, and totaled by groups of dates and by transactions for that

vendor.

Some of the reports created with this tool may be similar to exception

reporting described above. The distinction for the purpose of this

report is that exception reports are predesigned reports whereas

reports created by selecting, sorting, and summarizing data are ad hoc

reports. Exception reports can and should be changed periodically, but

their purpose is to identify problems that can be expected to occur

frequently or routinely. Ad hoc reports are designed to identify

candidates for new exception reports and specific problems that occur

infrequently or sporadically

Field Statistics. Enables the user to obtain statistics on numeric

fields within a file or table. This capability differs from file or table

summarization in that only the summary information is reported.

Various infonnation can be developed including sums; average,

minimum, and maximum values; and standard deviation infonnation.

Frequency Distribution Reporting. Allows management to obtain a

quantitative overview of how data categories arrange themselves within

different populations. For example, frequency distribution reporting can be used to verify the valid codes for a field and to identify erroneous data. Totals and other important infonnation can be

provided, including mean and standard deviation, in tabular or graphical

format.

File or Table Stratification. Provides managers with a clear

perspective on the contents of a file or table. This tool can help a manager determine what other methods should be used and how much

time should be spent in testing the file or table. Monetary bands are created and records are arranged into the bands to which they belong.

The user should be able to specify a number of ranges and obtain totals

and percentages for records within those ranges. For example, the

manager may want to determine how the file or table divides into

segments based on the dollar amount of the transactions.

A Manager's Guide for Monitoring Data Integrity in Financial Systems
Stratification differs from frequency distribution in that in

stratification the user defines the data categories and in frequency

distribution the natural arrangement of data categories is reported.

Aging Data. Accounts receivable and payable can be aged to determine the timeliness of processing and, in the case of receivables,

how likely the receipt of payment is. This capability is usually

provided in financial systems. However, it is often useful to age other

files as well. For example, it may be useful to age unposted collections

data.

Gap Detection and Duplicate Detection. Gaps and duplication

should be eliminated with automatically generated numbers in new

systems. If this is not possible or is too expensive to implement, a detection capability can test data files for missing or duplicate records.

Duplicate and missing records can be identified in sequenced records

such as checks or invoice numbers. Sorting can be used to detect

record duplication if sequential numbers are not used.

Calculated Fields. Provides the ability to perform any calculation on

any field or fields. For example, amounts claimed on travel vouchers can be recalculated and compared to established limits. Another

example is to perform extensions on vouchers and invoices.

Join Fields Between Files and Tables. This capability is useful Data Manipulation

when the desired data reside in multiple files or tables. If the data

reside in two or more files or tables whose records have the same key

fields, the records can be joined to allow the user to see more complete

information. For example, a report can be generated using an inventory

file and a sales file to show sales activity by part or stock number.

Appendix A. Description of Monitoring Tools 23
Compare/Match Two Files or Tables. Check for conflicts or matches between two different files or tables that contain related or equivalent information.

The following are examples of files or table matching:

• Changes in accounts receivable balances between two dates

compared with details of sales and cash receipts on transactions

files

• Payroll details compared with personnel records

• Current inventory files compared with prior-period inventory files to identify obsolete or slow-moving items.

Spreadsheets, graphical packages, and databases expedite data analysis

These software packages can help management discover errors in data

and analyze data from different perspectives. Creating ad hoc reports

with some systems can be challenging for people without experience.

It may be easier to bring the data into a spreadsheet or a database the user is familiar with and then perform the analysis.

The following tables provide monitoring module guidelines. They are organized by function and processes in accordance with the JFMIP

Core Financial System Requirements [9]. The samples provide

suggestions for using monitoring techniques and tools for specific data

integrity objectives. The samples are not intended to represent a complete set of monitoring objectives, techniques, or tools.

A Manager's Guide for Monitoring Data Integrity in Financial Systems
Monitoring Module Guidelines for Functions/Processes

Payment Management - Payee Information Maintenance

Data Integrity Illustrative Monitoring Suggested Monitoring

Objectives Techniques Tools

Information on all payees Reconcile payee information on invoices Join Fields between Files and

are accurately reflected with information in the database. 1 clUIco dl lU OclUULIIiy, OUIllliy,

in the payment and Summarizing Data

information database.

Identify changes to payment Exception Reporting

management files for supervisory review,

e.g., new suppliers, changes in payment

instructions, changes in pricing.

Payment Management - Payment Warehousing

Data Integrity Illustrative Monitoring Suggested Monitoring

Objectives Techniques Tools

Payments warehoused Identify payments that are not Join Fields between Files and

and scheduled warehoused and scheduled to Tables and Selecting, Sorting,

payments are accordance with contract terms, and Summarizing Data

maintained in loan/grant agreement, etc. accordance with contract terms, loan/grant Analyze automated records on contracts, Join Fields between Files and

agreement, applicable invoices, and receiving reports to Tables and Selecting, Sorting,

regulations, etc. determine compliance with contract and Summarizing Data

terms and applicable regulations.

Appendix A. Description of Monitoring Tools 25
Payment Management - Payment Warehousing Continued

Data Integrity

Objectives

Illustrative Monitoring

Techniques

Suggested Monitoring

Tools

Payments are both

timely and accurate.

Obtain summary information on number Selecting, Sorting, and

and dollar value of payables over 60 days Summarizing Data

old, 90 days old, etc.

Obtain summary information on Prompt

Payment Act Compliance (e.g., late payments, missed discounts, etc.).

Identify any payable with no scheduled

payment date.

Identify any payment with a debit

balance.

Selecting, Sorting, and

Summarizing Data

Exception Reporting

Exception Reporting

Recalculate payments of interest and

penalties on late payments to ensure

proper computations.

Obtain summary data on goods received

not invoiced, goods invoiced but not

received, or discrepancies between

purchase orders, receiving documents,

and supplier invoices.

Selecting, Sorting, and

Summarizing Data and

Calculated Fields

Exception Reporting

Identify payables established after year- Selecting, Sorting, and

end where receipt occurred in the Summarizing Data

previous month (to support establishing

year-end-accrual).

26 A Manager's Guide for Monitoring Data Integrity in Financial Systems
Payment Management - Payment Warehousing Continued

Data Integrity

Objectives

Illustrative Monitoring

Techniques

Suggested Monitoring

Tools

Goods and services paid

for represent those

actually ordered and

received.

Identify instances of goods received not

invoiced, goods invoiced but not received, or discrepancies between

purchase orders, receiving documents

and supplier invoices.

Join Fields between Files and

Tables and Selecting, Sorting,

and Summarizing Data

Goods and services

received are paid for only once and at the

agreed upon price.

Identify payments made to one vendor in the same amount (potential duplicate

payment).

Identify gaps in check numbers or other

transaction or document numbers.

Selecting, Sorting, and

Summarizing Data and Duplicate

Detection

Gap Detection

Recorded accrued

liability based upon

receipt and acceptance

of goods and services

agree with authorized

payment.

Funds control and

budget execution

balances reflect changes in the status of undelivered orders,

expended status of undelivered orders,

expended

appropriations, and

changes in amounts.

Identify duplicate transactions or document numbers.

Reconcile authorized payment (based on the invoice) to the amount accrued

(based upon receipt and acceptance).

Determine extent of discrepancies in the

funds control/budget execution

balances and the status of undelivered

orders, expended appropriations, and

amounts.

Duplicate Detection

Join Fields between Files and

Tables and Selecting, Sorting,

and Summarizing Data

Selecting, Sorting, and

Summarizing Data

Appendix A. Description of Monitoring Tools 27
Payment Management - Payment Execution

Data Integrity

Objectives

Illustrative Monitoring

Techniques

Suggested

Tools

Monitoring

Payments authorized in accordance witli applicable laws,

regulations and

managements policy.

Obligations and

expenditures do not exceed amount

authorized and available.

Determine extent of payments made

without, or coincident with,

establishment of an obligation.

Determine extent of over obligations and

over expenditures.

Join Fields between Files and

Tables and Selecting, Sorting,

and Summarizing Data

Join Fields between Files and

Tables and Selecting, Sorting,

and Summarizing Data

Payments made by

another system (e.g.,

payroll) are scheduled

for the core financial system.

Payments are made to the appropriate parties.

Payments are made

appropriately.

Summarize payments made by another

system by due date, vendor name, etc.

Reconcile payments made by another

system with records in the core financial system.

Compare identification numbers on checks and transfers to invoices.

Identify aberrancies by employees

related to authorizations, obligations,

and payments.

Identify ail payments made by a particular

employee.

Selecting, Sorting, and

Summarizing Data

Compare/Match Files or Tables

Join Fields between Files and

Tables and Selecting, Sorting,

and Summarizing Data

Selecting, Sorting, and

Summarizing Data

Selecting, Sorting, and

Summarizing Data

Identify gaps in check numbers. Exception Reporting

28 A Manager's Guide for Monitoring Data Integrity in Financial Systems
Payment Management - Payment Confirmation and Follow-Up

Data Integrity

Objectives

Illustrative Monitoring

Techniques

Suggested Monitoring

Tools

Selecting, Sorting, and

Summarizing Data

Payments were received Compare actual payments to remittance

and recognized by advice,

parties paid.

Identify payments that have been Exception Reporting

outstanding for an extended period.

Receipts Management - Receivables Estabiisliment

Data Integrity

Objectives

Illustrative Monitoring

Techniques

Suggested Monitoring

Tools

Changes to customer

credit limits and unusual

discounts are reviewed

by management.

Identify for supervisory review invoices Selecting, Sorting, and

that contain unusual prices or discounts. Summarizing Data

Identify for supervisory review changes Compare/Match Files or Tables

to customer credit limits.

Receivables are accurately recorded by

amount, type, and

period.

Match shipment dates with invoice dates

to identify revenue and receivables

booked in the wrong accounting period.

Identify entities for which new

receivables are established, when they

are already delinquent on other

payments.

Sort and summarize newly established

receivables by type (shipments, service,

overpayments, sustained audit findings,

Federal, public, etc).

Obtain statistical data such as total number of invoices processed, average

invoice amount, number of invoices

under $X dollars, sales by type/product,

etc. Develop above statistics by month

to identify trends and peaks and valleys.

Join Fields between Files and

Tables and Selecting, Sorting

and Summarizing Data

Selecting, Sorting, and

Summarizing Data and

Compare/Match Files or Tables

Selecting, Sorting, and

Summarizing Data

Selecting, Sorting, and

Summarizing Data

Appendix A. Description of Monitoring Tools 29
Receipts Management - Receivables Management

Data Integrity

Objectives

Illustrative Monitoring

Techniques

Suggested Monitoring

Tools

Accounts with credit

balances or large

adjustments are

identified and

investigated.

Receivables are

accurately aged and

summarized for follow- up.

Identify receivables with credit balances. Exception Reporting

Identify large adjustments to receivables

balances for supervisory review.

Obtain receivables aging data.

Match dates of goods returned and

crediting of receivables to ensure

posting to the correct accounting period.

Summarize large or old suspense items.

Compare/Match Files or Tables

and Selecting, Sorting, and

Summarizing Data

Aging Data

Join Fields between Files and

Tables and Selecting, Sorting,

and Summarizing Data

Exception Reporting and Aging

Data

Identify receivables with no activity over 90 days, 120 days, 180 days, etc.

Identify instances where collection

events do not comply with Debt

Collection Act (invoicing, interest accruals, penalties and administrative

charges, credit reporting, IRS offsets,

etc).

Obtain statistical data such as total returns, total adjustments, average

receivable amounts, average interest

rates, breakdown of receivables by dollar range, etc.

Selecting, Sorting, and

Summarizing Data

Join Fields between Files and

Tables and Selecting, Sorting,

and Summarizing Data

Selecting, Sorting, and

Summarizing Data

30 A Manager's Guide for Monitoring Data Integrity in Financial Systems
Receipts Management - Collections

Data Integrity

Objectives

Illustrative Monitoring

Techniques

Suggested Monitoring

Tools

All collections are posted accurately,

completely, and in a

timely manner.

Obtain summary aging data on unposted Exception Reporting

collections.

Obtain receivable and payment history

for a particular entity.

Obtain statistical data such as monthly

collections, average collection amount,

payment method, lapsed time between

collections and postings, etc.

Provide trend data such as receivable turnover ratio and number of days of receivables.

Join Fields between Files and

Tables and Selecting, Sorting,

and Summarizing Data

Selecting, Sorting, and

Summarizing Data

Selecting, Sorting, and

Summarizing Data and

Calculated Fields

Funds Management - Funds Allocation

Data Integrity

Objectives

Illustrative Monitoring

Techniques

Suggested Monitoring

Tools

Funds are allocated in accordance with

regulatory restrictions.

Identify any instances where

apportionments exceed appropriations,

allotments exceed apportionments,

commitments exceed allotments, etc.

Exception Reporting

Appendix A. Description of Monitoring Tools 31
Funds Management - Funds Control

Data Integrity

Objectives

Illustrative Monitoring

Techniques

Suggested Monitoring

Tools

All fund activity Is properly aged.

Fund activity for large

amounts Is Identified and followed up.

Payments are made only

for funds obligated.

Provide aging of open commitments and Aging Data

obligations.

Provide aging of transactions In suspense.

Identify commitments and obligations

with no activity for over 90 days, 1 20

days, 180 days, etc.

Identify commitments and obligations

with large balances.

Summarize number of payments made

without, or coincident with,

establishment of an obligation.

Obtain statistical data such as commitments and obligations by type,

monthly amount, average amounts, total decommitments/deobligations, etc. Provide data by month to identify trends

and peaks and valleys.

Exception Reporting

Selecting, Sorting, and

Summarizing Data

Selecting, Sorting, and

Summarizing Data

Join Fields between Files and

Tables and Selecting, Sorting,

and Summarizing Data

Selecting, Sorting, and

Summarizing Data

32 A Manager's Guide for Monitoring Data Integrity in Financial Systems
General Ledger Management - Posting

Data Integrity

Objectives

Illustrative Monitoring

Techniques

Suggested Monitoring

Tools

High dollar, unusual

items and suspense

items are identified and

summarized for follow- up.

Identify hiighi dollar/unusual postings.

Summarize suspense items.

All account activity is Obtain detailed transaction data to accurately posted to the support audit and review,

general ledger.

Selecting, Sorting, and

Summarizing Data

Exception Reporting

Selecting, Sorting, and

Summarizing Data and Exporting

Data

General Ledger Management - Accruals, Closing, and Consolidation

Data Integrity

Objectives

Illustrative Monitoring

Techniques

Suggested Monitoring

Tools

All general ledger

accruals, eliminations,

and summarizations are

identified and posted.

List eliminating entries.

Summarize year-end accruals and

compare to prior year accruals to determine reasonableness.

Selecting, Sorting, and

Summarizing Data

Selecting, Sorting, and

Summarizing Data and

Compare/Match Files or Tables

Appendix A. Description of Monitoring Tools 33
General Ledger Management - Analysis and Reconciliation

Data Integrity

Objectives

Illustrative Monitoring

Techniques

Suggested Monitoring

Tools

Subsidiary ledgers

should support general

ledger balances.

All unusual activity such

as unnatural account

balances or trial balances

that are out-of-balance are identified and

investigated.

Identify differences between general

ledger and subsidiary accounts.

Identify accounts with unnatural balances

(assets with credit balances, liabilities with debt balances, etc.).

Identify unbalanced trial balances.

Obtain trend data between years for individual accounts and groups of accounts.

Provide ratio data such as inventory turnover ratio, working capital ratio, receivable turnover ratio, number of days

of receivables, fixed asset turnover,

profit margin on sales, etc.

Selecting, Sorting, and

Summarizing Data

Exception Reporting

Exception Reporting

Selecting, Sorting, and

Summarizing Data and

Compare/Match Files

Selecting, Sorting, and

Summarizing Data and

Calculated Fields

Cost Management - Cost Recognition and Accumulation

Data Integrity

Objectives

Illustrative Monitoring

Techniques

Suggested Monitoring

Tools

Large and suspensed

cost transactions are

identified and

investigated.

All data from feeder cost systems are accurately

reflected in the cost systems accumulation.

Identify large or unusual costs.

Obtain aging data on cost transactions in suspense.

Identify differences between cost system data and data obtained from

feeder systems (e.g., labor distribution,

purchases, depreciation expenses,

overhead expense items).

Obtain summary data on cost by type.

Selecting, Sorting, and

Summarizing Data

Exception Reporting

Selecting, Sorting, and

Summarizing Data and

Compare/Match Files

Selecting, Sorting, and

Summarizing Data

34 A Manager's Guide for Monitoring Data Integrity in Financial Systems
Cost Management - Cost Distribution

Data Integrity

Objectives

Illustrative Monitoring

Techniques

Suggested Monitoring

Tools

Costs are distributed

accurately and

monitored tliough

periodic analysis.

Identify changes made to standard costs. Compare/Match Files or Tables

and Selecting, Sorting, and

Summarizing Data

Join Fields between Files or Tables and Selecting, Sorting,

and Summarizing Data and

Compare/Match Files

Join Fields between Files or Tables and Selecting, Sorting,

and Summarizing Data and

Compare/Match Files

Selecting, Sorting, and

Summarizing Data and

Calculated Fields or Exporting

Data

Compare standard costs, variances, and

actual costs between accounting

periods.

Compare production volumes between

accounting periods.

Recompute accumulation of component

costs to ensure accurate accumulation of standard cost data.

Recompute mathematical accuracy of variable analysis and overhead

allocations.

Selecting, Sorting, and

Summarizing Data and

Calculated Fields or Exporting

Data

Compare inventory quantities with

production volume to facilitate analysis

and allocation of variances.

Join Fields between Files or Tables and Selecting, Sorting,

and Summarizing Data and

Calculated Fields

Appendix A. Description of Monitoring Tools 35
Payroll - Employee Information Management

Data integrity Illustrative Monitoring Suggested Monitoring

Objectives Techiniques Tools

Employee information is Identify changes to employee master file Exception Reporting

accurately reflected in for supervisory review, e.g., additions,

the employee deletions, pay rate changes, etc. information database.

Payroll - Payroll Processing

Data Integrity

Objectives

Payroll processing is timely and accurate.

Illustrative Monitoring

Techniques

Suggested Monitoring

Select payments for supervisory review

based on dollar amount, specific

attributes, or random sample.

Identify multiple payments to one employee (potential duplicates).

Calculate average pay per employee for weekly/monthly trend analysis.

Identify payments to new or terminated

employees for supervisory review.

Identify individual payments to salaried

employees with variance from prior pay

periods.

Selecting, Sorting, and

Summarizing Data

Selecting, Sorting, and

Summarizing Data

Selecting, Sorting, and

Summarizing Data

Exception Reporting

Exception Reporting

36 A Manager's Guide for Monitoring Data Integrity in Financial Systems
Travel - Authorization

Data integrity

Objectives

Illustrative Monitoring

Techniques

Suggested

Tools

Monitoring

All official travel is properly authorized in accordance with

applicable laws,

regulations, and

management's policy.

All travel transactions are recorded in a systematic

order.

All travel information is accurately reflected in the system.

Analyze travel orders for instances of travel not authorized in accordance with

applicable laws, regulations, and

management's policy.

Identify instances of travel paid but not authorized.

Identify travel advances/vouchers that are authorized after payment.

Verify that traveler's personnel

information (e.g., staff ID, SSN, payment

address, permanent duty station, etc.) is retained by the system.

Verify that changes made to the system

(e.g., traveler information, budgetary

limits, etc.) are in accordance with

management's policy.

Selecting, Sorting, and

Summarizing Data

Exception Reporting

Exception Reporting

Compare/Match Two Files or Tables

Exception Reporting

Appendix A. Description of Monitoring Tools 37
Travel - Advances

nptr) Intpfiritv llli]^trf)ti\/p Mnnitnrinn ^iifinp^tpH Monitorinn

Objectives Techniques Tools

Auinonzeu Travel neconciie iravei auvances to auTnorizea oeiecTing, oorTing, ana

dUVdilOcb die Wlllilll dp|JI Upi IdllUI lb Ull d mjdilciiy Udolo. OUIIIIIIdll^lliy Udld

budgetary limits. Uclcl lllli Ic CAlclll Ul UVcl UUIiydllUllo dilU juiii rieiuo ueiweeM nieb ur over expenditures. Tables and Selecting, Sorting,

and Summarizing Data

Travel advances are Identify duplicate transactions or Gap Detection and Duplicate

made only once and to document numbers. Detection

the appropriate party.

Reconcile travel advances to the Join Fields between Files or appropriate authorizing and payment Tables and Selecting, Sorting,

documents. and Summarizing Data

Travel - Travel Voucher

Data Integrity Illustrative Monitoring Suggested Monitoring

Objectives Techniques Tools

Amounts claimed are in Determine whether the per diem, Exception Reporting

accordance with mileage allowances, and standard

authorized laws, mileage are accurately reflected in the

regulations, and database.

management's policy.

Compare amounts claimed on the Selecting, Sorting, and

payment voucher to the database of per oummarizing uaTa ana txpomng

diem, mileage allowances, standard uata

mileage, etc.

Amounts claimed are Identify invalid claims and instances Exception Reporting

valid and within where amounts claimed exceed

authorized limits. established limits.

Recalculate actual amounts claimed on Selecting, Sorting, and

the travel voucher and compare to Summarizing Data and

established limits. Calculated Fields

38 A Manager's Guide for Monitoring Data Integrity in Financial Systems
Travel - Status

Data Integrity

Objectives

Illustrative Monitoring

Techniques

Suggested Monitoring

Tools

Overdue travel advances are recorded

by the agency's

accounts receivable

system.

Reconcile overdue travel advances

recorded by the travel system to the

agency's accounts receivable system.

Join Fields between Files and

Tables and Selecting, Sorting,

and Summarizing Data

Identify any overdue travel advances with Exception Reporting no scheduled collection date.

Identify instances of overdue travel advances not established as an accounts

receivable.

Exception Reporting

Amounts authorized are both timely and

accurate.

All travel transactions are accurately reflected in the core financial system.

Summarize all overdue travel advances

by traveler name and due date.

Identify number and dollar value of outstanding travel advances over 60

days old, 90 days old, etc.

Recalculate authorized per diem, meals

and incidental expenses based on

itinerary and actual travel performed.

Reconcile travel advances obligations

and payments.

Selecting, Sorting, and

Summarizing Data

Selecting, Sorting, and

Summarizing Data

Selecting, Sorting, and

Summarizing Data and

Calculated Fields

Compare/Match Files or Tables;

and Selecting, Sorting, and

Summarizing Data; Join Fields

between Files and Tables

Appendix A. Description of Monitoring Tools 39
Direct Loan Management - Loan Extension

Data Integrity

Objectives

Illustrative Monitoring

Techniques

Suggested Monitoring

Tools

Applicants are evaluated

(accepted/rejected)

according to specified

guidelines

Compare applications against eligibility

and credit-worthiness criteria to determine if acceptance/rejection

complied with established guidelines

Identify historical results of individual

credit officers to evaluate underwriting

decisions

Selecting, Sorting, and

Summarizing Data and

Compare/Match Files or Tables

Selecting, Sorting, and

Summarizing Data

Lending is maintained

within legal funding

limits.

Obtain loan application demographic

data for management reporting.

Identify loans individually, or in the

aggregate for single borrowers, that exceeded allowed borrowing levels.

Identify loans that are not posted to both

the loan and funds control system.

Selecting, Sorting, and

Summarizing Data

Compare/Match Files or Tables

Exception Reporting

40 A Manager's Guide for Monitoring Data Integrity in Financial Systems
Direct Loan Management - Account Servicing

Data Integrity

Objectives

Illustrative Monitoring

Techniques

Suggested Monitoring

Tools

Loan systems contain

adequate and current

information.

Recompute loan balances based on disbursement and payment history to ensure the system is correctly posting

transactions and calculating balances

and escrows.

Selecting, Sorting, and

Summarizing Data, and

Calculated Fields

Modified loans are

properly approved and

presented.

Identification of past-due

accounts are made in a

timely fashion.

Recompute billings and statements to ensure that they are accurately prepared

by the system.

Identify loans with credit balances.

Selecting, Sorting, and

Summarizing Data, and

Calculated Fields.

Exception Reporting

Identify modifications made to loan terms Compare/Match Files or Tables

for management scrutiny.

Determine if loan status (e.g.,

delinquent, in repayment, etc.) is consistent with payment history.

Selecting, Sorting, and

Summarizing Data, and Join

Fields between Files and Tables

Appendix A. Description of Monitoring Tools 41
Direct Loan Management - Portfolio IVIanagement

Data Integrity

Objectives

Illustrative Monitoring

Techniques

Suggested Monitoring

Tools

Portfolio performance measures are needed to evaluate program

performance.

Loan data are required

to support loan sales.

Obtain statistical and demographic data on loan portfolio.

Obtain loan data by cohort to facilitate Credit Reform reporting.

Select loans from the portfolio for sale

based on specified loan characteristics.

Selecting, Sorting, and

Summarizing Data

Selecting, Sorting, and

Summarizing Data

Selecting, Sorting, and

Summarizing Data, and

Calculated Fields

Portfolio information is properly valued and

allowances are

disclosed.

Prepare aging of accounts and perform

collateral analysis to help calculate

allowance for the portfolio.

Aging Data and Calculated Fields

42 A Manager's Guide for Monitoring Data Integrity in Financial Systems
Direct Loan Management - Delinquent Debt Collection

Data Integrity

Objectives

Illustrative Monitoring

Techniques

Suggested Monitoring

Tools

Recovery of delinquent

debt is accomplished in a timely fashion.

Unrecoverable debts are

written off in a timely

fashion.

Determine whether required collection

actions were taken on delinquent loans

in accordance with the Office of Management and Budget's debt

collection requirements.

Prepare trend analysis of defaults and

recoveries to determine the

effectiveness of debt collection

techniques.

Analyze accounts and the aging of receivables and confirm that amounts

overdue over the stipulated amount of time are properly expensed.

Selecting, Sorting, and

Summarizing Data

Selecting, Sorting, and

Summarizing Data

Selecting, Sorting, and

Summarizing Data, and Aging

Data

Appendix A. Description of Monitoring Tools 43
Appendix B. Preventive Controls 7

The Monitoring Module tools discussed in this report and in

Appendix A are feedback or detective controls. As stated earlier,

preventive controls are crucial as a first line of defense in any financial

system. Preventive controls should prevent erroneous data from being

entered into the system. Two preventive controls found in financial

systems are discussed in this appendix.

Edit and Limit Edit and limit checks, already included in many financial systems, are Checks also included in the functional requirements published by JFMIP. In using this control, reasonable edit and limit checks are established as the system is implemented. Data are compared to these checks and

limits as they are entered. If the data fail the tests, the user must take

appropriate action to correct the problem. The issues of user interface

and implementation of edit and limit checks are important enough for

some coverage in this report. Preventive control can also be provided

through the use of pick lists. Pick lists are lists of items such as accounts or vendors that can be chosen by clicking on the item,

eliminating the need to key the infomiation and reducing the chance for

keyboard errors.

Financial systems should contain a set of edit and limit checks to ensure data integrity within the system. If the system is currently

being implemented, the computer technology for designing these edit

and limit checks is simple. Edit and limit checks can also be added to existing systems, but for some systems this may be difficult and

costly. A greater challenge is detemiining where and how to implement the edit and limit checks. The solution will probably be

different in each situation. In an organization with uniform divisions,

the financial system developer can probably implement the solution at a central point in the organization. However, in a very diverse

organization, implementation will probably be required for each

division. Usually a combination of these two methods will work best.

Organizations do things similarly in some areas, such as travel.

44 A Manager's Guide for Monitoring Data Integrity in Financial Systems
Most of the edit and limit check configuration could be perfonned for an entire organization in such cases, with minor changes needed for

some divisions. These changes will be most effective if they are developed with the help of the division personnel who will use them.

In all cases, the help of an audit organization is essential. In the case of the Federal government, the Office of Inspector General should

probably be involved.

It is important that the user interface for setting and changing edit and

limit check parameters be easy for authorized personnel to operate. A

possible implementation of the user interface would automatically

allow the user (through the use of a user profile that identifies the

person and the work area) to use a spreadsheet type of screen to set

all the data entry fields for that area of the organization.

Federal Information Processing Standards (FIPS) 186, Digital Signature Digital

Standard (DSS) [10], was issued May 19, 1994, and became effective Signatures

December 1, 1994. The trend of many Federal government agencies to transfonn paper-based systems into automated electronic systems has

created the need for a reliable, cost-effective digital signature system.

A digital signature can be used to identify and authenticate the

originator of information. Digital signatures can also be used as follows:

• To verify that information has not been altered after it is signed

• To ensure that transactions coming electronically from another part

of the accounting system are authentic, authorized transactions

• To ensure that proper separation of duties is maintained.

The verification provided by digital signatures includes not only the

name of the person authorizing the transaction but also relevant

transaction data. Any change in either the name or the data can be

detected.

Appendix B. Preventive Controls 45
Appendix C. Integrated Test Facility 8

The Integrated Test Facility (ITF) allows test data to be used safely to evaluate the system controls and processing in a live production

environment. The ITF is used to test the follov^ing:

• Edit and limit checking and verification criteria

• Database updates and security features (such as transaction

rollbacks)

• Computer generated transactions

• Output.

In addition, the ITF can be used to evaluate system perfonnance by

monitoring test transactions as they are processed.

The Integrated Test Facility can be integrated into the financial system

in a way that can improve the safety of using the tool by sending data

only to a "dummy database." This would ensure that test transactions

do not get mixed with live transactions. Security protection should

prevent all but authorized personnel from using this tool.

The Integrated Test Facility provides a safe way to test the

effectiveness of the Monitoring Module tools and other system

controls. The user can enter and process selected test transactions and

live data concurrently in a live production system, tracing the flow of

transactions through the various functions in the system and

comparing the results of the test transactions with predetermined or

precalculated results. The ITF requires a dummy entity (e.g., division,

store, dealer) integrated into the normal production system. Once this

fictitious entity has been established, transactions can be processed

against it using the standard production input process.

46 A Manager's Guide for Monitoring Data Integrity in Financial Systems
All transaction types, combination of types, logic paths,

computations, and controls within the application can be tested by

this method at any time.

When using an ITF, additional controls must be established to ensure

that test data are not posted to live production records. For example,

all test data must be keyed to or identified with a fictitious entity by

using a unique identification code. In addition, special procedures

must be established to (1) ensure that test data are not included in

final output reports and accounting records and (2) prevent test data

from being communicated to other applications. These additional

procedures require the following:

• Modification of existing programs to separate test data from live

production data

• Deleting test data and manually adjusting all totals and reports to remove transactions

• Designing the application so it can process, but keep separate,

both test data and normal production data.

Ideally, an ITF is established during the initial design of an application

process. This eliminates the cost of later modifying programs, and

reduces manual intervention for removing transactions from

production files. However, the ITF can be implemented after the

application is operational in a production environment [3]. The

accuracy of automatically generated transactions can be tested by the

ITF. Furthermore, there may be a desire to test transactions

calculated based on standing data in the system each time there are wide-spread changes. Most importantly, an ITF can be used by

management, users, software developers, modification personnel,

quality assurance personnel, and auditors.

Appendix C. Integrated Test Facility 47
Implementing an ITF in a new system built with relational database

technology is straightforward. When the ITF is in use, data go to a

test database. These data could have test balances established for all general ledger accounts. Test data would go only to this database but

would use the file structure and programming instructions of the actual

financial system. Test data would never go to the live database with

actual transactions.

Adding an ITF to an established system is more of a problem and can

require almost an entire system redesign. John Lainhart and Barry

Snyder in their articled Simultaneous Parallel Approach to Testing

Computerized Systems [11], describe a method that allows an ITF to be added to an existing system with little program modification. Two

modules of programming code are added to the existing program. One

splits test transactions from live transactions, and the other merges

them back together. These modules look for a certain code to be

present in a record that indicates that transactions are test transactions. It is also possible to add a menu choice to select the ITF

for use, just as with a new system. The user interface may or may not

be as user friendly, but the capability can still be there. These

transactions are split and the live transactions go to create a new

master file used for reporting. The merge module is then used to merge test transactions with live transactions to create a new master

file.

Only authorized, properly trained personnel should use the ITF. The

new system, with a built-in ITF, should be easy to use. System

designers who build the ITF into existing systems should make every

effort to provide as much ease of use and safety as possible in their

implementation. Each implementation will be different, and

implementation and safeguarding methods are left to the system

designer. It is important that management completely understands

how the ITF works and how it will be implemented, that there be

complete and clear documentation describing ITF implementation and

operation, and that system design professionals be used to ensure safe

operation.

A Manager's Guide for Monitoring Data Integrity in Financial Systems
Appendix D. Monitoring Module Tools

Examples and Samples 9

• Payments that are not made in a timely manner Exception

Reporting

• Payments that are legal but are for a high dollar value (these may be

correct, but management might want to review them)

• Travel transactions for all employees who received an advance of

$1,000 or more

• Travel transactions for all employees with travel claims that have

been outstanding for longer than a given period

• Travel transactions where the payment of transportation costs

exceeds the transportation costs authorized

• All equipment items that have not been inventoried in the last year

• All accounts receivable items authorized to exceed the established

credit limit

• All accounts receivable items with an amount over $25,000

• All accounts with balances that are older than acceptable

• Large payments to the same vendor

• Transactions for single vendors with multiple points of payment

• Possible duplicate payments - transactions with the same vendor

and same amount

Appendix D. Monitoring Module Tools - Examples and Samples 49
Some of the above could also be subjects of ad hoc reports produced

by the Selecting, Sorting, and Summarizing tool. The distinction

between Exception Reporting and Selecting Records is the frequency

with which they are used. Exception reporting is used in areas where

you expect problems. Selecting records is used to detemiine whether

there are problems in other areas. If problems are regularly found in a

certain area by selecting records, this area may be a candidate for

exception reporting.

Figure 1. Accounts

Payable Transaction

File.

Vendor Date Amount

Smith Office Machines 1/12/95 $10,950.00

Jones Meats Inc. 1/12/95 1,500.00

Harley Temps 1/12/95 1,200.00

Johnson Office Furniture 1/12/95 12,000.00

Smith Office Machines 1/14/95 28,500.00

Smith Office Machines 1/20/95 128,000.00

Jones Meats Inc. 2/1/95 1,250.00

Harley Temps 2/1/95 1,250.00

Jones Meats Inc. 2/13/95 1,450.00

Jones Meats Inc. 2/14/95 850.00

Harley Temps 2/14/95 1,400.00

Johnson Office Furniture 2/14/95 1,250.00

Smith Office Machines 2/23/95 2,750.00

Figure 2. Selected

Records from the

Accounts Payable

Transaction File where

Vendor equals "Smith

Office Machines."

Vendor Date Amount

Smith Office Machines 1/12/95 $10,950.00

Smith Office Machines 1/14/95 28,500.00

Smith Office Machines 1/20/95 128,000.00

Smith Office Machines 2/23/95 2,750.00

50 A Manager's Guide for Monitoring Data Integrity in Financial Systems
Vendor Date Amount

Figure 3. Selected

Smith Office Machines

Smith Office Machines

Smith Office Machines

1/12/95

1/14/95

1/20/95

$10,950.00

28,500.00

128,000.00

records from the

Accounts Payable

Transactions File

where Vendor equal

"Smith Office

Machines" and Date is equal to or greater

than 1/1/95 and less than or equal to 1/31/95.

Record selection:

• List authorized account balances greater than $ 1 00 in excess of

credit limits

• List all cash disbursements between two dates (this list could then

be used to verify the disbursements to actual documents)

• List accounts receivables over a specified dollar amount and pastdue more than a specified number of days

• List all benefit claimants who filed before, after, or between certain

dates.

Appendix D. Monitoring Module Tools - Examples and Samples 51
Examples of

Sorting Data

Figure 4. Data

before the sort.

Figure 5. Data after

sorting by Vendor and

then Date.

Vendor Date Amount

Smith Office Machines 1/12/95 $10,950.00

Jones Meats Inc. 1/12/95 1,500.00

Harley Temps 1/12/95 1,200.00

Johnson Office Furniture 1/12/95 12,000.00

Smith Office Machines 1/14/95 28,500.00

Smith Office Machines 1/20/95 128,000.00 . Jones Meats Inc. 2/1/95 1,250.00

Harley Temps 2/1/95 1,250.00

Innoc ^/loatc 1 nr* JUl lUo ivlcdLc) II IL*. P/1 '^/Q'^ Jones Meats Inc. 2/14/95 850.00

Harley Temps 2/14/95 1,400.00

Johnson Office Furniture 2/14/95 1,250.00

Smith Office Machines 2/23/95 2,750.00

Vendor Date Amount

Harley Temps 1/12/95 $1,200.00

Harley Temps 2/1/95 1,250.00

Harley Temps 2/14/95 1,400.00

Johnson Office Furniture 1/12/95 12,000.00

Johnson Office Furniture 2/14/95 1,250.00

Jones Meats Inc. 1/12/95 1,500.00

Jones Meats Inc. 2/1/95 1,250.00

Jones Meats inc. 2/13/95 1,450.00

Jones Meats Inc. 2/14/95 850.00

Smith Office Machines 1/12/95 10,950.00

Smith Office Machines 1/14/95 28,500.00

Smith Office Machines 1/20/95 128,000.00

Smith Office Machines 2/23/95 2,750.00

52 A Manager's Guide for Monitoring Data Integrity in Financial Systems
Vendor Date Amount

Harley Temps 1/12/95 $1,200.00

Harley Temps 2/1/95 1,250.00

Harley Temps 2/14/95 1,400.00

Johnson Office Furniture 2/14/95 1,250.00

Jolinson Office Furniture 1/12/95 12,000.00

Jones Meats Inc. 2/14/95 850.00

Jones Meats Inc. 2/1/95 1,250.00

Jones Meats Inc. 2/13/95 1,450.00

Jones Meats Inc. 1/12/95 1,500.00

Smith Office Machines 2/23/95 2,750.00

Smith Office Machines 1/12/95 10,950.00

Smith Office Machines 1/14/95 28,500.00

Smith Office Machines 1/20/95 128,000.00

Vendor Date Amount

Smith Office Machines 2/23/95 $2,750.00

Smith Office Machines 1/12/95 10,950.00

Smith Office Machines 1/14/95 28,500.00

Smith Office Machines 1/20/95 128,000.00

Figure 6. Data sorted

by Vendor and then

Amount.

Figure 7. Data

selected from the

Accounts Payable

Transaction File

where Vendor equals

"Smith Office

Machines" and then

sorted by Amount.

Sorting examples:

Sort inventory items by location to facilitate physical observations.

List all account balances more than $ 1 00 in excess of credit limits and

sort in descending order according to the size of the excess.

Appendix D. Monitoring Module Tools - Examples and Samples 53
Examples of

summarizing

files

Figure 8. Accounts

Payable Transaction

File.

Vendor Date Amount

Smith Office Macliines 1/12/95 $10,950.00

Jones Meats Inc. 1/12/95 1,500.00

Harley Temps 1/12/95 1,200.00

Johnson Office Furniture 1/12/95 12,000.00

Smith Office Machines 1/14/95 28,500.00

Smith Office Machines 1/20/95 128,000.00

Jones Meats Inc. 2/1/95 1,250.00

Harley Temps 2/1/95 1,250.00

Jones Meats Inc. 2/13/95 1 ,450.00

Jones Meats Inc. 2/14/95 850.00

Harley Temps 2/14/95 1,400.00

Johnson Office Furniture 2/14/95 1,250.00

Smith Office Machines 2/23/95 2,750.00

Vpnrlnr A mni 1 nt Payable Transaction

File Summarized by

Vendor.

Harley Temps

Harlpu Tpmnc;

Harley Temps

Total for Harley

1/12/95

?/1 /Q5

2/14/95

$1,200.00

1 ?50 on

1,400.00

$3,850.00

Johnson Office Furniture 1/12/95 $12,000.00

Johnson Office Furniture 2/14/95 1,250.00

Total for Johnson $13,250.00

Jones Meats Inc. 1/12/95 $1,500.00

Jones Meats Inc. 2/1/95 1,250.00

Jones Meats Inc. 2/13/95 1,450.00

Jones Meats Inc. 2/14/95 850.00

Total for Jones $5,050.00

Smith Office Machines 1/12/95 $10,950.00

Smith Office Machines 1/14/95 28,500.00

Smith Office Machines 1/20/95 128,000.00

Smith Office Machines 2/23/95 2,750.00

Total for Smith $170,200.00

Total for Accounts Payable $192,350.00

54 A Manager's Guide for Monitoring Data Integrity in Financial Systems
Vendor

Jones Meats Inc.

Date

1/12/95

Amount

$1,500.00

1 oiai Jones lor January <n cnn r\r\

JUIIcb Ivlcdlb II lO. Jones Meats Inc.

OL/I ICo IVIC7CILO II lO.

C.I 9/11 //QR y o 2/13/95

P/1 4/95

Cp 1 ,£iOU.UU

1,450.00

Total Jones for February $3,550.00

Total for Jones $5,050.00

Figure 10. Records

selected where Vendor

equals "Jones Meats

Inc." from the

Accounts Payable

Transactions File

Summarized by date.

A cruise ship company could use frequency distribution on its Frequency

automated master file to identify records having invalid codes in the Distribution

License Code Field. The License Code field is a two-digit code used to Reporting

represent a particular description. The following is a listing of

possible results. The valid codes are identified with a legitimate

description. Invalid codes are researched and the problems are

identified.

Appendix D. Monitoring Module Tools - Examples and Samples 55
Figure 11. Frequency

Distribution.

Items Count of Items Description

(bl) 9 Data entry Error G 1 Data entry Error

**

1 184 Unknown

AA 39639 Master Deck Officer

AB 3897 Chief Mate Deck Officer

AC 4262 Second Mate Deck Officer

AD 7043 Third Mate Deck Officer

AE 6612 Mate Deck Officer

AF 14063 First Class Pilot Deck Officer

AG 1

1

Data entry error AR 1 Data entry error BA 16944 Chief Engineer Officer

BB 6013 First Assistant Engineer

BC 7769 Second Assistant Engineer

BD 16077 Third Assistant Engineer

BE 1474 Assistant Engineer

BF 171 Designated Duty Engineer

CA 2190 Radio Officer

DA 75 Surgeon Staff Officer

DB 31 Professional Nurse

DC 97 Chief Purser

DD 7 Chief Purser/PYA

DE 9 Chief Purser/HM

DF 66 Purser

DG 2 Purser/PYA

DH 2 Purser/HM

Dl 20 Senior Assistant Purser

DJ 3 Senior Asst. Purser/PYA

DK 5 Senior Assistant Purser/HM

DL 147 Junior Assistant Purser

DM 2 Junior Assistant Purser/PYA

DN 22 Junior Assistant Purser/HM

DO 7 Medical Doctor

EA 47883 Kitchen

EB 1 Data entry error FA 22308 Dining Room

GA 41325 Entertainers

HA 27938 Social Director

HB 1946 Second Class Operator

HZ 1 Data entry error XX 1 Data entry error zz 4231 Endorsement

D t 1 Data entry error 6B 2 Data entry error 6E 1 Data entry error 7B 1 Data entry error 9e 1 Data entry error

56 A Manager's Guide for Monitoring Data Integrity in Financial Systems
Itsms r^niint nf ItpmQ 9/^ /O nfVJI Itpmc 1 Ld 1 lo IVIcal 1 OIU. LJtJV. 9 n nn 1 n G 1 0 no 1 n

**

1 1 84 0 . 43*~r O 1 0

AA 1 4 4Q 1 n AB 3897 1 42 1 n AC 4264 1 5fi 1 n AD 704? 1 n AE 661

2

2 4? 1 n AF 1 4063 5 14 1 n AG 1

1

0 on 1 n AR 1 0 00 11 nKJ BA 1 6944 6 20 1 0

BB 601

3

2 20 1 0

BC 7769 2 84 1 n BD 1 6077 5 88 1 0

BE 1 474 0 54 1 n BF 1 71 0 06 1 0

CA 21 90 0.80 1 0

DA 75 0 03 1 0

DB 31 0.01 1 0

DC 97 0 04 1 0

DD 7 0 00 1 0

DE 9 0 00 1 n DF 66 0 02 1 n DG 2 0 00 1 n DH 2 0 00 1 n

Dl 20 0 01 1 0

DJ 3 0 00 1 0

DK 5 0 00 1 0

DL 1 47 0 05 1 0

DM 2 0 00 1 0

DN 22 0 01 11 0\J DO 7 0 00 1 0

EA 47883 1 7 51

EB "I 0 00

FA 22308 8 1 6 1 0

GA 41 325 15 11 1 0

HA 27938 1 0.22 1 0

HB 1 946 0 71 1 0

HZ 0 00 1 0

XX 11 0 00 11 0\J ZZ 4231 1.55 1 0

5E 1 0.00 1 0

6B 2 0.00 1 0

6E 1 0.00 1 0

7B 1 0.00 1 0

9E 1 0.00 1 0

Figure 12. Frequency

Distribution Report.

Appendix D. Monitoring Module Tools - Examples and Samples 57
stratification of a

File or Table

Figure 13. The file has been divided into

five segments with

ranges specified to obtain information

regarding each file segment.

Figure 14. Results

from the set of ranges

specified in Figure 13.

Range Lower Limit Upper Limit

1 -250,000 0

2 0 250,000

3 250,000 500,000

4 500,000 750,000

5 750,000 1,000,000

Range Records % of Records Balance % of Balance

1 0 0.00 0.00 0.00

2 53 53.00 7,934,982.89 27.20

3 28 28.00 8,609,006.63 29.51

4 18 18.00 11,865,982.93 40.67

5 1 1.00 765,000.97 2.62

Totals 100 100.00 29,174,973.42 100.00

58 A Manager's Guide for Monitoring Data Integrity in Financial Systems
Appendix E. Contributors

to the Report 10

NIST formed a working group of accounting and auditing experts to develop a more complete set of Monitoring Module tools than

discussed in the previous report on this subject. The following

organizations were invited to attend and provide input to the report:

American Institute of Certified Public Accountants

Arthur Andersen & Co. L.L.P.

Coopers & Lybrand L.L.P.

Deloitte & louche L.L.P.

Ernst & Young L.L.P.

Institute of Internal Auditors

Joint Financial Management Improvement Program

KPMG Peat Marwick L.L.P.

Office of Management and Budget

Price Waterhouse L.L.P.

United States Department of Defense, Defense Finance and Accounting Service

United States Department of Health & Human Services, Office of Inspector General

United States Department of Treasury, Financial Management Service

United States General Accounting Office

United States House of Representatives, Office of Inspector General

United States Securities and Exchange Commission

The following individuals made significant contributions to the report:

Ms. Jane E. Altenhofen, CIA, CFE Institute of Internal Auditors

Mr. David T. Copenhafer United States Securities and Exchange

Commission

Mr. Stephen W. Greenfield, MBA, CGFM U. S. Department of Health & Human

Services, Office of Inspector General

Mr. Jonathan Korol, CPA Deloitte & Touche L.L.P.

Mr. John W. Lainhart IV, CISA United States House of Representatives,

Office of Inspector General

Appendix E. Contributors to the Report 59
Ms. Janet A. McLeod, CPA

Mr. Thomas F. Patterson, CPA, CISA

Mr. Peter A. Salinger, CDP

Mr. Ray Sexton

Mr. Stephen B. Watson, CPA, CIA,

CMA, CGFM

Ms. Linda Wilkinson

Mr. Chris Zoladz, CPA, CISA

U. S. Department of Health & Human

Services, Office of Inspector General

Coopers & Lybrand L.L.P.

KPMG Peat Marwick L.L.P.

U. S. Department of Defense, Defense

Finance and Accounting Service

Price Waterhouse L.L.P.

U. S. Department of Treasury, Financial

Management Service

Ernst & Young L.L.P.

We would also like to thank Ms. Terry Caton of KPMG Peat Marwick, and Andrea A.

Wargo, Ph.D., U. S. Department of Health & Human Services, for their fine work in editing

this document, and Ms. Sharon Wentling, NIST, for her assistance throughout the entire

project.

60 A Manager's Guide for Monitoring Data Integrity in Financial Systems
Glossary 11

Statistical sampling that reaches a conclusion about a population in

terms of a rate of occurrence.

Displaying specific records or groups of records identified by a set of

criteria regarding file or database data.

A variable sampling procedure that uses attributes theory to express a

conclusion in dollar amounts.

Closely related to the concept of artificial intelligence, these systems

assist in decision-making by bringing users the knowledge of

experts[12].

A process that allows data from one application to be automatically

transformed for use in another application.

A category of information, such as a name. A collection of data. A telephone book can be thought of as a file: each person in the telephone book has a record, each record contains a name field, an address field, and a phone number field. A computer program or series of programs that enable the auditor to directly access client computer data files, extract relevant data, and

perform useful audit functions [13]. These packages include a series

of computer programs that have been preprogrammed, thereby

providing the power and flexibility of programming languages while

requiring only minimal coding (using a high-level, problem-oriented

language). These packages permit such functions as reading data,

manipulating data, sorting data, and printing reports to be performed

without programming languages. Generalized audit software programs

can be coded, debugged, and executed in a fraction of the time it takes

in common programming languages. Generalized audit software can be

installed as microcomputer, minicomputer, or mainframe packages or

as a combination of environments [3].

attributes

sampling

data retrieval

dollar unit

sampling

expert system

exporting data

field

file

generalized

audit software

Glossary 61
integrated test The ITF functions as follows:

f aci I ity • Enter and process selected test transaction input along with live

data in a live production system

- • Trace the flow of transactions through the various system functions

• Compare test transaction results with predetemiined results. Entering test transactions through the ITF requires integrating a dummy entity into the normal production system. Once this fictitious

entity has been established, transactions can be processed against it using the standard production input process [3].

module : A suite of tools that are logically located together. The tools are described in one section of the documentation. The menu selections

' for all the module tools are under one main menu selection in the user

interface.

off-the-shelf Commercially available software that is not custom-designed for the

software organization.

q uery Allows the user to ask questions and define a set of criteria about the

data. The query is used to isolate specific records or groups of records

for display or processing.

random sample Every combination of the same number of items in the population has an equal probability of selection.

record A collecdon of related items or field data, for example all the

information regarding one entry in the telephone book.

relational A system using a data representation model based on tables,

database nonprocedural operations, and predicate logic [14].

62 A Manager's Guide for Monitoring Data Integrity in Financial Systems
A report generator allows automatic generation of pre-designed reports report generator

or allows ad-hoc reports to be defined. Some data manipulation

functions may be perfomied, and data extraction is possible.

Items selected from a population to reach a conclusion about that sample

population.

A collection of data about a particular subject. It can be thought of as table

columns (fields) and rows (records).

A set of software instructions designed to allow the user to easily tool

enter needed information and then use the infonnation to perform a

task.

Statistical sampling that reaches a conclusion on the monetary variables

amounts of a population. sampling

Glossary 63
References 12

[I] Sies, Roger F., Self Monitoring Accounting Systems, National Institute of

Standards and Technology Special Publication 500-226, March 1995.

[2] Joint Financial Management Improvement Project, Franfieworl< for Federal

Financial Management Systems, 1 994.

[3] Computerized Information Systems (CIS) Audit Manual, The Information Systems

Audit and Control Foundation, 1992.

[4] Federal Information Processing Standards (FIPS) 193, SQL Environments, FIPS

Publication 193, U.S. Department of Commerce, National Institute of Standards

and Technology, February 3, 1995.

[5] American National Standard for Information Systems (ANSI). Information

Systems - Database Language - SQL, ANSI X3.1 35-1 992, equivalent to International Standard ISO/IEC 9075:1992 and Federal Information Systems

Processing Standard (FIPS) Pub 127-2, October 1992.

[6] The Institute of Electrical and Electronics Engineers, Inc., Software Engineering

Standards, Third Edition 1989.

[7] Ruthberg, Zella G., Bonnie Fisher, William E. Perry, John W. Lainhart IV, James

G. Cox, Mark Gillen, and Douglas B. Hunt, Guide to Auditing for Controls and

Security: A System Development Life Cycle Approach, National Bureau of

Standards Special Publication 500-153, April 1988.

[8] Systems Auditability and Control Report, The Institute of Internal Auditors

Research Foundation, 1991.

[9] Joint Financial Management Improvement Project, Core Financial System

Requirements, Interim Final, April 1994.

[10] Federal Information Processing Standards (FIPS) 186, Digital Signature

Standard (DDS), FIPS Publication 186, U.S. Department of Commerce, National

Institute of Standards and Technology, December 1, 1994.

[II] Lainhart, John and Barry Snyder, A Simultaneous-Parallel Approach to Testing

Computerized Systems, EDPACS, October 1977.

64 A Manager's Guide for Monitoring Data Integrity in Financial Systems
[12] Coopers & Lybrand L.L.P., Handbook of IT Auditing, Warren, Gorham & Lamont,

1994.

[13] American Institute of Certified Public Accountants, Auditing Witli Computers,

1994.

[14] Schur, Steven G., Tine Database Factory, John Wiley & Sons, Inc., 1994.

References 65

ANNOUNCEMENT OF NEW PUBLICATIONS ON

COMPUTER SYSTEMS TECHNOLOGY

Superintendent of Documents

Government Printing Office

Washington, DC 20402

Dear Sir:

Please add my name to the announcement Hst of new pubhcations to be issued in the series: National Institute of

Standards and Technology Special Publication 500 -.

Name

Company

Address

City State

Zip Code

(Notification key N-503)

67

6 U.S. GOVERNMENT PRINTING OFFICE: 1996 404-52

Technical Publications

Periodical

Journal of Research of the National Institute of Standards and Technology—Reports NIST research and development in those discipHnes of the physical and engineering sciences in which the Institute is active. These include physics, chemistry, engineering, mathematics, and computer sciences. Papers cover a broad range of subjects, with major emphasis on measurement methodology and the basic technology

underlying standardization. Also included from time to time are survey articles on topics closely related to the Institute's technical and scientific programs. Issued six times a year.

Nonperiodicals

Monographs—Major contributions to the technical literature on various subjects related to the

Institute's scientific and technical activities. Handbooks—Recommended codes of engineering and industrial practice (including safety codes) devel- oped in cooperation with interested industries, professional organizations, and regulatory bodies. Special Publications—Include proceedings of conferences sponsored by NIST, NIST annual reports, and

other special publications appropriate to this grouping such as wall charts, pocket cards, and bibliographies.

National Standard Reference Data Series—Provides quantitative data on the physical and chemical

properties of materials, compiled from the world's literature and critically evaluated. Developed under a worldwide program coordinated by NIST under the authority of the National Standard Data Act (Public

Law 90-396). NOTE: The Journal of Physical and Chemical Reference Data (JPCRD) is published

bimonthly for NIST by the American Chemical Society (ACS) and the American Institute of Physics (AIP).

Subscriptions, reprints, and supplements are available from ACS, 1155 Sixteenth St., NW, Washington, DC

20056.

Building Science Series—Disseminates technical information developed at the Institute on building

materials, components, systems, and whole structures. The series presents research results, test methods, and

performance criteria related to the structural and environmental functions and the durability and safety

characteristics of building elements and systems.

Technical Notes—Studies or reports which are complete in themselves but restrictive in their treatment of

a subject. Analogous to monographs but not so comprehensive in scope or definitive in treatment of the subject area. Often serve as a vehicle for final reports of work performed at NIST under the sponsorship of

other government agencies.

Voluntary Product Standards—Developed under procedures published by the Department of Commerce

in Part 10, Title 15, of the Code of Federal Regulations. The standards establish nationally recognized

requirements for products, and provide all concerned interests with a basis for common understanding of

the characteristics of the products. NIST administers this program in support of the efforts of private-sector

standardizing organizations.

Order the following NIST publications—FIPS and NISTIRs—from the National Technical Information

Service, Springfield, VA 22161.

Federal Information Processing Standards Publications (FIPS PUB)—Publications in this series collectively constitute the Federal Information Processing Standards Register. The Register serves as the

official source of information in the Federal Government regarding standards issued by NIST pursuant to the Federal Property and Administrative Services Act of 1949 as amended. Public Law 89-306 (79 Stat. 1127), and as implemented by Executive Order 11717 (38 FR 12315, dated May 11, 1973) and Part 6 of

Title 15 CFR (Code of Federal Regulations).

NIST Interagency Reports (NISTIR)—A special series of interim or final reports on work performed by

NIST for outside sponsors (both government and nongovernment). In general, initial distribution is handled

by the sponsor; public distribution is by the National Technical Information Service, Springfield, VA 22161,

in paper copy or microfiche form.
O =3

o

O (^1

° £?

. o- £

3 Z §

3