Consulting at the Laser ISP (LISP) Company: Using Excel(TM) Metrics Capabilities to Solve Semi-structured Management Problems

Journal of Information Systems Education, Winter 2003 by Dean, Douglas L, Lowry, Paul Benjamin

Since no technician initially visits the house with install types 2 and 3, no labor is charged in Table 1. However, for the do-it-yourself installation kit (installation type 2) a cost of $20 is incurred to package these materials into a kit and mail it to a customer. For self-installation with the additional self-help tutorial CD (installation type 3), the cost is $35, which includes the $20 the packaging and mailing cost as well as $15, which covers the initial developmental costs and duplication of the CD tutorial. Thus, the last column in Table 1 should contain one of the following costs: a cost based on labor time spent for installation type 1, $20 for installation type 2, or $35 for installation type 3.

3.3 Customer Service

In addition to collecting data about the various installation types, your consulting team has also collected in-depth data so that you can analyze the customer service at LISP. This data has been categorized into two primary sources: (1) help desk calls to LISP customer support from customers having problems, and (2) actual home visits by technicians to solve customer problems after the initial installation.

The help desk at LISP helps customers who call with technical problems during and after installations. When customers call with problems, help desk personnel try to diagnose the problem and walk the customer through a series of steps in attempt to discover and resolve the problem. If help desk personnel are unable to resolve the problem over the phone, a technician is sent to the customer's house to further troubleshoot the problem. The help desk data from August has been summarized by your team in Table 2; the trouble-ticket data is summarized in Table 3.

Because your staff consultants lack business and Excel(TM) experience they were not able to complete all the information on these tables, but they were able to collect some useful information for you that will help you complete these tables. For example, in Table 2 the Call ID column contains a unique identifier for each call. The Installation ID in this table refers back to the Installation ID in Table 1. This makes it possible to track exactly which installations had problems that subsequently resulted in a help desk call. Table 2 includes one column that describes the problem and another column that can be used to calculate the length of each help desk call, based on the difference between the ending and beginning time. The call length should be multiplied by the rate of $60/hour to determine the cost of the call to the company. Finally, if the problem is resolved a "Y" is placed in the last column. If the problem is not resolved, an "N" is placed in the last column and a trouble ticket is opened (in Table 3) so that an onsite service technician can be sent to the customer's home to troubleshoot the problem.

Table 3 (trouble tickets) has an important relationship with Table 2 (help desk calls). When a trouble ticket is opened, it is assigned a unique identifier and is linked back to the Help Desk Data table through the Call ID (note that Call ID in Table 2 is not the same as the Initial Install ID in Table 1). When the onsite service technician completes the call at the customer's house, the technician records a brief comment describing the source of the problem in Table 3. The cost to perform the visit should be determined as previously noted. The costs to send a technician on a trouble ticket call is S90 per hour-the same hourly rate as when LISP sends a technician to the customer's home to do an initial onsite installation.