Accounting Information Systems
10-1
CHAPTER 10
INFORMATION SYSTEMS CONTROLS FOR SYSTEMS
RELIABILITY – PART 3: PROCESSING INTEGRITY AND
AVAILABILITY
SPECIAL INTRODUCTION TO EXCEL
This chapter includes a number of problems that use Excel’s built-in Data Validation tool to help
students better understand processing integrity controls by programming them in a spreadsheet.
The Data Validation tool is found on the “Data” tab, as shown below:
Ch. 10: Information Systems Controls for Systems Reliability – Part 3: Processing Integrity and
Availability
10-2
Click on “Data Validation” and then choose the option “data validation”:
This brings up the following window, which can be used to design a variety of processing
integrity controls that will apply to the currently selected cell (in the example above, the Data
Validation controls will be applied to cell C2):
Accounting Information
Systems
10-3
Clicking on the drop-down arrow in the “allow” box yields the following choices:
• Any value (the cell can take numeric, text, date, etc. input) without restrictions
• Whole numbers only allowed
Choosing either whole numbers or decimals, yields the following additional choices:
This default window can be used to create a “range check” with minimum and maximum values.
Ch. 10: Information Systems Controls for Systems Reliability – Part 3: Processing Integrity and
Availability
10-4
Click the drop-down arrow in the Data box to reveal other types of tests that can be created:
• List – permissible values must be selected from a list that the control designer creates
The list of permissible choices can appear in a drop-down menu (if that box is checked) using
values found in a set of cells in the spreadsheet (using the source field):
If the “In–cell dropdown” box is
checked, the values will appear in a
drop-down list when a user clicks
on that cell.
Accounting Information
Systems
10-5
If the “In–cell dropdown” box is not checked, users will still be restricted to entering values from
the list indicated in the source box, but will have to manually type in those values rather than
selecting from a drop-down menu.
• Date – only date values
Ch. 10: Information Systems Controls for Systems Reliability – Part 3: Processing Integrity and
Availability
10-6
• Custom – formulas can be used to limit input values
For example, we can create a “reasonableness test” that requires cell C2 to be less than or equal
to 10 times the value in cell B2 as follows:
Once the processing integrity control has been designed, the “Input Message” tab can be used to
create a message explaining the permissible input values that will appear whenever a user selects
that cell:
Accounting Information
Systems
10-7
Finally, the “Error Alert” tab can be used to create a meaningful error message whenever user
data violates the constraints:
Ch. 10: Information Systems Controls for Systems Reliability – Part 3: Processing Integrity and
Availability
10-8
The message can have a title, plus as much text as desired. In addition, there are three action
choices:
1. Stop – the user is prohibited from inputting the erroneous data
2. Warning – the user is informed that the data is not valid, but has the option of entering it
anyway.
3. Information – the user is informed that the data is not valid. Clicking OK results in the
data being entered anyway; clicking cancel rejects the data.
Accounting Information
Systems
10-9
SUGGESTED ANSWERS TO DISCUSSION QUESTIONS
10.1 Two ways to create processing integrity controls in Excel spreadsheets are to use the
built-in Data Validation tool or to write custom code with IF statements. What are
the relative advantages and disadvantages of these two approaches?
Excel provides a “Data Validation” tool on the Data tab:
Ch. 10: Information Systems Controls for Systems Reliability – Part 3: Processing Integrity and
Availability
10-10
=IF(AND(A1>=18,A1<=65),””,”Error: values must be between 18 and 65″)
An IF statement consists of three arguments, separated by commas: =IF(first argument,
second argument, third argument). The first argument is the test to be performed, the
The Data Validation tool is easier to use. However, it is limited to performing tests of just
one condition. More complex tests require the IF function. For example, perhaps we want
to treat values of 18, 19, and 20 different from values 21-65. This can be done by nesting
IF statements, as follows:
Step 2: If the first IF statement is true (i.e., the value in cell A1 is greater than or equal to
18) the next test is whether the value is less than 21. If it is, then the message “value is
18-20” is displayed. If the value in A1 is greater than or equal to 21, a third test is
performed, testing whether it is less than or equal to 65.
Accounting Information
Systems
10-11
10.2 What is the difference between using check digit verification and a validity check to
test the accuracy of an account number entered on a transaction record?
Check digit verification is designed to detect typographical errors such as transposing two
digits or entering the wrong digit (e.g., typing an 8 instead of a 3). Passing a check digit
10.3 For each of the three basic options for replacing IT infrastructure (cold sites, hot
sites, and real-time mirroring) give an example of an organization that could use
that approach as part of its DRP. Be prepared to defend your answer.
Many solutions are possible. The important point is to justify that the method yields an
appropriate RTO for the organization. Cold sites yield RTOs measured in days; hot sites
Ch. 10: Information Systems Controls for Systems Reliability – Part 3: Processing Integrity and
Availability
10-12
10.4 Use the numbers 10–19 to show why transposition errors are always divisible by 9.
A
B
B – A
Divisible by 9?
Original Number
Transposed Number
Difference
10
01
9
Yes
11
11
0
Not a transposition
12
21
9
Yes
13
31
18
Yes
14
41
27
Yes
15
51
36
Yes
16
61
45
Yes
17
71
54
Yes
18
81
63
Yes
19
91
72
Yes
10.5 What are some business processes for which an organization might use batch
processing?
Batch processing may be used when master files do not need to be updated in real-time.
For example, many organizations process accounts payable in batches once a day or once
10.6 Why do you think that surveys continue to find that a sizable percentage of
organizations either do not have formal disaster recovery and business continuity
plans or have not tested and revised those plans for more than a year?
Likely reasons include:
• Belief that “it won’t happen to us”
Accounting Information
Systems
10-13
SUGGESTED SOLUTIONS TO THE PROBLEMS
10.1 Match the following terms with their definitions:
__s__ 1. business continuity plan (BCP)
a. A file used to store information for long
periods of time.
__j__ 2. completeness check
b. A plan that describes how to resume IT
functionality after a disaster.
__o__ 3. hash total
the quantity ordered is greater than 0.
__u__ 4. incremental daily backup
transmitted correctly by counting the
number of odd or even bits.
__a__ 5. archive
a customer is 18 or older.
__v__ 6. field check
__c__ 7. sign check
g. A disaster recovery plan that contracts
for use of an alternate site that has all
necessary computing and network
equipment, plus Internet connectivity.
__w__ 8. change control
h. A disaster recovery plan that contracts
for use of another company’s
information system.
__i__ 9. cold site
for use of an alternate site that is pre-
wired for Internet connectivity but has
no computing or network equipment.
__e__ 10. limit check
a sales order.
__k__ 11. zero-balance test
an account does not have a balance after
processing.
__n__ 12. recovery point objective (RPO)
l. An application control that compares the
sum of a set of columns to the sum of a
set of rows.
__m__ 13. recovery time objective (RTO)
without its information system.
m. A measure of the length of time that an
organization is willing to function
Ch. 10: Information Systems Controls for Systems Reliability – Part 3: Processing Integrity and
Availability
10-14
intrinsic meaning.
__t__ 16. check digit verification
p. A batch total that represents the number
of transactions processed.
__x__ 17. closed-loop verification
transaction record.
__d__ 18. parity checking
r. An application control that verifies that
an account number entered in a
transaction record matches an account
number in the related master file.
__q__ 19. reasonableness test
center but also its headquarters.
__y__ 20. financial total
verifies the accuracy of an account
number by recalculating the last number
as a function of the preceding numbers.
s. A plan that describes how to resume
business operations after a major
calamity, like Hurricane Katrina, that
destroys not only an organization’s data
v. A data-entry application control that
could be used to verify that only numeric
data is entered into a field.
w. A plan to ensure that modifications to an
information system do not reduce its
security.
x. A data-entry application control that
displays the value of a data item and asks
the user to verify that the system has
accessed the correct record.
dollar value of a set of transactions.
and subsequently returned so that
preprinted data can be scanned rather
than manually reentered.
Accounting Information
Systems
10-15
10.2 Excel Problem
Enter the following data into a spreadsheet and then perform the following tasks:
Employee
Number
Pay rate
Hours
worked
Gross Pay
Deductions
Net pay
12355
10.55
38
400.90
125.00
275.90
2178g
11.00
40
440.00
395.00
45.00
24456
95.00
90
8550.00
145.00
8405.00
34567
10.00
40
400.00
105.00
505.00
a. Calculate examples of these batch totals:
• A hash total
Solution: sum of the employee number or pay rate columns, since these totals
• A financial total
Solution: sum of the hours worked (208), gross pay (9790.90), deductions (770),
or net pay (9,230.90) columns as all these results have financial meaning
• A record count
Solution: 4, which is a count of the rows
b. Assume the following rules govern normal data:
• Employee numbers are five-digits in length and range from 10000 through
99999.
Give a specific example of an error or probable error in the data set that each of the
following controls would detect:
• Field check
A field check on the employee number column would detect that the second row
Ch. 10: Information Systems Controls for Systems Reliability – Part 3: Processing Integrity and
Availability
10-16
• Limit check
A limit check on pay rate could flag row 3 as an error because $95 exceeds the
maximum pay rate of $25,)
• Reasonableness test
Comparison of deductions to gross pay would flag a potential problem in row 2
since it is not unlikely that a person being paid $440 have $395 of deductions.
• Cross-footing balance test
c. Create a control procedure that would prevent, or at least detect, each of the errors
in the data set.
• Employee number not numeric
Using the data validation tool, select the cells you want to test (in the employee
number column) and specify the legal limits (whole numbers beginning with
10000 through 99999) as follows:
Accounting Information
Systems
10-17
Alternatively, you could write the following IF statement to perform the same
test:
• Pay rate too high or too low
This range test could be programmed using the data validation tool as follows:
Alternatively, this logical test would catch such errors and display an appropriate
error message:
=IF(D6<9,”pay rate must be at least $9″,IF(D6>25,”pay rate must be less than
$25″,””))
Ch. 10: Information Systems Controls for Systems Reliability – Part 3: Processing Integrity and
Availability
10-18
pay rate in the cell must be between $9 and $25 (because the second IF test is
only evaluated if the first one is true), so no error message is displayed (hence the
two double-quotes).
• Hours worked too high
Using the data validation tool, a limit check to ensure that hours worked must be
less than or equal to 40 can be designed as follows:
Alternatively, the following IF statement would enforce the same limit check:
=IF(A4<=40,””,”Error: hours worked cannot exceed 40”)
Accounting Information
Systems
10-19
• Deductions too high relative to gross pay
This reasonableness test would be programmed using the data validation tool and
choosing “custom” in the allow field, as follows:
The formula would limit the deductions in cell M7 to be less than or equal to 40%
of the gross pay in cell L7.
Alternatively, the following IF statement would perform the same reasonableness
test:
Ch. 10: Information Systems Controls for Systems Reliability – Part 3: Processing Integrity and
Availability
• Error in calculating net pay
Alternatively, the following IF statement would catch the error:
=IF(L5-M5=N5,””,”net pay does not equal gross pay – deductions”)
10.3 Excel Problem
The Moose Wings Cooperative Flight Club owns a number of airplanes and gliders. It
serves fewer than 2,000 members, who are numbered sequentially from the founder,
Tom Eagle (0001), to the newest member, Jacques Noveau (1368). Members rent the
flying machines by the hour, and all must be returned on the same day. The following
six records were among those entered for the flights taken on September 1, 2010:
Member #
Flight Date
MM/DD/YY
Plane Used
Takeoff time
Landing time
1234
09/10/10
G
6:25
8:46
4111
09/01/10
C
8:49
10:23
1210
09/01/10
P
3:42
5:42
0023
09/01/10
X
1:59
12:43
012A
09/01/10
P
15:32
0999
09/01/10
L
13:45