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.
Need to be done in MS Access.
Attachment:- Assignment Files.rar