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

2) Using an Excel(TM) function, compute the installation cost in Table 1 for each customer (Install ID). Also calculate how much each Help desk Call costs for each Call ID in Table 2. Calculate how much each Trouble Ticket visit costs for each Trouble Ticket ID in Table 3. Make sure you use the appropriate formulas to make the computations.

3) Installation type is shown in Table 1 for each Install ID, but is not yet filled in for the related records shown in Tables 2 and 3; hence, you need to write a function in Table 2 to obtain the right installation type from Table 1 for each help desk call in Table 2. Once the Installation Type has been filled in Table 2, you can use a similar function to fill in the Installation Type in Table 3.

4) Finally, your engagement partner kindly reminded you that correctly completing Tables 1 through 3 is critical in being able to create correct summary data.

4.2 Guidelines for Completing Table 4

Based on your meeting with your engagement partner, you created a skeleton draft of the Excel(TM) table (see Table 4) you think would effectively summarize Tables 1-3.

In a follow up conversation she has reminded you that you need to complete your Excel(TM) solution in a way so that the answers calculated by you for Table 4 can be completed with functions that do not require the data in Tables 2, 3, and 4 to be sorted. Other specific guidelines for completing Table 4 include the following:

1) For each installation approach, use an Excel(TM) function to count the number of installations, help desk calls, and trouble ticket visits.

2) For each installation approach aggregate the total cost. For example, for Install Type 1, determine the total initial installation costs, the help desk support costs, and the trouble ticket visit costs. Include all of these three cost components to compute the total cost for all customers that received Install Type 1. Follow the same steps to calculate the total costs for all customers that received Install Type 2 and Install Type 3.

3) Next, for each of the installation approaches, calculate the average cost per occurrence of an initial installation, help desk call, and trouble ticket visit. Also, taking into a ccount alle osts from t he three tables, compute the total average cost per customer for each install type.

5. PARTB: MANAGEMENT RECOMMENDATIONS

At this point in the project, you have met with LISP executive management and presented the detail data from Tables 1-3 and the summary data from Table 4. They are impressed by the work you have performed, but this has opened up a new round of questions t hat t hey want you to answer in a written report that is to comprise your second deliverable. According to your firm's standards such a report must be written in Word(TM), it should be highly professional in appearance, and it must directly reference the questions the executives have given you. Your staff has organized the questions from the meeting, and has numbered them, so that you can easily respond to and directly reference each question number, as follows: