Management Information Systems

MIST 315 M01 Introduction to Management Information Systems

Instructor: Dr. Benjamin Khoo (Homepage: https://sites.google.com/site/benjaminkhoo888/)

Email: [email protected] or [email protected]

Data Analysis Assignment (Spreadsheet Project)

ASSESSING THE VALUE OF CUSTOMER RELATIONSHIP MANAGEMENT

Trevor Toy Auto Mechanics

THE ISSUE

Trevor Toy Auto Mechanics has grown substantially over the past few years. Based on

various pieces of information, students are asked to help Trevor improve service and add a

level of personalization to his customers. However, Trevor has no idea who his best

customers are, the work that is being performed, or which mechanic is responsible for the

repairs.

THE TECHNOLOGY

Spreadsheet software and a spreadsheet file, TREVOR.xls, which contains 8 columns of

information and 750 transactions.

AVAILABLE INFORMATION

Repair information on cars, mechanics, type of repair, hours worked, number of hours to

complete work, cost of parts, total amount charged.

• CUSTOMER # - A unique number assigned to each customer

• CUSTOMER NAME - the name of the customer

• MECHANIC # - a unique number assigned to the mechanic who completed the work

• CAR TYPE - the type of car on which the work was completed

• WORK COMPLETED - what type of repair was performed on the car

• NUM HOURS - the number of hours it took to complete the work

• COST OF PARTS - the cost of the parts associated with completing the repair

• TOTAL CHARGE - the amount charged to the customer for the repair

A PRE-ANALYSIS OF THE INFORMATION

• Considering the type of analysis to be performed, all the information is important.

• There are several other important pieces of information that your students will need to

derive including:

1. NET REVENUE PER REPAIR = TOTAL CHARGE minus COST OF PARTS

2. MOST FREQUENT CUSTOMERS = COUNT (CUSTOMER #)

• Your students will need to aggregate the above new information in various ways – by type

of repair, by mechanic, and by customer.

IMPORTANT NOTE: If you are wondering why Trevor Toy employs 21 employees but only

6 mechanics, note that Trevor has a paint-and-body shop and an industrial repair division.

The information you are working with deals with only the car repair portion of Trevor’s

business.

Note Students are REQUIRED to hand in a hardcopy print-out of your answer/spreadsheet AND email the electronic spreadsheet copy to [email protected] (Students may cc [email protected]).

Total Possible Score: 25% Due date: Week #11