Rockefeller College MPA Excel Workshop:Admissions Data Example

Rockefeller College MPA Excel Workshop:Admissions Data Example

As part of Welcome Week, you will have the opportunity to learn more about applicants to Rockefeller graduate programs by doing some analyses of the admissions dataset.[1]The basic Excel skills in this exercise are prerequisite for all of the MPA core courses. As a graduate student in the MPA program, you are responsible for understanding this material prior to the Fall term.

The Excel dataset (“Welcome Week MPA Admissions Exercise.xlsx”) for this exercise is located at Welcome Week Central:

Excel Workshops

The Excel workshops will introduce you to Excel and show you how to complete this exercise step-by-step. You may have experience with Excel from prior coursework or professional training. If you can complete this exercise and reproduce the results displayed here, you do not need to attend the Excel workshop.

The two workshops (“Excel Basics” and “Fun with Charts and Graphs”) cover the material from the first and second parts of this exercise. If you cannot complete this exercise in its entirety, then you must attend one or both of these workshops. If you plan to attend, then add your name to the sign-up sheet outside Sheena Loughlin’s office (Milne 107) so we can ensure there are enough seats.

*****

PART 1: EXCEL BASICS

How old are the applicants?

The secondworksheet (“Student Age”) lists the application date and the birth date.

Task #1.1: Write a function to calculate the age of each individual when they submitted their application. Format the cells so that there is only one decimal place.

The first five lines are reproduced below:

Appl Dt / Birthdate / Sex / Age
2/1/2008 / 2/16/1984 / F / 24.0
1/2/2009 / 4/18/1973 / M / 35.7
1/6/2009 / 9/18/1984 / M / 24.3
1/26/2009 / 3/16/1978 / M / 30.9
1/26/2009 / 9/17/1983 / M / 25.4

Task #1.2: Recalculate your age variable so that your answer is expressed in years, rounded down to the nearest age.In other words, someone who is 25 years, 10 months, and 2 days old should appear as “25.” She should not appear as “25.8” or rounded up to “26.” (Hint: Most likely, you have not previously used a formula to round down. This task will test whether you can use the help menu or other techniques to locate new formulas.)

The first 5 lines of the new variable (“Age”) are reproduced below:

Appl Dt / Birthdate / Sex / Age / Rounded Age
2/1/2008 / 2/16/1984 / F / 24.0 / 23
1/2/2009 / 4/18/1973 / M / 35.7 / 35
1/6/2009 / 9/18/1984 / M / 24.3 / 24
1/26/2009 / 3/16/1978 / M / 30.9 / 30
1/26/2009 / 9/17/1983 / M / 25.4 / 25

Task #1.3: Use Excel functions to calculate the average (mean), median, minimum, and maximum ages of the applicant pool.Use the exact age in years that you calculated in Task #1.1. Format your answers so that you have one significant digit.

Solution:

Average / 26.5
Median / 23.8
Minimum / 19.6
Maximum / 59.9

Task #1.4: Create a table that shows the number of applicants by age group and gender, using the sort function.Your table should have the same format shown below, with separate columns for males, females, and combined genders (“total”). Be sure that your numbers are the same. For this task, ignore the three applicants with unspecified genders.Use the rounded down age that you calculated in Task #1.2.

Male / Female / Total
19-24 / 59 / 89 / 148
25-29 / 23 / 21 / 44
30-39 / 23 / 6 / 29
40-49 / 4 / 4 / 8
50-59 / 3 / 3 / 6

How many applicants have applied to each program?

The “Applications” worksheet gives details on the program of study.

Task #1.5: Use the filter tool and the “degree desc” variable to identify the number of applicants applying to each degree type.Create a table with this information. Use absolute and relative cell references to generate the total number of applicants, and the percentage of applicants to each degree type. Your table should have the same format shown below.

Degree Type / Number / %
Certificate of Advanced Study / 1 / 0.4
Certificate of Graduate Study / 12 / 5.1
Masters of Public Administration / 166 / 70.0
Non-Degree / 12 / 5.1
Doctor of Philosophy / 46 / 19.4
Total / 237 / 100.0

Task #1.6: Sort the data by application number, and use conditional formatting to highlight the students who have applied to the PhD program. The student records should be sorted by application number in ascending order (smallest first).

The first 5 lines are reproduced below:

ApplNbr / Degree Desc / Plan Descr
00682563 / MPA / jnt MPA-JD
00713386 / PhD / PubAdm PhD
00713724 / PhD / PubAdm PhD
00717538 / MPA / PubAdm MPA
00717562 / PhD / PubAdm PhD

