DATABASE EXERCISE: DESIGNING A CUSTOMER SYSTEM FOR AUTO SALES
Ace Auto Dealers specializes in selling new vehicles from Subaru. The company advertises in local newspapers and is also listed as an authorized dealer on the Subaru Web site and the
major Web sites for auto buyers. The company benefits from good local word-of-mouth reputation and name recognition and is a leading source for Subaru vehicles in the Prince Edward Island area.
When a prospective customer enters the showroom, he or she is greeted by an Ace sales representative. The sales representative manually fills out a form with such information as the prospective customer’s name, address, telephone number, date of visit, and model and make of vehicle in which he or she is interested. The representative also asks where the prospective buyer heard about Ace—whether it was from a newspaper ad, the Web, or word of mouth and this information is also noted on the form.
If the customer decides to purchase an auto, the dealer fills out a bill of sale form, listing the vehicle identification number, vehicle make and model, colour, options selected, base price, total price, and date of the sale along with the purchaser’s name, address, and telephone number. This form also shows the amount of the total purchase price that was financed. Both forms are filed in manual folders—one for sales prospects and another for actual purchases.
Ace does not feel it knows enough about its customers. It cannot easily identify repeat customers at its dealership or customers who have purchased a Subaru in the past from another dealer. It cannot easily determine which prospective buyers have made auto purchases or the percentage of those who have been converted into buyers. Nor can it identify which customer touchpoints have produced the greatest number of sales leads or actual sales so it can focus its advertising and marketing more on the channels that generate the most revenue. Are purchasers coming from newspaper ads, from word of mouth, or from the Web? Additionally, the firm cannot tell whether its customers are more interested in no-frills cars or ones with luxury options. Prepare a systems analysis report detailing Ace’s problem and a systems solution that can be implemented using PC database management software. Then, use database software to develop a simple systems solution. Your systems analysis report should include the following:
- Description of the problem and its organizational and business impact.
- Proposed solution, solution objectives, and solution feasibility.
- Costs and benefits of the solution you have selected. The company has a PC with Internet access and the full suite of Microsoft Office desktop productivity tools.
- Information requirements to be addressed by the solution.
- Management, organization, and technology issues to be addressed by the solution, including changes in business processes.
On the basis of the requirements you have identified, design the database and populate it with at least 20 records per table. Print out the database design. Then, use the system you have created to generate queries and reports that would be of most interest to management. Create several prototype data input forms for the system and review them with your instructor. Then, revise the prototypes.
