Instructions

Using the attached Deliverable 5 Excel file, use the email request above to complete the following steps.

On the Prospective Student sheet:

1. Add two new rows at the top of the file to create a formula or function to determine the number of Admit Types (Column G). Tip you'll need to calculate the numbers and add text for the chart legend in the next step. [2 pts]
2. Create a Pie Chart that shows the distribution of prospects based on Admit Type. Put the chart on a new sheet and include a helpful title and clear legend. [2 pts]
3. In the pie chart just created, show the number of students and the percent of students for each slice. [1 pt]

On the Program Enrollments sheet:

1. Apply Conditional Formatting to the Program Enrollment data (Columns E:I) that highlights the bottom 20% of all program enrollments [1 pt]
2. Include Sparklines (lines) after the Program Enrollment information in Column J [1 pt]
3. Create a Line Chart of the enrollment data (in Columns E:I) that includes only the Master Programs using the Major name (tip: filters can help with this). Put the chart on a new sheet and include a helpful title and clear legend. [2 pts]
4. In the line chart just created, add a linear forecast line for the MBA program to understand a potential enrollment forecast [1 pt]

In the assignment textbox on this course site, provide the following answers:

1. How many more prospective students is the recruiter Lucy Lund working with than the average amount of the other recruiters? [1 pt]
2. About how many enrollments will the Masters of Business Administration (MBA) have in 20-21 based on the linear forecast on the Line Chart? Do you think that projection is accurate? Why or why not? [1 pt]

Once the changes are in place, save your file with a new name (as shown below) and submit the completed file here for grading.