A small family-owned chain of college sportswear, known as Kristi's Game Day, has become very well known in the past year. They have expanded their small shop at University of Michigan, to a $60,000,000 thriving business with stores in 88 universities throughout the U.S. They originally specialized in Wolverine wear but have now expanded to provide customers with their respective team mascot.
This expansion occurred from one university to the next and ran relatively smoothly. Kristi lives in California and is starting to grey, so she wants to revive herself with some sun and cut back on the 15 hours she spends each day managing Game Day. Unfortunately, Kristi's five siblings are social butterflies and are not diligent. Kristi is worried they will not commit to 15 hours a month, let alone 15 hours a day. In attempts to squelch the issues pre-disaster, Kristi decided to implement a computer system to streamline the process and win over her siblings with the "glamour" of technology. Being that Kristi's siblings are technology illiterate, she also decided to hire you to help them.
Your first week with Kristi has been very valuable. Kristi scheduled interviews for you with herself, all of her siblings, and all of the senior employees. The siblings as expected were not very helpful; however, the employees were very apt to help and shared a lot of great ideas. You took the following notes during your conversations:
Each physical store can carry all types of merchandise and mascots, but they usually specialize in local favorites. For example, at University of Michigan, customers can purchase tshirts with both Wolverines and Spartans (because all Wolverines secretly bleed green). Kristi's Game Day purchases its merchandise from local retailers to support the local economies. As such, the merchandise is very cheap but is marked up outrageously. Fortunately, because of all of the football publicity that the universities have been cited for on ESPN and because of Kristi's Game Day's international reputation, customers are willing to pay an arm and a leg for the logo. There is not a single store that is not successful.
Customers (except alumni) are not allowed to buy on credit; all sales are for cash only. Only alumni are allowed to buy on credit but they must pay for all purchases by the 15th of the following month. Additionally, since all of the merchandise is trendy, only 1 out of 2000 sales results in an item being returned. Kristi's Game Day refunds the alumni with a credit for the returned amount but returns cash to all other customers. Kristi's also has cash receipts from other sources, such as partnerships (which do not need to be modeled). Kristi does not link individual sales or cash receipts to customers (with the exception of alumni). Interestingly enough, Kristi records which employees are linked to particular sales and cash receipts for walk-in customers as well as deliveries to alumni. Total sales are recorded on a weekly, monthly and year-to-date basis for each store.
Normal business proceeds as follows. An employee decides approximately how much merchandise is needed each day to service "his/her" customers. They purchase that amount of merchandise from various retailers in the early morning, get it to the store, hang it, and have it ready by the noon store opening time. They are so good that there is hardly any on-hand inventory of merchandise that is carried from day to day. Information about merchandise is maintained as soon as a store purchases that type of merchandise. Each retailer supplies merchandise to only the local store (and Kristi needs to know what store a retailer supplies); merchandise is never transported between locations. This ensures that the merchandise will always be trendy and never out-of-style. A retailer can provide many different types of merchandise, and a merchandise type (e.g., baseball cap) can be provided by many different local retailers. Not all retailers provide the same quality (or types) of merchandise. Since it is important for Kristi's to only carry the highest quality merchandise, Kristi needs to know which merchandise should be purchased from which retailer (i.e., Aunt Eva has high quality shirts but only medium quality shorts, while Pam's Cheer has high quality foam hands and low quality baseball caps). Many retailers have been in business for a long time; however, a few new retailers have started up on an occasional basis. Kristi needs information about these retailers even though she might not have made any purchases from them, because she may purchase high quality merchandise from them in the future. Because different merchandise is plentiful in different areas, Kristi has a standard price that she is willing to pay for a given product based on the various conferences (e.g., Big Ten, Big 12, Big East, ACC, etc.). Please note that some merchandise is not available in every region.
Kristi handles all of the interviewing and hiring process at each store. This allows her to select generalists who will not only be good for the store, but also be willing to work for pennies. As a result, each employee can perform many functions such as purchasing, selling and hanging merchandise. These employees earn a wage based on their hourly rate and the number of hours worked. The hours worked are recorded on weekly time cards which are identified by Time Card Numbers which are unique throughout the company. Although employees are assigned to a store as soon as they are hired, they may be transferred to another store. An employee (who has at least 5 years of experience of working for Kristi) is assigned as a manager of the store. Every store has exactly one manager (although it may have had many managers over the course of many years). A store might not have any other employees assigned to it (it could be a store that was just opened or it might be a very small store), or a store can have many employees assigned to it. Kristi wants to keep track of the current manager of each store, when an employee was assigned as manager to a particular store, and the last day that employee was manager of that store. She also wants to know when a given employee was assigned to a given store (or the last day that employee worked in a given store). Kristi will never re-assign an employee (manager) to the same store.
Kristi's also owns a fleet of trucks. Painted with their Game Day logo in green and white, they are used to bring the merchandise from the local retailer to the store and to deliver the merchandise to alumni. Alumni will generally place an order with a store the day before so that they are guaranteed delivery of the type and quantity of merchandise they need for an upcoming game (Kristi wants to track the orders and sales by store). Walk-in customers do not place an order; they just select the merchandise they want off the racks. Alumni will place at most one order per day (which is always completely delivered the next day) and will have at least one merchandise item listed. Kristi also would like to be able to improve her cash planning and would like to know the amount and when cash receipts from these orders are expected (she also wants to track the actual cash receipts from each store). Each delivery is for exactly one order and only one truck is needed for an order. Each truck is uniquely numbered, and may be transferred between the stores. However, Kristi always likes to know what truck(s) is (are) currently assigned to what store. Each store has at least one truck assigned to it once is has begun to sell merchandise. A truck is generally assigned to one store within a week of the lease signing. Company trucks are leased on yearly contracts from truck vendors. Each contract supplies one truck for one year on a negotiated fee. Truck information is recorded as soon as the lease contract is signed. Lease payments against each contract are made monthly by Kristi who will send a truck vendor just one check to cover all existing contracts with that vendor.
Kristi's uses ten checking accounts for all of the disbursements and receipts. As a result, these accounts are used to pay retailers for their product and each employee for his/her service. One check (unique number) is cut for each merchandise purchase submitted properly (it will have a unique purchase invoice number) and for each time card.
Kristi does not track customer data for customers who are "walk-up" customers (i.e., they go to the store and get seafood, pay cash for the seafood and go home). She does track customer information for the alumni that call in orders that are then delivered the following day.
You have also kept a list of data elements that you want to include in Kristi's system. They are as follows (you might decide to include more based on your interviews, but this is a starting point):
Store # Sale $ amount
Employee name Employee SSN
Truck lease monthly payment QOH of each merchandise item at each store
Truck make Merchandise purchase $ amount
Merchandise trendy life Sale date
Item selling price (standard across US) Retailer #
Purchase date Truck VIN
Order date Retailer name
Truck vendor # Merchandise product #
Quantity of each merchandise item purchased Date of Merchandise return
Customer # Store phone #
Store inspector name for region Store manager #
Total hours worked ea. period per employee Check #
Employee merchandise hanging speed Region #
Qty of each merchandise item sold for each sale Truck model
Store address Check amount in dollars
Total QOH of each merchandise Merchandise purchase price for region
Truck mileage for this sale Truck vendor name
Year to date sales by store type Store type description
Date manager started in store Bank account #
Truck mileage for purchase of merchandise Merchandise description
Cash account balance Lease #
Purchase invoice # Sales Order #
Time card # Customer name
Date employee started in store Employee# who took order
Qty of merchandise item ordered by customer Merchandise quality
Store region # Name of Retailer's store
Cash Receipt # Cash Receipt Date
Customer address Date manager ended in store
Cash Receipt $ amount Merchandise return#
Sale# of returned merchandise Merchandise item# returned
Quantity of merchandise item returned Cash amount refunded on return
Customer# who returned merchandise Date employee ended in store
Store type # Sales invoice #
Region name
At this point, you realize that it is important to begin your business analysis, and you need to document the system you plan on implementing. In addition, you have not been able to convince Kristi's siblings that a system will be valuable to them. Because of their resistance, and unfamiliarity with computers, you plan on implementing a pilot system using ACCESS.
You have identified the following steps to guide your implementation. Do not expand the project beyond the information given.
1. Develop a REA model for Kristi's Game Day including classes, relationships, cardinalities, and attributes.
2. Use the REA Model and table structures to create a partial relational database in ACCESS.
3. Create tables in ACCESS only for the following entities and their related relationships:
SALE SALES ORDER PURCHASE
CASH DISBURSEMENT STORE MERCHANDISE
EMPLOYEE CUSTOMER RETAILER
4. Enter at least five instances (full set of new values) in each entity table (identified above), and the needed rows in the relationship tables. Make sure that the foreign key and data values are consistent with the other tables so that you can link tables and your information will be meaningful.
5. Create the following queries using your own tables and data (and save the queries).
a. Total sales by store. The stores should be arranged according to total sales, from highest to lowest.
b. Merchandise Quality: show the merchandise items and their quality provided by each local retailer arranged by retailer name (includes both number and name).
c. Summary Cash Disbursements for Employee: List each employee by name and number and the total amount that has been distributed to him or her. NOTE: This is not asking for merely the Month-To-Date wages for the employees, but requires a sum of the checks distributed to employees. The query should show the total of all of the checks the employee has received. Make sure that at least one employee has gotten more than one payroll check so the amount of the sum is greater than the value of one check (and it does not have to be the amount of the MTD wages).
6. Prepare a formal proposal of the system for Game Day. The proposal should include any assumptions you have made about the company that have affected your REA model. It should look professional (i.e. be typed, no spelling/grammar errors, etc.; however the REA model can be hand drawn).
Your Proposal to Game Day should include, at a minimum:
• Introduction Section
• Explanation of the REA Model
• REA Model
• Explanation as to why Game Day has been successful
• Opinion as to whether the success is sustainable and why/why not
• Critical factors that need to be managed for continued success
• Explanation of the prototype Access system, including the queries