HR Consultancy Project using Excel Skills | MLT Ltd.

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 3 to 4 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.

Background Information about the Dataset

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):
o What formulas did you use and why?

o Email – delete all the personal email addresses and using excel features, create company email addresses for all employees. 
o What was the rationale to use a table rather than leaving the data as it was?
o Explanation of Excel Features used for the different Business tasks

4. Delete the following columns:
o 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:

 o 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).
o 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.
o 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.
o Salary – salaries are dollars and should be reflected as such.

For Project Deliverable #2 – Week 9

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.

For Project Deliverable #1 – Week 6

Some Business Task Requirements:

1. With the company policy having a retirement age of 65 (for all employees), insert a column to the right of Years of Service column and give it the heading “Years to Retirement”. Using Excel features, calculate the number of years that each employee has left before they retire from the company at age 65 (this must be a whole number). Your calculation should make reference to the retirement age on the References Table worksheet.

2. Salary negotiations have taken on different spin and instead of individual percent increase in employee salaries, a flat percentage increase will be implemented for 2022. With salary negotiations for 2022 almost completed. The VP wants to see what the salaries will be if there is a 3.5% increase from 2021. Insert a column to the right of the Last % Hike column and name it “Projected 2022 Salary”. Using Excel features, calculate the projected salaries for 2022 for all employees. Remember that salaries are dollars and should be reflected as such.

3. Using an appropriate chart, display the breakdown of the gender of the employees. The chart must show the labels along with the percentage breakdown beside it. This chart should be placed in a worksheet called “Requested Charts”.

NB. Remember to make use of the Reference Tables worksheet, to capture data for your charts.

4. Using an appropriate chart, display the number of employees in the different departments i.e. how many employees are in the Finance Department, in the International Department, etc. The chart must show the labels along with the numbers beside it. This chart should be placed in the “Requested Charts” worksheet.

NB. Remember to make use of the Reference Tables worksheet, to capture data for your charts.

5. Using a Clustered Column-Line chart, display the breakdown of the gender of the employees in the various departments. The chart must show the M, F gender as columns and the X gender as a line. The chart must show axis titles. This chart should be placed in the “Requested Charts” worksheet.

NB. Remember to make use of the Reference Tables worksheet, to capture data for your charts.

6. Using an appropriate chart, display the average salary for each gender based on their department i.e. the average salary for all F, M and X gender in the Finance Department etc. The chart must show the labels along with the numbers beside it. This chart should be placed in the “Requested Charts” worksheet.

For Project Deliverable #2 – Week 9

NB. Remember to make use of the Reference Tables worksheet, to capture data for your charts.

7. Each of the charts should have a coloured border.

8. Make 3 copies of the Revised Employee Data worksheet. Give them appropriate worksheet names based on the task that you will do on each. On each of the 3 worksheets, perform a 3- level sort of the table, to obtain useful information that the VP can use to analyse employee data. For each sorting activity, provide an explanation as to the importance of the information and why the VP would use this information in her analysis.

9. Create a new worksheet called “Employee Lookup Sheet”. On the Employee Lookup Sheet, using Excel features, create a means of inputting an Employee’s ID and Excel returning information on the employee. E.g. typing in Employee ID 850297, should return Shawna Buck’s name, her email address, date of birth, address, etc. The returned or display employee information must be all the column headings on the Employee Data for Lookup worksheet i.e. it must show the Pronoun, First Name, Middle Initial, Last Name, Gender, Department, …, User Name. 

For Project Deliverable #3 – Week 12

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. Modify the name of the Requested Charts worksheet to “Dashboard”. Ensure this is the first worksheet in the workbook.
2. Make sure to position the Explanation Sheet, so it is the second worksheet.
3. At the top of the Dashboard worksheet, design and insert a logo for the MLT Ltd. Company.
4. Create a table just to the right of the logo with an appropriate style and give it the title “Summary”. In this summary table, you must use Excel features to include the following information:
o Total number of employees
o Total Annual Salaries for 2021
o Total Annual Projected 2022 Salary
o Average Employee Age
o Maximum 2021 Salary

o Names of employees that will retire in 1 year (so celebration plans can get started)
o Any 2 other summary information that you deem to be important to highlight in the dashboard
5. Position the charts on the Dashboard worksheet.
6. Ensure that all Worksheets have a heading in cell A1. Colours, themes, styles are to be utilized to enhance the presentation.
7. For each of the data sheets:
o set the page orientation to landscape.
o set the Print Titles to repeat the heading row.
o create a Custom Header that shows “Version Date: [insert date]” on the left side, the “Sheet Title” in the middle, and the Page Number on the right.
o create a Custom Footer to show “Author: [type Last Names of Group members]” in the bottom right.