CHAPTER 9

AUDITING THE REVENUE CYCLE

REVIEW QUESTIONS

1. What document initiates the sales process?

Response: A customer order, usually in the form of a purchase order, initiates the sales process.

2. Distinguish among a packing slip, a shipping notice, and a bill of lading.

Response: The packing slip travels with the goods to the customer, and it describes the contents
on the order. Upon filling the order, the shipping department sends the shipping notice to the
billing department to notify it that the order has been filled and shipped. The shipping notice
contains additional information that the packing slip may not contain, such as shipment date,
carrier, and freight charges. The bill of lading is a formal contract between the seller and the
transportation carrier; it shows legal ownership and responsibility for assets in transit.

3. What are three input controls?

Response:
 Authorization procedures
 Credit check procedures
 Validation controls
 Batch controls

4. What are the three rules that ensure that no single employee or department processes a
transaction in its entirety?
Response: The three rules that ensure segregation of functions are as follows:
a. Transaction authorization should be separate from transaction processing.
b. Asset custody should be separate from asset record keeping.
c. The organization structure should be such that the perpetration of a fraud requires
collusion between 2 or more individuals.

5. What is automation, and why is it used?

Response: Automation involves using technology to improve the efficiency and effectiveness of
a task. Automation of the revenue cycle is typically used to reduce overhead costs, make better
credit granting decisions, and collect outstanding accounts receivable better.

6. What is the objective of integration?

Response: The objective of integration is to improve operational performance and reduce costs by
identifying and eliminating nonvalue-added tasks.

7. Distinguish among an edit run, sort run, and update run.

Response: An edit run is the first run; it detects most data entry errors. Only clear data progresses
to the sort run. The sort run sequences the transaction records according to its primary key field
and possibly a secondary key field. Once the data are sorted, the update program posts the
transactions to the appropriate corresponding records in the master file. During a sequential
update, each record is copied from the original master file to the new master file, regardless of the
effect on the balance.

8. How is the record’s primary key critical in preserving the audit trail?
Response: The primary key provides the link between the magnetic records stored on a computer
disk and the physical source documents and business events that they represent. In database
systems the primary key of one table’s record is the embedded foreign key in related table
records. For example, the invoice primary key (Invoice Number) is a foreign key in the related
line item detail records, thus forming an audit trail.

9. What are the advantages of real-time processing?

Response: Real-time processing greatly shortens the cash cycle of the firm. Lags inherent in
traditional systems can cause delays of several days between taking an order and billing the
customer. Real-time processing can give a firm a competitive advantage in the marketplace.
Manual procedures tend to produce clerical errors, such as incorrect account numbers, invalid
inventory numbers, and price–quantity extension miscalculations. Real-time processing reduces
the amount of paper documents in a system.

10. Why does billing receive a copy of the sales order when the order is approved but does
not bill until the goods are shipped?
Response: The billing department’s receipt of the sales order occurs in most instances before the
goods are actually shipped; thus, the economic event is not complete. Since credit checks need to
result include credit already extended for orders not yet shipped, the billing department (and other
departments) receive copies of the sales order once credit is approved. Of course, some of the
goods may not be available to ship; thus, the customer is not billed until the shipping department
sends the shipping notice to the billing department.

11. How do tests of controls relate to substantive tests?

Response: Tests of controls and substantive tests are auditing techniques used for reducing total
audit risk. Substantive tests traditionally follow tests of controls because the results of tests of
controls are used to determine the nature, timing, and extent of the substantive tests.

12. After which event in the sales process should the customer be billed?
Response: Billing occurs after the product is shipped to the customer (and the shipping
department sends a shipping notice to the billing department).

13. What is a bill of lading?

Response: A bill of lading is a formal contract between the seller and the shipping company
(carrier) to transport the goods to the customer. The bill of lading establishes legal ownership and
responsibility for assets in transit.

14. What document initiates the billing process?

Response: The billing process is initiated by the shipping notice, which signals the shipment of
the goods to the buyer.

15. Where in the cash receipts process does supervision play an important role?
Response: Supervision plays an important role in the mail room where both the check (asset) and
remittance advice (accounting record) are in the hands of one person. Mail room fraud can result,
which involves stealing the check and destroying the remittance advice to cover the theft.

16. List the revenue cycle audit objectives derived from the “existence or occurrence”
management assertion.
Response:
 Verify that the accounts receivable balance represents amounts actually owed to the
organization at the balance sheet data.
 Establish that revenue from sales transactions represent goods shipped and services
 rendered during the period covered by the financial statements.

17. List the revenue cycle audit objectives derived from the “completeness” management
assertion.
Response:
 Determine that all amounts owed to the organization at the balance sheet date are reflected
in accounts receivable.
 Verify that all sales for shipped goods, all services rendered, and all returns and allowances
for the period are reflected in the final statements.

18. List the revenue cycle audit objectives derived from the “accuracy” management
assertion.
Response:
 Verify that revenue transactions are accurately computed and based on current prices and
correct quantities.
 Ensure that AR subsidiary ledger, the Sales Invoice file, and the Remittance file are
mathematically correct and agree with the general ledger accounts.

DISCUSSION QUESTIONS

1. Distinguish among the sales, billing, and accounts receivable departments. Why can’t the
sales or accounts receivable departments prepare the bills?
Response: The principles of segregation of duties apply to these departments.
The sales order department (included in the sales department in the text) is responsible for taking
the customer order and placing it into a standard format. This department records information
such as the customer’s name, address, account number, quantities and units of each item,
discounts, freight preferences, etc. The sales order processing may, in some instances, play a role
in verifying or determining the promised shipping date. The billing department receives a copy of
the sales order from the sales department. Upon receipt of the shipping notice and the stock
release documents, the billing department prepares the sales invoice, which is the customer’s bill
reflecting charges for items shipped, which may be different from items ordered, taxes and freight
and any discounts offered. The sales order department should not prepare the bills because the
salespeople may bill their favorite clients less than they should be billed. The salespeople place
the order and thus start the wheels in motion for inventory to be shipped. Further, the salespeople
should not be allowed to determine how much the customers pay for their inventory, because they
may be tempted to charge lower prices and receive kickbacks.
The accounts receivable department receives the sales orders and posts them to the accounts
receivable subsidiary ledger. As remittance advices are received, they are posted to the
customer’s account in the accounts receivable subsidiary ledger. The accounts receivable
department should not be allowed to prepare the bills because this department has custody over
the accounts receivable assets. It records customer payments and tracks customers’ unpaid bills.
If it was allowed to prepare the bills, it might not bill certain customers and receive a kickback
from the customers for the free goods.

2. Explain the risks associated with mailroom procedures.

Response: The checks received in payment for accounts receivable are a crucial asset for the
firm. The mailroom is a point of exposure for any firm. The individual who opens the mail has
access both to cash (the asset) and to the remittance advice (the record of the transaction). A
dishonest employee may use this opportunity to steal the check, cash it, and destroy the
remittance advice, thus leaving no evidence of the transaction. Ultimately, this sort of fraud will
come to light when the customer receives another bill and, in response, produces the canceled
check. However, by the time the firm gets to the bottom of this problem, the perpetrator may have
committed the crime many times over and left the organization. Detecting crimes after the fact
accomplishes little. Prevention is the best solution. The deterrent effect of supervision can
provide an effective preventive control.

