Part A

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.[1]

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

Please take note of the VPs 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):

1. What formulas did you use and why?
2. What was the rationale to use a table rather than leaving the data as it was?
3. Explanation of Excel Features used for the different Business tasks
4. Delete the following columns:
   • Fathers name, Mothers 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 address 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 employees 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 employees age this year (as a whole number). On the References Table worksheet, there must be a cell with todays date and the calculation of the age must reference this cell.

Hint: The TODAY function should be used.

1. 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 todays date and the calculation of the years of service must reference this cell, as well as use the ROUND function.
2. Salary salaries are dollars and should be reflected as such.
3. Ensure that each tab has a different colour

Rubric for Deliverable Part A:

	Exceed Expectations	Met Requirements	Needs Improvements	Not Met
	3	2	1	0
Explanation Sheet (1st worksheet), providing some insight into the purpose of the workbook and the different types of business questions that it will answer. Must include details of the group of students working on the project.
All BUSINESS TASK REQUIREMENTS (the above steps) completed
Professionalism including presentation, formatting, spelling, grammar, proof reading, and attention to the readers overall experience
TOTAL

For Project Deliverable Part B

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.[2]

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

Please take note of the VPs preferences.

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.

1. 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.

1. 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.

1. 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.

1. 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.

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

1. Each of the charts should have a coloured border.
2. 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.

Rubric for Deliverable Part B:

	Exceed Expectations	Met Requirements	Needs Improvements	Not Met
	3	2	1	0
Explanation Sheet expanded to include additional insight into the purpose of the workbook and the different types of business questions that it will answer.
All BUSINESS TASK REQUIREMENTS (the above steps) completed
Professionalism including presentation, formatting, spelling, grammar, proof reading, and attention to the readers overall experience
TOTAL

For Project Deliverable Part C

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.[3]

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

Please take note of the VPs 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:
   • Total number of employees
   • Total Annual Salaries for 2021
   • Total Annual Projected 2022 Salary
   • Average Employee Age
   • Maximum 2021 Salary

• Names of employees that will retire in 1 year (so celebration plans can get started)
• Any 2 other summary information that you deem to be important to highlight in the dashboard

1. Position the charts on the Dashboard worksheet.
2. Ensure that all Worksheets have a heading in cell A1. Colours, themes, styles are to be utilized to enhance the presentation.
3. For each of the data sheets:
   • set the page orientation to landscape.
   • set the Print Titles to repeat the heading row.
   • 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.
   • create a Custom Footer to show Author: [type Last Names of Group members] in the bottom right.
   • freeze panes to always show the headings row

Rubric for Deliverable #3:

	Exceed Expectations	Met Requirements	Needs Improvements	Not Met
	3	2	1	0
Explanation Sheet (2nd worksheet) expanded to include additional insight into the purpose of the workbook and the different types of business questions that it will answer.
All BUSINESS TASK REQUIREMENTS (the above steps) completed
Professionalism including presentation, formatting, spelling, grammar, proof reading, and attention to the readers overall experience
TOTAL

[1] Your work should NOT look like another students or groups work or the example given.

[2] Your work should NOT look like another students or groups work or the example given.

[3] Your work should NOT look like another students or groups work or the example given.