Scenario:You are a HR manager for a convenient store and you have been asked to create aninteractiveexcel sheet that will allow you to input the data about the staff’s performance and automatically...

Scenario:

You are a HR manager for a convenient store and you have been asked to create an

interactive



excel sheet that will allow you to input the data about the staff’s performance and automatically create analysis of the same data.

Must have details:

1.
Minimum of three excel sheets need to be created within one excel file

2.
Sheet one (Data Sheet) should contain 7 fields:

1.
first name

2.
last name

3.
gender

4.
age

5.
e-mail,

6.
education

7.
salary.

3. Assume you need the following analysis in sheet two.

>Firstly, you need their full name.

>Secondly, you need their Initials

>Thirdly, because your company need to give staff who’s age is over 25 special training, therefore, you need to excel sheet to show you whether the individual is over the age of 25 or not. Highlight the one that are.

>If they are over the age of 25, you need the sheet to display their email address, because you need to contact them. If not,display the word ‘None Applicable’.

>In addition, education level is important for the company, and those whose education level is only ‘primary’ need to be displayed.

>Then, because all female staff whose age is 30 is eligible for social support funds, your excel must be able to display either ‘Eligible for social support funds’ or ‘Not applicable’.

>Lastly, the table must have filter.

4. Assume you need the following analysis in sheet three:

·
Have a graph and a table that allow you to see how much salary you pay between different education level

·
Have a graph and a table that show how much salary you pay between different age (with gender as filter)

·
Create a table that count how many “Eligible for social support funds” you have, as well as how many people need special training.

·
Please note there is various ways for you to complete sheet three. If you manage to create an efficient and functioning display, then you may choose to use any excel functions at your disposal

·
Create a table with header as Education and Average Salary, and ONLY in row below. The cell under education should be a dropdown list of all the education options. Depend on the selection, the cell under salary should reflect your selection.

5. Addition mark will be awarded to student who done the following optional objective(s):

·
Add whatever formatting you feel you needed to make the interactive worksheet presentable. Use the attached material as inspiration.

·
Add extra column to the data and do additional analysis you would be interested in. Note: If you just add extra column but does not show how you would use the data, then it will not be counted.

·
Use Marco to auto refresh the data

·
Create a dashboard for user

Testing yourself!

Your excel database MUST be functioning, therefore after you have successful create your sheet. Go to


http://www.randat.com(Links to an external site.)



, generate a random data basing on the 7 columns the question provided to you and generate 50 rows. Copy the data into the sheet one. If the excel works, then sheet two and three should display properly

Helpful Links:

https://www.youtube.com/watch?v=K74_FNnlIF8&t=1850s(Links to an external site.)