Unlock document.
This document is partially blurred.
Unlock all pages and 1 million more documents.
Get Access
Accounting Information
Systems
10-21
a. Identify and describe any errors in the data.
Five of the six records contain errors as follows:
1st - Wrong date is used (September 10 instead of September 1).
b. For each of the five data fields, suggest one or more input edit controls that could be
used to detect input errors.
Field 1 - Member number:
• Range check to verify that the field contains only four digits within the range of
0001 to 1368.
• Validity check on member number if a file of valid member numbers is
maintained.
Field 3 - Plane used:
• Validity check that character is one of the legal characters to describe a plane (G,
C, P, or L).
• Field check to verify that only a single character is used.)
Ch. 10: Information Systems Controls for Systems Reliability – Part 3: Processing Integrity and
Availability
10-22
c. Enter the data in a spreadsheet and create appropriate controls to prevent or at
least detect the input errors.
Field 1 - Member number:
• Range check to verify that the field contains only four digits within the range of
0001 to 1368.
Using the Data Validation tool in Excel (under the Data tab) this range check
could be programmed as follows:
Alternatively, the following IF statement would do the same thing:
=IF(AND(A4>0,A4<1369),””,”Error: Values must be between 1 and 1368”)
The first argument tests whether the cell value for member numbers is a whole
Accounting Information
Systems
10-23
• Validity check on member number if a file of valid member numbers is
maintained.
Using the data validation tool, the validity check would be programmed as
follows:
This tools says that the value input must match a list of legal values that are found
in cells A4:A7 (which would hold the values C, G, L and P)
Alternatively, the following IF statement would perform the same test:
Ch. 10: Information Systems Controls for Systems Reliability – Part 3: Processing Integrity and
Availability
10-24
Field 2 - Date of flight start:
• Check that day, month, and year correspond to the current date.
In the data validation tool, you would select the cells you want to test and enter
the date value you want to compare to, as follows:
Accounting Information
Systems
10-25
Field 3 - Plane used:
• Validity check that character is one of the legal characters to describe a plane (G,
C, P, or L).
This tools says that the value input must match a list of legal values that are found
in cells A4:A7 (which would hold the values C, G, L and P)
Alternatively, the following IF statement would perform the same test:
Ch. 10: Information Systems Controls for Systems Reliability – Part 3: Processing Integrity and
Availability
10-26
• Check that only a single character is used. (field check)
Alternatively, the following IF statement also checks this:
=IF(LEN(S4)=1,"","Plane character must contain only one character")
Accounting Information
Systems
10-27
Field 4 - Time of take off:
• Field check to verify that the field contains valid time format.
Field 5 - Time of landing:
• Field check to verify that the field contains valid time format.
Same as for field 4
Ch. 10: Information Systems Controls for Systems Reliability – Part 3: Processing Integrity and
Availability
10-28
d. Suggest other controls to minimize the risk of input errors.
• prompting to request each required input item.
• preformatting to display an input form including all required input items.
(SMAC Examination, adapted)
10.4 The first column in Table 10-3 lists transaction amounts that have been summed to
obtain a batch total. Assume that all data in the first column are correct. Cases a
through d each contain an input error in one record, along with a batch total
computed from that set of records.
Accounting Information
Systems
10-29
Analysis of these differences:
a. The difference of $9 is evenly divisible by 9, which suggests the possible
transposition of adjoining digits in the hundredths and tenths columns. More careful
inspection indicates that the amount $1,978.95 from the correct transactions
calculation was incorrectly transposed to $1,987.95 in the Case A calculation.
c. The difference of $8,800.00 is not divisible evenly by 9, which rules out a
transposition error. The difference affects multiple columns, which rules out a single
transcription error. The difference amount is not equal to any of the entries in the
correct transactions batch total calculation, which rules out an error of omission.
Dividing the difference by 2 gives $4,400.00, which is one of the entries in the correct
transactions column. More careful inspection reveals that this amount has been
inadvertently subtracted from the Case C batch total calculation rather than added.
10.5 Excel Problem
Create a spreadsheet with the following columns:
• Plaintext character
• ASCII code (7-bits, binary number)
• First bit
• Second bit
Ch. 10: Information Systems Controls for Systems Reliability – Part 3: Processing Integrity and
Availability
10-30
a. Enter the 26 letters a-z (lowercase) and the ten digits (0-9) in the plaintext
column
b. The ASCII column should convert the plaintext character to the binary code
Accounting Information Systems
The solution should look like this:
NOTE: Tell students that one of the objectives of this exercise (besides illustrating how parity bits work) is for them to explore the large
number of built-in Excel functions. You may wish to provide one or two examples from the solution to get them started.
Ch. 10: Information Systems Controls for Systems Reliability – Part 3: Processing Integrity and Availability
10-32
Functions used to populate columns in the solution:
Column b: converting the ASCII character in column A to its binary equivalent. This is accomplished by using the DEC2BIN and CODE
functions: =DEC2BIN(CODE(A2))
• The DEC2BIN function is one of Excel’s built-in Engineering functions. It transforms a number, in this case the result
of the CODE function, into binary (0s and 1s):
Accounting Information Systems
10-33
Columns C-I: the individual bits in the binary string. These are found using Excel’s Text functions as follows:
• Column D: =VALUE(LEFT(RIGHT(B2,6))). The combination of LEFT and RIGHT functions is used to return the
second digit from the left in the binary number 1100001. The RIGHT function can take two arguments: the cell
containing the numeric value to be manipulated (in this case B2) and the number of digits, beginning with the
rightmost one, to return. In this case, it returns the 6 right-most digits: 100001. Next, the LEFT function lops off the
left-most digit in that string, yielding text string of “1”. Finally, the VALUE function converts that text into the
number 1.
• Column F: =VALUE(LEFT(RIGHT(B2,4))). The combination of LEFT and RIGHT functions is used to return the
fourth digit in the binary number 1100001. The RIGHT function can take two arguments: the cell containing the
numeric value to be manipulated (in this case B2) and the number of digits, beginning with the rightmost one, to
Ch. 10: Information Systems Controls for Systems Reliability – Part 3: Processing Integrity and Availability
10-34
fifth digit in the binary number 1100001. The RIGHT function can take two arguments: the cell containing the
numeric value to be manipulated (in this case B2) and the number of digits, beginning with the rightmost one, to
return. In this case, it returns the 3 right-most digits: 001. Next, the LEFT function lops off the left-most digit in that
string, yielding text string of “0”. Finally, the VALUE function converts that text into the number 0.
COLUMN J: the number of bits with the value 1. Since columns C through I contain either the number 1 or the number 0, a
simple SUM(C:I) yields the number of bits with the value of 1.
=IF(ISODD(J2),1,0)
The ISODD function tests whether the value in cell J2 is odd. If it is, the IF function evaluates to true and displays a 1 in
column K. If the ISODD function is false, the IF function returns the value 0.
COLUMN L: The objective here is to calculate the parity bit value for odd parity. Odd parity means that there should be an odd
number of bits, including the parity bit, that have a value of 1. Therefore, if the value in column J is even (there are an even
Accounting Information Systems
to determine whether a number is even. The ISEVEN function returns a value of “True” if the reference cell is an even number
and false otherwise. Therefore, the following IF function can be used to calculate the parity bit value assuming we want odd
parity:
=IF(ISEVEN(J2),1,0)
The ISEVEN function tests whether the value in cell J2 is even. If it is, the IF function evaluates to true and displays a 1
in column L so that the resulting 8-digit binary number contains an odd number of bits set to value of 1. If the ISEVEN function
is false, the IF function returns the value 0 for the parity bit.
Adjustment for special characters:
Note that the five special characters (? ! % & ;) have only 6-digits to begin with (column B). Therefore, columns H and I
Accounting Information Systems
10.6 The ABC Company is considering the following options for its backup plan:
1. Daily full backups:
• Time to perform backup = 60 minutes
2. Weekly full backups plus daily incremental backup:
• Same time, storage, and restoration as above to do a weekly backup on Friday,
plus
3. Weekly full backups plus daily differential backup:
• Same time, storage, and restoration as above to do a weekly backup on Friday,
plus
o Time to perform daily backup = 10 minutes first day, growing by 5 minutes
each day thereafter
Accounting Information
Systems
10-37
Solution: Management must weigh the trade-offs shown below.
Full daily backups take the most time to perform and require most storage, but in the event of a
disaster have the quickest restore time.
Type of Backup
Plan
Time spent weekly to
backup
Storage
requirements
Time to Restore
Option 1: Full Daily Backup
Option 2: weekly full backup plus daily incremental backup
Full Weekly
Backup on Friday
60 Minutes
50 GB
30 Minutes to restore
last full backup
Option 3: weekly full backup plus daily differential backup
Full Weekly
Backup
60 Minutes
50 GB
30 Minutes to restore
last full backup
Ch. 10: Information Systems Controls for Systems Reliability – Part 3: Processing Integrity and
Availability
10-38
10.7 Which control(s) would best mitigate the following threats?
a. The hours worked field in a payroll transaction record contained the value 400
instead of 40. As a result, the employee received a paycheck for $6,257.24 instead
of $654.32.
A limit check on hours worked. The limit would have to be higher than 40 (such as 55
b. The accounts receivable file was destroyed because it was accidentally used to
update accounts payable.
All files should have header labels to identify their contents, and all programs should
c. During processing of customer payments, the digit 0 in a payment of $204 was
mistakenly typed as the letter “O.” As a result, the transaction was not processed
correctly and the customer erroneously received a letter that the account was
delinquent.
A field check should be performed to check whether all characters entered in this
field are numeric.
d. A salesperson mistakenly entered an online order for 50 laser printers instead of
50 laser printer toner cartridges.
A reasonableness test of quantity ordered relative to the product if 50 is an unusually
large number of monitors to be ordered at one time.
Accounting Information
Systems
10-39
e. A 20-minute power brownout caused a mission-critical database server to crash,
shutting down operations temporarily.
An uninterruptible power system should be used to provide a reserve power supply in
the event of power failure. The UPS should at a minimum allow enough time for the
f. A fire destroyed the data center, including all backup copies of the accounts
receivable files.
FILES: A backup copy of the files should be stored off-site.
g. After processing sales transactions, the inventory report showed a negative
quantity on hand for several items.
A sign test of quantity on hand.
h. A customer order for an important part did not include the customer’s address.
Consequently, the order was not shipped on time and the customer called to
complain.
Ch. 10: Information Systems Controls for Systems Reliability – Part 3: Processing Integrity and
Availability
i. When entering a large credit sale, the clerk typed in the customer’s account
number as 45982 instead of 45892. That account number did not exist. The
mistake was not caught until later in the week when the weekly billing process
was run. Consequently, the customer was not billed for another week, delaying
receipt of payment.
Check digit verification on each customer account number
j. A visitor to the company’s Web site entered 400 characters into the five-digit Zip
code field, causing the server to crash.
A size check would prevent 400 characters from being entered into a field that allows
for only 5 characters.
k. Two traveling sales representatives accessed the parts database at the same time.
Salesperson A noted that there were still 55 units of part 723 available and
entered an order for 45 of them. While salesperson A was keying in the order,
salesperson B, in another state, also noted the availability of 55 units for part 723
and entered an order for 33 of them. Both sales reps promised their customer
next-day delivery. Salesperson A’s customer, however, learned the next day that
the part would have to be back-ordered. The customer canceled the sale and
vowed to never again do business with the company.
Concurrent update controls protect records from errors when more than one salesman
l. The warranty department manager was upset because special discount coupons
were mailed to every customer who had purchased the product within the past 3
years, instead of to only those customers who had purchased the product within
the past 3 months.
