VOICE - Mail Order Sales and Services

Created by Prof. Al Bento, University of Baltimore. Version 2.1 - January 2002.

VOICE's business was originally to sell electronic components, from which it derives its name: Volume Integrated Circuits Express. Presently VOICE is focused on selling computer products by mail. Its catalog includes items like DOS and Windows software, hardware add-on, and PCs. VOICE used to sell products by mail only. But computers and software change so fast that VOICE decided to develop an automated phone order system to receive and process orders and hired you to do the job.

You had the lowest bid, because you do not have that much experience in developing systems, and consequently won the contract. What follows are the results of interviews with VOICE management and other personnel.

Phone order entry

A potential customer uses the current VOICE catalog or its ad placed in a computer magazine to make his/her choices and identify the product catalog number(s). The potential customer calls VOICE's new 1-800-VOICE number. The sales representative obtains the product number from the customer. If the customer does not know the catalog number the sales representative will need to search the catalog (or a file containing the catalog information) to find the product number. Once the product number is obtained, the sales representative searches the catalog (or a file containing the catalog information) to quote a price for the customer, and checks the list (or file) of product quantity on-hand, to provide availability information to the customer. VOICE charges a flat fee for shipping and handling of $10.00 for overnight delivery.

If the customer decides to order the product, the sales representative assigns a customer number and enters the customer name, address, phone number, credit card number, date, item(s) number, description(s), quantity, unit price, shipping&handling, and totals into a request form. The ideal system would then transcribe this information to a customer file and a sales order file, and update the list (or file) of product quantity on-hand. Repeat customers should be able to either give their names or customer number and provide only changes to their existing information with VOICE. Repeat customers are key to VOICE's competitive ability because the cost of obtaining a new customer is twice that of maintaining existing customers. Obtaining credit-card approval of the customer charge to pay for the order is to be done after the order is entered.

Order processing

The Distribution Department receives the orders and checks availability one more time, using the inventory file. The inventory file includes the item number, description, unit price, quantity-on-hand, quantity-on-order, quantity reserved and safety stock level. If the quantity ordered by the customer is lower than or equal to the quantity-on-hand less quantity reserved for an item, the order is filled, otherwise the order is back-ordered. In the first case, the Distribution Department obtains credit-card approval of the customer charge to pay for the order, and forwards the order to the Shipping Department. In the second case it sends a back-order notice to the sales representative, including the quantity ordered and the quantity available. The sales representative will contact the customer and will either cancel or issue another order.

Shipping

The Shipping Department receives the processed orders, picks the items and packages the orders. Then, it prepares a bill including customer name, mailing address, items shipped, unit price and totals, shipping and handling costs and tax, if any. The bill and the package(s) are sent to the customer.

Management reports

VOICE's management would like to receive a daily summary sales report by product and a daily exception stockout report, both listing the product number, product description, unit price, quantity and total. They mentioned that percentages should be included. The first report if for them to know how much they are selling, and the second for them to know how much they may be losing on sales.

What to do?

Given your limited experience with developing systems you secured the advice of Dr. Alben (a nationally recognized expert ) to be sure that you can do the job well. Dr. Alben advised you to:

  1. develop a data flow diagram for the system based on the above description.
  2. define the files necessary to support the system -- design the data base.
  3. design the order entry screen for the sales representative to use, and the format of the sales report by product.
  4. Finally, implement and test the system using an end-user data base language like Paradox, Access, etc. Data for testing the system was provided by VOICE -- see Appendix 1and 2.

APPENDIX 1

Testing data for VOICE phone order entry system

Inventory file

Catalog Number description unit price QTY

on-hand

QTY

on-order

QTY

reserved

safety stock
12001 WPWIN 8.0 123.34 100 20 30 20
12002 WORDWIN 97 119.95 80 10 24 16
12003 Adobe 5.0 239.57 30 06 12 06
13001 Quatro 8 99.95 50 20 20 12
13002 Excel 97 95.59 50 20 20 15
13003 Office 97 295.95 35 15 15 10
14001 Access 97 119.95 30 15 25 20
14002 Paradox Win 8 135.67 35 17 30 25
14003 Norton System Works 59.96 20 10 15 12
23001 AMD Athlon PC 980.00 30 20 20 20
23002 Pentium III PC 1,538.00 60 30 40 30
23003 Pentium IV PC 2,459.00 30 20 20 17
31001 128 MB SDRAM 40.00 100 40 55 40
31002 256 MB SDRAM 70.00 80 30 45 30
41001 Accel.Graph.Card 70.23 50 20 35 25
42001 19'SVGA Monitor 260.00 50 20 35 25
51001 MPC CD-ROM 49.87 80 20 50 50
53001 HD 40 GB 59.00 50 20 35 25
53002 HD 80 GB 89.00 50 20 35 25
61001 Laser Printer 600.00 40 10 25 25

APPENDIX 2

Testing data for VOICE phone order entry system

Customer file

Number Last

Name

First

Name, I.

Address Phone C.Card

Number

Exp. Date
E1237789 Smith John S. 13 Monroe St.

Tolltale, AL 32077

(510)350-8139 13567898901 0602
A3247209 Lewis Belle M. 256 Jones St.

Laerne, CA 91000

(710)256-9023 45679905472 1102
G9912567 Faria Peter N. 780 Peace Blvd.

Kyaville, LA 45120

(440)375-1936 88794529315 0105
B5674320 Curtiss Tony G. 1 Happy Ave.

Heaven, CA 91006

(619)456-9845 45017380023 0603
M0052713 Britt Anne M. 1277 !st Ave.

Nearby, MD 21004

(309)767-4531 23678178814 1204

Sales order examples

Order # Date Cust. Number Cat. Number Quantity
2001 06/04/01 E1237789 12001 50
2002 06/04/01 G9912567 12002 40
2003 06/04/01 M0052713 12001 25
3001 06/05/01 M0052713 13003 20
3003 06/05/01 A3247209 14001 03
3007 06/06/01 E1237789 23001 12
4004 06/06/01 B5674320 31001 30
4005 06/07/01 G9912567 12002 15
5002 06/08/01 A3247209 53001 10
5004 06/08/01 E1237789 14002 10

Note: assume that when stock-out occurs the sales representative enters another order, in the next day, for the quantity available of the product, after talking with the customer.

This page is maintained by Al Bento who can be reached at abento@UBmail.ubalt.edu This page was last updated on January 12, 2002. Although we will attempt to keep this information accurate, we can not guarantee the accuracy of the information provided.