Using information supplied in the Cactus Spine Country Club Case Study:

1. Draw an Entity Relationship Diagram to describe the data structures required for the development of a membership database which will track, all charges to and monies received from members.
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

CASE STUDY

CACTUS SPINE COUNTRY CLUB^[1]

Cactus Spine Golf Course is a private country club located outside Las Vegas. It currently provides its members with an 18-hole golf course, driving range, golf shop, and restaurant. Recently a committee was formed to examine the feasibility of expanding member services. In particular, they were interested in developing plans to design and finance a swimming pool complex for the property. Members currently pay for recreational services as they use them. In addition, they pay an initiation fee upon joining the club and an annual fee of $1200 that is due and payable on the anniversary date of membership.

Organisation Structure

Figure 1 shows the structure of the Cactus Spine Country Club. The club is governed by a board of directors that is elected from the membership. The board appoints a qualified member to the position of financial secretary for a two-year period. Although the board of directors is not compensated, the financial secretary receives a monthly stipend for services rendered to the club.

John Parr is the new club manager. He was hired two months ago after the retirement of Jerry Putter, who held the position for 10 years. John came to the club highly recommended and with considerable experience. John reports to the board of directors. He also works closely with the financial secretary, Mary Adams, and Joan Peters, his personal secretary.

Tom Birdie, golf pro, reports directly to John Parr. Tom oversees the maintenance of the course and the operations of the golf shop. He manages 3045 full and part-time employees, including Harry Eagle, golf shop manager, and John Greene, maintenance manager. Sally Jones also reports directly to John Parr. As restaurant manager, she supervises 1020 full and part-time staff members. There is a fairly high turnover in these areas since many employees are hired on a temporary basis for peak demand periods.

The accounting department is headed by Fran Smith, a long-time employee. Two accounting clerks and the club cashier report to Fran. In addition, several full-time golf shop employees are cross-trained in cashier and accounting duties. These people are used to maintain the seven-day staffing needs in the cashier and accounting departments and to further fill in during longer summer days.

The Golf Shop

Harry Eagle maintains a small inventory of quality athletic apparel and golf equipment in the golf shop. He and his staff also maintain a supply of related accessories and a cooler with cold beverages and snacks that can be heated in the microwave by golfers waiting to tee off. Golf reservations are made at the golf shop either by phone or in person. They are recorded manually on a daily reservation sheet (Fig. 2) that is preprinted with the available tee times. Up to four people can sign up for one tee time.

Members usually sign up in pairs or foursomes. When checking in for their reservation, members sign the daily reservation sheet. The clerk on duty then distributes the key for the mandatory electric cart and records the cart number next to the signature of the party responsible for the key. The members signatures authorize that the greens fee be charged to their account. Greens fees include the use of one electric cart per two golfers. Fees for a guest can be charged to the members account or they can be paid in cash. The guest column on the reservation sheet is used to record the few cash payments. These cash payments also are rung into the cash register.

All golf lessons, food, clothing, equipment, and accessory sales are recorded on the cash register whether they are cash or credit purchases. Pre-numbered sales invoices also are prepared in triplicate when members charge items at the golf shop (except for the greens fees). Members receive the original invoice. At the end of each day, Harry Eagle takes the daily reservation sheet, cash register tape, duplicate invoices, and cash to the cashier in the accounting department.

Cash Handling and Accounts Receivable Processing for the Golf Shop

When the club cashier receives the daily reservation sheet, cash register tape, invoices, and cash from the golf shop, the amounts from the register tape are compared to the charge invoices and the collected cash. These amounts are recorded by the cashier on the cash drawer summary sheet (Fig. 3). Harry, or his representative, then signs the cash drawer summary sheet to transfer the amounts collected. The cashier combines these funds with those that were collected from the restaurant and prepares a daily bank deposit for all cash sales. This deposit is separate from the one that is prepared to deposit the payments on account that arrive in the mail. All deposits are made by John Parrs secretary. The cashier turns over the daily reservation sheet and one copy of the charge invoices to the accounts receivable clerk, who enters the amounts in the accounting system. Posted invoices and the daily reservation sheet are maintained in a file in chronological order in the accounts receivable department. The second copy of the invoice is attached to the cash drawer summary sheet and filed in the cashiers file. At one time, the second copy was sent to the member with the monthly statement, but this procedure was deleted when the accounting records were automated. Members who question an item on their statement can obtain a copy of the invoice from the accounting department.

Restaurant and Catering Services

Sally Jones, restaurant manager, supervises 1020 full- and part-time individuals who provide breakfast, lunch, and light dinner services. She also arranges for outside catering when the club plans large, formal events or when club members want to utilize the facilities for private parties. Members may charge their daily purchases or pay cash. The daily sales procedures are similar to those that are used in the golf shop. The restaurant opens daily one-half hour before sunrise. It closes at 7 P.M. in the summer and at 4 P.M. in the winter.

Members may rent restaurant and meeting room facilities for private parties. All catering services for private parties must be arranged through Sally Jones. If a private caterer is used in place of or in addition to the regular staff, the club is billed directly and Sally charges the catering fees to the members account. This is in addition to the rental fee that is charged for using the facilities. The rental fee has remained stable for a number of years. Several private parties are held each month, and the facilities are usually fully booked during holiday and graduation periods. Sally Jones prepares a party charge slip in triplicate for billing members accounts for room rentals, food, services, and private catering fees. After the event, she mails the original to the member, keeps a copy, and sends another to the accounts receivable clerk.

