Lee and Ali are two Holmesglen students who never seem to have enough spare cash. Lee is from China and has an Uncle who owns a wholesale Arts and Crafts Emporium. Ali is a great salesman, has a car, and a large lock up garage. The two friends decide to import unusual gifts from Lee’s Uncle and sell them to various florists and small gift shops around Melbourne.

Neither knows about databases or accounting, and since you are studying Accounting Information Systems they have asked you to build a database for them to help keep tabs on stock, clients and accounting. Your task will involve performing a system analysis, documentation, design, and implementation of their accounting system including recommendation of relevant internal controls. 

The two young men have already established a company called Al Lee Exotic Gifts Pty Ltd, registered a logo with IP Australia, set up a simple website and opened a cheque account in the company name. Ali and Lee will divide the work and profits equally and all cash receipts go into a single company bank account.

A shipment of traditional Chinese porcelain pieces such as bowls, tea sets, vases, jars and plates has recently arrived from Lee’s Uncle in China and is currently being stored in Ali’s garage, ready for business. The boys are expecting another shipment next week of some silk scroll paintings, cushion covers, table runners and silk brocade purses and bags. 

Future plans:

Currently, Lee can only get these items from his Uncle overseas, however, he has admitted that he keeps a list of potential local artisans who specialise in jewellery, ceramics, sculpture and leather-goods that he might buy from in the future.   

As part of the supply deal, Lee’s Uncle must be provided with a report on the operations of the business (sales and cash receipts) at the end of each month.  Lee’s Uncle will also require remote view-only access to the database so he can monitor how much stock the boys are holding.Lee completes the bank reconciliation each month when he receives a bank statement. 

Revenue Narrative

The Sales Order Process

The revenue process begins with the customer’s order.  A customer will go to the website and check the list of available products and prices. The customer will then call Ali (the salesman) for a meeting and verbally tell him what they want. 

If the customer is new, Ali enters their details into the electronic database via his laptop and allocates a customer number. If the customer is an existing client, the customer quotes their customer number and Ali then checks that the customer is not on the “bad client list”.  If the client has a good rating, Ali will enter the sales order details into the electronic database via his laptop and print four copies of the sales order.  The client signs the first copy (copy 1) of the sales order to confirm that the order is correct and gives it back to Ali. Ali leaves an unsigned copy (copy 2) of the sales order with the customer.

The sales order has a place for the date, customer number, client signature, and details of each product purchased of product description, quantity, price, extension, order total, deposit received, receipt number and amount outstanding.  Ali has given the occasional discount, but this is entirely at his discretion.  Ali also takes a 10% deposit for the order immediately and hand writes a receipt in triplicate in his carbonless receipt book. He tears off the top (original) copy of the receipt and hands it to the customer. Upon his return to his business premises, he locks the cash or cheques received as deposits in the safe, along with the carbonless receipt book. At the end of the week, Ali prepares a report of the total sales, and sales by customer. Copy 3 of the sales order is sent to the warehouse function (Ali’s garage) for packaging and delivery to the customer. Copy 4 of the sales order is sent to the delivery department (Ali’s vehicle), which is temporarily filed away in numerical order, whilst copy 1 of the sales order is filed numerically for the billing department.

When the warehouse receives the stock release (Copy 3 of the order), the warehouseman (Lee) prints a packing slip and tax invoice, picks the order, wraps the items in bubble wrap and places them into sturdy cardboard boxes which are unsealed. The packing slip and tax invoice also goes into one of the boxes.

 Lee uses the stock release form to update the inventory ledger quantities when the order is complete. In order to maintain sufficient records of all the inventory, the inventory records are kept by hand. The warehouseman will also perform a stock count at the end of the month to ensure it reconciles with the ledger and make sure they have sufficient levels of stock.

Lee dates the stock release, stamps it “FILLED OK” and then sends the boxes together with the stock release form to the delivery department. A door to door delivery run is made once a day. The delivery department (Ali and his vehicle) receives the packaged order and the stock release form, which now becomes the delivery docket. The packaged order and delivery docket are matched to copy 4 of the sales order (shipping notice) which had been filed numerically. The products and documents are reconciled and the packed boxes are sealed up. The shipping notice is stamped “CHECKED OK” and is then re-filed.

The goods are delivered to the customer with the delivery docket. The customer must sign the delivery docket when the delivery man hands over the goods. This document is then returned to Al Lee’s business and filed permanently in numerical order as proof of delivery to the customer.

 At month end, the billing department (Lee) has the computer system create a statement for each customer which is then posted to the client. Each statement has a perforated section (remittance advice) on the bottom that contains the customer’s details and total amount owing. A total sales report for the month and a current inventory count report, are sent to Lee’s Uncle via email.  Customers mail their cheques together with a remittance advice to Al Lee Exotic Gifts and generally always pay in full.

