CASE STUDY 1 - BONES & NARBLE BOOKS (SYSTEMS DOCUMENTATION)
Business Background
Bones & Narble Books is a large book distributor whose office is located in Springvale. It was established in 1982 by two friends (Bob and Nick) who invested some of their savings to buy an abandoned warehouse and convert it into a bookstore, mainly selling used books that were donated by friends and family. The business proved to be very popular and by the end of the first financial year it had grossed $20,000 in sales.
Over the next decade more employees were hired, more books were traded in and more sales were achieved each passing year. However, during the mid-1990s the business was faced with two problems: many large, upscale book-stores were being built in the area, and the use of the Internet for finding and ordering books was becoming cheaper and more popular for current customers. In 1995 sales finally started to decline.
Deciding to take a risk because of the newfound competition, Bob and Nick closed their doors to the local trade, invested more money to expand the building and transformed the business from just selling used books to being a distributor of new books. The business now stores books that have been sent to it by publishers and then resells the books to large bookstore chains upon request.
Bones & Narble has rapidly become one of the largest book distributors in Australia. Although the business is still at its original location in Springvale, it distributes books to each state and territory of Australia, making over $1 million per year. From simple beginnings of two workers, the business has expanded to now having more than 145 employees.
As already mentioned, all of Bones & Narble's customers are large-chain bookstores who themselves see many millions of dollars in revenue per year. However, some of these bookstores have had bad relationships with the business in the past year. There have been many disputes between them, such as books that were ordered from Bones & Narble but were never sent, poor inventory management by Bones & Narble, and the inability of the business to provide legitimate documentation of transactions.
According to projections of this year's financials, the sour relationship that Bones & Narble Book and many of its customers have is going to take a toll on year-end revenue. Bob is convinced that some of the business's problems are due to lack of proper internal controls. You have been hired as an independent expert to inspect and analyse the internal controls currently in place at Bones & Narble Books. You start by documenting the existing systems to better understand the people, processes and documents involved.
Required:
Using Microsoft Visio:
a) create a physical data flow diagram of the Purchases system
b) create a logical data flow diagram of the Cash Disbursements system
c) create a systems flowchart of the existing systems (both purchases and cash disbursements)
Please note: To help you create the DFDs, you may wish to create a Structured Narrative Table first, as demonstrated in the tutorials. If you decide to do a Structured Narrative Table, there is no need to submit it.
This question does not require you to analyse the internal controls currently in place and their weaknesses. All you are doing is documenting the existing systems to better understand the people, processes and documents involved.
CASE STUDY 2 - JEN & BERRY'S ICE-CREAM SHOP (REA diagram and relational database structure)
‘Jen & Berry's' is a small ice-cream shop located on Warrigal Road, just over the road from Holmesglen Institute. Jen & Berry's serves walk-in customers only. The shop carries 26 flavours of ice cream.
Customers can buy cones, sundaes or shakes. When a customer pays for an individual purchase, a sale transaction usually includes just one item. When a customer pays for a family or group purchase, however, a single sale transaction includes many different items. All sales must be paid for at the time the ice cream is served. Joe's maintains several bank accounts but deposits all sales receipts into its main cheque account.
Required:
a) Draw an REA diagram, complete with cardinalities, for Jen & Berry's revenue cycle. Use the REA Entity-Relationship (ER) modelling techniques as discussed in class. You can use Microsoft Word or Visio to complete this part.
b) Convert your REA diagram into a relational database structure, i.e. tables and relationships. At this stage you are designing the database on paper. There is no need to start entering it in Access. Make sure you use meaningful table and attribute names and show primary and foreign keys. Use the conventions as discussed and shown in classes. You can use Microsoft Word or Visio to complete this part.
CASE STUDY 3 - SCHAFER REFRESHMENT KIOSK
a. Database application
b. Business report
c. Class presentation
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 3/4 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 month, 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.
Schafer Refreshment Kiosk (SRK) Requirements:
Part a. Database application
1. 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 the REA model in Appendix 1. The application should have a navigational menu system designed for everyday users, i.e. switchboard.
The following forms need to be created to capture the following:
a. Record Customer Sales Order
• OrderID, Date, MemberID, EmployeeID, ProductID, Description, QuantityOrdered, InventoryPrice, Extension, and OrderTotal
• After you have tested the Sales Order Form, add about 12 orders for the month of April, 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 and update Accounts Receivable
• 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 May, 2017. Ensure that there are some outstanding totals, both for full amounts and partial 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)
f. Monthly outstanding accounts
Note: Reports b through f must be set up as parameter queries first.
2. Part b. 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.
Purpose of the query:
The purpose of this query is to gather the information necessary to create a Sales 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 (provided in Appendix 2), 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.
Part c. 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.
Outputs/Submission requirements
Refer to Assessment Requirements and What needs to be submitted? below.
The actual application should be named SRK plus the students' last names (e.g., SRK_jones_milligan.mdb). The application should be submitted electronically through Brightspace. Also, some amount of data should be entered so that you can test whether your queries and reports work as planned.
What needs to be submitted?
1. Case Study 1 - Microsoft Visio File containing:
a) a physical data flow diagram of the Purchases system
b) a logical data flow diagram of the Cash Disbursements system
c) a systems flowchart of the existing systems (both purchases and cash disbursements)
2. Case Study 2 - Word and/or Visio file containing:
a) An REA diagram, complete with cardinalities, for Jen & Berry's revenue cycle.
b) A relational database structure, i.e. tables and relationships.
3. Case Study 3
a) Microsoft Access file containing your database application
b) Word document containing your Business Report Class Presentation to be given on Tuesday 6 June