3. How could an employee embezzle funds by issuing an unauthorized sales credit memo if
the appropriate segregation of duties and authorization controls were not in place?
Response: An employee who has access to incoming payments, either cash or check, as well as
the authorization to issue credit memos may pocket the cash or check of a payment for goods
received. The employee could then issue a credit memo to the customer’s account so that the
account does not show a balance due.

4. What task can the accounts receivable department engage in to verify that all checks sent
by the customers have been appropriately deposited and recorded?
Response: The company should periodically, perhaps monthly, send an account summary to each
customer listing invoices and amounts paid by check number and date. This form allows the
customer to verify the accuracy of the records. If any payments are not recorded, the customer
can notify the company of the discrepancy. These reports should not be handled by the accounts
receivable clerk or the cashier.

5. Why is access control over revenue cycle documents just as important as the physical
control devices over cash and inventory?
Response: Access control to the billing and accounts receivable records that are part of the
revenue cycle is just as important as the physical control devices over cash and inventory because
these records affect the collectability of an asset—accounts receivable—that should eventually be
converted into cash. If these records are not adequately controlled, inventory may not be
ultimately converted into the cash amount the firm deserves.

6. For a batch processing system using sequential files, describe the intermediate and
permanent files that are created after the edit run has successfully been completed when
processing the sales order file and updating the accounts receivable and inventory master
files.
Response: After the edit program tests each record for clerical or logical errors, it writes the
correct records to a clean transaction file and the records with errors to an error file. Both of these
files are considered intermediate or temporary files. The error file is considered to be a temporary
file because the records will be resubmitted after they have been reviewed and corrected. The
clean transaction file is considered to be an intermediate file because it will be sorted and written
to a new tape. The old, unsorted transaction file will not be used in further processing once a
sorted transaction file is made.

7. Why has the use of magnetic tapes as a storage medium declined in recent years? What
are their primary uses currently?
Response: Both tapes and disks can be used as the physical storage medium for such systems.
However, the use of tapes has declined considerably in recent years. For day-to-day operations,
tapes are inefficient because they must be physically mounted on a tape drive and then
dismounted when the job ends. This approach is labor intensive and expensive. The constant
decline in the cost of disk storage in recent years has eliminated the economic advantage once
associated with tapes. Most organizations that still use sequential files store them on disks that are
permanently connected (online) to the computer system and require no human intervention. The
operational features of sequential files described earlier are the same for both tape and disk
media. Today, tapes are used primarily as backup devices and for storing archive data. For these
purposes, they provide an efficient and effective storage medium for a large system.

8. Discuss both the tangible and intangible benefits of real-time processing.

Response: Real-time processing yields the following tangible benefits. Real-time processing
greatly shortens the cash cycle of the firm. Lags inherent in traditional systems can cause delays
of several days between taking an order and billing the customer. Real-time processing can give a
firm a competitive advantage in the marketplace. Manual procedures tend to produce clerical
errors, such as incorrect account numbers, invalid inventory numbers, and price–quantity
extension miscalculations. Real-time processing reduces the amount of paper documents in a
system. Further, real-time processing may also achieve the following intangible benefits. It
may increase customer satisfaction through faster operation response time to requests and
inquiries, decreased lag time between order date and shipment date, and more accurate records
with fewer corrections requested.

9. Distinguish between positive and negative confirmations.

Response: Positive confirmations ask recipients to respond whether their records agree or
disagree with the amount stated. Positive confirmations are particularly useful when the auditor
suspects that a large number of accounts may be in dispute. They are also used when confirming
unusual or large balances or when a large proportion of total accounts receivable arises from a
small number of significant customers. A problem with positive confirmations is poor response
rate. Customers who do not dispute the amount shown in the confirmation letter may not respond.
The auditor cannot assume, however, that lack of response means agreement. To obtain the
highest response rate possible, second and even third requests may need to be sent to non-
respondents.
Negative confirmations request the recipients to respond only if they disagree with the amount
shown in the letter. This technique is used primarily when accounts receivable consist of a large
number of low-value balances and the control risk of misstatement is considered to be low. The
sample size for this type of test is typically large and may include the entire population. Evidence
from non-returned negative confirmations selected from a large population provides indirect
evidence to support the auditor’s expectation that accounts receivable are not materially
misstated. Responses to negative confirmations, particularly if they are widespread in a large
population, may indicate a potential problem. Since the negative confirmations approach does not
prove that the intended recipients actually received and reviewed the confirmation letters,
evidence of individual misstatements provided by returned responses cannot be projected to the
entire population. In other words, responses to negative confirmations cannot be used as a basis
for determining the total dollar amount of the misstatement in the account. Such evidence can be
used, however, to reinforce the auditor’s prior expectation that the account balance may be
materially misstated and that additional testing of details is needed to determine the nature and
amount of the misstatement.

10. What is the purpose of analytical reviews in the audit of revenue cycle accounts?
Response:
Auditors often precede substantive tests of details with an analytical review of account balances.
In the case of the revenue cycle, an analytical review will provide the auditor with an overall
perspective for trends in sales, cash receipts, sales returns, and accounts receivable. In addition,
analytical procedures can provide assurance that transactions and accounts are reasonably stated
and complete and may thus permit the auditor to reduce substantive tests of details on these
accounts.
11. Explain the open-invoice system. What effect might it have on confirmation responses?
Response: Under this approach, invoices are recorded individually rather than summarized or
grouped by creditor. In this environment, no accounts payable subsidiary ledger exists. Each
invoice is paid (closed) as it comes due. For financial reporting purposes, total accounts payable
is calculated simply by summing the open (unpaid) invoices. Determining the liability due to a
particular creditor, which may consist of multiple open invoices, is not such a simple task. The
auditor should not assume that an organization that uses this approach would invest the time
needed to respond to the confirmation request. The confirmation process may thus be ineffective.

12. What financial statement misrepresentations may result from an inconsistently applied
credit policy? Be specific.
Response:
 Accounts receivable may be overstated because allowance for doubtful accounts is
understated due to poor credit policy.
 Bad debt expense may be understated.

13. Give three examples of access control in a POS system.

Response: The following are examples:
 Steel cables to secure expensive leather coats to the clothing rack.
 Locked showcases to display jewelry and costly electronic equipment.
 Magnetic tags attached to merchandise, which will sound an alarm when removed from the
store.
 Password log-in to cash registers.

Note to Instructor: Some physical security devices could also be classified as supervision.

14. What makes POS systems different from revenue cycles of manufacturing firms?
Response: In point-of-sale systems, the customer literally has possession of the items purchased,
thus the inventory is in-hand. Typically, for manufacturing firms, the order is placed and then the
good is shipped to the customer at some later time period. Thus, updating inventory at the time of
sale is necessary in point-of-sale systems since the inventory is changing hands, while it is not
necessary in manufacturing firms until the goods are actually shipped to the customer.

