ASSESSING THE VALUE OF CUSTOMER RELATIONSHIP MANAGEMENT Trevor Toy Auto Mechanic

Question

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.

Explanation / Answer

Below please find my answer -

NET REVENUE PER REPAIR By Type Of Repair
Work Type Sum of Net Reveue Per Repair
Brakes 14158
Engine Overhaul 30563
Flat tire fix 6730
Hose/Belt Replacement 5978
Oil change 4014
Grand Total 61443

Net REVENUE PER REPAIR By Mechanic
Mechanic# Sum of Net Reveue Per Repair
1 3463
2 11447
3 3300
4 33824
8 5677
12 3732
Grand Total 61443

Net Revenue Per Repair By customer
Customer Name Sum of Net Reveue Per Repair
A. J. Henley 440
Adanfoh J. Okojie 1598
Al Chardwick 590
Alfred B. Pentony 478
Andre C. Dasent 498
Anthony J. Mcintyre 1015
Arnold Staloff 673
Austin McGreal 501
Barbara Potts Esq 843
Bower Lewis Thrower Architects 1783
Carl L. Primavera 778
Cleosie Kirland 967
Cozen & O'Connor PAC 326
Daniel Cher 219
Daniel J. Keating III 186
David McCarron 52
David P. Montgomery 2580
Denise Joy Smyler 1097
Dennis Martin 1066
Dominic Chila 1328
Douglas Overton 258
Dugan Brinkmann Maginnis & Pace 1808
Eckert Seamans PA Government PAC 961
Emanuel Kelly 1729
Ernest L. Strother Jr. 508
F. Michael Medway 420
Fareed Ahmed 1071
Frederick B. Waechter 1188
Garnett C. Littlepage 566
Gary Reinert Sr. 227
Glen B. Bronson 582
GPCC PENNPAC 1066
Herbert E. Wetzel 1609
Howard A. Trauger 243
Irvin J. Borowsky 1000
James A. Ounsworth 474
James J. Anderson 959
James W. Franklin Sr. 495
James W. Schleiden 939
John B. Haines IV 587
John F. Harkins 1506
John K. Binswanger 930
John R. Kibblehouse 652
Jon R. Hamilton 440
Joseph G. Pulicare 493
Joseph Gatta 398
Karen A. Robinson 262
Lawrence F. Flick 1012
Lawrence J. Arem Esq 1129
Leonard Tillery & Sciolla 1241
Leonel J. Simmons 1009
Mark Segal 975
Mary C. Hamilton 334
Mattioni 185
Mercedes Sanchez 695
Michael A. Stepnowski 1117
Michael Disandro 457
Michael J. J. Barrist 1833
Noel O. Lowe 349
Norfolk Southern Corp. Good Gov. Fund 157
Pennoni Associates PAC 1110
Richard M. Sand 387
Robert C. Boucher 375
Robert L. Nicholas 419
Russell Reid Responsible Wastewater Management 1254
Scott A. Isdaner 287
Stephen P. Depalma 1401
Sylvan M Cohen Esq 917
Thelma Duggin 424
Thomas G. Moore 1152
Thomas J. Maher 399
Thomas P. Godish 372
Trinity Advisors 1042
Warren C. Pannell 456
Wayne L. Bromley, Jr 532
William E. Morin 2034
Grand Total 61443

Count Of Customer

Customer Name Count
A. J. Henley 11
Adanfoh J. Okojie 13
Al Chardwick 12
Alfred B. Pentony 13
Andre C. Dasent 11
Anthony J. Mcintyre 10
Arnold Staloff 13
Austin McGreal 16
Barbara Potts Esq 16
Bower Lewis Thrower Architects 12
Carl L. Primavera 16
Cleosie Kirland 12
Cozen & O'Connor PAC 7
Daniel Cher 5
Daniel J. Keating III 8
David McCarron 2
David P. Montgomery 23
Denise Joy Smyler 8
Dennis Martin 8
Dominic Chila 7
Douglas Overton 6
Dugan Brinkmann Maginnis & Pace 11
Eckert Seamans PA Government PAC 11
Emanuel Kelly 9
Ernest L. Strother Jr. 13
F. Michael Medway 6
Fareed Ahmed 6
Frederick B. Waechter 7
Garnett C. Littlepage 11
Gary Reinert Sr. 5
Glen B. Bronson 13
GPCC PENNPAC 7
Herbert E. Wetzel 6
Howard A. Trauger 8
Irvin J. Borowsky 7
James A. Ounsworth 12
James J. Anderson 8
James W. Franklin Sr. 12
James W. Schleiden 11
John B. Haines IV 9
John F. Harkins 8
John K. Binswanger 7
John R. Kibblehouse 15
Jon R. Hamilton 11
Joseph G. Pulicare 8
Joseph Gatta 11
Karen A. Robinson 8
Lawrence F. Flick 14
Lawrence J. Arem Esq 14
Leonard Tillery & Sciolla 11
Leonel J. Simmons 14
Mark Segal 7
Mary C. Hamilton 8
Mattioni 5
Mercedes Sanchez 13
Michael A. Stepnowski 14
Michael Disandro 13
Michael J. J. Barrist 13
Noel O. Lowe 6
Norfolk Southern Corp. Good Gov. Fund 7
Pennoni Associates PAC 7
Richard M. Sand 6
Robert C. Boucher 11
Robert L. Nicholas 9
Russell Reid Responsible Wastewater Management 10
Scott A. Isdaner 6
Stephen P. Depalma 10
Sylvan M Cohen Esq 8
Thelma Duggin 11
Thomas G. Moore 11
Thomas J. Maher 7
Thomas P. Godish 6
Trinity Advisors 12
Warren C. Pannell 8
Wayne L. Bromley, Jr 8
William E. Morin 12
Grand Total 750

