Instructions:

Your first task will be to determine how many people have applied for which areas of study thus far. To do this:

1. Open the file
2. Highlight the column "Desired Area of Study"
3. Select Insert->PivotTable->OK from the Excel ribbon. This will open a new worksheet with "PivotTable Fields" on the right hand side of the screen.
   1. Click on Desired Area of Study and drag it to the area in PivotTable Fields that says "Rows".
   2. Click on Desired Area of Study again and drag it to the area in PivotTable Fields that says "Values". You should see "Sum of Desired Area of Study" in the section called "Values". Click on "Sum of Desired Area of Study" and you will see a pop-up menu. Select "Value Field Settings" from this menu and then select "Count" from the drop down menu. This set of actions will count the number of applicants for each area of study! You should see a table with the programs and the number of applications for each program listed by the program number. This is your pivot table.
4. After creating your first PivotTable, please answer the following question:
   1. Which areas of study has more applicants than freshman advising slots? To answer this question you will need to do the following:
      1. Highlight and copy your pivot table cells A4 through B9. (Ctrl-C usually works for copying unless you are using an Apple product. If you are using an Apple product, contact me!).
      2. Open a new worksheet by clicking on the + at the bottom of your workbook. Name this worksheet "Analysis" by double clicking on the current sheet name which will be something like Sheetx where x is a number.
      3. Paste your table into the new worksheet "Analysis" by putting your mouse on and clicking on cell A1 in the Analysis worksheet. Then press Ctrl-V. This short-cut will not work if you are using an Apple product.
      4. In cell A1 in the Analysis worksheet, type "Program" and in cell B1, type "Number of Applicants".
      5. Now, go to Table 1 in this instructional document and highlight the column titled "Number of Freshman Advising Slots Available". Make sure you include the column name AND the total!
      6. Paste this column into your Analysis worksheet, starting with cell C1. You should now be able to see how many applicants you had and how many advising slots you have side by side! If this is not what is happening, email me ASAP!
      7. To verify everything is correct in your Analysis worksheet, you should see in Row 2, the following in each cell:
         1. A2: 1, B2: 925, C2: 150.
      8. You now have the information to let you know where you have too many applicants and too few. Since you have 925 applicants for program 1 which is engineering/computer science but only 150 advising slots, you know this is may not be an area where you will need to do a lot more recruiting. However, for program 5 which is Social Sciences, you should have 99 applicants but 150 advising slots so this is an area where you might need to do more recruiting!
5. Although we know how many applicants are interested in the various fields of study, we need to know how many applicants are actually eligible for admission. To determine this if a student is eligible for automatic admission to the college based on their proposed area of study/program, we need to create a formula in the Applicants worksheet based on the college's automatic admission rules. The rules for automatic admission are as follows:
   1. The student is interested in either Social Sciences/Liberal Arts or Education and has a GPA of 3.0 or higher
   2. The student has a 3.9 GPA or higher, regardless of the quality of the high school
   3. The student is interested in either Engineering/Computer Science or Business and has a GPA of 3.4 or higher
   4. The student is from a school of rank 1 and has a GPA of 3.5

To calculate which students are eligible for automatic admission, we will need to generate a formula based on the rules above. If we have too many automatic admits, we may need to tighten the rules for those areas of study where we have too many applicants!

Recall the column headings in the Applicants worksheet: GPA (column B), High School (column C), and Desired Area of Study (column D). These columns represent the data we need to determine if an applicant is eligible for automatic admission! To perform write our formula, let's think through our rules and associate them with each column. Recall the rules:

1. The student is interested in either Social Sciences/Liberal Arts or Education and has a GPA of 3.0 or higher
   1. This rule requires us to use data in the Desired Area of Study column (column D) and the GPA column (column B). Specifically, this rule should indicate a "Yes" if the student has a desired area of study of either 5 or 6 AND has a GPA of 3.0 or higher.
2. The student has a 3.9 GPA or higher, regardless of the quality of the high school
   1. This rule requires us to use the data in the GPA column (column B). Specifically, this rule should indicate a "Yes" if the student has a GPA of 3.9 or higher.
