Project 4: Conduct Quantitative AnalysisStep 1: Refresh Your Math, Statistics, and Excel Skills

Everyone has a different background. As in previous projects, you will start by assessing your current baseline. First, think about what it means to engage in  . Next, refresh your skills in  ,  , and  . You will evaluate how much you already know and where you need to concentrate more attention.

If you need help outside the classroom, you can access tutoring by selecting Tutoring Assistance under Resources in your classroom. There are also videos when you   to help you complete this project successfully. 

Once you have a good idea of your skill levels in math and stats, and you know where to go for help, move on to Step 2, where you will apply some of your stats knowledge. Remember to choose the instructions of the course in which you are currently enrolled.

Project 4: Conduct Quantitative AnalysisStep 2: Opening and Saving an Excel Spreadsheet

In this step, you will be opening and saving an Excel spreadsheet.

Download the  . 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.

Project 4: Conduct Quantitative AnalysisStep 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.

Note: You should strive to work through the first four steps this week. Take a breather here if you need it. Check in with your instructor as needed.

In the Excel spreadsheet   that you have downloaded, review the Statistical Analysis worksheet. You will use the   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?

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!

Project 4: Conduct Quantitative AnalysisStep 4: Add Information to Your Spreadsheet

In this step, things get interesting! You will expand your analysis by employing descriptive statistics, or  , 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  , 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.

Project 4: Conduct Quantitative AnalysisStep 5: Use the Data Analysis Toolpak

Now that you have calculated descriptive statistics using individual Excel functions, we'll look at another approach. Did you know that you can generate the same descriptive statistics in one easy step?

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   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.

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 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.

Project 4: Conduct Quantitative AnalysisStep 6: Use IBM Cognos to Analyze Data

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   to learn more about how it can be effectively applied to data.

· Use the   to create a free trial account with Cognos Analytics, log in to it, import your IT help desk data into Cognos Analytics, and  create at least two visualizations using Cognos to support your analysis. 

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?