What is the country of citizenship of MPA applicants?

The “Citizenship” worksheet lists the citizenship country (CitzCntry) and state residency (instate or outstate) of applicants to the MPA program.

Task #1.7: How many countries are represented?Count USA as a country.Assume that missing values are USA.

Solution: 14 countries, including USA

Task #1.8: What country outside the USA has the largest applicant pool?

Solution: China, with 22 applicants

Do PhD applicants have higher GRE-quantitative test scores and undergraduate GPAs than MPA applicants?

Task #1.9: Using data from the “GPA” worksheet, generate the average GRE-quantitative score and undergraduate GPA for PhD applicants and MPA applicants. Reproduce the table below. Some students do not have valid GRE score values because they took an alternate test such as the GMAT. Similarly, some students do not have a reported undergraduate GPA or else their value does not fall in the typical 0.0-4.0 range. For all of these students, change the values to “.” before calculating the averages.

Degree Type / GRE-Quantitative / Undergraduate GPA
Master of Public Administration / 624 / 3.4
Doctor of Philosophy / 752 / 3.2

Copy and paste tables into a new Word document

Task #1.10: Copy and paste the tables from Tasks #1.4, 1.5, and1.9 into a Word document.Your final document should be identical to the next page.

To:Sheena Loughlin

From:Little Old Me

Date:Today

Re:Summary of Findings from Excel Basics Exercise

The following tables summarize the main findings from my analysis of the MPA admissions dataset.

Table 1: Applicants by age group and gender

Male / Female / Total
19-24 / 59 / 89 / 148
25-29 / 23 / 21 / 44
30-39 / 23 / 6 / 29
40-49 / 4 / 4 / 8
50-59 / 3 / 3 / 6

Table 2: Applicants to each degree program

Table 3: Average quantitative GRE scores and undergraduate GPA

Degree Type / GRE-Quantitative / Undergraduate GPA
Master of Public Administration / 624 / 3.4
Doctor of Philosophy / 752 / 3.2

PART 2: FUN WITH CHARTS AND GRAPHS

How old are the applicants?

Task #2.1:Copy and paste the table from Task #1.4 into a new Excel worksheet, and create a bar chart of the number of applicants by age group and gender. Include a title and use the University at Albany school colors (gold and purple).

Task #2.2: Adjust the formatting to match the chart below. Remove the grid lines, change the colors to blue and pink, and include the raw numbers above each column.

(Are you having FUN yet?)

Task #2.3: Turn this chart into a horizontal bar chart that displays the same information by percentage (i.e. the fraction of applicants in each age group that are male and female). Do not do any additional calculations in Excel – you should be able to reproduce this using the chart tools. Males should be represented by stripes, and females bysmall polka dots. Outline the bars in red, and remove the grid lines.

Task #2.4: Adjust the formatting to match the chart below. Add a title to the Y-axis, move the legend to the bottom, change all text to purple, make the background yellow, set the vertical axis values at an angle, and adjust the colors of the bars.

(This exercise is getting progressively more FUN!)

How many applicants have applied to each program?

Task #2.5: Copy and paste the table from Task #1.5 into a new Excel worksheet, and create a pie chart of the number of applicants applying to each type of degree.Include a title, separate the slices, and include the count of applicants for each slice.

Task #2.6: Adjust the formatting to match the chart below. Move the legend to the bottom, and add a shadow effect to the pie slices.

(We are having so much FUN that this deserves three smiley faces! )

Copy and paste tables into a new Word document

Task #2.7: Copy and paste the tables from Tasks #2.1, 2.2, 2.3, 2.4, and 2.5 into a Word document. Your final document should be identical to the next page.

To:Sheena Loughlin

From:Happy MPA Student

Date:Today

Re:Fun Charts and Graphs for Your Review

Welcome Week Excel Workshop- 1 -

I made the following charts and graphs using the MPA admissions dataset. I would like your advice on which charts and graphs would be the most suitable for inclusion in your report. I had a lot of fun working on these, and I hope you enjoy them as much as I do!

The four graphs below display the number of applicants by age and gender.

The last chart is my favorite, which is why I have centered it in my memo.

Welcome Week Excel Workshop- 1 -

[1]The dataset contains information on all applicants, which includes individuals who were denied admission to the program. Information on matriculation status has been omitted to maintain confidentiality.