Using information supplied in the Videos Tonite Case Study:
1.Draw an Entity Relationship Diagram to describe the data structures required for the development of a customer database which will trackthe hire, payment for and return of DVD’s and DVD players.
2.Convert the data structure into data tables to third normal form.
3.Implement the data tables in MSAccess (You will have to make assumptions about field lengths and types). Print out a relationship table which identifies the primary and foreign keys necessary to create the database for the proposed new system.
Programme learning goals:
- Be self-directed, reflective learners
- Be knowledgeable in their major field of study
- Be critical enquirers and problem solvers
- Be able to work collaboratively
- Be effective communicators .
Paper learning outcomes:
- Analyse and evaluate the accounting requirements for various business entities and devise practical accounting information
- Implement an accounting information system in respect of procedures, controls, and documentation.
- Evaluate computerised accounting packages and devise alternative methods of recording and reporting.
Videos Tonite is a family business that is owned and operated by Bob and Mary Smithson. The business consists of two DVD rental stores in a suburban area of Auckland. Their daughter, Barbara, is a full-time employee who eventually will take over the business when Bob and Mary reach retirement age in a few years. Each store is open for business from 10 A.M. until 8 P.M., Monday through Saturday, and from noon until 5 P.M. on Sunday. There are four part-time employees at each location. The three family members divide their time between the two stores so that new employees are scheduled to work with a family member.
Videos Tonite rents the most popular movies. Each store has approximately 2000 DVDs in stock, and there are 10 DVD players (DVDPs) that are available for rent. Bob orders all merchandise, and he has been quite successful in assessing the DVD rental market. Recently at the urging of his suppliers, Bob added a small line of new DVDs for sale. The DVDs seem to be selling well. He has placed two restocking orders since adding this merchandise. Each store also offers bagged popcorn and candy that is restocked on Monday and Friday of each week by the popcorn company supplier. The supplier leaves a delivery slip after each restocking, and the company sends an invoice every two weeks.
Videos Tonite rents only to members of the Videos Tonite Rental Club. A person becomes a member by filling out an application and paying a $75 deposit. The company requires the application information and deposit because the store originally experienced problems with DVDs and machines being stolen by customers. New customers now receive a customer number, which is recorded on their application and used on all subsequent transactions. Once a customer has rented items and promptly returned them six times, the customer can request that the $75 deposit be returned. Refund request forms are kept at the counter. A customer deposit log (Fig. 1) is used to maintain a record of the deposits and refunds. Mary designed the log and had it printed at a local printer, because there was a problem tracking deposit returns when they first initiated the deposit policy. All deposit refunds are made by cheque. If the original deposit has been returned and a customer does not respond to return requests, Bob will use the services of a collection agency. The collection agency has been successful in tracking down the few customers who move from the area.
Each rental DVD and DVDP has its own identification number and checkout card. All of the DVDPs and rental DVDs are kept behind the counter. When club members want to check out a movie or DVDP, they select its checkout card from the display rack and give the card to a clerk on duty. The clerk selects the DVD, writes the customer number on the checkout card, and places the checkout card in the DVD slot. The clerk then pulls the customer card (Fig. 2) from the customer file and enters the rental information. Customers may purchase an optional damage waiver by paying an additional 20 cents on each DVD rental. If they take this option, the clerk puts a check in the fee waiver column on the customer card. Customers are charged $65 for a damaged or lost DVD if they have not taken out the separate damage waiver. The customer card is stored in the checkout file box. When a DVD is returned, the checkout card must be put back on the correct display rack. The customer card is then put back in the customer file after the clerk puts a checkmark next to the item that was returned. When a customer card is full, a new one is prepared and the old one is destroyed.
A club member may also reserve a movie or machine by calling the store and having the clerk pull the checkout card, DVD, and customer card and hold them for the customer behind the counter.
When Videos Tonite first opened, the checkout system was adequate. Now, however, there are approximately 1500 club members at each store. Bob estimates that about 30% are regular customers. Another 40% come in less often, but they still rent DVDs on a regular basis. Two of the biggest problems are lost or misplaced cards and lengthy lines of customers waiting for service, especially during peak hours. Additional problems include an inability to determine which movies and DVDPs are available for rental, movies not being annotated with a checkmark on the member’s card after a return, and members’ finding out that the movies they reserved have been checked out to someone else. Bob and Barbara go through the checkout box periodically to find overdue rentals and misfiled cards. When the store is busy, cards sometimes get piled up behind the counter before they are filed.
The company only has a few suppliers. When invoices for merchandise are received, Bob checks that the invoice contains only those items that he actually received. Since Bob does all the ordering and unpacking, it is easy for him to keep track of the incoming merchandise. The invoice then goes to Mary, who prepares and signs all the checks. In fact, Mary does all of the bookkeeping. There are no formal financial statements. At the end of the year, Mary takes the records to their tax prepares. She uses columnar paper to maintain the equivalent of a cash receipts journal and an expenditures journal. Mary also prepares and keeps records for the payroll. At the end of each month, she carefully totals her work sheets and reconciles her records to the bank statement. Bob and Mary sit down at the end of each month to review the work sheets and their bank balances. They must manage their cash flows and plan for major purchases and improvements because there are several slow rental seasons during the year.
All sales are recorded on a cash register. In addition, a pre-numbered sales invoice is prepared in duplicate when a new member pays a deposit or when a customer pays for a damaged DVD or DVDP. The original is given to the member, and the copy goes in the cash register. At the end of the day, these invoices and the money in the cash register are bagged and put in the floor safe. Before each store opens, Mary opens the bag and prepares the bank deposit. Because the register drawer starts with $35 in change each day, Mary calculates and records the day’s sales revenue by subtracting the $35 and the amounts recorded on the invoices. She checks for missing invoices, and she makes sure that the total amount recorded on the register agrees with the amount in the sales drawer. She records the information on a daily sales sheet (Fig. 3) that is later consolidated on columnar paper. There is seldom a shortage or overage. The duplicate invoices for damage deposits are attached to the application form and stored in the application file in alphabetical order. The duplicate invoices for payments of damaged goods are attached to the daily sales sheet.
Last month one of Bob’s old college buddies, Doug Alano, offered to sell Bob his two DVD stores that are known as Adventures in Video. These stores are located in nearby suburbs and they have done well since they opened. The stores are being offered for sale because Doug’s doctor recommended that he take an early retirement due to the onset of a serious illness. Although Doug’s two stores have many operating procedures that are similar to Videos Tonite, they cater to several additional market segments with different products and service policies. In addition to daily rentals of popular current releases, which are targeted to residents in the immediate vicinity of the stores, there are specialty rental sections for foreign language and classic films. These specialty films target several market segments, and the two stores draw customers from the entire metropolitan area. These customers willingly pay a premium price to rent foreign and classic films, and they expect special services since they travel greater distances. Doug offers a variable rental period rate and variable fees. He has two full-time managers, Janet Wilson and Ricardo Hernandez, who oversee the foreign language and classic film stock and maintain a phone reservation system. Current membership is estimated to be at 12,000. Of the 9500 DVDs in stock, approximately 1500 are foreign language DVDs.
Bob, Mary, and Barbara are excited about this expansion opportunity. Mary, however, is worried because she already has her hands full with the records from the two stores. She says that doubling her work load would keep her tied to paperwork all day. They asked their tax accountant for advice. After examining the sales proposal and the tax records of Adventures in Video, he noted that it appeared to be a good financial opportunity to expand in the DVD business. He suggested, however, that their ability to manage the additional facilities might require some reorganization. He suggested that Bob, Mary, and Barbara speak with a consultant about automating the records and procedures. They agree that they must do something, but they aren’t sure what approach to take.