Incoming Mail and Purchasing

John Parrs secretary, Joan Peters, opens all the mail and prepares a daily cash receipts prelist. She forwards the prelist, the cheques, and the remittance advices to the cashier. She also handles all correspondence and prepares purchase orders (Fig. 4) for the approval of John Parr. Department heads must submit purchase requisitions to her before any purchase orders are prepared. When bills arrive, she attaches a copy of the appropriate purchase order and forwards it to the accounts payable clerk after the club manager has reviewed and initialed the bill. Catering bills are handled separately. These are forwarded to Sally Jones, who authorizes payment by initialing the invoice and forwarding it to the accounts payable clerk. The accounts payable clerk inputs all bills to the accounts payable system. The accounting department supervisor reviews the accounts payable listing and authorises payment. The accounts payable clerk then prints the cheques, which must be signed by the financial secretary before being mailed. All accounts payable records are maintained in the accounting department.

The Current Accounting System

Fran Smith is the head of the accounting department. She started with the club 10 years ago as a clerk in the golf shop. After taking several night school courses in accounting, she was promoted to accounts receivable clerk 4 years ago. Fran then was promoted to her current position after the untimely death of the head accountant 3 years ago. Fran carefully has maintained the same procedures that were started before her promotion. However, there is no formal documentation for the current accounting system.

The current accounting system was installed by the former head accountant. It consists of a small IBM personal computer with a simple general ledger package that has accounts payable and accounts receivable modules. The system was designed so that the head accountant made all general journal entries. One clerk is responsible for maintaining the accounts receivable records. Another clerk is responsible for accounts payable records. This second clerk also is responsible for preparing, distributing, collecting, and reviewing time sheets and time cards. Once a week the payroll items are sent to an outside payroll service that prepares the cheques and payroll records. The number of employees fluctuates depending on the season of the year. Many temporary employees are hired during summer and holiday periods. Pay cheques must be picked up in person from the cashier.

The cashier, Cynthia Smith, is responsible for safeguarding all monies. Incoming receipts are deposited intact with separate bank deposit slips for cash sales, mail receipts, and other cashier-generated monies. Remittance advices are forwarded to the accounts receivable clerk with the prelist. Daily deposits are recorded on a cash receipts report, a copy of which goes to Mary Adams at the end of the month. Since members are provided with cheque-cashing privileges, Cynthia must replenish the amount of cash in the club safe twice a week. In addition to her other duties at the end of the day, she prepares the beginning bank for each cash register. The accounting department supervisor acts as cashier when Cynthia Smith is away from the cashiers cage. They each maintain a separate cash drawer. At the end of the day, all paperwork and monies must balance on the reconciliation sheet.

The accounts receivable clerk also is responsible for entering the annual fee into the billing system so that it will be included on the members bill. Although it is an annual fee, members are allowed to pay it in three monthly installments. The clerk keeps a manual card file which indicates when each members installment is to be entered into the billing system. The clerk dates and then initials the card when the amount is posted into the automated system.

Responsibilities of the Financial Secretary

Mary Adams is responsible for monitoring the financial position of the club. On a monthly basis, Mary receives a financial report detailing overall income and expenses. She reviews the results and presents the report at the board of directors meeting. Mary spends approximately 5 hours per week working at the club. She signs all cheques, and she receives reports which indicate the members who are in arrears. Mary is responsible for monitoring and contacting delinquent members before they are turned over to the board of directors for further action. She also receives copies of the validated bank deposit slips, the monthly bank statement, and a copy of the cash receipts report, which itemises the daily deposits. She reconciles these records.

Discussion at the Last Board Meeting

At the last board meeting, Mary reported the financial results for the previous month and noted that operating expenses again had exceeded operating revenues. Mary also reported that she and John Parr had reviewed the financial statements for the past year. They both agreed that the club must increase its revenues in the near future. Expenses have risen at a steady pace over the past six months. They recommended that an immediate 10% across-the-board increase in prices be approved by the board. John reported that the club must soon upgrade its maintenance equipment. He currently is working with Tom Birdie and the equipment custodian in inventorying and examining the condition of the maintenance equipment and electric golf carts. He stated that equipment records were incomplete.

During the meeting, John noted that the annual club dues were less than those at comparable facilities, but he refrained from making a recommendation to increase these dues. John also recommended hiring a firm to examine the accounting system and to make recommendations for its improvement. He explained that his requests to the accounting department for departmental reports cannot always be handled by the current system. Although some reports have been manually prepared, the department does not have the time to compile others. He also noted that the accounting department supervisor, Fran Smith, didnt appear to understand all of his concerns about the operations of the accounting system. After considering the reports by John and Mary, the board decided to hire outside consultants. The board wants a review of current operations and recommendations for upgrading the accounting system along with the development of a membership database. It is proposed that the membership database would track all charges to, and all monies received, from members.

[1] 1This case was prepared by Carol F.Venable, School of Accountancy, San Diego State University, as a basis for classroom discussion rather than to illustrate effective or ineffective handling of an accounting system. Copyright 1993 by Carol F.Venable.