Customer Per Mechanic Count

Count of MECHANIC # Mechanic#
Cutomer Name 1 2 3 4 8 12 Grand Total
A. J. Henley 1 2 2 1 2 3 11
Adanfoh J. Okojie 1 2 3 2 5 13
Al Chardwick 1 1 1 4 5 12
Alfred B. Pentony 1 2 2 2 3 3 13
Andre C. Dasent 1 2 1 4 1 2 11
Anthony J. Mcintyre 2 1 1 1 2 3 10
Arnold Staloff 1 2 2 3 2 3 13
Austin McGreal 6 2 2 2 4 16
Barbara Potts Esq 5 3 2 2 4 16
Bower Lewis Thrower Architects 1 2 2 3 2 2 12
Carl L. Primavera 5 3 2 2 4 16
Cleosie Kirland 4 1 2 3 2 12
Cozen & O'Connor PAC 1 1 1 1 2 1 7
Daniel Cher 2 1 1 1 5
Daniel J. Keating III 2 2 1 3 8
David McCarron 2 2
David P. Montgomery 2 3 2 7 2 7 23
Denise Joy Smyler 2 1 1 1 3 8
Dennis Martin 1 2 2 1 2 8
Dominic Chila 2 1 2 1 1 7
Douglas Overton 1 2 1 2 6
Dugan Brinkmann Maginnis & Pace 1 2 3 1 4 11
Eckert Seamans PA Government PAC 1 2 1 2 2 3 11
Emanuel Kelly 1 1 2 1 2 2 9
Ernest L. Strother Jr. 1 2 2 2 3 3 13
F. Michael Medway 1 1 1 2 1 6
Fareed Ahmed 1 2 1 2 6
Frederick B. Waechter 1 2 1 2 1 7
Garnett C. Littlepage 2 1 4 1 3 11
Gary Reinert Sr. 2 1 1 1 5
Glen B. Bronson 1 2 2 2 3 3 13
GPCC PENNPAC 2 1 2 2 7
Herbert E. Wetzel 1 1 1 2 1 6
Howard A. Trauger 1 1 1 2 1 2 8
Irvin J. Borowsky 2 1 2 2 7
James A. Ounsworth 4 2 2 2 2 12
James J. Anderson 2 1 1 1 3 8
James W. Franklin Sr. 1 1 1 3 1 5 12
James W. Schleiden 3 1 2 3 2 11
John B. Haines IV 1 2 2 1 3 9
John F. Harkins 2 1 2 1 2 8
John K. Binswanger 2 1 2 2 7
John R. Kibblehouse 1 2 3 2 3 4 15
Jon R. Hamilton 2 1 4 1 3 11
Joseph G. Pulicare 1 1 1 2 1 2 8
Joseph Gatta 1 1 2 2 2 3 11
Karen A. Robinson 2 1 2 1 2 8
Lawrence F. Flick 5 1 2 2 4 14
Lawrence J. Arem Esq 5 1 2 2 4 14
Leonard Tillery & Sciolla 1 2 1 2 2 3 11
Leonel J. Simmons 5 1 3 1 4 14
Mark Segal 2 1 2 2 7
Mary C. Hamilton 1 2 1 2 2 8
Mattioni 2 2 1 5
Mercedes Sanchez 1 2 2 2 3 3 13
Michael A. Stepnowski 5 1 2 2 4 14
Michael Disandro 1 2 2 2 3 3 13
Michael J. J. Barrist 1 2 3 2 5 13
Noel O. Lowe 1 1 1 2 1 6
Norfolk Southern Corp. Good Gov. Fund 2 1 2 2 7
Pennoni Associates PAC 2 1 2 1 1 7
Richard M. Sand 1 1 1 2 1 6
Robert C. Boucher 1 2 2 2 2 2 11
Robert L. Nicholas 2 1 2 1 3 9
Russell Reid Responsible Wastewater Management 1 1 2 2 4 10
Scott A. Isdaner 1 2 1 2 6
Stephen P. Depalma 1 1 2 3 3 10
Sylvan M Cohen Esq 2 1 1 1 3 8
Thelma Duggin 2 1 4 1 3 11
Thomas G. Moore 3 1 2 3 2 11
Thomas J. Maher 1 2 1 2 1 7
Thomas P. Godish 1 2 1 2 6
Trinity Advisors 4 1 2 3 2 12
Warren C. Pannell 1 2 1 2 1 1 8
Wayne L. Bromley, Jr 1 2 1 2 1 1 8
William E. Morin 1 2 3 2 4 12
Grand Total 139 96 59 150 121 185 750

