Accounting Information Systems
12-21
Step 5: Click the “Design Mode” option in the tool bar to exit Design Mode. You can now click on the spin button and change the
value of the sales growth rate. Notice how all of the values in the spreadsheet change simply by clicking the spin button arrows – no
need to repeatedly type in the new sales growth rate value.
c. Add a scroll bar to your spreadsheet that will let you modify the amount of initial sales to vary from $4,000,000 to
$6,000,000 in increments of $100,000.
A scroll bar is another spinner tool. The difference between a scroll bar and a spin button is that a scroll bar has a space between its
Ch. 12: The Revenue Cycle: Sales and Cash Collections
Step 2: Click on one corner of the scroll bar and drag it so that it fills cell F5 horizontally. Your spreadsheet should now look like this:
Accounting Information Systems
12-23
Step 3: As with the spin button, we have to link the scroll bar to the cell that will display the values we wish to vary. Our goal is to
vary sales from $4,000,000 to $6,000,000 in increments of $100,000. The spinner tool, however, cannot work with such large values.
Ch. 12: The Revenue Cycle: Sales and Cash Collections
Accounting Information Systems
12-25
Step 4: Now right-click on the scroll bar tool in cell F5, select properties, and enter the following values:
LinkedCell = E4
Max = 6000
Min = 4000
SmallChange = 100
Step 5: You can now click on the left and right arrows in the scroll bar to vary the amount of initial sales and see the effects ripple
through the spreadsheet – without having to retype new initial sales values.
Ch. 12: The Revenue Cycle: Sales and Cash Collections
d. Design appropriate data entry and processing controls to ensure spreadsheet accuracy.
Chapter 10 describes the various data input validation controls that can be used. In this problem, students should be instructed to set
reasonable range checks on the allowable values for the percentage of sales that are cash sales and what percentage of credit sales is
never collected.
In addition, user data entry should be restricted to the cells that contain the initial assumptions. All other cells in the spreadsheet
should be locked.
12.5 For each of the following activities identify the data that must be entered by the
employee performing that activity and list the appropriate data entry controls:
a. Sales order entry clerk taking a customer order
Data that must be entered
Appropriate Data Entry Edit Controls
User ID
Validity check
Compatibility test (is user authorized to perform this task?)
Completeness check (cannot be null)
Password
Validity check
Compatibility test (is user authorized to perform this task?)
Completeness check (cannot be null)
Note: All other fields on the sample sales order entry screen (see Figure 12-6) can be
completed by the system.
Delivery method
Choose from pull-down list of options
Desired delivery date
Field check (date)
Item number
Field check
Validity check
Check digit
Ch. 12: The Revenue Cycle: Sales and Cash Collections
b. Shipping clerk completing a bill of lading for shipment of an order to a customer
Data that must be entered
Appropriate Data Entry Edit Controls
User ID
Validity check
Compatibility test (is user authorized to perform this task?)
Completeness check (cannot be blank)
Password
Validity check
Compatibility test (is user authorized to perform this task?)
Completeness check (cannot be blank)
Carrier name
Choose from pull-down list of approved carriers
Note: All other fields on the sample bill of lading (see Figure 12-11) can be completed by
the system.
Customer name (consigned to)
Choose from pull-down list of customers
Completeness check (cannot be blank)
Number of packages
Field check (numeric only)
Sign check (>0)
Completeness check (cannot be blank)
Description
Completeness check (cannot be blank)
Weight
Field check (numeric only)
Completeness check (cannot be blank)
Class or rate
Choose from pull-down menu of options
Completeness check (cannot be blank)
Accounting Information Systems
12-29
12.6 Create a questionnaire checklist that can be used to evaluate controls for each of the
four basic activities in the revenue cycle (sales order entry, shipping, billing, and
cash collections).
a. For each control issue, write a Yes/No question such that a “No” answer
represents a control weakness. For example, one question might be “Are
customer credit limits set and modified by a credit manager with no sales
responsibility?”
A wide variety of questions is possible. Below is a sample list:
Question
No
1. Is access to master data restricted?
8. Are physical counts of inventory taken regularly and used to adjust the
perpetual inventory records?
9. Are the credit approval and sales order entry tasks performed by
separate individuals?
10. Are picking list quantities compared to sales orders?
11. Is physical access to inventory controlled?
12. Are reports of open sales orders regularly created and reviewed?
13. Are shipping documents reconciled with sales orders?
14. Are the shipping and billing functions performed by different
individuals?
15. Are monthly statements mailed to customers?
16. Are the functions of processing customer payments and maintaining
accounts receivable performed by separate individuals?
17. Is the bank account reconciled by someone other than the person who
processes customer payments?
18. Are lockbox arrangements used?
19. Are customer credit limits set and modified by a credit manager with no
sales responsibility?
2. Is the master data regularly reviewed and all changes investigated?
3. Is sensitive data encrypted while stored in the database?
4. Does a backup and disaster recovery plan exist?
5. Have backup procedures been tested within the past year?
6. Are appropriate data entry edit controls used?
7. Are digital signatures required for online orders?
Ch. 12: The Revenue Cycle: Sales and Cash Collections
b. For each Yes/No question, write a brief explanation of why a “No” answer
represents a control weakness.
Question
Reason a “No” answer represents a weakness
1
Unrestricted access to master files could facilitate fraud by allowing employees to
5
If the backup plan is not regularly tested, it may not work.
6
Without proper data entry edit controls, errors in sales order entry may occur
resulting in shipments that are not billed, sending the wrong items, etc.
7
Without a digital signature, orders may be processed and sent that the customer later
refuses, resulting in increased costs
8
Without periodic physical counts, the perpetual inventory records are likely to be
incorrect, creating problems in filling customer orders on time
9
If the same individual approves changes in credit and takes customer orders, they can
increase credit limits for friends which may result in sales that are not collected.
10
Not comparing picking lists to sales orders can result in shipping the wrong
merchandise or the wrong quantities to customers.
11
If physical access to inventory is not restricted, theft may occur.
12
Failure to monitor sales orders may result in delays in filling customer orders
13
Failure to compare shipping documents to sales orders may result in errors in filling
customer orders
14
Not segregating the billing and shipping functions increases the risk of deliberately
not billing for shipments
15
Not mailing monthly statements to customers increases the risk of not detecting
errors or fraud in maintaining accounts receivable
16
Not segregating handling of customer payments and maintenance of accounts
receivable creates the possibility of lapping
17
If the bank account is reconciled by the same person who processes customer
payments, theft can occur and be covered up by adjusting the bank balance on the
bank reconciliation
18
Not using lockboxes, where feasible, creates delays in receiving customer payments
which could result in cash flow problems
bonuses earned) without regard to the risk of having to write off the sales as
uncollectible.
change account balances to conceal theft
2
Failure to investigate all changes to customer master data may allow fraud to occur
because unauthorized changes to credit limits may not be detected.
3
Failure to encrypt sensitive data can result in unauthorized disclosure of personal
information about customers
4
If a backup and disaster recovery plan does not exist, the organization may suffer
loss of important data.
Accounting Information Systems
12-31
12.7 O’Brien Corporation is a midsize, privately owned, industrial instrument
manufacturer supplying precision equipment to manufacturers in the Midwest. The
corporation is 10 years old and uses an integrated ERP system. The administrative
offices are located in a downtown building and the production, shipping, and
receiving departments are housed in a renovated warehouse a few blocks away.
receiving the merchandise.
Customer orders are picked and sent to the warehouse, where they are placed near
the loading dock in alphabetical sequence by customer name. The loading dock is
used both for outgoing shipments to customers and to receive incoming deliveries.
There are ten to twenty incoming deliveries every day, from a variety of sources.
The system is configured to prepare the sales invoice only after shipping employees
enter the actual quantities sent to a customer, thereby ensuring that customers are
billed only for items actually sent and not for anything on back order.
Ch. 12: The Revenue Cycle: Sales and Cash Collections
Identify at least three weaknesses in O’Brien Corporation’s revenue cycle activities.
Describe the problem resulting from each weakness. Recommend control
procedures that should be added to the system to correct the weakness.
(CMA Examination, adapted)
Weaknesses and Potential Problem(s)
Recommendation(s) to Correct
Weaknesses
1. Orders from new customers do not
require any form of validation, resulting in
Require digital signatures on all online
orders from new customers.
2. Customer credit histories are not
checked before approving orders, resulting
in excessive uncollectible accounts.
Customers’ credit should be checked and
no sales should be made to those that do
not meet credit standards.
3. Outgoing shipments are placed near the
loading dock door without any physical
Separate the shipping and receiving docks.
accounts for the inaccuracies in the
perpetual inventory records and may also
prevent timely detection of theft.
4. Physical counts of inventory are not
made at least annually. This probably
Physical counts of inventory should be
made at least once a year.
5. Shipments are not reconciled to sales
orders, resulting in sending customers the
wrong items.
The system should be configured to match
shipping information to sales orders and
alert the shipping employees of any
discrepancies.
unanticipated shortages that result in delays
in filling customer orders.
they are discovered.
6. The perpetual inventory records are only
updated weekly. This contributes to the
The warehouse staff should enter
information about shortages as soon as
Accounting Information Systems
12.8 Parktown Medical Center, Inc. is a small health care provider owned by a publicly
held corporation. It employs seven salaried physicians, ten nurses, three support
staff, and three clerical workers. The clerical workers perform such tasks as
reception, correspondence, cash receipts, billing, and appointment scheduling. All
are adequately bonded.
The servicing physician prepares a charge slip that is given to one of the clerks for
pricing and preparation of the patient’s bill. At the end of the day, one of the clerks
uses the bills to prepare a revenue summary and, in cases of credit sales, to update
the accounts receivable subsidiary ledger.
The clerks take turns preparing and mailing monthly statements to patients with
unpaid balances. One of the clerks writes off uncollectible accounts only after the
physician who performed the respective services believes the account will not pay
and communicates that belief to the office manager. The office manager then issues
a credit memo to write off the account, which the clerk processes.