Final Project GCI, Inc. Revenue Cycle
Greene Camping, Inc. (GCI) is an exclusive distributor of super high tech camping equipment in the Chicago area. The founder and owner, Brian Greene, started the company two years ago and organized it as a commercial corporation, which he manages and runs with five hourly employees. The company grew at a modest pace, becoming popular with the young Chicago business professionals who have more money than time and own big SUVs. Recently one of GCI's products, a solar powered beer cooler, was featured as an online promotion and GCT's sales immediately explored.
Since the company is growing fast, Brian Greene has retained you to replace GCI's manual accounting system with a database. The new system will be built and implemented piecemeal, one cycle at a time, beginning with GCT's revenue cycle. The initial feasibility study and user needs have already been analyzed and the logical work for the revenue cycle is complete. You are provided with a narrative and an ERD of the new system.
Assignment -
1. Convert the ERD into a database schema and make sure it is normalized. Do a strict conversion.
2. Build the GCI, Inc, revenue cycle database in Access using the procedure and specifications on the next pages.
Procedure for building an Access database application
1) Convert the ERD into database tables and build these tables in Access. Use the table and field names of the common schema for the established tables and fields. You may add additional tables and fields as needed, as you further develop your database.
2) Create the relationship between the tables (primary key to foreign key links) and enforce referential integrity for this relationship.
3) Create forms for the maintenance of all resources an agent entities.
4) Use the maintenance forms to populate the resources and agent tables.
5) Create forms for the event entities (use the form wizard for forms with subforms).
6) Create the reports (start with easiest forms, save hardest for last).
7) Create a navigation form that opens automatically upon startup that provides a user interface with navigation buttons to all functions in the application.
8) Enter event transactions, process them completely, and teat all conditions and scenarios to make sure that the system can handle them. Make any necessary modifications and add any necessary functionality to make your system work.
Need to be done in MS Access.
Alternate Form Versions for Approvals - Create alternative versions of the sales invoice from for sales approval and the sales return form for return approval. These versions allow Brian Greene to access to approval field, whereas the user of the regular form version can only view the approval field. Access to the alternate form version will be available from the navigation form. Normally this across would be password protected but we will not implement that control.
The navigation form should open automatically upon startup. The navigation form should be easy to use and logically organized. The navigation form(s) should allow access to all transaction processing and report generation. Your navigation form(s) are the "front end" of your system and it should have the following buttons:
Enter Sales Invoices.
Approve Credit for Saks Invoices
Enter Shipment
Enter Cash Receipts
Enter Sales Return s
Approve sales Returns
View/Print Sales Invoice
View/Print Bill of Lading (for shipment)
View Print Credit Memo Document
View/ Print Monthly G/L. Journal Fairy Report
View/Print Monthly Sales By Customer Report
View/Print A/R Summary Repent
Open Customer Maintenance
Open Inventory Maintenance
Open Employee Maintenance
Open Cash Account Maintenance
Open Shipper Maintenance
Reports
- Reports should be well designed and in good form. Although autoreports can be used to begin the process of creating reports. no autoreports should be in the final database. Autoreports or reports created by report wizard should be altered to improve fonts, layout and readability
- Reports that are business documents should be properly paginated, one document to a page.
The following reports are required:
o Sales Invoice document - example in Perry text p. 371
o Bill of Lading document - example attached
o Credit Memo document - example attached
o Monthly General Ledger Journal Entry Report (example attached - use a parameter query so the user will be able to generate this mood only for transactions in a given month)
o Monthly sales By Customer Report (example attached - use a parameter query so the be user will be able to generate this report only for transactions in a given month)
Record and Transaction Specification
Populate the resource and agent entities with at least 6 records each (expect for cash where 3 accounts are sufficient) FOR THE EVENT ENTITIES MAKE SURE YOU PROCESS ENOUGH TRANSACTIONS TO TEST EVERY POSSIBLE CONTINGENCY AND TRANSACTION COMBINATION. For the event entities enter at least 14 sales invoices, 12 shipments (including at least one invoice with 2 shipments), & cash receipts from customers (including one receipt applied to 2 shipments and 2 receipts applied to one shipment).
Process at least 4 sales returns. Make sure the sales invoice, shipments, cash receipts and sales returns transactions occur over the 3-month period of February-April 2017.
While building your database remember to backup frequently! This will minimize the amount of work lost if your file were to be corrupted or lost.
To be submitted:
#1 Hand in a Word document with a full schema conversion of the ERD of this project.
#2 Hand in your Access file containing all of the objects required in procedure steps 2 - 4 + the sales form (with subform) from step 5. These procedure steps are described on the first page.
#3: Your final completed project. Your project will consist of one Access database file.
Narrative of the New System
Sales
GCl will both modernize and simplify in revenue cycle. An employee will continue to open the mail to obtain customer purchase orders. She will use that customer P.O. as the basis for entering a sales invoice in the computer. Brian Green will get on the computer periodically to approve the sales invoices for fulfillment and shipment. He will use a special screen that allows him access to the approval field in the sales record. The employee will then fills and ship the order.
Shipments
Invoices usually take 1-3 days to fill and ship. Currently invoices are shipped in one or two shipments, so GCI does occasionally have partial shipments [we are ignoring formal backorder procedure], Shipping data is entered through shipping screen by choosing an invoice and adding shipping information. A bill of lading number is assigned, the shipper number and ship dale are entered and then the weight and number of cartons of each inventory item on the invoice is inputted. Shipping data is stored in a shifting table and the weight and number of cartons of each item is stored in an associative entity (a N-N table).
After the shipping data is entered a bill of lading document (report) is printed out and given to the shipper.
Billing
Billing will be done daily by printing off the sales invoices for that day's shipments and mailing than to customers. Credit terms extended to customers is net 30.
Cash Receipts
One cash receipt can pay for one or many invoices: one invoice can be paid by one or more cash receipts. An employee enters each cash receipt in a data entry screen. Each day the receipts are grouped into a bank deposit and taken to the bank.
Sates Returns
Sometimes customers return some of the items on an invoice. Returns are made before the goods have been paid for, when the items are received an employee opens the sales return screen and enters the inventory items and quantities returned and winch invoice number and BOL number apply to these items. Only items from a single invoice are recorded on one sales return. A Credit Memo document (report) for the credit to the customer's account is printed and mailed to the customer.
Reports
Brian also needs to do a monthly evaluation of accounts payable. He needs the new system to produce a Monthly G/L Journal Entry report, a Monthly Sales by Customer report, and an A/R Summary Report, which he uses to monitor his customer accounts.
Specifications for the final project
All objects in your database should be properly named.
Tables
The metadata of table must include:
Use the Autonumber data type for all event entity primary keys, sales order number, sales invoice number, bill of billing number and cash reports number, and credit memo number.
Input masks . list all phone numbers and dates.
Validations rules and validation key text for ALL EVENT ENTITY TRANSACTION DATES. The rule should allow the entry of only the current date or earlier, but not future dates. If the date covered is ahead of date of entry, the error message. "Please enter today's date or earlier" should appear.
Forms
Forms should be well designed and in good form. Although autofoms can be used to begin the process of entering form, no autoforms should be in the final database. Autoforms or forms created by form wizard should be altered to improve fonts, layout and readability and include internal control listed below, Fields containing reference data should be protected in the manner described in perry chapter 8. the forms will also have navigation buttons (described below) that will allow the user to maintain records and print hand copies of the resulting business documents (i.e. enter a sales order record and then view or print the resulting sale order, same with the sales return record to the resulting credit memo, same with the shipping record to the resulting bill of loading)
Forms should include the following internal controls.
Combo boxes must be used on all forms and subforms for ALL FOREIGN KRY input fields that connect to AGENT & RESOURCE ENTITIES Combo boxes are typically created when there is a limited and known list of proper value, including state abbreviations, customers numbers, employee numbers, and Inventory numbers, Size and formal use drop down lists and their columns properly.
o Dollar totals, where appropriate, such as on sales Invoice Forms, cash receipts and credit Memo forms for sales return.
o Closed loop verification must be implemented on ALL EVENT FORMS for all customer numbers, employee numbers and inventory numbers.
o Navigation buttons can be created using the button wizard in the toolbox. Buttons required on each form are First Record. Previous record, Next Record, Last record, Save Record. New Record and Close Form. For event keno, if a document is generated from the form input, then include these two buttons on the form. View Document and Print Document. 'View" will open the report. "Print- will send the report to the printer.