15. Is a POS system that uses bar coding and a laser light scanner foolproof against
inaccurate updates? Discuss. Response: No, the bar-codes are not read with 100% accuracy.
Another potential error can occur if the wrong bar-coded stickers are attached to the merchandise,
which can occur in some discount retail stores that do not update the database; they just print out
bar-coded stickers and attach them to the merchandise. Devious customers may switch stickers on
price tags. In addition, an error can be made when entering the bar-codes into the system, thus the
wrong item rings up when the tag is scanned.
MULTIPLE CHOICE
1. c
2. a
3. a
4. c
5. b
6. d
7. c
8. e
9. a
10. d

PROBLEMS

1. Systems Description and Internal Controls

Customer checks and remittance advices are received in the mailroom each morning along
with the general mail. Mail volume is typically high requiring a mailroom staff of 40 clerks
and one supervisor. The clerks share equally in the task of sorting the mail and directing it to
the appropriate recipient. In the case of cash receipts the clerks open the envelopes to verify
that the checks are signed and are constant in amount with the remittance advice. The checks
and remittance advices are collected into batches and sent to the Accounts receivable
department where the AR clerk reviews them for correctness, posts to the AR-Sub ledger.
The clerk then prepares two copies of a remittance list. One of these is filed in the department
along with the remittance advice and the other is sent to the cash receipts department with the
checks. Finally the clerk summarizes the batch of cash receipts transaction and post to the
general ledger AR-Control account and the Cash account.
Upon receipt of the checks and remittance list, the Cash Receipts clerk reconciles the
documents and post the checks to the Cash Receipts journal. At the end of the day the clerk
prepares a deposit slip and sends it along with the checks to the bank.

Required:
a. Prepare a flowchart of the cash receipts procedures described
b. Describe the risks, if any, that are inherent in the current system configuration.
c. Describe the controls, if any, that are needed to reduce or eliminate the risks identified in
(b) above.

Response:
a) See flowchart on the next page

b. Describe the risks, if any, that are inherent in the current system configuration.
1) Risk of Cash misappropriation in mailroom fraud (skimming)
2) Risk of cash misappropriation by skimming, lapping, or other forms of larceny in the AR
department
3) Risk of incorrect record keeping in the AR Department

c. Describe the controls, if any, that are needed to reduce or eliminate the risks identified in
(b) above.
1) Supervision in the Mailroom is inadequate with one supervisor overseeing 40 clerks. This span
of control can be reduced by having customers submit their payment to a separate POX address.
The US mail service will then pre-sort and separate cash receipts from the general mail. The
smaller number of cash receipts can then be processed in a smaller mailroom area where fewer
clerks who work exclusively with cash receipts can be more effectively supervised.

2) A remittance list should be prepared under supervision in the mailroom.

Remittance Checks should be separated from remittance advices in the mailroom and not go to
the AR department.
3) The AR department should not have access to general ledger accounts

2. Internal Controls and Flowchart Analysis

Required:
a. Identify the physical control weaknesses depicted in the flowchart for Problem 2.
b. Describe the IT controls that should be in place in this system.
Response:

• No credit check is performed. Billing clerk should not record sales in the Sales Journal before
the economic event (shipping the goods) has occurred.
• Billing department bills customer before the goods are shipped and without confirmation of
shipment and quantity shipped. A shipping notice should trigger the billing process.
• Warehouse clerk, who controls the physical inventory, should not also maintain the inventory
subsidiary records.
• Warehouse clerk updates the Inventory subsidiary ledger and the GL Inventory Control.
• Accounting clerk updates AR subsidiary, and various GL accounts.

IT Controls for a centralized integrated system should include:

3. Flowchart Analysis
Use the flowchart for Problem 3 to answer these questions:
a. What accounting document is represented by symbol A?
b. What is an appropriate name for the department labeled B?
c. What would be an appropriate description for process C?
d. What is the location represented by symbol D?
e. What accounting record is represented by symbol E?
f. What is an appropriate name for the department labeled H?
g. What device is represented by symbol F?
h. What device is represented by symbol G?
i. What accounting record is represented by symbol G?

Response:
a. Cash prelist or remittance list
b. Cash Receipts department
 c. Post to Cash Receipts Journal and deposit checks or Process Cash Receipt
d. Bank
e. Cash Receipts Journal
f. Accounts Receivable Department
g. Computer terminal
h. Computer disk
i. Accounts Receivable file

4. Internal Control Evaluation

Identify the control weaknesses depicted in the flowchart for Problem 4.

Response:
 Sales clerk should not record sales in the Sales Journal before the economic event
(shipping the goods) has occurred, and the Billing Department should perform this role.
 No credit check is performed.
 Billing department bills customer before the goods are shipped and without confirmation
of shipment and quantity shipped. A shipping notice should trigger the billing process.
 Accounts Receivable should not process cash receipts and maintain the AR subsidiary
records.
 Warehouse clerk, who controls the physical inventory, should not also maintain the
inventory subsidiary records.
 The general ledger department should receive journal vouchers and account summaries
from AR, Cash Receipts, Billing, and Inventory control. Instead they inappropriately use
source documents to update GL accounts.

5. Risks and Internal Controls

Following describes the credit sales procedures for clothing wholesaler that sells name-brand
clothing to department stores and boutique dress shops. The company sells to both one-time and
recurring customers. A flowchart of the system is provided in the figure labeled Problem 5:
Internal Control.
Customer orders are received by fax and e-mail in the sales department. The sales clerk, who
works on commission, approves the credit sale, calculates commissions and discounts, and
records the sale in the sales journal from the PC in the sales department. The clerk then prepares a
sales order, a customer invoice, and a packing slip, which are sent to the accounting department
for processing. The accounting clerk updates the AR Subsidiary ledger and sends an invoice to
the customer. The clerk then forwards the sales order and packing slip to the Warehouse-Shipping
Department. The warehouse-shipping clerk picks the items from inventory and sends them and
the packing slip to the carrier for shipment to the customer. Finally the clerk updates the
inventory subsidiary ledger and files the sales order in the department.
Cash receipts from customers go to the mailroom, which has one supervisor overseeing 32
employees performing similar tasks: a clerk opens the envelope containing the customer check
and remittance advice, inspects the check for completeness, reconciles it with the remittance
advice, and sends the remittance advice and check to the accounting department. The accounting
department clerk reviews the remittance advice and the checks, updates the AR subsidiary ledger
and records the cash receipt in the cash receipts journal. At the end of the day, the clerk updates
the AR Control, Cash, and Sales accounts in the general ledger to reflect the day’s sales and cash
receipts.
Required

a. Describe the uncontrolled risks associated with this system as it is currently designed.

b. For each risk describe the specific internal control weakness(s) in the system that causes or
contributes to the risk.

RISKS CONTROL WEAKNESSES

Sales to un-creditworthy customers Sales clerk approves credit

Inaccurately recording the sales transactions in Sale is recorded when the sales clerk takes
journals the order rather than after it is shipped.

Misappropriation of cash Accounting department clerk has access to

