Your job is identify the data files for a database Sales and Job Costing System for LongDistance Golf, Inc. LongDistance Golf, Inc. is a golf club manufacturer that produces a comprehensive line of golf clubs. Their primary customers are large retail golf outlets and large country club golf shops. LongDistance Golf has a line of standard golf clubs, which they customize to meet the individual customer’s needs and desires. These customizations may change the materials used in the clubs, such as the shaft and grip, and generally include adding the customer’s own brand name or logo to the club. The purpose of your system will be to track sales visit, sales, shipments and cash receipts information and to accumulate costs for unique batches of golf clubs produced by LongDistance Golf (jobs) as the clubs are produced (work in process inventory) and to track those costs as they become part of finished goods inventory and cost of goods sold. The job costing system maintains the subsidiary ledgers for the work in process, finished goods inventory and cost of goods sold accounts. Following is a description of LongDistance Golf, Inc.'s business processes for which data must be captured by the system. Note that LongDistance wishes to capture information about both accounting and nonaccounting-related events. Narrative Sales representatives are in charge of visiting potential customers (buyers, clubhouse managers; who are referred to as contacts) and working with them to identify items that LongDistance Golf might provide to their golf shops. Sales representatives make many visits to contacts prior to actually making a sale. Information about each visit is recorded by the sales representative in LongDistance’s customer relationship management system on their laptop computer. Once a contact decides to buy, the sales representative works with them to identify one or more of the standard products produced by LongDistance that come closest to meeting their needs. Then, the salesperson helps them customize the product and provides them with a price. The sales representative prepares a sales order that lists the standard item(s) to produce, describes all of the necessary customizations for each one and lists the per unit price for each item, as determined by the sales representative. The sales order also lists normal sales order information such as the customer’s number, name, address, phone, date to be delivered, etc. Standard items that the company can produce are all listed together in a bill of materials file in LongDistance’s computer system. The bill of materials file shows the standard item number, the standard price for the item and the standard materials, labor and overhead cost for the item. It also lists all of the raw materials and quantities used to manufacture the item. Sales representatives use this file to prepare the sales order and determine an appropriate price based on the modifications being made. 2 If the sales order is for a new customer, the salesperson must have the sales manager approve the customer’s credit and enter their information into the computer system before a sales order can be entered. An established customer will have records in both the Contacts data file and the Customers data file. When a new customer is entered, their record in the Contacts data file can be accessed electronically by the system to automatically enter most of the customer information. Sales orders are automatically forwarded via e-mail to a production supervisor. For each item to manufacture, the production supervisor creates a production order that lists the standard item number to be produced, and the date and time manufacturing will take place. Because a single sales order may list several different standard items to be manufactured, a sales order may be associated with several production orders. When the production order is created, a work-in-process inventory record for the item being manufactured is automatically created. The WIP inventory record has fields to keep running totals of materials, labor and overhead used to manufacture the job. Each production order is associated with only one WIP inventory record and each WIP inventory record is associated with only one production order. This WIP inventory record is similar to a job cost sheet, in that there is a separate record for batch produced, rather than single records for each type of item (as with previous inventory systems we have discussed). When the WIP inventory record is generated, a unique bar code showing the WIP inventory record number is printed and attached to a cart that is used to store the goods as they work their way through the factory. Bar codes are scanned periodically by bar code scanners that record the carts as they pass predetermined check points in the factory to track the status of WIP inventory item. For example, a check point exists just as shafts are inserted in the tempering furnace and again when they are removed. When these check point scans take place, information about the WIP inventory item number, check point number and date and time are automatically recorded. These check point scans are tracked and used to calculate cycle time information for each of the stages of production. A Cycle Time Report summarizing the average, minimum and maximum cycle time for each standard item is automatically printed and sent to the production supervisor weekly. As the goods are produced, raw materials are requested from the storeroom clerk by the appropriate production supervisor using a materials requisition. The printed materials requisition completed by the production supervisor is entered into the computer by the storeroom clerk. The production order authorizing request of the materials will be used is recorded for each item requested. A single materials requisition can be used to order several materials items and might also be used to order items needed for more than one production order that particular day. As workers work on the jobs, they complete daily time tickets that record the hours they worked, the type of service performed (i.e. their job title) and the production order providing authorization for the work. Theses time tickets are maintained electronically and the worker enters their own information using a special computer on the factory floor. An individual worker may work on many different production orders in a day. A single production order may be worked on by the same worker on multiple days and the worker can perform more than one service in relation to the production order. More than one worker may work on a single production order. Instead of basing overhead costs on labor hours, LongDistance Golf uses activity-based costing to estimate overhead for each job. Workers enter detailed cost driver quantity information for overhead activities performed on a special form called an overhead burden record on a daily basis. Workers 3 who perform overhead activities each maintain a separate daily overhead burden record. The overhead burden record lists the production order authorizing the use of the activity, the overhead code of the overhead activity (for example, grip insertion setups, which is overhead code 4056) and the quantity of the cost driver used (for example, 20 minutes of setup time). A worker may enter overhead activity information for multiple production orders on the same overhead burden record. Information about overhead activities, including a description of the activity, the overhead code, the cost driver and the predetermined overhead rate is stored in a special Overhead Activities table that is used as a resource when completing the overhead burden record. When a WIP inventory item is completed, the final check point scan triggers a maintenance process that changes the status of the WIP inventory item from “in process” to “completed” and records the date and time complete. (Important Note: Completion of a WIP inventory item is not recorded in a separate event file except for the check point scan file; it is treated only as a maintenance process). At this time, a Cost Report is automatically generated showing the standard item and quantity produced, describing the modifications made (as recorded in the sales order) and showing total actual costs for materials, labor and overhead for the job (as accumulated in the WIP inventory item record) as well as the standard costs (as shown in the bill of materials file). The Cost Report is sent to the sales supervisor, who reviews it and forwards it on to sales representatives as needed. When WIP Inventory items are completed and their due date arrives, shipping documents are automatically generated and the goods are shipped to the customer by a shipping clerk. The shipping clerk records the information about the shipment in the computer system. The shipment is not connected to any other previous event in the information system, but shipments are tied to the specific FG Inventory item being shipped. A single shipment may be used to ship several different FG Inventory items. Invoices are printed by accounts receivable clerks on a weekly basis and are sent to customers. Invoices contain information about the customer, original sales order, shipment date, item shipped and the price of the goods. All payments by customers are required to be made on an electronic basis directly to one of the company’s bank accounts. Therefore, it is not necessary to separately deposit the receipts. Receipts are matched to the shipment they are associated with by LongDistance. Each receipt may be for several shipments, and a single shipment might be paid for by one or more receipt.

Required 

1. Identify the database tables and associated fields for LongDistance Golf Company. List Tables and few associated fields next to each table. Separate tables into two lists, master and transaction tables.

2. Prepare REA [ER] Diagram to help design the job costing information system module.