3. The student is interested in either Engineering/Computer Science or Business and has a GPA of 3.4 or higher
   1. This rule requires us to use data in the Desired Area of Study column (column D) and the GPA column (column B). Specifically, this rule should indicate a "Yes" if the student has a desired area of study of either 1 or 2 AND has a GPA of 3.4 or higher.
4. The student is from a school of rank 1 and has a GPA of 3.5
   1. This rule requires us to use data from the High School Quality column (column C) and the GPA column (column B). Specifically, this rule should indicate a "Yes" if the student attends a high school of rank 1 and has a GPA of 3.5 or higher.

In Excel, we can indicate "Yes" in a number of ways. We can use 1 for "Yes" and 0 for "No." We can also use the actual words such as "Yes" and "No", "auto admit" and "no auto admit", and so forth. I am going to demonstrate the formula using 1 for "Yes" and 0 for "No". To prepare our data, we will need to go to the Applicants worksheet and prepare a column and formula to accept our rules.

1. Go to the Applicants worksheet.
2. In cell E1, type "Automatic Admit".
3. Let's develop our formula for the rules. We will need to use a "nested if" statement to build our formula based on the rules. Recall our rules. I am going to provide the basic formula for each rule for the first applicant in row 2. Note. I want you to use a nested IF formula and will get to that further down. However, if you are interested in HOW a student is potentially automatically admitted, you can put each rule in a separate column.
   1. The student is interested in either Social Sciences/Liberal Arts or Education and has a GPA of 3.0 or higher
      1. This rule requires us to use data in the Desired Area of Study column (column D) and the GPA column (column B). Specifically, this rule should indicate a "Yes" if the student has a desired area of study of either 5 or 6 AND has a GPA of 3.0 or higher.
      2. Using 1 for "Yes" or "Auto Admit", and 0 for "No" or "Not Auto Admit", this rule would be written as if it was a standalone formula. (Hint: It's not so don't stop here!)=IF(AND(OR(D2=5,D2=6),B2>=3),1,0)
   2. The student has a 3.9 GPA or higher, regardless of the quality of the high school
      1. This rule requires us to use the data in the GPA column (column B). Specifically, this rule should indicate a "Yes" if the student has a GPA of 3.9 or higher.
      2. Using 1 for "Yes" or "Auto Admit", and 0 for "No" or "Not Auto Admit", this rule would be written as if it was a standalone formula. (Hint: It's not so don't stop here!)=IF(B2>=3.9,1,0)
   3. The student is interested in either Engineering/Computer Science or Business and has a GPA of 3.4 or higher
      1. This rule requires us to use data in the Desired Area of Study column (column D) and the GPA column (column B). Specifically, this rule should indicate a "Yes" if the student has a desired area of study of either 1 or 2 AND has a GPA of 3.4 or higher.
      2. Using 1 for "Yes" or "Auto Admit", and 0 for "No" or "Not Auto Admit", this rule would be written as if it was a standalone formula. (Hint: It's not so don't stop here!)=IF(AND(OR(D2=1,D2=2),B2>=3.4),1,0)
   4. The student is from a school of rank 1 and has a GPA of 3.5
      1. This rule requires us to use data from the High School Quality column (column C) and the GPA column (column B). Specifically, this rule should indicate a "Yes" if the student attends a high school of rank 1 and has a GPA of 3.5 or higher.
      2. Using 1 for "Yes" or "Auto Admit", and 0 for "No" or "Not Auto Admit", this rule would be written as if it was a standalone formula. (Hint: It's not so don't stop here!) =IF(AND(C2=1,B2>=3.5),1,0)
4. The final nested formula would be

=IF(AND(OR(D2=5,D2=6),B2>=3),1,IF(AND(OR(D2=1,D2=2),B2>=3.4),1,IF(AND(C2=1,B2>=3.5),1,IF(AND(C2=2,B2>=3.7),1,IF(B2>=3.9,1,0)))))

Note that the nested if statement requests an output of 1 if the formula's "analysis" indicates the student should be automatically admitted, and 0 if not. We use both 1 and 0 when we have individual formula but we only use the 0 when the formula is nested and there are no more rules to evaluate! Copy and paste the formula above to cell E2. Your answer in E2 should be 0.