the cash, the remittance advice, the AR sub –
ledger, and the General ledger. Opportunity
for embezzlement such as lapping

Mailroom span of control is wide (32

employees) for a single supervisor. This
inhibits close supervision. The mailroom
clerks, with access to the cash and
remittance advices, have an opportunity to
commit mailroom fraud.
Warehouse / shipping are combined allowing
Shipping customers the wrong items for no reconciliation between what is picked
and what is ordered and shipped.

Misappropriation of inventory Warehouse clerk has custody of inventory

and the inventory sub - Ledger
6. Segregation of Functions

Which, if any, of the following situations represent improper segregation of functions?

a. The billing department prepares the customers’ invoices, and the AR department posts to the
customers’ accounts.
b. The sales department approves sales credit memos as the result of product returns, and
subsequent adjustments to the customer accounts are performed by the AR department.
c. The shipping department ships goods that have been retrieved from stock by warehouse
personnel.
d. The general accounting department posts to the general ledger accounts after receiving journal
vouchers that are prepared by the billing department.

Response:
All are proper segregation of functions except b. The sales department should not be allowed to
approve credit memos since it could potentially overstate sales in one period to meet quotas and
boost bonuses and reverse them in a subsequent period. The receiving report indicating that goods
have been received by the receiving department should be the source document for credit memos
and it should be authorized by someone independent of the sales department.

7. Stewardship
Identify which department has stewardship over the following journals, ledgers, and files:
a. Customer open order file
b. Sales journal
c. Journal voucher file
d. Cash receipts journal
e. Inventory subsidiary ledger
f. AR subsidiary ledger
g. Sales history file
h. Shipping report file
i. Credit memo file
j. Sales order file
k. Closed sales order file

Response:
a. Sales
b. Billing
c. General ledger
d. Cash receipts
e. Warehouse/Inventory control
f. Accounts receivable
g. Sales
h. Shipping
i. Sales
j. Sales
k. Sales
8. Control Weaknesses
For the past 11 years, Elaine Wright has been an employee of the Star-Bright Electrical
Supply store. Elaine is a very diligent employee who rarely calls in sick and takes her vacation
days staggered throughout the year so that no one else gets bogged down with her tasks for more
than one day. Star-Bright is a small store that employs only four people other than the owner. The
owner and one of the employees help customers with their electrical needs. One of the employees
handles all receiving, stocking, and shipping of merchandise. Another employee handles the
purchasing, payroll, general ledger, inventory, and accounts payable functions. Elaine handles all
of the point-of-sale cash receipts and prepares the daily deposits for the business. Furthermore,
Elaine opens the mail and deposits all cash receipts (about 30 percent of the total daily cash
receipts). Elaine also keeps the AR records and bills the customers who purchase on credit.

Required:
a. Point out any control weaknesses you see in the above scenario.
b. List some recommendations to remedy any weaknesses you have found working under the
constraint that no additional employees can be hired.

Response:
a. Elaine performs many incompatible tasks. She opens the mail, deposits all cash and check
receipts, and keeps the accounts receivable records. She could easily keep checks and alter the
accounts receivable to cover her theft. Furthermore, she records the bills, so she could potentially
bill a customer, not record it in the books, and keep the money when the check is received. Even
more troublesome is the fact that she handles the point-of- sale receipts and prepares the daily
deposits, which are a substantial amount of sales (30 percent). Elaine never takes enough vacation
time so that anyone else can perform her duties long enough to check the books.
The employee who handles the inventory and accounts payable function also has incompatible
tasks. This employee could be making payments to a family or friend for inventory not received.
The employee who handles all receipts, stocking, and shipping of inventory is also performing
incompatible tasks and could be pilfering some inventory as it comes in and shipping it to himself
or herself.

b. Close supervision is needed for the employee working in the receiving, stocking, and shipping
department. This employee needs to be kept from stealing inventory. Close supervision should
help reduce this risk. Pre-numbered shipping forms that must be accounted for may deter this
employee from shipping any goods to himself or herself or friends. The accounting function
should be redistributed among the remaining two employees and close supervision should be
exercised. One possible reallocation of tasks would be:
Employee 1 Employee 2
• record point-of-sale receipts • prepare the daily cash deposits and
• update the accounts receivable reconcile to daily cash sales
account records open the mail and make a list of all
• prepare the bills for accounts incoming checks—prepare deposit
receivable accounts payable
• inventory • general ledger purchasing
• payroll

This system is not perfect and close supervision is important.

9. Internal Control
Iris Plant owns and operates three floral shops in Magnolia, Texas. The accounting functions have
been performed manually. Each of the shops has a manager who oversees the cash receipts and
purchasing functions for the shop. All bills are sent to the central shop and are paid by a clerk
who also prepares payroll checks and maintains the general journal. Iris is seriously considering
switching to a computerized system. With so many information systems packages on the market,
Iris is overwhelmed.

Required:
a. Advise Iris as to which business modules you think her organization could find beneficial.
b. Discuss advantages, disadvantages, and internal control issues.

Response: Iris needs to consider whether she wishes to purchase one microcomputer system or
three. Assuming that she wishes to purchase only one microcomputer for the central shop, she
should definitely consider an accounting software package that has an accounts payable and
general ledger module. The purchase of a payroll module will depend upon the number of
employees paid each period. Iris will need to determine if the time saved is worth the cost.
The payroll module may also help with year-end forms such as W-2’s and 1099’s. Iris may also
wish to consider centralizing the purchasing function in order to obtain quantity discounts by
placing larger orders. If she wishes to do this, then an inventory control module may be
appropriate. As the system is currently designed, Iris has no good way to determine whether the
managers are purchasing the right mix of inventory items, or if items are being used efficiently.
Floral shops, because of the perishability of inventory and need to respond to unexpected orders,
may not lend themselves to centralized purchasing and/or centralized inventory control.
If Iris wishes to purchase a computer for each store, she should consider purchasing software
that can process point-of-sale transactions and balance the cash receipts at the end of the day.
Inventory control software might be considered as it helps track the profitability and spoilage of
certain items as well as aids the managers in their purchasing decisions. The system could then
provide summary reports for Iris so that she may examine the inventory purchasing and usage
decisions of the managers.
The cash receipts system should provide better management over cash receipts errors than the
manual system, and if the correct controls are included, control may increase. For example, a
notice might be placed over the cash register saying “If you do not get a receipt from the
computer, your order is free.” The information system then will limit the possibility that a
customer pays cash and the employee or manager keeps the money by not ringing up the sale.
Iris may be able to find software packages specifically designed for florists. She should examine
them to see if they will suit her partially decentralized management. With the correct system, Iris
should see increased control over cash receipts and maybe even over inventory purchases and
usage. A disadvantage is that the managers may feel that they are being watched more closely and
this may cause some resentment.

10. Internal Control

You are investing your money and opening a fast-food Mexican restaurant that accepts only cash
for payments. You plan on periodically issuing coupons through the mail and in local
newspapers. You are particularly interested in access controls over inventory and cash.