The Cash Receipts Process

The mail Clerk (Lee) opens the safe and takes out the cash receipt book and the money (10% deposit on each order) collected by Ali when he visited each customer. He removes copy 2 of each cash receipt from the book and reconciles the cash or cheque received with each receipt. Lee also receives the cheques and remittance advices that have arrived in the mail and these documents are also reconciled.

The reconciled cash receipts and remittance advices are then used to prepare two copies of a remittance list.  The first copy is sent to the billing department for filing. The remaining copy is sent to treasury (Ali) with the cheques, cash, remittance advices and the cash receipt copies. The treasury clerk (Ali) uses the copy of the remittance list, the cash receipt copies and the remittance advices to update cash received and accounts receivable. The remittance list, cash receipt copies and the remittance advices are filed in chronological order.

The treasury clerk also prepares a deposit slip. The treasury clerk takes the deposit slip to the bank with the cheques for deposit. The deposit slip receipt from the bank is filed in date order.

Expectations for Part 1:

• This assignment is to be completed either individually, or in groups of two (maximum).
• You may use reference material, such as your textbook/s, class notes, YouTube videos, etc., to assist in completing each of the required steps of Part 1 of the project
• You are expected to use the appropriate computer application to produce professional documents for grading each of the required steps (e.g., word processor, Microsoft Project or equivalent, Microsoft Visio or equivalent, etc.).
• There is no requirement to reference or include a bibliography in Part 1 of the project. 

Assumptions and other issues:

• The case assumes that the database can be updated wirelessly and remotely (i.e. outside of the office).  This particular functionality is difficult and outside the scope of this course. Therefore, your database need only be operated from one single file on a single desktop computer.
• For this exercise, our scope is to limit the business operation to the revenue process. We are not creating entries for monthly expenses such as rent and preparing the financial statements. 
• Clients are added to the client database when they place their first order.

The case narrative is purposely vague and closer to what you would find in a conversation with a client than say a textbook problem. The narrative is designed such that it might come from someone without a background in viewing a system from a document flowchart view. You need to realise that many businesses might have some strange and “extra” processes and documents floating around that may or may not be needed for efficient and effective processing of the business information and paper that is not needed in a computerised system. Also, in almost all organisations, you can also expect that some internal control weaknesses will exist. The point is that you might not need a Microsoft Access table for every document they want to create or every activity performed.

• If there is any information that you will need to assume into the model, then you should specify your assumptions clearly.
• Your audience will understand flowcharts and documentation, so you do not need to include a symbol legend. 

Outputs/Submission requirements:

Refer to Assessment requirements below.

Penalties for late lodgement:

Pro rata reductions in the awarded mark, (10% per calendar day) will be made for assignments received late without a granted extension. However, assignments received more than two weeks from the due date without an extension having been granted will not be marked. 

Assessment coversheet:

A scanned soft copy of the assignment cover sheet that has been signed and dated by the student must accompany the assignment files when they are submitted to the Brightspace assignment drop box. If the assignment has been done in groups, then each student must complete a separate assignment cover sheet. Assignment cover sheets will be made available on Brightspace. 

Assessment criteria & learning outcomes assessed:

Addresses learning outcomes 1, 2 & 3  

Al Lee Exotic Gifts Pty Ltd Part 1 Requirements:

Design an Information System

Successful completion of Part 1 of this project includes [marks allocated]:

90 marks converted to 15%

1. Completed ASIC form 201: Application for registration as an Australian company [1 mark]
2. Create a Gantt chart in Microsoft Project showing project milestones and responsibilities in the design phase [4 marks]
3. An engagement letter to the client outlining the project and your company’s solution [4 marks]
4. Create a process narrative table in Word (refer below) for Al Lee Exotic Gifts Pty Ltd. This table will help you with each step of the project and should facilitate drawing the DFDs, flowcharts and REA model. In addition, it should help with the database design related to table attributes, forms, and reports needed to meet user requirements. Please note: the first three entries have already been entered for you in the table.                                                                                                                        [8 marks]

Answer:

Gantt Chart: 

Process Map: Sales Order Process: 

Cash Receipts Process:  

Data Flow Diagrams: Sales Order:

 

 

Physical:

DFD Level 0: 

Cash Receipts: 

Physical: 

DFD Level 0:

System Flowchart: Sales Order:

REA Diagram:

ERD:  

 

 

Bibliography:

Adi, S., & Kristin, D. M. (2014). Strukturisasi Entity Relationship Diagram dan Data Flow Diagram Berbasis Business Event-Driven. ComTech: Computer, Mathematics and Engineering Applications, 5(1), 26-34.

Nurmasani, A., Kurnia, R. S., Astuti, I. A., Ekastini, E., & Aminudin, M. N. (2016).