Ace Auto Dealers specializes in selling new vehicles from Subaru in Portland, Or

Question

Ace Auto Dealers specializes in selling new vehicles from Subaru in Portland, Oregon. The company advertises in local newspapers and is also listed as an authorized dealer on the Subaru Web site and other major Web sites for auto buyers. The company benefits from a good local word-of-mouth reputation and name recognition

When a prospective customer enters the Ace 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 noted on the form also. If the customer decides to purchase an

auto, the dealer fills out a bill of sale form.

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?

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. You can find this

database in the MyMISLab. Print out the database 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.

ust_ID Last Name First Name Street City State Zip Telephone 1 Flynn Colleen 44 Hastings Ave. Portland OR 97223-0974 503-665-5594 2 Bauer Richard 322 Scenic Drive Portland OR 97253-9944 503-297-4493 3 DelFino Joseph 119 Young Ave. Portland OR 97202-4432 503-668-5503 4 Hardman Pauline 57 Beekman Road Portland OR 97269-0055 503-789-9143 5 Yang Walter 83 Hunter's Trail Portland OR 97299-9453 503-789-1742 6 Kozlowski Peter 92 Mountain Road Portland OR 97266-0482 503-297-5780 7 Rodriguez Elena 751 Lounsbury Ave. Portland OR 97228-0516 503-297-7921 8 Stillman Charles 82 County Road Portland OR 97269-5043 503-789-4032 9 Nagel Ellen 901 Forest Road Portland OR 97253-9540 503-297-5021 10 Sheridan Amy 29 Glen Ridge Portland OR 97299-5903 503-789-4920 11 Venable Stephen 653 Ridge Road Portland OR 97223-4921 503-665-2943 12 Spadafino Anthony 43 8th Ave. Portland OR 97202-4320 503-668-2399 13 Ng Victor 72 Lincoln Pl. Portland OR 97266-2039 503-297-5927 14 Nielsen Elizabeth 67 Forest Trail Portland OR 97228-8522 503-297-7020 15 Downey Joseph 12 Monica Court Portland OR 97255-6902 503-668-3901 16 St. John Bettye 893 Pleasant Ave. Portland OR 97250-5030 503-665-2935 17 Keating Christopher 95 Roundhill Road Portland OR 97223-4939 503-665-1194 18 Melman Jonathan 42 College Dr. Portland OR 97228-3094 503-297-8925 19 Grote Teresa 69 Rocky Hill Portland OR 97202-5926 503-668-3049 20 Tripper Benjamin 723 Pine St. Portland OR 97223-9504 503-665-4929

Explanation / Answer

1. Requirement analysis:

Problem:

Ace Auto dealers wants to keep track of following information:

1. Customers visiting them and customer actually purchasing vehicles

2. Customer touch points which has produced maximum sales

3. Improve marketing using this data

Solution:

1. Record information regarding SalesRepresentative, Customer, VEHICLE and CustomerTouchPoints in database

2. Use Bi tools like Excel (Power Pivot) or Power BI or SSRS to create reports for analysing the data further

Benefits:

1. Excel tool is user firendly and charts and grids can be easily created as per requirement to analyze data

2. They can easily copy paste data for further computations

3. Reports can be generated easily and quickly

This solution will allow Ace to keep track of their customers, touch points and using that information they can find out where they need to focus to enhance their marketing strategies

Couple of business processes related to gathering customer information, bill payments etc will be changed from manual forms to feed data into database using tool

Database design:

CREATE TABLE SalesRepresentative(
   REPID INT IDENTITY PRIMARY KEY,
   NAME VARCHAR(100),
   ADDRESS VARCHAR(1000),
   DESIGNATION VARCHAR(100),
   NUMBEROFSALES INT
)

CREATE TABLE Customer(
   CUSTOMERID INT IDENTITY PRIMARY KEY,
   NAME VARCHAR(100),
   ADDRESS VARCHAR(500),
   PHONENUM VARCHAR(20),
   VISITDATE DATETIME,
   SOURCEID VARCHAR(500),
   VEHICLEID INT,
   PURCHASEDVEHICLE BIT
)

CREATE TABLE VEHICLE(
   VEHICLEID INT IDENTITY PRIMARY KEY,
   MAKE VARCHAR(100),
   MODEL VARCHAR(100)
)

CREATE TABLE CustomerTouchPoints(
   SOURCEID INT IDENTITY PRIMARY KEY,
   NAME VARCHAR(100)
)

Related Questions

Navigate

• Browse (All)
• Browse A
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.