Required:
a. Design a carefully controlled system and draw a document flowchart to represent it.
b. Identify and discuss the key control issues.
Response: The document flowchart is illustrated on the following page.
One key control issue is that all sales are recorded. Merchandise should not be given to a
customer and the sale not entered into the system: an employee could pocket the money received
for the sale. Another issue is reconciling physical coupons with the number of coupons entered
into the system. Again, an employee should not be able to ring up a sale at the lower price for a
customer without a coupon, charge the customer full price, and keep the difference. Another
concern is that employees will steal inventory by giving away free food to their friends and
relatives. The system should track all food items prepared and related waste. The flowchart
presented provides checks and balances for ensuring that employee theft of cash received from
customers is prevented.

11. Data Processing

The computer processing portion of a sales order system is represented by the Problem 11
flowchart presented in the text. Answer the following questions:

Required:
a. What type of data processing system is this? Explain, and be specific.
b. The auditor suggests that this system can be greatly simplified by changing to direct access
files. Explain the major operational changes that would occur in the system if this were done.
c. The auditor warns of control implications from this change that must be considered. Explain
the nature of the control implications.
d. Sketch a flowchart (the computerized portion only) of the proposed new system. Use correct
symbols and label the diagram.

Response:
a. This is a batch processing system that uses direct access storage devices rather than sequential
tapes, but the records are stored in sequential files. The transactions are keyed in, and the batch
totals are calculated. The batch totals accompany the sales order file through all the data
processing runs that follow. The edit run is used to test each record for the existence of clerical or
logical errors. Any records with errors are removed and written to an error file to be corrected
later by an authorized person. The batch totals are recalculated for the clean transactions. The
edited file is then sorted based on the primary customer account number. The batch totals are
used to verify the integrity of the sorted database file. The sorted file is used to update the
accounts receivable file. The original accounts receivable file remains intact and is used for
backup. The newly created accounts receivable becomes the new master file. The customers’
invoices are processed during the update and billing run. The sales order file is sorted on the
inventory number so that the inventory master file can be updated. The original inventory file
remains intact and is used for backup. The newly created inventory file becomes the new master
file. The batch totals are still used to verify the completeness and accuracy of the transaction file.
At the end of the day, the batch totals are sorted and used to update the general ledger. The
original general ledger remains intact and is used for backup. The newly created general ledger
becomes the new master file.

b. If the sequentially stored data files are not used and real-time processing of data using indexed
direct access files are used instead, the editing and sort runs are no longer necessary. Transactions
will be immediately checked for input errors, and the accounts receivable file and inventory files
will automatically be updated as each transaction is processed. Thus, batch totals will no longer
be used. Accumulators may be used to accumulate values that are periodically posted to the
general ledger. The accounts affected by each transaction in the master file will be overwritten
with each transaction that is processed.

c. Because the computers will be performing tasks that individuals used to perform, such as
inventory controls, the control procedures over program code need to be secured from
unauthorized access. The master files are overwritten each time a record is processed. Controls
should be put into place for periodically making a backup copy of the master file, so that the
records may be covered in case the current working copy of a master file is destroyed or
incorrectly overwritten.

d. See the following diagram.

12. System Configuration
The flowchart for Problem 12 represents the computer processing portion of a sales order system.
Answer the following questions.
a. What type of data processing system is this? Explain, and be specific.
b. The marketing manager suggests that this system can be greatly improved by processing
all files in real time. Explain the major operational changes that would occur in the
system if this were done.
c. The auditor warns of operational efficiency implications from this change that must be
considered. Explain the nature of these implications.
d. Sketch a flowchart of the proposed new system. Use correct symbols and label the
diagram.

Response:
a. This system uses real-time data collection and real-time updating of critical records (subsidiary
accounts that are unique to the transaction). General ledger accounts that are common to all
transactions are processed in batch mode.
b. As each transaction is received, all records associated with it will need to be updated
immediately. This would eliminate the batch-processing step and the sales order transaction file
from the current system. A new real-time update program will be required. Sales summaries,
currently prepared periodically, can now be extracted on demand by the marketing manager
directly from his/her terminal.
c. Updating all general ledger accounts in real-time may cause operational delays. Each customer
will need to wait until the previous customer’s transaction is completely processed, including
general ledger accounts that are common to all customers. The extent of the delays will depend in
part on transaction volume and the number of simultaneous transactions executed.
d. Please refer to the diagram on the following page.
Solution to problem 9-12
INTERNAL CONTROL PROBLEMS

1. Smith’s Market (Small Business POS Accounting System)

Required:
a. Create a data flow diagram (DFD) of the current system.
b. Create a system flowchart of the existing system.
c. Analyze the physical internal control weaknesses in the system. Model your response
according to the six categories of physical control activities specified in the COSO internal
control model.
d. Describe the IT controls that should be in place in this system.

a), b) See diagrams on the following pages.

c) Internal Control Weaknesses

1) Access to the cash drawers by sales clerks requires more accountability. Each drawer
is accessed by various clerks throughout the day and cash may be withdrawn by any
of them.

2) The internal cash register tape should be used as a control to determine how much
cash (including checks, and credit card vouchers) should be in the register drawer.

3) The shift supervisor does not sign for the specific amount of cash received or
returned at the end of the day. He simply logs the drawers in and out.

4) The treasury clerk is unsupervised in the counting of cash.

5) The treasury clerk has asset custody and responsibility for recording sales and cash in
the journal and General Ledger.

d) IT Controls
 Credit Check
Customer Credit
Card

Sales
Approval

Cash or Process Cash,

Check Sale Check,
Credit Card
Receipt Sales
Cash Receipt, Credit Card Signature Journal
Log,
Receipt
Count, Total
Cash Record
In/Out Sales
Amount General
Ledger
Deposit
Bank Slip
Cash,
File
Check,
Credit Card
Smith’s Market Sales Order Receipt and
Deposit Slip
DFD
Customer Sales and Treasury Clerk
Check Out Shift Supervisor

Bank 3
Cash
Card
/Credit Signs
Card
Proce 1 Cash
In Cash
ss and /
Credi
Cash Cash
t
Credi In/
/Credi
t Ou 4
Rece t Count,
ipt 2 Record,
and
Deposit Deposit
Slip cash Sales
Shift Supervisor
5
Jour
Cash
/ Gene
Credi ral
t
Depo
Smith’s Market sit
Sales System
Flowchart Bank
2. Discount Tools, Inc. (Networked Computer System with Manual Procedures)

Required
a. Create a data flow diagram of the current system.
b. Create a system flowchart of the existing system.
c. Analyze the physical internal control weaknesses in the system.
d. Describe the IT controls that should be in place in this system.
e. (Optional) Prepare a system flowchart of a redesigned computer-based system that resolves the
control weaknesses that you identified. Explain your solution.
Solution to Discount Tools

a), b), d) See diagrams on the following pages.

c) Internal Control Weaknesses

1) Transaction is recorded in Sales Journal before goods are shipped.

2) Warehouse and Shipping functions are combined. This removes control over picking and

shipping the wrong products.

3) Mail room clerk should prepare a remittance list to control remittance advices and checks

d) IT Controls
 Invoice
File

Sales Order
Sales
Journal

