Reference no: EM132188173

Question: 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.

Information related to above question is enclosed below:

Attachment:- MIST315_TREVOR.rar