25.Comprehensive Case

Classic Restorations

Classic Restorations has a legacy accounting system that employs a combination of manual procedures supported by standalone PCs in various departments. You have been retained by Classic Restorations to design a relational database system that will allow the company to automate its processes. The companys revenue cycle and expenditure cycle procedures are presented in the following paragraphs.

Sales Order Procedures

Classic Restorations sales department is at its headquarters in Los Angeles. All customer orders go through sales representatives who visit current and prospective customers in their sales districts. The orders are faxed, mailed, or delivered in person to the sales department by the representative at the end of each day.

When the order is received, the sales clerk checks the customers credit worthiness from a computer terminal by running a validation application against the credit history. The credit-check program determines if the customers account is up-to-date regarding payments and the customer has not exceeded his or her credit limit. Computer controls in the application prevent further processing of any transactions that fails the credit check. Under certain extenuating circumstances, the credit manager may override the validation control. Once validated, the sales clerk prepares a three-part hard-copy sales order from the computer terminal. The clerk sends the sales order copy to the billing department, the stock release copy to the warehouse, and the packing slip copy to the shipping department. The original customer order is filed in the sales department.

Upon receipt of the sales order, the billing clerk records the sale in the digital sales journal using the department PC. The clerk then prints a hard-copy invoice, which is sent to the customer. Next, the clerk sends the sales order to the AR department for further processing. At the end of the day, the clerk prints a hard-copy sales journal voucher from the PC and sends it to the general ledger department.

The warehousing clerk uses the stock release copy to pick the goods from the shelves and then updates the digital inventory subsidiary ledger using the warehouse PC. Next, the clerk sends the goods and the stock release to the shipping department. At the end of the business day, the clerk prints an inventory summary from the PC and sends it to the general ledger department.

The shipping clerk reconciles the packing slip sent from the sales department with the stock release and goods received from the warehouse. If all is correct, the clerk manually prepares a hard-copy bill of lading. He then attaches the packing slip and bill of lading to the goods, which go to the carrier for delivery to the customer. Finally, the clerk files the stock release in the department.

The AR clerk receives the sales order from the billing department and uses it to update the digital AR sub ledger from the department PC. The sales order is then filed in the department. At the end of the day, the clerk prints an AR summary from the PC and sends it to the general ledger department.

Cash Receipts Process

Customer payments come into the mail room where mail clerks open the envelopes and send the checks and remittance advices to the AR department. The AR clerk reconciles the remittance advice with the check and updates the customers account in the digital AR subsidiary ledger. The clerk then files the remittance advices in the department and sends the checks to the cash receipts department. As previously mentioned, at the end of the day, the AR clerk prints a hard-copy AR summary from the department PC and sends it to the general ledger department.

The cash receipt clerk receives the checks and records the payments in the digital cash receipts journal. The clerk then manually prepares a hard-copy deposit slip and sends the checks and deposit slip to the bank. At the end of the day, the clerk prints a cash receipt journal voucher from the department PC and sends it to the general ledger department.

Expenditure Cycle

When the quantity on hand of a particular product falls to a low level, the warehouse clerk selects a vendor and manually prepares two hard copies of a purchase order. The clerk sends one copy of the purchase order to the vendor and the other to the receiving department. When the goods arrive from the vendor, the receiving clerk matches the packing slip in the shipment to the purchase order that the warehouse clerk had previously sent. If everything is in order, the receiving clerk signs the purchase order and sends it to the accounting department. The clerk then manually prepares a hard-copy receiving report, which he sends with the goods to the warehouse. From his department PC, the warehouse clerk uses the receiving report to update the inventory subsidiary ledger to reflect the receipt of the goods.

Subsequently, the accounting departments AP clerk receives the suppliers invoice, which she matches and reconciles to the previously received purchase order from the receiving clerk. From her department PC, the AP clerk then updates the digital AP subsidiary ledger to reflect the new liability and records the event in the digital purchases journal.

Cash Disbursements Procedures

The AP clerk in the accounting department reviews the liabilities that are due by searching the AP subsidiary ledger from the department PC. The clerk then prints out a hard-copy cash disbursement voucher for each item due for payment. The clerk then sends a cash disbursement voucher to the cash disbursements department for payment. At the end of the day, the clerk prints a hard-copy AP summary from the department PC and sends it to the general ledger department.

From his department PC, the cash disbursements clerk uses the cash disbursement voucher to record the payment in the digital check register and then prints a three-part check. The clerk signs the negotiable portion of the check and sends it to the vendor. One check copy is filed in the department, and the clerk sends the second check copy, along with the original cash disbursement voucher, to the accounting department AP clerk. At the end of the day, the clerk prints a hard-copy summary of the check register and sends it to the general ledger department.

From the accounting department PC, the AP clerk uses the check copy and cash disbursement voucher to record the payment in the voucher register and to close out the liability in the AP subsidiary ledger. The clerk then files the hard-copy cash disbursement voucher and check copy in the department.

The GL clerk receives the journal vouchers and account summaries from the various departments, reconciles these documents, and posts them to the appropriate control accounts in the digital general ledger from the department PC. Finally, the GL clerk files the summaries and journal vouchers in the department.

Required

Given that the processes described are to be automated using a relational database system. Perform the tasks a through d. You will need to make assumptions about how certain automated activities will be performed.

1. Create a data model in 3NF to support the processes described in the case.

2. Show cardinalities between the entities.

3. Add data attributes to each entity.

4. Include primary keys and foreign keys.