Pick Goods
Approval
Receive Sales Order Sales
/Rejection
Customer Order
Check Credit Prepare Sales
Order
Order

Ship Notice
Sales Order
Customer Order

File
Batch Totals
Sales Order
Approval/ Reconcile and
Rejection Bill Customer
Customer
Sales Order

Batch Totals File

Update
Check
Post to Cash General Ledger
Rec Journal
Check
Remit Advice
Batch Totals

Receive Bank
Customer Check
Payment
Invoice Copy

Remit Advice
Update Accts
Receivable

Premier Sports Revenue Cycle DFD

Discount Tool DFD
e) Student solutions to this part of the case will vary. The solution should address the control

issues identified in part C.

3. ABE Plumbing (Centralized Small Business Accounting System)

Required

a. Create a data flow diagram of the current system.

b. Create a system flowchart of the existing system.
c. Analyze the physical internal control weaknesses in the system.
d. (Optional) Prepare a system flowchart of a redesigned computer-based system that resolves the
control weaknesses that you identified. Explain your solution.

a), b) See diagrams on the following pages.

c) Internal Control Weaknesses
1) No Credit check is performed.
2) The sales clerk closes the open sales order causing the sale to be recorded before the
goods are actually shipped.
3) The warehouse clerk has asset custody and should not also update the inventory records.
4) The shipping clerk does not reconcile the stock release with the original order. This
allows for the wrong items and or quantities to be shipped.
5) Customer is billed before the goods are shipped. Billing should be triggered by shipping
notice. Instead, the customer invoice is printed from the closed sales order, which was
prepared before the goods were shipped.
 Invoice
1
Charg AR
Customer Bill es
File
Closed
Customer
Sub
Order
Customer Custome
r Data
Invoice
Invoic
Item,
quanti 2 e File
Prepare
Sales
Order
ty, Open/Cl
Order Custo
osedOrd
mer Update
GL
Open Order Accou
Item
Picked
Pick Stock
GL
Item
and Goods Release 1 Close File
Quantity SO

Inventory
Sub File BOL,
Stock Release 2 Ship Stock
Goods Release
Dept
File
B
O
L Carrier
ABE Plumbing DFD
d) Flowchart of revised system
Student responses will vary for this part of the assignment. The following issues,
however, need to be addressed.
 The internal control problems already covered that need to be corrected in
the new system.
 A system configuration similar to figure 4-18 would be appropriate.
4. TRV Classics (Manual and Stand Alone Computer Processing)

Required

a. Create a data flow diagram of the current system.

b. Create a system flowchart of the existing system.
c. Analyze the physical internal control weaknesses in the system.
d. (Optional) Prepare a system flowchart of a redesigned computer-based system that resolves the
control weaknesses that you identified. Explain your solution.

Solution to TVR Classics. a) and b) See diagrams on the following pages.

c) Internal Control Weaknesses

1) No credit check is performed before placing the order

2) The Sales Journal is updated before the goods are shipped. This can result in sales being
incorrectly matched to the period.

3) The warehouse clerk has access to inventory and also updates the inventory ledger. The clerk
may be capable of stealing inventory and covering up the theft by adjusting the inventory records.

4) Mailroom clerk has access to both the remittance advice and the checks, no remittance list is
prepared. This weakness can result in mailroom fraud through skimming cash and destroying the
remittance advice.

5) AR clerk has access to both the checks and the remittance advices. This can result in theft of
cash through skimming or lapping.
d) Student responses will vary for this part of the assignment, but should address the internal
control issues identified above.
5. Tight Lines Fishing and Camping Supplies (Networked Computer System and Manual
Procedures)

Required
a. Create a data flow diagram of the current system.
b. Create a system flowchart of the existing system.
c. Describe internal control weaknesses in the system and discuss the risks associated with these
weaknesses.
d. (Optional) Prepare a system flowchart of a redesigned computer-based system that resolves the
control weaknesses that you identified. Explain your solution.

Solution to Tight Lines Fishing and Camping

a, b, and d, see pages that follow
c) Internal Control Weaknesses. The following tie to the numbered circles on the flowchart.

1) The sales clerk performs the credit check this is a segregation of duties and transaction
authorization problem.

Risk: Clerk may grant credit to non-creditworthy customers

2) Warehouse should not update the inventory subsidiary and General ledger control accounts.
Multilevel security controls are needed to provide a separation of duties.

Risk: Clerk could steal inventory, adjust the subsidiary ledger, and adjust the GL control account
to cover the theft.

3) and 7) AR Clerk should not update the general ledger.

Risk: The ability to reconciliation the AR Sub Ledger and the AR Control account is diminished
when both are updated by the same person.

4) Billing and AR are combined.

Risk: This structure will mask discrepancies between what was billed and what was recorded as a
sale.

5) Supervision is needed in the mailroom.

Risk: Employees who open the mail have access to both cash and the remittance advice. This
increases the risk of mailroom fraud through skimming.

6) The cash receipts clerk has access to the assets (cash) and is responsible for updating the
general ledger.

Risk: The clerk could steal cash and adjust the cash account to cover the theft.
d)
6. Green Products Garden Supply (Stand-Alone PC-Based Accounting System)

Required
a. Create a data flow diagram of the current system.
b. Create a system flowchart of the existing system.
c. Analyze the physical internal control weaknesses in the system.
d. (Optional) Prepare a system flowchart of a redesigned computer-based system that resolves the
control weaknesses that you identified. Explain your solution.

Solution to Green Products Garden Supply

a), b), See diagrams on the following pages.

c) Internal Control Weaknesses

1) No credit check

2) Inventory control function is performed by warehouse clerk.

3) Accounting department bills customer, updates the AR account, and records sales in the
Sales Journal thus reducing the opportunity to detect discrepancies between total sales and
AR postings.

4) Customer is billed before order is actually shipped

5) Remittance List should be prepared in the mailroom

6) No journal voucher prepared by cash receipts clerk.

d) Flowchart of revised system

Student responses will vary for this part of the assignment. The following issues, however, need

to be addressed.

 Upgrade stand-alone computers to a networked environment

 The internal control problems already covered that need to be corrected in the new

system.
7. Performance Water Pumps (Centralized System with Distributed Terminals)

Required
a. Create a data flow diagram of the current system.
b. Create a system flowchart of the existing system.
c. Analyze the physical internal control weaknesses in the system.
d. Describe the IT controls that should be in place in this system.
e. (Optional) Prepare a system flowchart of a redesigned computer-based system that resolves the
control weaknesses that you identified. Explain your solution.

Solution to Performance Water Pumps

a), b) See diagrams on the following pages.

c) Internal Control Weaknesses

1) The sales clerk who processes the orders also performed the credit check. This creates
internal control problems as sales staff pay is sometimes linked to sales levels.

2) The shipping function does not notify the billing function that goods are shipped. Without
this necessary transaction authorization, customers could be billed before items are shipped which
leads to inaccurate record keeping.

3) The billing department records the accounts receivable and also prepares and sends the
AR summary to the general ledger function. The problem here is that the billing department also
prepares and sends to the general ledger function the sales journal voucher. This approach
eliminates the GL reconciliation function.