If you get FALSE or #VALUE, the copy and paste isn't working for you so you will need to type the formula. Pay attention to typos! Once you have a working formula, copy it from E2 and paste it to E3 through E3001.

We will now create a new Pivot Table to help us count the number of applicants we can automatically admit. This one will be a "crosstab" table!

1. Highlight the columns Desired Area of Study and Automatic Admit in the Applicants worksheet.
2. Select Insert->PivotTable->OK from the ribbon. This will open a new worksheet with "PivotTable Fields" on the right hand side of the screen.
   1. Click on the field Desired Area of Study in the PivotTable Fields area and drag it to the area in PivotTable fields that says "Rows".
   2. Click on field Automatic Admit in the PivotTable Fields area and drag it to the area in PivotTable fields that says "Columns".
   3. Click on Automatic Admit again and drag it to the area that says "Values". You should see "Sum of Automatic Admit". Click on "Sum of Automatic Admit" and you will see a pop-up menu. Select "Value Field Settings", then select "Count" from the drop down menu. This set of actions will count the number of applicants for each area of study that qualifies for automatic admit and those who do not!
   4. For Desired Area of Study 1 you should see 643 under "0" and 282 under "1". If you are not seeing these numbers, email me your workbook!
   5. Name this worksheet "AutoAdmit"
3. We now want to compare the number of potential automatic admits to the current capacity, We can admit up to 1.7 times the number of freshman advising capacity because not everyone we admit will accept our offer. We need to set up a new worksheet to (1) calculate the total number of students we can admit (remember we need more because not everyone admitted will attend our college), (2) compare the number that we can already automatically admit, and (3) determine which areas of study would benefit from additional marketing and which ones doesn't require much more marketing efforts. To do this,
   1. Open a new worksheet in the Applicants workbook.
   2. Name the worksheet Comparison.
   3. Copy the table below to the new worksheet Comparison starting in cell A1. The cells A1 through C8 should be populated once you have copied and pasted the table.

Program #Area of StudyNumber of Freshman Advising Slots Available1Engineering/Computer Science1502Business2003Pre-Med404Pre-Law305Social Sciences/Liberal Arts1206Education60Total600

2. In cell D1 in the Comparison worksheet, type "Admission Goal". In cell D2, type =C2*1.7. This will give us an initial goal for each area of study. Remember, we can admit 1.7 times the number of freshman advising slots available! Your value in D2 should be 255. If you have the correct value, copy and paste the formula in cell D2 to cells D3 through D7. This tells you the number of people you can potentially automatically admit.
3. In cell E1 in the Comparison worksheet, in the, type "Current Automatic Admits". In cell E2, type "=AutoAdmit!C5". This should bring in the value from Area of Study 1 students who we can currently automatically admit and should be 282. Copy and paste this formula to cells E3 through E7.
4. In cell F1 in the Comparison worksheet, type "Slack". In cell F2, type "=D2-E2". This tells us if we have too many students to auto admit or not enough. Remember, if we have too many, we will reduce marketing for those areas of study. If we don't have enough, we need to increase marketing!
   1. If the value is negative, we have too many automatic admits for the area of study
   2. If the value is positive, we need to recruit more students for the area of study

Your last step is to draft a memo in Microsoft Word to the VP of Strategic Enrollment to inform them of the status of applications. Make recommendations on whether or not the "automatic admit" rules need to be adjusted for any of the desired areas of study (e.g., for any desired areas of study where we have too many automatic admits and why (we already have too many people automatically admitted is one reason to change the rule). Copy and paste your table into your memo to show the number of applicants for each area of study and the number of automatic admits. Any other data you wish to share such as the number of automatic admits from each quality of school can earn you extra credit points!

Your memo should have the following format:

To: VP Strategic Enrollment

From:

Date:

Re: Application Capacity year-to-date

The purpose of this memo is to update you on our application status. As of , we have XXXX applicants for the 600 freshman slots for Fall 2024. Using our current automatic admit rules, we still need to recruit for the following areas of study: . However, we need to pull back on the areas of study of because we have exceeded our capacity for those areas. Alternatively we could consider changing the automatic admit rules for areas such as

Please replace "XXXX" in your memo with the current number of applications.

Please submit your memo to . Your memo will be graded based on the Have fun!