AIM OF THE ASSIGNMENT

The purpose of this assignment is to show your Excel skills.

The assignment is based around you developing a suitable spreadsheet which you can use to track your study progress, assignment due dates and assignment results.

While you must meet the minimum requirements set out below, feel free to add additional information which you think will be useful. In particular, for students who have already completed courses, feel free to add the final results to the course planning worksheet.

TASK 1 - SPREADSHEET SETUP

Skills to Show

• Create, and use at least once, a suitably named range for the Course data listed on the Courses tab
• Create, and use at least once, a suitably named range for the Grade data listed on the Grades Tab
• Use a suitable formula for the AVERAGE column on the Grades tab
• Create a suitably named tab for Task 2
• Create a suitably named tab for Task 3
• Create a suitably named tab for Task 4 Master
• Create suitably named tabs for each of the 6 actual courses being entered in Task 4
• Overall Structure of worksheets
• Includes: layout, formatting, alignment, spelling, and suitable use and structure of formulas and functions, use of ISBLANK to make the spreadsheet more visually appealing

TASK 2 - COURSE PLANNING WORKSHEET

Create a new worksheet (not workbook) which you could use by a student to record your planned courses of study for all courses completed to date, courses currently enrolled in, and future enrolments.

Remember to refer to the examples in Appendix B for guidance.

Features

Your worksheet should include details for Year, Semester, Course Code and Name, Final Grade, Grade

Range summary for each grade received as a minimum but other details can also be included.

Skills to Show

• Add your name and date birth to this worksheet
• Use at least 2 VLOOKUPs (must be for 2 different purposes)
• Calculate statistics for each course showing minimum and maximum possible mark for each grade and a grade average
• Calculate overall statistics for all courses completed to date showing the minimum mark, the maximum mark and the average mark obtained overall
• Use a COUNT function to calculate the frequency of each Grade received to date
• Create at least 1 drop down list with suitable messages
• Use at least 1 IF(ISBLANK) to ensure all calculations work as intended
• Use data validation to ensure the semester is one of the following: 1, 2 FY or SS where FY stands for full year and SS stands for summer school. Include suitable messages.
• Use at least 1 absolute cell reference (in addition to using named ranges)

Data to Enter

• Enter the relevant study plan data provided in Appendix A and use calculated values to check your formulas are accurate
• Add at least 1 other course details of your choice (either real or desired)

TASK 3 - ASSIGNMENT TRACKING WORKSHEET
Create a new worksheet (not workbook) which you can use to summarise your assignments and their due dates. Remember to refer to the examples provided in Appendix B for further guidance.

Features
Your spreadsheet must include the following:
• Date so it will show the current date when the spreadsheet is opened
• Course code and name
• Assessment number, due date, whether it has been submitted or not and days until due

• The days until due should show either the number of days from the current date until the day due, or
• "submitted" if it has been handed in, or
• "overdue" if the assignment due date has passed and it has not yet been submitted

• Raw mark and raw percentage mark

• Raw mark is the actual mark received
• Raw Percentage is raw mark divided by total mark

Skills to show

• Use dropdown list at least once. Include suitable messages.
• Use VLOOKUP at least once
• Create a nested IF statement to display the message showing the number of days until the assignment is to be submitted or that it has been submitted, or is overdue (ie to show the information in the Status column above)
• Use at least 2 different DATE functions
• Use at least 1 absolute reference in addition to any named ranges used
• Calculate the Raw Percentage mark

Data to enter
Enter all the relevant assessments for the course XXX104 shown above and use to check your formulas
Enter all your assessments for NZD550 including your result for assignment 1 (if not marked, make up a mark!)
Enter all your assessments for at least 1 other course you are enrolled in for 2016.

TASK 4 - COURSE ASSESSMENT SUMMARY SHEET
Create a new worksheet (not workbook) which you could use to track your assignment results and calculate the minimum mark required for the final exam for each course you are enrolled in.

Create a new worksheet which could be used as a master sheet (ie has formulas entered where required but no numbers for the actual results) labelled as Course Master

Copy the master to a different worksheet for each course in Appendix A and label each worksheet with the course code

Remember to refer to the examples in Appendix B for further information.

Features
Your spreadsheet must contain the following:
• Year and semester (a course may be offered in semester 1, 2, full year or summer school)
• Course code, name and credits
• The raw percentage, weighting and weighted mark for each assessment
• Calculates the minimum mark required for the final exam (as a mark out of 100)

• To pass the course, the final exam mark must be at least 40%
• Some courses do not have a final exam. If there is no final exam, display a suitable message instead
• All course weightings (assessments and final exam) must total 100%

Refer to the examples provided to see how the minimum mark calculations are made.

Skills to Show - in master

• Use VLOOKUP at least once
• Create at least 2 different Drop Down Lists, including suitable messages
• Create at least 3 different IF statements (NB a nested IF statement would count as more than 1 IF statement based on number of IFs included)
• Use of suitable formulae throughout
• Use data validation to provide information on the data to be included in
• the Assessment, Weighting and Raw Percentage cells.

Data to enter - copy your master to a separate tab for each course

• Enter all the assessments for the Introductory courses XXX101, XXX102, XXX103 and XXX104 as shown in Appendix A. Only enter the relevant information and check and use to make sure your formulas are working correctly. Choose your own semester of study.
• Enter all your assessments for or NZD550 including your result for assignment 1.
• Enter all your assessments for at least 1 other course you are enrolled in for 2016 or completed in a prior year. If this is the only course you are enrolled in, choose any other course and make up the data.