4) The inventory warehouse clerk updates the inventory records. This can lead to inventory
theft and concealment by adjusting the inventory records.

d) IT Controls
e) Student solutions will vary, but should address the internal control issues identified above
 8. Custom Fabrications (Stand-Alone PC Based Accounting System

Required
a. Create a data flow diagram of the current system.
b. Create a system flowchart of the existing system.
c. Analyze the physical internal control weaknesses in the system.
d. Describe the risks associated with these control weaknesses
e. (Optional) Prepare a system flowchart of a redesigned computer-based system that resolves
the control weaknesses that you identified. Explain your solution

Solution to Custom Fabrications

a), b) and e) See diagrams on the following pages.

c) Internal Control Weaknesses

1. The customer should not be billed until the goods are shipped. The billing process, however, is
triggered in this system by the sales order, rather than the shipping notice.

Risk: Billing before shipment occurs leads inaccurate record keeping and the possibility of
recording sales in the wrong period.
This activity can also damage customer relationships.

2. The billing process includes updating accounts receivable.

Risk: This prevents a meaningful independent verification between sales and AR by the general
ledger because both numbers are created in the same function.

3. Asset custody should be kept separate from record keeping. In this system, however, the
warehouse clerk has custody of inventory and also updates the inventory records.

Risk: The Warehouse clerk could steal inventory and cover the theft by adjusting the inventory
records.

4. The shipping department fails to reconcile the stock release with a sales order copy or the
packing slip.

Risk: The wrong product or quantities could be shipped to the customer. The shipping function
serves as an important independent verification checkpoint and is the last control point to
determine if the order is correct before the goods change hands.

5. The General ledger function updates the cash account and AR control account from a
remittance list. It should receive a journal voucher from the cash receipts function and a
summary of the AR subsidiary. The journal voucher plays an important audit trail role.

Risk: The GL accounts may be corrupted with unauthorized transactions.

 Customer Invoice Sales Details Sales Journal

Customer Order Copy

Customer Order
Update Accounts
Sales Order Copy 1 Bill Customer Invoice Copy Amount, Date AR Subsidiary
Receivable

Receive Order Sales Order Copy 3

Update Accounts
Summary of Sales Journal and AR Subsidiary
Receivable

Sales Order Copy 2

Schedule Production Order 2 Open
Production Order 1
Production Materials Requisition Production File

Open Sales
Order File Voucher

Production Order 2
Materials Requisition

Produce Goods Materials Used Update Inventory

Revenue Cycle DFD Production Order 2 Store Goods

Item, Quantity

CUSTOM FABRICATIONS
Inventory
Subsidiary
Stock Release

Bill of Lading Copy 3 Ship Goods Shipping Details Shipping Log

Carrier Bill of Ladings 1 & 2, Packing Slip

 Cash Amount

Open Mail, Prepare Update General

Customer Check, Remittance Advice Remittance List 3 Posting Details General Ledger
Remittance Advice Ledger

Check, Remittance List 1

Remittance List 2
Record and Deposit Remit Advice
Checks

Reconcile and
Payment Amount General Ledger
Update AR

Check, Deposit Slips 1 & 2

Bank

Cash Receipts DFD

CUSTOM FABRICATIONS
Cash Receipts System Flowchart

Mail Room Cash Receipts Billing General Ledger

Customer Remit List 1 B A

Check

Check Remit List 3

Remit List 2
Remit Advice Update AR Remit Advice
Subsidiary

Prepare Update AR Update General

Remittanc Cash Subsidiary Subsidiary Ledger
e List

Deposit Slip

Remit List 1 Deposit Slip AR Subsidiary General Ledger

Check Check

Remit List 2

Remit Advice

Remit List 3 Bank

B A
Will Richens
Revised Cash Receipts System Flowchart

Mail Room Cash Receipts Accounts Receivable Data Processing

Customer Remit List 1 A B

Check

Check
Remit List 2
Sales Order System
Remit Advice
Remit Advice
Update AR
Subsidiary, Prepare
Deposit Slips

Prepare Cash Subsidiary

Remittance
List
B Update AR
Subsidiary

General Ledger

Remit List 1
Deposit Slip
Check
Deposit Slip B AR Subsidiary
Remit List 2 Check

Remit Advice

Remit List 3

Bank

Temporary
Production
File
Will Richens
SOLUTIONS TO ACL ASSIGNMENTS

The AR and Customer files used for the following assignments are located in the
sampleproject.acl that accompanies ACL. The AR file is actually an invoice file that contains
several related records as designated by the Trans Type field:
IN = Sales invoice
PM = Payment from customer
CN = Credit note (Credit memo)
TR = Transfer (write off)
Sales invoices should be represented by positive Trans Amount values, while the other
transaction types are negative.
Some of the following assignments employ the ACL’s Relation and Join features. For
detailed information on the use of these and other commands, consult ACL’s online Help.

1. Open the AR file, Profile the data, and Stratify on the Trans Amount field. Print the Last
Results window and write an analysis providing possible explanations for the results
obtained.

As of: 02/21/2010 12:42:41

Command: PROFILE FIELDS Amount

Table: Ar

Field Name Total Value Absolute Value Minimum Maximum

Trans Amount 468,880.69 585,674.41 -3,582.98 5,549.19

2. Open the AR file, stratify the file on the Trans Amount field, and use the expression builder
to create filters that limit the strata to
a. sales invoice transactions only
b. credit note (memo) transactions only
c. payment transactions only
d. transfer (write off) transactions

As of: 02/21/2010 12:53:59

Command: STRATIFY ON Amount INTERVALS 10 IF Type = "IN" TO SCREEN

Table: Ar

If Condition: Type = "IN" (588 records matched)

Minimum encountered was 6.66
Maximum encountered was 5,549.19

Trans Amount Count Percent of Count Percent of Field Trans Amount

-3,582.98 - -2,669.77 0 0% 0% 0.00
-2,669.76 - -1,756.55 0 0% 0% 0.00
-1,756.54 - -843.33 0 0% 0% 0.00
-843.32 - 69.88 38 6.46% 0.25% 1,324.49
69.89 - 983.10 354 60.2% 38% 199,579.57
983.11 - 1,896.32 149 25.34% 37.42% 196,557.17
1,896.33 - 2,809.53 29 4.93% 12.14% 63,742.37
2,809.54 - 3,722.75 14 2.38% 8.62% 45,268.71
3,722.76 - 4,635.97 2 0.34% 1.58% 8,283.02
4,635.98 - 5,549.19 2 0.34% 2% 10,503.83
Totals 588 100% 100% 525,259.16

As of: 02/21/2010 13:15:45

Command: STRATIFY ON Amount INTERVALS 10 IF Type = "CN" TO SCREEN

Table: Ar

If Condition: Type = "CN" (108 records matched)

Minimum encountered was -663.54

Maximum encountered was 0.00

Trans Amount Count Percent of Count Percent of Field Trans Amount

