You are a student enrollment analyst at a local university. Currently your data management systems contain student information, course offerings, and student grades. You w

#Exp22_Excel_Ch10_Cumulative – Student Enrollment 

#Exp22 Excel Ch10 Cumulative Student Enrollment 

#exp22_excel_ch10_cumulative – student enrollment 

#Excel Chapter 10 Cumulative – Enrollment Report 

#Exp22_Excel_Ch10_Cumulative_EnrollmentReport 

  

Project Description:

You are a student enrollment analyst at a local university. Currently your data management systems contain student information, course offerings, and student grades. You want to create a report to examine total enrollment and student performance. You will use Power Query to import data, Power Pivot to analyze grades, and Power Maps to visualize student demographic information.

     

Start Excel. Download and open   the file named Exp22_Excel_Ch10_Cumulative_EnrollmentReport.xlsx.   Grader has automatically added your last name to the beginning of the   filename.

 

Use Get & Transform to   import the file e10c1StudentEnrollment.csv   into a new worksheet. Be sure   to load the table to the data model during import.

 

Rename the worksheet StudentEnrollment.

 

Use Get & Transform to   import the ClassesOffered table located in the database e10c1ClassOfferings.accdb into a new worksheet. Be sure to load   the table to the data model during import.

 

Import the GradeReport table   from the e10c1Grades.accdb database   into a new worksheet. Be sure to load the table to the data model during   import.

 

Open the StudentEnrollment query   in the Power Query Editor. Split the Name column using space as a delimiter.   Rename the split columns Last Name and First Name respectively. Close and load the query.

 

Use Get & Transform to   import the file e10c1StudentEnrollment2024.txt   into a new worksheet. Prior to loading the data into the worksheet, use   the Power Query Editor to split the Name column using space as a delimiter.   Rename the split columns Last Name and First Name respectively.

 

Append the   e10c1StudentEnrollment table with the data from the   e10c1StudentEnrollment2024 table. Close and Load the table.

 

Define the following   relationship between the Grade Report table and Classes Offered table:
 

  Table:
  GradeReport
  Field:
  ClassNumber
  Table:
  ClassesOffered
  Field:
  ClassNumber

 

Define the following   relationship between the Grade Report table and the e10c1StudentEnrollment   table:
 

  Table:
  GradeReport
  Field:
  StudentID
  Table:
  e10c1StudentEnrollment
  Field:
  StudentID

 

Use Power Pivot to create a   PivotTable on Sheet1 starting in cell A1. Add ClassName from the   ClassesOffered table to the Rows area, Grade from the GradeReport table to   the Values area, and ClassNumber from the ClassesOffered table to the Filters   area.

 

Change the Implicit measure of   GPA created in the previous step from Sum to Average.

 

Rename the worksheet GradeAnalysis.

 

Format the grades in the range   B4:B19 to display 2 decimal places.

 

Insert a Clustered Column   PivotChart. Add the chart title Grade Analysis. Move and resize the chart so that the upper-left   corner is inside cell D4 and lower-right corner is inside cell K18.

 

Create a 3D Map based on student   addresses in the enrollment table. Add Address as the Location dimension. Add   Grade as the Height dimension and change the aggregation from Sum to Average.

 

Add Grade as the Height   dimension and change the aggregation from Sum to Average. Zoom and tilt the   map so the entire US is visible. Edit Scene 1 by changing the name to Over View, setting the Scene duration to   6 seconds, and then setting the Transition Effect to Dolly.

 

Add a new scene called East Coast. Zoom out and reposition the   map to view the Eastern seaboard. Set the Scene duration to 6 seconds and set   the Transition Effect to Dolly.

 

Edit the connection properties   so all queries do not refresh in the background and do not refresh on Refresh   All.

 

Save and close Exp22_Excel_Ch10_Cumulative_EnrollmentReport.xlsx.   Exit Excel. Submit the file as directed.