Project 4: Conduct Quantitative Analysis 

There are 9 steps that will lead you through this project:

Step 1: Refresh Your Math, Statistics, and Excel Skills

Step 2: Opening and Saving an Excel Spreadsheet
Download the Technology Template. Note: 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.

Step 3: Use Functions to Summarize the Data
In this step, you'll begin to see patterns in the data that inform the “story” of the data table that you have prepared up to this point.
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:

Use the COUNTIF Function to count each item in each table.
Use the SUM Function to total the tables when required.
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?

Take Note: Don’t forget to format cells appropriately. 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!

Step 4: Add Information to Your Spreadsheet
You will expand your analysis by employing descriptive statistics, or summary statistics, using Excel formulas. Now you will calculate mean, median, and mode for the categories of data and derive the deviation, variance, dispersion, and distribution. Format all the results to two decimal places.
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, and =COUNT to derive these statistics for the three data categories. Standard error and range should also be calculated.
Step 5: Use the Data Analysis Toolpak
Now, you will use Excel's built-in Analysis Toolpak, an add-in that allows you to work with statistics and confirm the answers of your summary statistics. It will help you to save time by performing various complex analyses based on your needs.
You will first need to make sure the toolpak is enabled. Feel free to references How to Enable Data Analysis Toolpak for assistance. When you have completed that process successfully, you will see the words "Data Analysis" or an icon on the top right corner on the Data tab. Select Data Analysis and then choose Descriptive Analysis from the list.
Note: There may be some minor differences in the answers depending on the version of Excel you are using. Mac users will need Excel 2016 or later to download the toolpak.
When you have succesfully enabled the Data Analysis Toolpak, proceed to the tasks below to calculate the statistics using the toolpak.
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.
Label your new sheet "Summary Stats" and format the columns for readability.
Compare your calculations from the data analysis feature to the ones you got in the previous step, using 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 next step.

Step 6: Create Visual Representations of the Data: Charts and Graphs
Working with Excel Charts will provide an overview of the type of charts available such as pie and bar charts. Refer to it to create a histogram along with Use the Analysis Toolpak as needed. 
In this step, you will add a new tab, name it "Graphs," and provide the following graphs in the worksheet:
pie charts showing
the distributions of requestors' seniority and
types of problem
bar charts showing
the distributions of the severity of the problems and
priority level assigned
Be sure to select data labels and create legends for each graph.

Step 7: Copy and Sort Data
In this step, you will copy and sort data in an Excel worksheet and create a tab for sorted data. You will be able to use this rearrangement of data when you are conducting quantitative analysis. This skill is useful for reporting purposes and can be applied to any Excel application. 
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 Analytics to learn more about how it can be effectively applied to data.
Use Getting Started With Cognos Analytics to create a free trial account with Cognos Analytics, log in to it, import your 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 to analyze the data set with Cognos Analytics. Then answer the following questions and type your answers into 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?

Step 8: Submit Your Completed Workbook with Responses and Analysis
prepared to manipulate data fields, analyze data, and create reports that your boss may request in the future. 
Now that you have completed your analysis, it is time to share what you have found. Answer the questions listed below and in the QR Analysis Essay worksheet in the Technology Template Excel file that you have already downloaded. Your answer to each of the questions should be two to three paragraphs and should provide supporting information, including visualizations, gathered from your analysis using Cognos Analytics. It might be easiest to compose it in a Microsoft Word document and then copy and paste what you've written into your workbook on the QR Analysis Essay tab. You can enlarge the text box to contain your essay and images. Reflect on what you learned from analyzing the data using IBM Cognos Analytics. Incorporate at least two visualizations into your report. Use appropriate mathematical and statistical vocabulary. Keep in mind that you are preparing this report for the head of the department. Provide relevant information that will lead to recommendations that address concerns about the help desk.
You should answer the following questions:
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?
The primary purpose of the analysis you have just completed is to prevent the misinterpretation of data. You can never assume that the end user will be able to interpret the data the way it should be understood. It is up to you. Provide concise examples to back up your recommendations. Never simply provide a raw data dump. Instead, seek to provide quantitative analysis!

Your work will be evaluated using the competencies listed below. You can use the list below to self-check your work before submission.
1.1: Organize document or presentation clearly in a manner that promotes understanding and meets the requirements of the assignment.
1.2: Develop coherent paragraphs or points so that each is internally unified and so that each function as part of the whole document or presentation.
1.4: Tailor communications to the audience.
1.5: Use sentence structure appropriate to the task, message, and audience.
1.6: Follow conventions of Standard Written English.
3.1: Identify numerical or mathematical information that is relevant in a problem or situation.
3.2: Employ mathematical or statistical operations and data analysis techniques to arrive at a correct or optimal solution.
3.3: Analyze mathematical or statistical information, or the results of quantitative inquiry and manipulation of data.
3.4: Employ software applications and analytic tools to analyze, visualize, and present data to inform decision-making.