COMP 1214 Project Instructions Assignment

Objective

• This project gives students the opportunity to work with real-world data and perform some endto-end development of a workbook, without step-by-step instructions.
• Students can use a template, to explain the rationale behind the work completed in the project.
• Apply the Excel skills that are covered throughout the course to modify and manipulate the spreadsheet, whilst providing explanation for the modifications.
• To give students the opportunity to demonstrate their ability to work towards milestones leading to the completion of the project.
• Allow students to collaborate and work in groups, as the project can be completed in groups of 2 to 3 students.
• Group members are required to review the Project Charter, make group decisions based on the guidelines in the Charter, sign the charter and upload the signed charter to DC Connect.

Background Information about the Dataset

• A Master Excel Dataset has been provided for you. The dataset is raw data that MLT Ltd. has gathered on their employees.
• MLT Ltd. is a huge retail store in the US, with locations all over the country.
• MLT Ltd. has hired you as their HR Consultant and your job is to modify and manipulate the data to create a meaningful dashboard to present to the VP Human Resources. The VP has made it clear that she enjoys viewing colourful and visual presentations.
• You are advised to utilize tables and structured references when working through the project.

For Project Deliverable #1 – Week 6

Apart from a few main tasks that you must complete (and provide rational for how they were completed), the beauty, creativity and originality of the Workbook is up to you as the Consultant.

• You are advised to utilize tables and structured references when working through the project.

Please take note of the VP’s preferences.

Some Business Task Requirements:

1. The raw data worksheet that was provided to you, must be included in your workbook. Name the raw data worksheet “Raw Employee Data”. Make a copy of the worksheet and name it“Revised Employee Data” – this is the worksheet that you will then start to work with. Hide the original data worksheet.
2. To use the Excel built-in features, you will need to create a worksheet, that should be called “Reference Tables”. This worksheet will house all tables/dataset that you will generate and reference when creating formulas on the Revised Employee Data worksheet or any other worksheet that you will create in the future.
3. Insert a worksheet named “Explanation Sheet” at the beginning of the workbook. The purpose of this worksheet is to provide the VP some insight into the purpose of the workbook and how the different types of business tasks were addressed. Worksheet must also include details of the group of students working on the project.

   Sample details for this worksheet (not limited to only these):

   • What formulas did you use and why?
   • What was the rationale to use a table rather than leaving the data as it was?
   • Explanation of Excel Features used for the different Business tasks
4. Delete the following columns:
   • Father’s name, Mother’s Name, Time of Birth, Weight in kgs., Quarter of Joining, Half of Joining, Year of Joining, Month of Joining, Month Name of Joining, Short Month, Day of Joining, DOW of Joining, Short DOW, and Password
5. Modify the following column information:
   • Email – delete all the personal email addresses and using excel features, create company email addresses for all employees. The format of the emails should be FirstName.LastName@mlt.com. If there are employees that have the same email address, then the format of the email adress for those employees should be FirstName.MiddleInitial.LastName@mlt.com. Your worksheet should provide proof that you used an Excel feature to check if there are duplicate email addresses.

     Hint: There are no duplicate email addresses, but your worksheet should still show that you used Conditional Formatting to make the check.

   • Name Prefix – change the heading to “Pronouns”. Delete all the name prefixes and using Excel features, insert “She/Her” where the dataset shows that the employee’s gender is recorded as F, insert “He/Him” where the records show the employee is M, and “Other” where the employee is non-binary (X).
   • Age in Yrs – change the heading to “Age in Years (this year)”. Delete all the ages shown and using Excel features, calculate each employee’s age this year (as a whole number). On the References Table worksheet, there must be a cell with today’s date and the calculation of the age must reference this cell.

     Hint: The TODAY function should be used.

   • Age in Company (Years) – change the heading to “Years of Service”. Delete all the numbers shown and using Excel features, calculate the number of years that each employee has been hired by the company (this must be a whole number). On the References Table worksheet, there must be a cell with today’s date and the calculation of the years of service must reference this cell, as well as use the ROUND function.
   • Salary – salaries are dollars and should be reflected as such.
6. Ensure that each tab has a different colour

Are you struggling to keep up with the demands of your academic journey? Don't worry, we've got your back! Exam Question Bank is your trusted partner in achieving academic excellence for all kind of technical and non-technical subjects.

Our comprehensive range of academic services is designed to cater to students at every level. Whether you're a high school student, a college undergraduate, or pursuing advanced studies, we have the expertise and resources to support you.

To connect with expert and ask your query click here Exam Question Bank