Improving Decision Making: Using Database Software to Design a Customer System for Auto Sales

 

    Software skills: Database design, querying, reporting, and forms

    Business skills: Sales lead and customer analysis

 

This project requires you to perform a systems analysis and then design a system solution using database software.

 

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 other major auto-buyer Web sites. The company benefits from a good local word-of-mouth reputation and name recognition and is a leading source of information for Subaru vehicles in the Halifax 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 information such as the prospective customer’s name, address, telephone number, date of visit, and make and model of the vehicle in which the customer is interested. The representative also asks where the prospect 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.

 

However, Ace does not believe it has enough information about its customers. It cannot easily determine which prospects have made auto purchases, nor can it identify which customer touch points have produced the greatest number of sales leads or actual sales so it can focus advertising and marketing more on the channels that generate the most revenue.Are purchasers discovering Ace from newspaper ads, from word of mouth, or from the Web?Queries and reports

 

Prepare a systems analysis report detailing Ace’s problem and a system solution that can be implemented using PC database management software. The company has a PC with Internet access and the full suite of Microsoft Office desktop productivity tools. Then use database software to develop a simple system 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

    • 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 10 records per table. Consider whether you can use or modify Ace’s existing customer database in your design. Then use the system you have created to generate queries and reports that would be most useful to management. Create several prototype data input forms for the system and review them with your instructor. Then revise the prototypes.

 

 

Based on Ace’s stated requirements and relational database design theory this is one possible design :

 

o   CUSTOMER_PROSPECT

·         Customer_ID   Primary Key

·         Customer_First_Name

·         Customer_Last_Name

·         Customer_address_street

·         Customer_address_city

·         Customer_address_province

·         Customer_address_postalcode

·         Customer_Home_phone

·         Customer_cell_phone

·         Customer_Touchpoint

·         Customer_Prospect_Flag  Y/N

 

o   SALESPERSON

§  Salesperson_Id   Primary Key

§  Salesperson_First_Name

§  Salesperson_Last_Name

 

·         SALE(Order)

§  With all relevant data related to the SALE of the vehicle only, such as:

·         Order_ID                  Primary Key

·         Vehicle_Id

·         Customer_Id

·         Salesperson_Id

·         Sale_Order_date

·         Sale_Order_amount

 

o   VEHICLE

§  With all relevant vehicle/car  data only

·         Vehicle_ID (or VIN#)    Primary Key

·         Customer_id

·         Vehicle_Make

·         Vehicle_Model

·         Vehicle_List_Price

 

o   VISIT

§  Visit_Id   Primary Key

§  Visit Date        

§  Vehicle_Id

§  Customer_Id

§  Salesperson_Id

§  Visit Notes

·                        

 

 

·         Tables are related correctly?

o   When relating tables you should first determine the CARDINALITY between the tables. (see Access HELP)

o   In the relational model, tables can be related as any of:

Many-to-many, One-to-many, or one-to-one. This is said to be the cardinality of a given table in relation to another.

One-to-Many

o   You take the Primary key from the ONE side of the relationship (CUSTOMER) table and insert it into the table that represents the MANY side of the relationship (VEHICLE).  

 

 

Reports and Queries                                                                                          

Where do prospects/customers find out about Ace?

§  Newspaper, Web, Word of Mouth? Other

 

 

 

Systems Analysis Report                                                                                               8/10

·         Problem definition:

o   Ace does not have enough about its customers. It cannot easily determine which prospects have made auto purchases.  It also cannot identify which customer touch points have produced the greatest number of sales leads or actual sales.

·         Proposed Solution: Ace re-design their current database to ensure accurate collection and reporting on:

o   Customers, Prospects:which prospects become actual customers

o   Touch Points:which customer touch points produced the greatest number of sales or sales leads?

·         Costs: A table or list of potential costs for the development and implementation of this system (estimates or based on conceptual data).

·         Information Requirements:provide a detailed description of the functions the system must perform. This basically takes the user requirements reinterpreted as your new system requirements.

o   Prospective Customer Management:

§  Prospective Customers VISIT the dealership.  A salesperson records their information in the new system on a Prospective Customer DATA ENTRY FORM.

·         Prospective Customer Name, address, phone number date of VISIT, make and model of the vehicle of interest and customer Touch Point(s) are entered.

§  If a Prospective Customer purchases a vehicle,

·         then the Salesperson creates a Bill of Sale form/report (which can be printed by the system from the Prospective Customer Data Entry form when the SALE area is checked.

§  If Prospective Customer does not purchase a vehicle they leave the dealership to return at a later date again as a Prospective Customer.

o   Marketing Management

§  In order to better know its Customers have the following reports from the new system:

·         Prospective Customers who have made purchases

·         Customer Touch points which have produced the greatest number of SALESleads or actual sales.