-3,582.98 - -2,669.77 0 0% 0% 0.00
-2,669.76 - -1,756.55 0 0% 0% 0.00
-1,756.54 - -843.33 0 0% 0% 0.00
-843.32 - 69.88 108 100% 100% -9,025.02
69.89 - 983.10 0 0% 0% 0.00
983.11 - 1,896.32 0 0% 0% 0.00
1,896.33 - 2,809.53 0 0% 0% 0.00
2,809.54 - 3,722.75 0 0% 0% 0.00
3,722.76 - 4,635.97 0 0% 0% 0.00
4,635.98 - 5,549.19 0 0% 0% 0.00
Totals 108 100% 100% -9,025.02

As of: 02/21/2010 13:17:25

Command: STRATIFY ON Amount INTERVALS 10 IF Type = "PM" TO SCREEN

Table: Ar

If Condition: Type = "PM" (71 records matched)

Minimum encountered was -3,582.98

Maximum encountered was 539.97

Trans Amount Count Percent of Count Percent of Field Trans Amount

-3,582.98 - -2,669.77 1 1.41% 7.91% -3,582.98
-2,669.76 - -1,756.55 5 7.04% 22.27% -10,085.74
-1,756.54 - -843.33 17 23.94% 45.45% -20,578.75
-843.32 - 69.88 40 56.34% 27.87% -12,618.56
69.89 - 983.10 8 11.27% -3.5% 1,584.65
983.11 - 1,896.32 0 0% 0% 0.00
1,896.33 - 2,809.53 0 0% 0% 0.00
2,809.54 - 3,722.75 0 0% 0% 0.00
3,722.76 - 4,635.97 0 0% 0% 0.00
4,635.98 - 5,549.19 0 0% 0% 0.00
Totals 71 100% 100% -45,281.38

As of: 02/21/2010 13:18:53

Command: STRATIFY ON Amount INTERVALS 10 IF Type = "TR" TO SCREEN
Table: Ar

If Condition: Type = "TR" (4 records matched)

Minimum encountered was -1,298.43

Maximum encountered was 66.06

Trans Amount Count Percent of Count Percent of Field Trans Amount

-3,582.98 - -2,669.77 0 0% 0% 0.00
-2,669.76 - -1,756.55 0 0% 0% 0.00
-1,756.54 - -843.33 1 25% 84.4% -1,298.43
-843.32 - 69.88 3 75% 15.6% -240.05
69.89 - 983.10 0 0% 0% 0.00
983.11 - 1,896.32 0 0% 0% 0.00
1,896.33 - 2,809.53 0 0% 0% 0.00
2,809.54 - 3,722.75 0 0% 0% 0.00
3,722.76 - 4,635.97 0 0% 0% 0.00
4,635.98 - 5,549.19 0 0% 0% 0.00
Totals 4 100% 100% -1,538.48

3. Open the AR file and use the expression builder to create a filter that screens for invalid transaction
types. Print the results and comment.

Command: SET FILTER TO Type <> "CN" AND Type <> "PM" AND Type <> "TR" AND Type <>
"IN"

The filter reveals one invalid transaction type “AA” reproduced below:

Cust Num Invoice date Due Date Ref No Trans Type Trans Amount

301037 09/30/2000 10/29/2000 213666 AA (533.59)

4. Using the Relation feature create a view from data in both the AR and Customer files that
shows customer details (name and street address) for payment transactions with abnormal
(positive) amount values. Print the view and comment on the results.

The filter to return the results is:

Type = "PM" AND Amount > 0

The abnormal results from the related tables are below:

Invoice Date Due date Ref No Cust No Trans Amount Street address Name

12/04/2000 12/09/2000 214088 065003 PM 13.08 7000 S WABASH AVE UNIVERSITY ELECTRONICS

12/04/2000 12/12/2000 214114 065003 PM 9.76 7000 S WABASH AVE UNIVERSITY ELECTRONICS

12/04/2000 12/16/2000 214129 065003 PM 116.72 7000 S WABASH AVE UNIVERSITY ELECTRONICS

12/04/2000 12/12/2000 214121 065003 PM 29.40 7000 S WABASH AVE UNIVERSITY ELECTRONICS

12/04/2000 12/23/2000 214185 065003 PM 9.17 7000 S WABASH AVE UNIVERSITY ELECTRONICS

07/17/2000 01/01/2000 43614X 222006 PM 539.97 951 WEST STREET DYNAMIX INDUSTRIES

12/04/2000 11/21/2000 213914 262001 PM 18.58 8 WEST STREET BULLY INDUSTRIES

12/02/2000 11/05/2000 213755 501657 PM 18.58 377 SAN MARINA DR FAXON NATURAL RESOURCES

12/02/2000 11/07/2000 213799 501657 PM 4.64 377 SAN MARINA DR FAXON NATURAL RESOURCES

12/04/2000 11/18/2000 213881 501657 PM 60.94 377 SAN MARINA DR FAXON NATURAL RESOURCES

12/04/2000 11/18/2000 213882 501657 PM 109.96 377 SAN MARINA DR FAXON NATURAL RESOURCES

12/04/2000 10/10/2000 213423 501657 PM 146.80 377 SAN MARINA DR FAXON NATURAL RESOURCES

12/04/2000 10/10/2000 213424 501657 PM 146.80 377 SAN MARINA DR FAXON NATURAL RESOURCES

12/04/2000 10/10/2000 213425 501657 PM 92.18 377 SAN MARINA DR FAXON NATURAL RESOURCES

12/04/2000 10/21/2000 213572 501657 PM 54.66 377 SAN MARINA DR FAXON NATURAL RESOURCES

10/29/2000 01/01/2000 13065X 516372 PM 303.81 71130 SUNRISE VALLEY DR SECOND POWER CORP

11/25/2000 10/29/2000 213672 516372 PM 58.35 71130 SUNRISE VALLEY DR SECOND POWER CORP

11/25/2000 10/29/2000 213672 516372 PM 24.46 71130 SUNRISE VALLEY DR SECOND POWER CORP

11/25/2000 01/01/2000 13452X 516372 PM 66.06 71130 SUNRISE VALLEY DR SECOND POWER CORP

11/25/2000 11/05/2000 213753 516372 PM 128.41 71130 SUNRISE VALLEY DR SECOND POWER CORP
5. Using the Join feature create a view from data in both the AR and Customer files that show
customer details (name and address) for payment transactions with abnormal (positive)
amount values. Print the view and comment on the results.

The filter to return the results is:

Type = "PM" AND Amount > 0

The abnormal results from the joined table are the same as problem 4 above.

The following Assignments are located in the ACL Tutorial folder in the Student Resources
section of this textbooks website.

6. Tutorial 2 relates to the following commands:

TOTAL, PROFILE, STATISTICS, SAMPLE, SEQUENCE, SORT, DUPLICATES, GAPS

7. Tutorial 5 relates to the following commands:

AGE, JOIN, MERGE

8. Tutorial 6 relates to the following commands:

TOTAL, COUNT, EXTRACT, EXPORT, SORT, INDEX

9. Bradmark Comprehensive Case

Required:
Access the Bradmark ACL Case in the Student Resource section of textbook’s web site. Your
instructor will tell you which questions to answer.