Unlock document.
This document is partially blurred.
Unlock all pages and 1 million more documents.
Get Access
Step 4: Now right-click on the scroll bar tool in cell F4, select properties, and enter the following values:
Step 5: Now, the amount of initial sales can be varied by clicking on the left and right arrows in the scroll bar– without having to
retype new initial sales values.
d. Design appropriate data entry and processing controls to ensure spreadsheet accuracy.
12.5 Excel Project.
Required
a. Create a spreadsheet that contains the following data:
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
Yes
No
1. Is access to master data restricted?
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?
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?
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 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.
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
19
If credit limits are set by someone with sales responsibility, that person may be tempted to
grant credit to customers to maximize sales (and thereby commissions or bonuses earned)
without regard to the risk of having to write off the sales as uncollectible.
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.
Customers place orders on the company’s website, by fax, or by telephone. All sales
are on credit, FOB destination. During the past year sales have increased
dramatically, but 15% of credit sales have had to written off as uncollectible,
including several large online orders to first-time customers who denied ordering or
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 increased volume of sales has resulted in a number of errors in which customers
were sent the wrong items. There have also been some delays in shipping because
items that supposedly were in stock could not be found in the warehouse. Although
a perpetual inventory is maintained, there has not been a physical count of
inventory for two years. When an item is missing, the warehouse staff writes the
information down in log book. Once a week, the warehouse staff uses the log book to
update the inventory records.
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.
Required:
a. Identify at least three weaknesses in O'Brien Corporation's revenue cycle procedures,
explain the associated problem, and propose a solution. Present your answer in a three-
column table with these headings: Weakness, Problem, Solution.
b. Draw a BPMN diagram to depict O'Brien Corporation's revenue cycle revised to
incorporate your solutions to step a.
(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 several
large shipments being sent and never paid for.
Require digital signatures on all online orders
from new customers.
Require a written customer purchase order as
confirmation of telephone and fax orders.
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
security. The loading dock is also used to
receive incoming deliveries. This increases the
risk of theft, which may account for the
unexplained shortages in inventory.
Separate the shipping and receiving docks.
Physically restrict access to the loading dock
area where customer orders are placed.
4. Physical counts of inventory are not made at
least annually. This probably accounts for the
inaccuracies in the perpetual inventory records
and may also prevent timely detection of theft.
Physical counts of inventory should be made at
least once a year.
Inventory records discrepancies should be
corrected and investigated.
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.
6. The perpetual inventory records are only
updated weekly. This contributes to the
unanticipated shortages that result in delays in
filling customer orders.
The warehouse staff should enter information
about shortages as soon as they are discovered.
Solutions to part b will vary depending upon which weaknesses the students identified and corrected. The following corrects all six weaknesses
listed above.
Employee
Activity Performed (sequential, left-to-right across all rows)
Sales Clerk
Warehouse
Clerk
Shipping Clerk
Credit Manager
Accountant
Receive
Customer
Order
Prepare
Sales
Order
Pick &
Pack
Order
Ship to
Customer
Sale >
Credit Limit?
Prepare &
Mail Invoice
to Customer
Update
Accounts
Receivable
No
Yes
Approve
Credit Sale?
No
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.
Most patients pay for services rendered by cash or check on the day of their visit.
Sometimes, however, the physician who is to perform the respective services
approves credit based on an interview. When credit is approved, the physician files
a memo with one of the clerks to set up the receivable using data the physician
generates.
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 front office clerks receive cash and checks directly from patients and give each
patient a prenumbered receipt. The clerks take turns opening the mail. The clerk
who opens that day’s mail immediately stamps all checks “for deposit only.” Each
day, just before lunch, one of the clerks prepares a list of all cash and checks to be
deposited in Parktown’s bank account. The office is closed from 12 noon until 2:00
p.m. for lunch. During that time, the office manager takes the daily deposit to the
bank. During the lunch hour, the clerk who opened the mail that day uses the list of
cash receipts and checks to update patient accounts.
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.
The office manager supervises the clerks, issues write-off memos, schedules
appointments for the doctors, makes bank deposits, reconciles bank statements, and
performs general correspondence duties.
Additional services are performed monthly by a local accountant who posts
summaries prepared by the clerks to the general ledger, prepares income
statements, and files the appropriate payroll forms and tax returns.
Required:
a. Identify at least three control weaknesses at Parktown. Describe the potential
threat and exposure associated with each weakness, and recommend how to best
correct them.
