Note that sometimes, particularly with Windows, Excel has a hard time performing calculations on the pivot table results.

Simon Business School University of Rochester

CIS 220, Business Information Systems and Analytics

Fall 2020

Team Pivot Table Assignment1

Due Wednesday, October 7th, at 11:59pm

For both parts, CIS 220 Survey and Salaries, you need to submit an Excel workbook with your answers and work. Note that there is an Answers worksheet that has the questions and room for answers and you should put your answers to each of the questions there. Be sure to answer the questions, no more and no less. In addition, for each question, there should be a separate worksheet renamed Question 1, 2, 3, 4… showing your work for answering the question (See Question 0 for an example). Please answer to nearest whole number, i.e., nothing to right of decimal point. Note that sometimes, particularly with Windows, Excel has a hard time performing calculations on the pivot table results. If you run into this problem, copy the pivot table cells and then do paste values into another part of the worksheet, and do your calculations on the pasted values. Part 1 CIS 220 Survey Use pivot tables, and any additional calculations as necessary, to answer the following questions. Be sure to put your answers in the Answers worksheet and have a separate worksheet renamed Question 1, 2, 3, 4… showing your work for each question (See Question 0 for an example). Note that it is very important when counting, to count a field that has data for every record (row), e.g., Year or Working Alone. Many columns have missing entries for at least one record. 0. What year has the most students and how many students does it have? Note that it is very

important when counting, to count a field that has data for every record, e.g., Year. Answer: Junior, 66

1. What is the most popular Business Track and how many have chosen that track? 2. Create a Pivot table to see which programming language is the most popular. You should find that

37 people chose “Python”. How many other unique answers (rows of the pivot table) have the word “Python” in upper or lower case in them? This is why it’s important to view and cleanse the data.

3. Create a pivot table to see what Industry is most popular. What is the most popular industry

according to the pivot table results? How many other pivot table rows have an answer that starts with the same first three letters as the most popular industry? This is one reason we offer choices when we can.

1 Copyright ©2016-20 by Roy Jones

4. What percent of the class is planning to go to grad school when they graduate? Note that it is fine to use pivot tables to count and then calculate the percentage in Excel (if this was a 0/1 variable, the average would be the percentage).

5. What percent of the class as a whole would like a CIS 220 Happy Hour? What percent of students

currently living in the US would? Note that to do this you will need to sum subtotals for all of the choices that people might choose if they are currently living in the US.

6. Play with the dataset and tell me something else interesting you learned.

Part 2 Salaries The Salaries spreadsheet has Employee #, Education Level, Job Grade, Gender, IT Job, and Salary for 208 employees at a fictitious firm. Use pivot tables, and any additional calculations as necessary, to answer the following questions. Remember that your answers to each question should go in the template provided to the right of the data on the Salaries worksheet. In addition, you should create a worksheet (when you create a pivot table) for each question showing how you answered the question and you should rename each worksheet Question 1, 2, 3, 4… See Question 0 for an example. Please answer to nearest whole number, i.e., nothing to right of decimal point. 0. How many of the 208 employees are women? Answer: 140

1. What is the most common education level? How many employees have that level? 2. What is the average salary for men? Women?

3. In which job grades is the average salary of men more than 2% more than the average salary of

women, and what percent more do they make in each of these cases? Note that you may need to do a calculation on the pivot table results. If Excel gives any trouble doing so, copy the whole pivot table and then do paste special values and then do the calculation on the pasted table.

4. Which job grade has the highest percentage of women? What percent women is it?

Which job grade has the lowest percentage of women? What percent women is it? Note that I am asking within a job grade, so if a job grade has 3 women and 2 men, then that job grade is 60% women (even though most women work in other job grades).

Finally, think about what the answers to Questions 2, 3, and 4, each say about possible gender discrimination at this fictitious firm (you don’t need to turn in anything for this part).