Excel Case: Julias Cookies IT Help Desk Employee Insights

What You Need

Download and save the following file:

Chapter 6 Raw Data.xlsx

Case Background

Big Picture:

Identify help desk employee patterns.

Detail:

Why is this data important, and what questions or problems need to be addressed?

IT help desk information helps the company monitor underlying problems that need to be addressed within systems. This information is useful to the ERM team, Internal Audit department, and management, as recurring technology issues can indicate failing internal controls or unmitigated risks.

The data generated by the IT help desk offers more insight when it is combined with another data source, such as the human resources file. Enriching the help desk data set through a join of the human resources data will allow management to monitor which departments employees are submitting a larger number of tickets than employees in other departments, which may indicate a need for retraining.

To identify help desk employee patterns, we should consider the following questions: Which office location had the most help desk tickets? Which department had the highest help tickets per employee ratio? Which department had the lowest requestor overall satisfaction score?

Plan:

What data is needed, and how should it be analyzed?

The necessary data is extracted from the central ticketing management system, which for Julias Cookies is ServiceNow.

To ensure an appropriate amount of data is available to identify patterns, we use data covering a period of eight months: 3/1/202X through 10/31/202X.

The second data set is a human resource set of all active employees.

Now its your turn to evaluate, analyze, and communicate the results!

Hint: To answer the following questions, connect the tables using their ID fields.

Answer Questions:

1.What percentage of tickets submitted belonged to the Main CampusChicago location?

2.Which employee type submitted a total percentage of 0.08% of all tickets during this time period?

3.Which department had the lowest number of tickets submitted related to login issues? (Hint: Issue category Access/Login.)

4.How many tickets were submitted by employees with less than two years tenure?

5.What percentage of employees in the Finance department are dissatisfied with the help desk?

6.How many medium severity tickets were submitted by employees in the Investments department?

7.On what date did employees in the Marketing department submit the most help desk tickets?

8.How many of the currently open tickets were submitted by employees in the Accounting department?

9.Which department has the lowest help tickets per employee ratio?

10.Which department has a 3.000 ratio of help tickets per employee?