Excel Integration Assignment: Two-Year Academic Plan
DUE DATE: Check your Class Schedule for due date.
Create a spreadsheet that shows your planned courses for your first two-years at Longwood
University. This spreadsheet should display your planned courses grouped by semester within
each year (freshman and sophomore). If you are a Business student, you will find it very helpful
to use the advising checklist for general education requirements and for your concentration
located on the College of Business & Economics web site
(http://www.longwood.edu/business/academics/Catalogs.htm). If you are a non-Business
student, you need to go to your College’s Web site for more information.
On the 1st row, first column, put the Semester (Fall or Spring) and Year and merge the
cells across the five columns.
On the 2nd row, 1st column, labeled it as Classes, and list your courses using the four-
letter course code, the course number, and the course title.
On the 2nd row, 2nd column, labeled it as Requirement Fulfilled, and indicate which
requirement that course fulfills. For general education requirement, you must label which
goal the course fulfills.
On the 2nd row, 3rd column, labeled it as # of Credits.
On the 2nd row, 4th column, labeled it as Grade.
On the 2nd row, 5th column, labeled it as Quality Points.
On the 3rd row, 1st column, start entering the courses that you took at Longwood. For example,
for your Longwood Seminar class, write “LSEM 100 Longwood Seminar” in your 1st column
and “Goal 1” in your 2nd column. For your “CIMS 170 Computer Applications”, labeled it as a
major requirement in your 2nd column. Don’t forget that elective courses are a requirement too.
Then, fill in the number of credits in the 3th column, your grades or estimated (make-up) grades
in the 4th column, and use a FORMULA [given below -- Formula to Calculate Quality Points and
GPA = Steps # 1-3] to calculate the quality points in the 5th column.
After listing all your classers for that semester, on the next row, labeled Semester Total in the 1st
column, and also sum up the 3th and 5th columns for this row. On the next 2 rows, you need to
use a FORMULA [given below -- Formula to Calculate Quality Points and GPA = Step # 4] to
calculate that Semester’s GPA and the Cumulative GPA to that point.
Formula to Calculate Quality Points and GPA
1. Quality Points = Credit Hours * Grade for each class
You must use a VLookUp table for the Grade System: F=0, D=1, C=2, B=3, A=4
For example, if you had a C in a 3-credit course, quality points would be (3*2) =6
You must use relative cell reference for this calculation.
2. Add all quality points together.
3. Add all credit hours together.
4. GPA = [Total Quality Points (#2 above)] / [Total Credit Hours (#3 above)]
If you use the correct functions for your Quality Points and GPA calculations, the correct GPA
will be displayed for just the courses taken during that one semester and the formula will
CIMS170 Computer Applications 1
