[30 marks] Submit the answer to this question as a .xlsx file. Imagine that you are the Teaching Assistant for a fictional COMP 1042 course. The professor wants to create a spreadsheet to record the marks for the students in the class. As the TA it is your responsibility to design, implement, and test this spreadsheet.

Please read all of the instructions before you begin.

Import the grades data from the sampleMarks.txt file containing a list of students and the raw data for the marks. The file is comma delimited, (i.e., the items are separated by commas).

The marks for the COMP1042 course consist of 5 assignments, 2 midterms, and 1 final exam. The following is the marking scheme for each:

Assignment 1 is out of 25 marks

Assignment 2 is out of 35 marks

Assignment 3 is out of 20 marks

Assignment 4 is out of 50 marks

Assignment 5 is out of 65 marks

The first midterm is out of 55 marks

The second midterm is out of 40 marks

The final exam is out of 125 marks

Each item in the course is worth a different weight of the final grade:

The assignments are worth 30% of the final mark

The tests are worth 30% of the final mark (15% each)

The final exam is worth 40% of the final mark

Note: eventhough each assignment is marked out of a different total amount they are all worth the same weight.

Create a spreadsheet with labels indicating what each column represents and a row for each student in the class. There should be at least one column for each item in the input data. (You will likely wish to add additional columns for intermediate calculations).

The spreadsheet should calculate the final grade for each student using the above weighting criteria. This should be formatted as a number with two decimal places (e.g.: 90.70)

To calculate the final marks you will need to compute the weighted sum of all of the items, given the weighting criteria: final = (item1 * weight1) + (item2 * weight2) ..., where each item here is the student's mark (grade / maximum mark) for each assignment or test. You may wish to do this in stages: A1% = (grade/max)*weight, then final = A1% + A2% + ...

The worst assignment for each student will be dropped from their final mark, and the remaining 4 assignments will make up the 30% (7.5% each).

In addition to calculating the final marks for each student (above), your sheet should also show:

The class average for each assignment, test, and final exam.

The average final grade for the class.

The highest and lowest grades for each assignment, test, and final exam.

The highest and lowest final grades in the class.

These items should be clearly labelled and placed on your spreadsheet in appropriate location(s).

Next, you should also have a column indicating whether each student's final grade is above or below the average grade of the class. If a student's grade is above write "above" otherwise write "below". This should be done using a formula. Do not manually walk through each grade and write the value out yourself.

Next, there should be a column with a comment regarding each student's final grade using the following marking scheme:

80 and above: "Excellent"

Between 70 and 79.99: "Very good"

Between 60 and 69.99: "O.K."

Below 60: "Not great"

Again, to get the marks for this column this must be done using a formula, not manually entered text.

Next, use conditional formatting to highlight the entire row of any students that failed the course (final mark < 50%) with a red text. With a second formatting rule, highlight any students that will receive an A or better (final mark > 80%), with green background. Note: again, do not use manual formatting for this. The TA's will look for the conditional formatting rules to ensure your sheet was built correctly.

Finally, your worksheet should be sorted alphabetically by student's first name. Be sure that your spreadsheet is easily readable and well formatted.

Your spreadsheet will be evaluated as follows:

Importing sample marks file:	/3 marks
Spreadsheet appearance and formatting:	/3 marks
Correct final grades: (includes all required intermediate calculations (e.g. dropping the lowest assignment))	/8 marks
Correct averages, maximums, and minimums:	/4 marks
Correct comment columns: (above/below and "Excellent" - "Not Great")	/5 marks
Conditional formatting requirements satisfied:	/5 marks
Correctly sorted table:	/2 marks

Total: /30 marks