Developing an Information System for the Revenue Process
Working database application and business report (SRK2) are Introduction
"Dad, this is a really good idea and we think that we can make a lot of money!" exclaimed Abby Schafer as she presented her request for capital financing to her potential venture capital source (also known as "Dad"). The venture capitalist listened intently to the presentation and concluded that the project was intriguing, but he insisted that the managers of 'Schafer Refreshment Kiosk' (SRK) have computerised accounting information systems (AIS) developed for their business processes, and have their internal controls examined. You have been retained as an independent consultant to perform the system analysis, documentation, design, and implementation of their accounting system including recommendation of relevant internal controls.
Background
Abby and Emma Schafer, two children who live beside a golf course, were tired of asking Dad for money and were trying to figure out a way to earn money on their own. Noticing the large number of overheated looking golfers who walked by their house, Abby and Emma decided to start a refreshment stand.
The golf course near the Schafer house is a private course with an established membership group. Members are allowed to "charge" all activities and products used and/or acquired at the course to their "account." The golf course sends a statement to each member at the end of each month as an invoice for the month's activity. When surveyed, the members of the golf course indicated that it would be desirable to have a refreshment kiosk offering cool refreshing soft drinks, chocolate bars and packets of chips for patrons located on the course.
Abby and Emma decided to exhibit an entrepreneurial spirit and start 'Schafer refreshment Kiosk' (SRK). Their request for venture capital to their Dad (above) resulted in his requirement for reports detailing how the children will operate their business (what the accounting system will look like).
After interviewing Abby and Emma you found that SRK opened a bank account and an account at the local supermarket (with terms 2/10 net 30). SRK will offer many flavors of soft drink including; blackberry, blueberry, cherry, cinnamon, fuzzy navel, grape, green apple, kiwi, lemon, lemon lime, lime, orange, orange pineapple, peach, peppermint, red raspberry, strawberry, strawberry banana and watermelon. Abby and Emma will also sell a number of different chocolate bars such as Flake, Cherry Ripe, Mars and Picnic and various flavours of chips such as Salt & Vinegar, Chicken, Plain and BBQ. Currently, the children divide the work and profits equally and all profits go into a bank account where % is saved to an education fund and 1/4 is available for the girls to spend.
The golf course requires a membership to play on the course. Anyone playing will be able to stop at the refreshment kiosk and purchase drinks, chocolate bars and/or chips on their membership account. The refreshment kiosk will be located beside the clubhouse and members can feasibly stop before the round, at the halfway point, and after the round of golf. Abby takes the money to the bank for deposit, and Emma updates the books to record the receipt of cash for each member's account. Also at the end of the mont,h Dad must be provided with a report on the operations of the business (sales and cash receipts).
Abby completes the bank reconciliation each month when she receives a bank statement.
Aevenue Narrative
The Sales Order Process
The revenue process begins with the member order. A member walks up to the kiosk and orally tells the sales clerk what product(s) they would like and their member number. First, the sales clerk (Emma) checks the customer's credit on the PC (a list of members in good standing). If the customer's credit is approved, the sales clerk creates an order using the PC based sales system and prints three copies of the sales order. The member signs the first copy of the sales order. The sales order has a place for the date, member number, member signature, and details of each product purchased, such as product description, quantity, price, extension and order total. At the end of the day, Emma prepares a report of the total sales, and sales by customer. The first copy of the sales order is sent to the production function
for packaging and delivery to the customer. The second copy is sent to shipping. The last copy is filed numerically at the point of sale for the billing department.
When production receives the stock release (one copy of the order), the operator (Abby) pours the selected product from an individual bottle into a cup with ice. If the customer has ordered any chocolate bars or chips, Abby also takes the items out of the storage boxes.The operator then uses the stock release to update the inventory ledger quantities. In order to maintain sufficient records of all the inventory, the inventory ledgers are kept by hand. A journal voucher is created once per day and used to update quantity on hand in inventory on the computer system and is then filed by date. The stock release and product/s are then sent to the shipping department. The shipping department (Emma) receives the product/s, and the stock release form. The product/s and stock release are matched to the second copy of the sales order (shipping notice) which had been filed numerically. The product's and document are reconciled. The shipping notice is then re-filed. The goods are delivered to the customer with a packing slip. At month end, the billing department has the computer system create a statement for each member and a total sales report for the month and delivers all statements and the sales report to the golf course management. A remittance advice is attached to the bottom of each member's statement. Members issue a separate cheque to SRK for their monthly refreshment purchases and always pay in full.
The Cash Receipts Process
The golf course manager sends out the members' statements and collects the payments. The cash receipts system begins when the golf course returns the remittance advice and the cheque for each member's purchases. The golf course is sent 10% of the total refreshment sales as a service charge and rent for the refreshment kiosk. The mail clerk (Abby) receives the cheques and remittance advices. These documents are reconciled and used to prepare two copies of a remittance list. A copy of the remittance list is sent to the controller. The remaining copy is sent to treasury (Abby) with the cheques and the remittance advices. The treasury clerk (Abby) uses the copy of the remittance list and the remittance advices to update cash received and accounts receivable. The remittance list and the
remittance advices are filed. 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.
Assumptions
The assumptions of this project include: only one kiosk at one course, two employees that can expand to more employees, cups and ice are not listed in inventory; soft drink is purchased in bottles and delivered with a cup and ice. Members receive monthly statements (in one envelope from each "store" such as refreshment kiosk, pro-shop, restaurant, etc.) from the golf course, to be remitted. The member the a
s send cheque made payable to the refreshment kiosk for all of their refreshment stand charges. The golf course receives a statement from the refreshment kiosk for the total sales for the month to charge rent for the facilities (refreshment kiosk).
1. Database application
Using MS Access create the AIS design with all necessary tables, forms, queries, and reports for the day to day operations as described and drawn in your REA model. The application should have a navigational menu system designed for everyday users, i.e. switchboard
(**see below).
The following forms need to be created to capture the following:
a. Record Customer Sales Order
- OrderlD, Date, MemberlD, EmployeelD, ProductlD, Description, QuantityOrdered, InventoryPrice, Extension, and OrderTotal
- After you have tested the Sales Order Form, add about 12 orders for the month of September, 2017. These should range from a single line item to three or four line items.
Make sure you include the same member for 3 different orders.
b. Record Cash Receipts
- Think carefully about the design of your form and the details it needs to record. Don't forget that any cheque we receive from a member could relate to more than one Sales Order. In addition, although the case study states that members pay their monthly refreshment purchases in full, it is very possible that some Sales Orders may remain fully or partly unpaid each month.
- After you have tested the Cash Receipts form, add about 6 receipts for the month of October, 2017. Ensure that there are some outstanding totals, both for full sales order amounts and partial sales order amounts.
Reports need to be created for the following:
a. Customer Sales Order (for printing)
- The Sales Order must include an area for the member's signature
- In addition to the Sales Order total, the extension for each inventory item must be shown
b. Daily total sales report (broken down into sales by customer)
c. Monthly customer statements (sent to Golf Course Management)
d. Monthly sales report (sent to Golf Course Management)
e. Monthly sales and cash receipts report (to be given to Dad)
Note: Reports b through e must be set up as parameter queries first.
2. Business Report
Your business report of approximately 1,500 words must include the following:
1. You should submit several screen shots from the application as documentation. The screen shots include:
a. the relationship view of the database showing tables, attributes, and relationships,
b. both forms, and
c. all reports
Note: Your tutor will demonstrate how to do a screen capture in the tutorial.
2. In addition to the screen shots, you are to list and explain each query you have written. For example, if we refer below we can see that a Sales Invoice (report) has been created from a query.
Query Name: Sales Invoice Query
Purpose of the query:
The purpose of this query is to gather the information necessary to create a Sates Invoice (report) to be given to the customer. It contains the invoice number and date, customer name and address details and details pertaining to inventory purchased such as description, quantity ordered and selling price.
3. Identify eight potential risks for the system and corresponding potential internal controls (manual and computerised) to mitigate risks for the planned system. This is to be presented in a two column table listing business risks and corresponding suggested (or implemented) controls for the business.
4. Using the structured narrative table (requirement 2 from part 1), modify the duties of the refreshment kiosk to include Frank, a third employee, to provide adequate segregation of duties.
a. Explain (using examples) what is meant by the term 'segregation of duties'.
b. Modify the original structured narrative table by adding a new column for employee (see below). In the new column, identify which employee is performing each task with changes footnoted to explain why this improves segregation of duties.
5. Discuss three future improvements and additions to the database, why you consider them important and the order in which these would be implemented.
3. Class Presentation
Create a presentation that explains the database to Abby & Emma Schafer. Demonstrate how they can use the database by using the forms and generating the reports. Discuss three future improvements and additions to the database. Your presentation should take less than 15 minutes, including a brief question-and-answer period.
4. Self and Peer Assessment of Contribution to the Team Project
5. Continuity (i.e. consistency between all submitted items)
6. Overall company professionalism