Quantitative Analysis

There are four worksheets or tabs in this Excel spreadsheet. Take a few minutes to review the spreadsheet in its entirety. The four worksheet titles and the purpose of each worksheet are as follows.
• IT Help Desk Data—contains the data to be analyzed
• Data Legend—provides information about the data.
• Statistical Analysis—used in conjunction with Excel functions to determine the average number of days the tickets are open, the distribution of the requester’s seniority, the type and severity of the problem, the assigned priority level, and the end user satisfaction level. The missing information may then be filled in as it is calculated.
• QR Analysis Essay—provides essay questions to answer after you perform your analysis using Excel and Cognos. Answers should be entered directly inside each box.
• Scenario
• Your supervisor has been assigned to be the interim manager of the IT Help Desk and needs your help. The previous help desk manager retired after many years in that position and never conducted any analysis of his team’s performance. Coming into this new job, your supervisor would like to gain more insight into the strengths and challenges of her help desk team.
• First, you will need to collect data on the help desk tickets. This data will provide insight into the help desk’s performance and give you clues on how to improve the unit. You learn that the help desk has several goals. These goals relate to the measurement of key performance metrics, such as the number of days tickets are open and client satisfaction ratings. You would like to know how well the team members achieved these goals under the previous manager. Analysis of the help desk data will help you understand the team’s performance on these goals.
• You plan to analyze the data using standard statistical methods in Microsoft Excel. However, one of your colleagues suggests that you also use a data analytics tool called Cognos Analytics, which could help you uncover hidden information. Cognos Analytics can also create graphic depictions of the key information to help you visualize the data. You decide to use both approaches.
• Your task is to conduct data analysis and prepare a final report for your supervisor about your findings. Your report will also include a set of recommended solutions for improving the performance of the help desk.
• Once you have reviewed the scenario, review the project overview, approximate time commitment, and competencies that you will be responsible for in this project.
In the Excel spreadsheet Technology Template that you have downloaded, review the Statistical Analysis worksheet. You will use the COUNTIF and SUM Function in Excel to prepare the data for future analysis. This will include some simple statistical analysis as well as charts and graphs to present the data. Use Excel formulas to fill in the gray cells under the column labeled “Count” in the five tables in that worksheet.
A. Summarize the IT data by presenting categories of data in summary tables and counting them, totaling them, and calculating percentages:
o Use the COUNTIF Function to count each item in each table.
o Use the SUM Function to total the tables when required.
o Calculate percentages for each table as required.
Note: Format cells appropriately. Remember to make smart use of reference cells in formulas (avoid typing in numbers or text into formulas—instead, point to other cells) and use relative and absolute cell references to make copying formulas faster and easier. Your supervisor will look for your appropriate use of these tools!
B. Complete your analysis based on the results that appear. Answer the following questions by typing into the text box in the “QR Analysis Essay” tab:
• Which types of employees are most likely and least likely to open a ticket?
• Which types of problems are most and least common?
• What can you tell about the satisfaction level?
• What can you tell about the number of days a ticket is open?

In Section 2 of the Statistical Analysis tab of the Excel spreadsheet Technology Template, use the appropriate Excel function to complete the table. Calculate the summary statistics of the DaysOpen data on the IT-Help-Desk-Data tab (Column 1). Use the summary statistic Excel functions of =AVERAGE, =MEDIAN, =MODE, =STDEV.S, =VAR.S, =KURT, =SKEW, =MIN, =MAX, =SUM, =COUNT to derive these statistics for the three data categories. Standard error and range should also be calculated.
A. The next task will be to provide the input and output. Since you want to have statistics for the DaysOpen data, you will provide the location of the data on the spreadsheet in the input box. Indicate the inclusive cells for the selected categories. To do so, type in the field or capture the column with your mouse and the field will show in the input range. Check the labels box so you have titles for the categories. Then select “New Worksheet Ply,” and your output will be a new sheet on your tab.
B. Label your new sheet “Summary Stats” and format the columns for readability.
C. Compare your calculations using the data analysis feature to the results you reached in the previous step, which you calculated manually with individual functions. You should not have a large disparity. This tool is handy for quick computations and you will use it again to create your pivot table in the following step!
In this step, you will add a new tab, name it “Graphs,” and provide the following graphs in the worksheet:
A. pie charts should show the following:
o the distributions of requestor’s seniority
o types of problem
B. bar charts should show the following:
o the distributions of the severity of the problems
o priority level assigned.
Be sure to select data labels and create legends for each graph.

Source: Used with permission from Microsoft.
In the next step, you will use IBM Cognos Analytics to perform data discovery.
This step will introduce you to a powerful, state-of-the-art data analytics tool, IBM’s Cognos Analytics. This dynamic tool supports quantitative reasoning.
Click the following resources to learn about Cognos Analytics and to complete this part of the project:
• Review Cognos Anayltics to learn more about how it can be effectively applied to data.
• Use the Getting Started with Cognos Analytics document to create a free trial account with Cognos Analytics, log in to your Cognos Analytics account, import the IT Help Desk Data into Cognos Analytics, and review the Cognos Analytics tutorials.
Finally, you will analyze the IT Help Desk Data using Cognos Analytics. Use the knowledge and techniques that you learned about Cognos Analytics to analyze the data set. Then answer the following questions and type your answers in on the “QR Analysis Essay” tab.
• How is the help desk department performing?
• Which specific ITOwner (help desk technician) is a high performer? Which ITOwner is the lowest performer?
• What relevant information about the help desk did you uncover from your analysis?
• What recommendations do you have for the help desk?

Sample Solution

find the cost of your paper

This question has been answered.

Get Answer