Ch. 11 The Revenue Cycle ` SM 11-1 Chapter 11 The Revenue Cycle REVIEW QUESTIONS 1. The typical events in a revenue cycle are: (a) respond to customer inquiries, (b) develop agreements with customers to provide goods and services in the future, (c) provide services or ship goods to the customer, (d) recognize claim for goods and services provided, (e) collect cash, (f) deposit cash in bank, (g) prepare reports. 2. The types of revenue cycles can be characterized according to ordering, payment timing and form of payment. There are two alternatives for ordering: order before delivery, or immediate customer pickup (no order). The timing of payment can be before delivery, at the time of delivery or after delivery of goods or services. The form of payment can be cash, check, credit/debit card or sale on account. The configuration that would require the most record keeping is one where goods or services are ordered before delivery, payment is after delivery and the sale is ‘on account.’ With this arrangement, the organization must keep a record of the order, the delivery, the billing, and the collection. Thus there will be an order table, shipping/delivery table, invoice table, inventory table (to help in taking orders) and a customer table (to help in billing customers). In addition, customer statements must be mailed. The configuration that would require the least amount of record keeping is one where the customer simply picks up what is needed from the store or shop floor, and pays cash (currency) to the company. This type of arrangement is known as ―cash and carry.‖ There would be no need to track orders, keep a record of customers, create and store invoices, or send customer statements. 3. The swimlanes in an overview activity diagram indicate who is responsible for events in that lane. This indicates segregation of duties. The order in which the events occur is plainly indicated in the diagram with arrows. This shows the required sequence of events. The documents produced by one event can be seen as input into the next event, showing the use of information from prior events. There are also some aspects of overview diagrams that lead one to consider workflow controls. For example, the document symbols can remind the user to consider prenumbering those documents. The swimlanes may lead to a plan for assuring accountability for events that occur in an individual’s swimlane. SM 11-2 Part III Transaction Cycles and Accounting Applications 4. There are very few opportunities for automatic checking in customer maintenance. Most of the data is reference data that is not stored in other tables. It is not really possible to validate the name and address that is entered since there is no related information available in the system. The exception to this is the zip code, which can be validated, given the town and state. Most of the automatic editing that can be done is format checking. For example, a social security number, zip code and telephone number should have nine, five, and ten digits, respectively. Only numbers, not letters can be entered in these fields. There may also be a maximum figure above which a credit limit would not be permitted. The balance due, or other cumulative quantities are not part of file maintenance because only reference data can be added, deleted or changed by file maintenance. One field that could be constrained by referential integrity is the default general ledger code for sales that a system might include in the customer master record. In addition the identification number assigned to a customer can be checked to make sure that it is not used by any other customers in the table. In many cases, the best way to control customer maintenance is simply by being very careful. In some cases, it might be worth requiring the data entry clerk to enter the same reference data twice to catch typing mistakes. The situation is similar for inventory file maintenance, although there are a few more opportunities for control, especially for a merchandiser. If the inventory item is a standard item that is sold by others, the company may be able to use a standard number that has been determined by the vendor or an organization. For example, book sellers use the ISBN (International Standard Book Number) to identify books, a standard number that for American publishers, is assigned by the U.S. ISBN agency. Given an ISBN, on- line resources are available for obtaining the title and author and can be used to enter information in the author and title fields of inventory record. Uniform Product Codes (UPCs) for manufactured products are also obtainable from the Uniform Code Council. Data entry in an inventory master record can also be constrained by creating validation rules. For example, a unit-of-measure field can be constrained to ―each‖, ―dozen‖, ―gross‖, etc. and maximum limits can be set for allowable reorder points. As with the customer maintenance, data entry of default general ledger codes for sales and cost of goods sold can be limited to accounts in the existing general ledger by enforcing referential integrity. 5. A third-party credit card processor performs the following services for a merchant (assuming that the merchant has a terminal that is on-line with the processor): (1) validate a customer’s credit card (could be any one of a variety of cards --- Visa, MasterCard, etc.), (2) receive a batch of transactions from the merchant and disperse the transactions to the correct credit card companies, (3) consolidate the funds sent by the credit card company banks, and electronically deposit the funds in the merchant’s bank. Ch. 11 The Revenue Cycle ` SM 11-3 6. It is unlikely that a customer will be billed for a product different than the one shipped because the product’s identification number is recorded in the Shipping_Detail Table and the invoice is based on data that comes from that table. The shipping records are likely to be accurate because they are compared to the order by the shipper, and because product numbers are taken by default from the order that was recorded for the customer. 7. The sales journal lists the sales for a particular period, such as one day. The journal, of course, provides information to managers who want to track the company’s sales success. It is also a control device. The total on the sales journal can be compared to a batch control of sales transactions, if one had been calculated. The sales journal also provides information needed for making a journal entry, involving, for example, a debit to accounts receivable and credit to cash. Since it also lists individual invoices, it provides an audit trail between the original invoice and the amount of sales in the general ledger. The same comments apply to the cash receipts journal. It lists the cash receipts over a period of time, such as a day, and provides information for those responsible for managing cash. It can be a control device in the same way as the sales journal, providing a total amount to be compared to a batch control total, and providing information needed to make a journal entry. It also provides an audit trail between deposits to the bank and the original cash receipt transactions. 8. The General_Ledger_Transfer Table accumulates journal-entry records from revenue cycle and acquisition cycle transactions. For example, a sale of $100 of merchandise to customer# 135 would be recorded in the revenue cycle module. The system can be established so that when the sale is recorded in the revenue cycle module, a general ledger transfer record is also created indicating the general ledger accounts to debit and credit as well as the amounts. After records have accumulated in the General_Ledger_Transfer Table, they can be posted from within the general ledger module. The posting process results in updating the balances of the accounts in the general ledger master records. 9. The post menu option can be used in two ways. After sales invoice or cash receipt records have been recorded they can be posted to the customer master records in the revenue cycle module. The sales invoice would increase the customer’s balance, and the cash receipt would reduce it. A different option within the post menu can be used to update the general ledger account balances (accounts receivable, sales, cash) for the changes caused by sales and receipts. The purge records menu item is used to remove unneeded transaction records from on-line storage space. When an order has been filled, the customer has been billed, and the cash has been collected, the company can copy the related sales order, shipment, SM 11-4 Part III Transaction Cycles and Accounting Applications invoice and cash receipt records to an off-line storage medium such as magnetic tape and then delete the records from the files that are stored on-line. 10. There are several controls that can prevent a company from sending more inventory items than ordered. The control starts with order entry. If the wrong quantity is recorded as ordered in the first place, it will be difficult to prevent sending too much inventory. In a real-time system, there is really no substitute for simply being careful. A validation rule that sets a maximum limit for the quantity ordered could help here, but this control is really a rather blunt instrument. If orders are taken from customers on the web, the responsibility for the correct recording falls to the consumer who may be more accurate because he is more familiar with the transaction and realizes that he will be charged for the quantity ordered. Once the correct order quantity is captured in the system, a system could print a picking ticket based on the information in the order. The chance for error is small because the quantity of inventory shown on the picking ticket is taken directly from the order record. If the warehouse employee were to accidentally pick too many items for an order, the shipper may detect the error when the quantity presented is compared to the packing slip (which is also based on the quantity in the original order record). EXERCISES E11.1. ELERBE, Inc. a. Event records: All of the event records have a Date field. Four out of the five event records have an Employee# field. The Shipment and Sales_Invoice records have a field that refers to a previous record in the sequence of events. b. Detail records: Each of the detail records has a field that makes reference to the related event record (e.g., Sales_Order_Detail record includes the Order# of the related record in the Sales_Order Table). Each detail record has a quantity or amount field. c. Master records: There is one master record for each customer in the Customer Table, and one master record for each inventory item in the Inventory Table. Each record has reference data (e.g., name, address, author, title) and summary data (e.g., Balance_Due, Quantity_on_Hand, Quantity_Allocated, and YTD_Quantity Sold). d. Accountability: The Employee# fields in the Sales_Order, Shipment, Cash_Receipt and Deposit Tables makes it possible to hold employees accountable for recording these events. e. General ledger accounts: The general ledger sales field in the Customer table is the default account number for recording journal entries involving sales with this customer. f. General ledger accounts: The general ledger cost-of-goods sold field in the Inventory table is the default account number for recording the cost-of-goods sold resulting from a sales transaction. Ch. 11 The Revenue Cycle ` SM 11-5 E11.2 ELERBE, Inc. If an inventory record indicates 300 units in the quantity on-hand field, and 35 units in the quantity allocated field, there are 265 units available for sale. E11.3 ELERBE, Inc. This problem could have been caused by (a) the picker who may have failed to mark the actual quantity picked on the picking ticket; (b) the shipper who either relied on an incorrect picking ticket (see ―a‖ above) or recorded the shipment accepting the default quantities (which are the amounts ordered); or (c) the accounts receivable clerk. The accounts receivable clerk is less likely to have been responsible for the problem since the clerk cannot see how many items have been shipped. However, if the shipper had sent a correct packing slip to the clerk, with the 10 items sent, but failed to record the correct amount on the shipping record, the accounts receivable clerk should have discovered the error by comparison of the shipping records and packing slip. E11.4 ELERBE, Inc. The events are shown in the proper order, with descriptions below. Accept customer order. The order entry clerk accepts a customer’s order (if the customer is qualified and if inventory is available) and records the information in the sales order and sales order detail records. A picking ticket is printed. A packing slip is printed. Pick goods. The warehouse employee picks the items on the picking ticket and presents the ticket and the goods to the shipper. Ship goods. The shipper receives the goods from the warehouse employee, updates the packing slip and creates a bill of lading. The package is given to the carrier. Bill customer. The accounts receivable clerk uses the information from the shipper to bill the customer for goods actually delivered. Collect cash. The mail clerk opens the mail (with another employee present), prepares a remittance list and gives one copy of the list with the checks to the cashier, and another copy of the list with the remittance advice to the accounts receivable clerk. Deposit cash. The cashier uses the system to review the list of new cash receipts and to print a deposit slip. The cashier takes the deposit slip and the checks to the bank. One copy of the deposit slip goes to the controller. E11.5 The sales journal provides a basis for verifying the amount of sales. The amount of sales in the income statement should agree with the sales account in the general ledger, and the postings to the general ledger account should agree with the amounts on the sales journal. Further, since the sales journal SM 11-6 Part III Transaction Cycles and Accounting Applications lists each individual invoice, the auditor can go back to original invoice and shipping records, if desired. The cash receipts journal may be helpful for auditing the cash balance, but it is not directly relevant to the sales and accounts receivable balances. However, a month or two after the cutoff date for the aged accounts receivable report, the auditor could determine whether reported accounts receivable balances were actually collected in cash. If not, further investigation may be undertaken to determine whether the sales were real. The aged accounts receivable report provides a good basis for auditing the amount of accounts receivable shown on the balance sheet. If all customers are included in an aging reported showing transactions up to and including the end of the fiscal year, the total amount on the report should agree with the amount of accounts receivable in the general ledger. However, even when the totals agree, the auditor is concerned that some of the customers could be fictitious, or some of the balances could be incorrectly recorded. To overcome this problem, the auditor can use the report to select a sample of customers and then send letters asking customers how much they owed at the end of the fiscal year. The open customer orders report is not of great interest to the auditor. A sales order is not an accounting transaction and has no consequence until the order is filled. E11.6 The following documents appear in the order in which they usually occur, with descriptions. picking ticket. A document listing the items and quantities on a particular customer order. Used by an employee to pick the required quantities from the warehouse. packing slip. A document placed within or attached to the outside of a package that lists the details of the contents in one or more packages. bill of lading. A contract between the owner of the goods and the carrier. It identifies a shipment by providing such information as a count of the number of boxes, ship-from and ship-to addresses, freight charges, name of the seller, carrier, and customer. sales invoice. A bill sent to a customer representing a single sale that has been completed. It indicates what goods were shipped or services provided, the amount owed, the due-date and the discount date if any. remittance advice. A removable portion of a sales invoice or customer statement. It includes the customer#, sales invoice# (if open items system), and invoice amount(s). It is enclosed with a check in an envelope and mailed by the customer to the company. deposit slip. A document that is used to make a deposit at a bank. It indicates the total amount of the deposit as well as the currency and individual check amounts to be deposited Ch. 11 The Revenue Cycle ` SM 11-7 customer statement. A bill sent to a customer with the total amount due for all unpaid invoices. In an open item statement, all unpaid bills are individually listed. In a balance forward statement, only the current period’s (e.g., month’s) transactions are individually listed. The balance is computed based on the balance forward from the last period, and the current period’s transactions. E11.7 ELERBE, Inc. ELERBE , INC SALES INVOICE 5 Invoice # 3003 6 Customer# 1 5 Educate, Inc . 3451 Invoice Date 5/12/06 6 5 Fairhaven,MA Due Date 6/11/06 3 Order # 219 5 Shipment# 831 Quantity Quantity Extended ISBN Price Ordered Shipped price 4 2 4 2 8 0-256-12596-7 15 14 $78.35 $1096.90 4 2 4 2 8 0-146-18976-4 1 1 $70.00 70.00 Subtotal 8 $1166.90 Freight 3 3.00 Tax 8 0.00 8 Amount due $1169.90 Name of table that was the source of the above information: 1 = Sales_Order, 2 = Sales_Order_Detail, 3 = Shipment, 4 = Shipment_Detail, 5 = Invoice,6=Customer Table, 8 = Calculated amount. SM 11-8 Part III Transaction Cycles and Accounting Applications E11-8 Christmas Emporium The number of records added to the tables below are indicated. a. The Sales_Order Table: 2 (one from Sherry, one from Sue). b. The Sales_Order_Detail Table: 4 (two items on Sherry’s order, two items on Sue’s). c. The Shipment Table: 3 (one for each delivery). d. The Shipment_Detail Table: 5 (two on Sherry’s first delivery, one on Sue’s delivery, and one on Sherry’s last delivery). e. The Sales_Invoice Table: 3 (one for each delivery). PROBLEM SOLVING ON YOUR OWN PS11.1. Revenue Cycle Types 1. No order necessary, pay at time of service, pay by cash only. 2. Order nearly at time of service, pay nearly at time of service, pay by cash, check or credit card. 3. Usually – order placed before service, pay at time of order, pay by credit card 4. Order placed before delivery, pay before delivery, pay by credit card 5. Usually - order placed before delivery, pay after delivery, purchase on account PS11.2. ELERBE, Inc. (a) Recording the employee# means that the employee is accountable for any errors. (b) There could be a look-up table with valid employee numbers (c) The employee must belong to the correct access group before the system will allow him/her to record data. PS11.3. Peachtree Sales Order Screen. 1. Data entered by user: Customer ID, Quantity, and Item. 2. Data displayed from other tables: Name and Bill To: (displayed as soon as Customer ID was entered), Description and Unit Price (displayed as soon as the Item was entered). Note: The SO# may be a serial number that is computer generated. The Date value is probably a default using the current date. The Amount was calculated based on quantity times price. Ch. 11 The Revenue Cycle ` SM 11-9 PS11.4. ELERBE, Inc. 1. Open Item Customer Statement—August ELERBE, Inc. Customer Statement Customer# 3451 Statement period 08/01/06–08/31/06 Educate, Inc. Fairhaven, MA Date Document ID Amount 06/20/06 Invoice# 4231 2,500.00 07/18/06 Invoice# 4598 700.00 08/15/06 Payment on # 4231 (2,500.00) 08/30/06 Invoice# 4903 800.00 Balance Due $1,500.00 2.Balance Forward Customer Statement—July ELERBE, Inc. Customer Statement Customer# 3451 Statement period 08/01/06–08/31/06 Educate, Inc. Fairhaven, MA Balance Forward $3,200.00 Date Document ID 08/15/06 Payment (2,500.00) 08/30/06 Invoice# 4903 800.00 Balance Due $1,500.00 PS11.5. ELERBE, Inc. Sales_Order Detail Table: Quantity shipped will increase to 8 for 0-256-12596- 7, and will increase to 12 for 0-146-18976-4. Inventory Table: For 0-256-12596-7 Quantity_On_Hand will decrease to 22, and Quantity_Allocated will decrease to 14. For 0-146-18976-4, Quantity_On_Hand will decrease to 28 and Quantity_Allocated will decrease to 0. SM 11-10 Part III Transaction Cycles and Accounting Applications PROBLEMS P11.1 Customer Statements 1. Customer Statement under open item system: XYZ Company Customer Statement March 1 - March 31 Customer # 6614 Name Fred Marcus Address 27 High Street San Jose, CA Date of transaction Invoice # Payment# Amount Feb 20 142 20,000 Feb 27 151 7,000 Mar 10 178 15,000 Mar 23 142* 398 (20,000) Amount due $22,000 *Under the open item system it is necessary to identify the invoice (using the Invoice#) that is being paid so that customers know to which invoice the payment is being applied. In April’s statement, this invoice will not be displayed. 2. Record layout of Customer Table for an open item system: Customer#, Name, Address Ch. 11 The Revenue Cycle ` SM 11-11 3. Customer statement under balance forward system. XYZ Company Company Statement March 1 - March 31 Customer # 6614 Name Fred Marcus Address 27 High Street San Jose, CA Balance forward: $ 27,000 Date of transaction Document # Amount Mar 10 Invoice 178 15,000 Mar 23 Payment 398 (20,000) Total amount due $22,000 4. Record layout for balance forward system Customer#, Name, Address, Balance_Forward P11.2. Journal Entry Cash 1,982.47* Cash short/over 1.05 Sales 1,911.00 Sales tax payable 70.42 * $2,082.47 (Cash at end of day) - $100 (Cash at beginning of day) SM 11-12 Part III Transaction Cycles and Accounting Applications P11.3 Bowden Building Supplies 1. Each of the workflow controls listed in Key Point 4.7 is considered in turn. a. Segregation of duties among order entry clerk (authorization and record), warehouse employee, driver (execution), and billing clerk (record). b. Using information from prior events. Delivery slip with order information is used by the warehouse employee to pick the goods. Driver compares goods to the delivery slip. Customer reviews delivery slip and then signs. c. Required sequence of events. The company requires this sequence of events: create delivery slip, pick goods, deliver goods, customer signature, bill customer. No other sequence is possible in this system. d. Following-up on events. The billing clerks compares the signed delivery slips to the original received from the order entry clerk. Any slips that were not followed by delivery can be investigated. e. Prenumbered documents. The delivery slips are pre-numbered facilitating the follow-up described above. f. Accountability --- recording of internal agent(s) responsible for an event. There is no indication of this in the system, except that the external agent (customer) must sign the delivery slip. More accountability could be created by also requiring the picker and delivery person to add their initials to the delivery slip. g. Limitation of access to assets and information. The narrative does not make this clear. However, access to the inventory could be limited to the warehouse employee, access to order entry records could be limited to the order entry clerk, and access to the truck could be limited to the driver. h. Reconciliation of records with physical evidence of assets. This is not in the system described. 2. Driver keeps cash and pockets delivery slip. The billing clerk would detect the missing delivery slip when making sure that the sequence of prenumbered delivery slips is complete. 3. Driver steals unordered goods. This could be prevented by not giving the driver access to the warehouse. 4. Driver steals ordered goods: The customer will not sign the delivery slip. 5. Wrong recording of amount by billing clerk. She will catch her own error when she compares her calculated batch control total with the information entered into the computer. Ch. 11 The Revenue Cycle ` SM 11-13 P11.4 Smith’s Video Shoppe 1. The system uses a real-time system to record rentals, but not returns. 2. The amount due is shown in the table below. Although the current date is 12/20/06, the earliest that a customer could make a return is 12/21/06 since it is the end of the business day on 12/20. Customer# Rental# Actual or earliest Date_Due Days past Late charge and possible return date due amount due 101 301 12/18/06 12/17/06 1 1 x $2 = 2 Subtotal $2 102 303 12/21/06 12/18/06 3 3 x $2 = $6 304 12/21/06 12/20/06 1 1 x $2 = $2 Subtotal $8 103 302 12/19/06 12/17/06 2 2 x $2 = $4 305 12/21/06 12/18/06 3 3 x $2 = $6 Subtotal $10 Grand Total $20 3. The need for the Returns Table could be eliminated by adding a ―Date_Returned‖ field to the Rentals Table. 4. Data entry screen to record rentals. Customer# 105 Customer Name John Smith Date: 12/28/06 Rental# Video # Video Title Price Due Date 310 201 Gone with the Wind $5.00 12/31/06 311 203 Austin Powers $4.00 12/31/06 Data in gray were provided by the system using defaults or information from other tables. A drop-down list taken from the Customer Table with Name and Customer# could be helpful in entering the Customer#. Since it appears that the due date is frequently three days after the rental date, a default Due_Date could be provided by the computer by adding three days to the current date. Referential integrity could be enforced so that any Video_Title# entered during rental must have a matching record in the Video_Titles table. The Rental# could be automatically assigned by the computer. SM 11-14 Part III Transaction Cycles and Accounting Applications 5. Adding the clerk’s employee number to the RENTALS table would enhance accountability. 6. When the rental table records are posted (a) the fields in the Video_Title Table for Copies_on_Loan and YTD_Rentals are increased and (b) the Post_Date field in the Rentals Table is set to the date of posting. ACCOUNTING SOFTWARE EXERCISES All of the following questions were answered using Great Plains A11.1. Revenue cycle menu Below is an identification of the Great Plains menu items that are similar to the menu items in ELERBE’s revenue cycle menu. In Great Plains the user moves through a series of menus and submenus as described below. ELERBE’s Revenue Cycle Menu Your software acquisition menu Enter sales orders Transactions --> Sales --> Sales Transaction Entry--> Enter Order * Enter shipments Transactions --> Sales --> Order Fulfillment Enter sales invoice Transactions --> Sales --> Invoice Entry Enter collections Transactions --> Sales --> Cash Receipts Enter deposits Transactions --> Financial --> Bank Deposit Entry Post sales invoices Transactions --> Sales --> Series Post Post collections Transactions --> Sales --> Series Post Purge records Routines --> Sales --> Paid Transactions Removal * The user first selects Transactions from the main menu, then Sales from the Transactions menu, then Sales Transaction Entry from the Sales Menu, and finally Enter Order. A11.2. Revenue cycle reports Below is an identification of the Great Plains sample reports that are similar to the reports in ELERBE’s revenue cycle menu. Reports under ELERBE’s system Sample Reports from Great Plains Sales journal Sales Posting Journal Cash receipts journal Cash Receipts Posting Journal Open customer orders report Sales Open Order Report Aged accounts receivable --- detail Aging Report -- Detail , Receivables Management Aged accounts receivable --- summary Aged Trial Balance -- Summary, Receivables Management Ch. 11 The Revenue Cycle ` SM 11-15 A11.3. Sales invoice identification When the Enter Invoices window opens, and the user clicks in the Document field, an invoice # is automatically entered. A11.4. Applying collections to invoices Collections are applied to invoices using the Apply Sales Documents form. A11.5. General ledger distribution You can record the general ledger account numbers and amounts for a particular sales invoice by selecting the Distributions button on the Invoice Entry form. A11.6. Deposits After the Bank Deposit Entry form is opened, undeposited receipts are displayed. The user then clicks on the particular receipt transactions that will be deposited. A11.7. Open item and balance forward statements Under Great Plains, you can create some customer accounts that use the open-item system, and some accounts that use the balance forward system. When adding a customer, the Customer Maintenance Options Form provides radio buttons to select between Balance-Forward and Open-Item. A11.8. Purging records For open item customers, the procedure for deleting invoice and payment records that are no longer needed is to choose Routines, then Sales, then Paid Transactions Removal. For balance forward customers, at the end of a period, the user selects ―Balance Forward Consolidation‖ within the Paid Transactions Removal form. This computes the current balance, removes the transactions and updates the balance forward for the next period so that it equals the current balance. In either case, deleted transaction records can be archived in a history file. COMPREHENSIVE CASE See solution to entire case in separate file.