Customer Per Work Type Count

Count of WORK COMPLETED Column Labels
Row Labels Brakes Engine Overhaul Flat tire fix Hose/Belt Replacement Oil change Grand Total
A. J. Henley 2 3 2 4 11
Adanfoh J. Okojie 2 1 4 2 4 13
Al Chardwick 4 4 4 12
Alfred B. Pentony 2 6 3 2 13
Andre C. Dasent 2 4 1 4 11
Anthony J. Mcintyre 1 1 3 2 3 10
Arnold Staloff 2 5 2 4 13
Austin McGreal 3 5 2 6 16
Barbara Potts Esq 4 5 2 5 16
Bower Lewis Thrower Architects 2 1 4 2 3 12
Carl L. Primavera 4 5 2 5 16
Cleosie Kirland 2 1 4 3 2 12
Cozen & O'Connor PAC 1 3 2 1 7
Daniel Cher 1 1 2 1 5
Daniel J. Keating III 5 3 8
David McCarron 2 2
David P. Montgomery 5 1 7 2 8 23
Denise Joy Smyler 1 3 1 3 8
Dennis Martin 1 4 1 2 8
Dominic Chila 1 1 1 1 3 7
Douglas Overton 2 1 2 1 6
Dugan Brinkmann Maginnis & Pace 2 1 4 1 3 11
Eckert Seamans PA Government PAC 2 1 3 2 3 11
Emanuel Kelly 1 1 4 2 1 9
Ernest L. Strother Jr. 2 5 3 3 13
F. Michael Medway 1 2 2 1 6
Fareed Ahmed 2 1 1 2 6
Frederick B. Waechter 2 1 1 2 1 7
Garnett C. Littlepage 3 4 1 3 11
Gary Reinert Sr. 1 1 2 1 5
Glen B. Bronson 2 5 3 3 13
GPCC PENNPAC 1 1 2 3 7
Herbert E. Wetzel 1 1 1 2 1 6
Howard A. Trauger 1 5 1 1 8
Irvin J. Borowsky 1 1 2 3 7
James A. Ounsworth 4 3 2 3 12
James J. Anderson 1 3 1 3 8
James W. Franklin Sr. 4 3 1 4 12
James W. Schleiden 2 1 3 3 2 11
John B. Haines IV 2 2 1 4 9
John F. Harkins 1 1 2 2 2 8
John K. Binswanger 1 3 3 7
John R. Kibblehouse 2 6 3 4 15
Jon R. Hamilton 2 4 2 3 11
Joseph G. Pulicare 1 5 1 1 8
Joseph Gatta 1 5 2 3 11
Karen A. Robinson 1 3 2 2 8
Lawrence F. Flick 2 1 5 2 4 14
Lawrence J. Arem Esq 2 1 6 2 3 14
Leonard Tillery & Sciolla 2 1 3 2 3 11
Leonel J. Simmons 2 1 7 1 3 14
Mark Segal 1 3 3 7
Mary C. Hamilton 2 4 2 8
Mattioni 2 2 1 5
Mercedes Sanchez 2 5 3 3 13
Michael A. Stepnowski 2 1 6 2 3 14
Michael Disandro 2 7 3 1 13
Michael J. J. Barrist 2 1 4 2 4 13
Noel O. Lowe 1 1 2 2 6
Norfolk Southern Corp. Good Gov. Fund 4 3 7
Pennoni Associates PAC 1 1 1 1 3 7
Richard M. Sand 1 1 2 2 6
Robert C. Boucher 2 4 2 3 11
Robert L. Nicholas 2 3 1 3 9
Russell Reid Responsible Wastewater Management 1 1 3 2 3 10
Scott A. Isdaner 2 1 2 1 6
Stephen P. Depalma 1 1 3 3 2 10
Sylvan M Cohen Esq 1 4 1 2 8
Thelma Duggin 2 4 2 3 11
Thomas G. Moore 2 1 3 3 2 11
Thomas J. Maher 2 2 2 1 7
Thomas P. Godish 2 2 2 6
Trinity Advisors 2 1 4 3 2 12
Warren C. Pannell 2 3 1 2 8
Wayne L. Bromley, Jr 2 3 1 2 8
William E. Morin 2 1 2 2 5 12
Grand Total 127 34 261 128 200 750

Related Questions

Navigate

• Browse (All)
• Browse A
• Subjects

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