Unlock document.
This document is partially blurred.
Unlock all pages and 1 million more documents.
Get Access
CHAPTER 10
PROCESSING INTEGRITY AND AVAILABILITY CONTROLS
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.
Some students will already be familiar with this tool, others will not. Therefore, this brief
introductory tutorial may be useful as a hand-out prior to assigning the Excel questions in this
chapter.
The Data Validation tool is found on the “Data” tab, as shown below:
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):
Clicking on the drop-down arrow in the “allow” box yields the following choices:
• Decimals allowed (but not required)
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.
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
If the “In-cell dropdown” box is not checked, users will still be restricted to entering values from
If the “In-cell dropdown” box is
checked, the values will appear in a
drop-down list when a user clicks
on that cell.
The list of permitted values in the
drop-down box can be found in the
portion of the spreadsheet as
indicated in the “Source” box
Choosing either Date, Time, or Text Length yields the same set of choices as for “whole numbers”
or “decimals”, making it easy to create limit checks, range checks, size checks, etc.:
• 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
Which yields the following:
Finally, the “Error Alert” tab can be used to create a meaningful error message whenever user data
violates the constraints:
choices:
1. Stop – the user is prohibited from inputting the erroneous data
anyway.
3. Information – the user is informed that the data is not valid. Clicking OK results in the data
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?
18. If it is true, then it proceeds to evaluate the second if statement. If the value entered is
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
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?
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
times.
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
When two numbers are transposed, the difference between the original number and the
transposed number is divisible by 9 except when the two digits have the same value.
