Exp22_excel_ch10_cumulative – student enrollment | Computer Science homework help
#Exp22_Excel_Ch10_Cumulative – Student Enrollment
#Exp22 Excel Ch10 Cumulative Student Enrollment
#exp22_excel_ch10_cumulative – student enrollment
#Excel Chapter 10 Cumulative – Enrollment Report
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:
Define the following relationship between the Grade Report table and the e10c1StudentEnrollment table:
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.