Accounting Information
Systems
10-41
m. The clerk entering details about a large credit sale mistakenly typed in a
nonexistent account number. Consequently, the company never received
payment for the items.
Check digit verification on each customer account number
n. A customer filled in the wrong account number on the portion of the invoice
being returned with payment. Consequently, the payment was credited to
another customer’s account.
o. A batch of 73 time sheets was sent to the payroll department for weekly
processing. Somehow, one of the time sheets did not get processed. The mistake
was not caught until payday, when one employee complained about not receiving
a paycheck.
Batch totals would have caught this.
q. Sunspot activity resulted in the loss of some data being sent to the regional office.
The problem was not discovered until several days later when managers
attempted to query the database for that information.
Parity checks and checksums will test for data transmission errors.
Ch. 10: Information Systems Controls for Systems Reliability – Part 3: Processing Integrity and
Availability
10-42
10.8 MonsterMed Inc. (MMI) is an online pharmaceutical firm. MMI has a small
systems staff that designs and writes MMI’s customized software. The data center is
installed in the basement of its two-story headquarters building. The data center is
equipped with halon-gas fire suppression equipment and an uninterruptible power
supply system.
The computer operations staff works a two-shift schedule, five days per week.
MMI’s programming staff, located in the same building, has access to the data
center and can test new programs and program changes when the operations staff is
not available. Programmers make changes in response to oral requests by employees
using the system. Since the programming staff is small and the work demands have
increased, systems and programming documentation is developed only when time is
available. Backups are made whenever time permits. The backup files are stored in
a locked cabinet in the data center. Unfortunately, due to several days of heavy
rains, MMI’s building recently experienced serious flooding that destroyed not only
the computer hardware but also all the data and program files that were on-site.
a. Identify at least five weaknesses in MonsterMed Inc.’s backup and DRP
procedures.
1. No written backup.
2. No written disaster recovery plan.
3. Backups are not done on a regular basis.
b. Evaluate change controls at MonsterMed Inc.
1. There does not appear to be a separate testing and development system, so changes
are made directly in the production system.
Accounting Information
Systems
10-43
10.9 Excel Problem
Create data validation rules in a spreadsheet to perform each of the following controls:
a. Limit check – that values in the cell are < 70
b. Range check – that values in the cell are between 15 and 65
Ch. 10: Information Systems Controls for Systems Reliability – Part 3: Processing Integrity and
Availability
10-44
c. Sign check – that values in the cell are positive
d. Field check – that values in a cell are only numeric
Accounting Information
Systems
10-45
e. Size check – that cell accepts no more than 40 characters of text
f. Reasonableness check – that cell’s value is less than 75% of cell to its left
Accounting Information Systems
If the preceding data validation rule was applied to cell C7, the spreadsheet would look like this:
And clicking the drop-down arrow would display the following:
Ch. 10: Information Systems Controls for Systems Reliability – Part 3: Processing Integrity and Availability
10-48
10.10 Excel Problem
Creating and testing check digits.
a. Create a spreadsheet that will take as input a five-digit account number and calculate a check digit using this formula: (5
x left-most digit + 4 x next digit + 3 x third digit + 2 x fourth digit + fifth digit) modulus division by 7. (Modulus division
returns the remainder – for example: 11 modulus division by 3 = 2). The check digit then becomes the 6th (right-most)
digit in the account number. Your spreadsheet should look like this:
Accounting Information Systems
10-49
case, the number is a formula (5 x the first digit in cell C4 plus 4 x the second digit in cell D4 plus 3 x the third digit in cell E4
plus 2 x the second digit in cell F4 plus the last digit from cell G4). This result is then divided by 7, and the MOD function
Students should use the text formulas (LEFT and RIGHT) plus the VALUE formula to parse the raw account number from
column B in order to automatically fill in columns C through G as follows:
• Column C: =VALUE(LEFT(B4)). The LEFT function with one argument is used to return the left-most digit from
reference cell (B4). The result is a text value of 1. Then the VALUE function converts that text into the number 1.
• Column E: =VALUE(LEFT(RIGHT(B4,3))). The combination of LEFT and RIGHT functions is used to return the
third digit in reference cell (B4). The RIGHT function can take two arguments: the cell containing the numeric value
to be manipulated (in this case B4) and the number of digits, beginning with the rightmost one, to return. In this case,
it returns the 3 right-most digits: 345. Next, the LEFT function lops off the left-most digit in that string, yielding text
string of “3”. Finally, the VALUE function converts that text into the number 3.
Ch. 10: Information Systems Controls for Systems Reliability – Part 3: Processing Integrity and Availability
the reference cell (B4). In this case, it returns the text string of “5”. Then the VALUE function converts that text into
the number 5.
b. Add another panel to the spreadsheet that takes as input a six-digit account number and uses the check digit formula
in part a to test whether or not the account number is valid. Your solution should look like this:
Accounting Information Systems
10-51