MIS 300 Assignment 2

Assignment 2

Instructions: Depending on your Excel skill level, you may find this assignment challenging

1. This assignment is from Extended Learning Module D in your textbook. Locate the chapter, and review the process of how to use Filter, Custom Filter, Conditional Formatting and Creating Pivot tables in this chapter.

2. Download Customer.xls provided in the homework assignment link to practice these Excel tools, applying the explanation provided in this module (or chapter).

Description of the assignment:

3. FINDING OUT INFORMATION ABOUT YOUR EMPLOYEES – download employee.xls.Suppose you own a small business and have a workbook with the following list:

1. ID—Unique employee’s identification number
2. First Name—Employee’s first name
3. Last Name—Employee’s last name
4. Department—Employee’s department
5. Title—Employee’s job title
6. Salary—Employee’s annual salary
7. Hire Date—Date employee was hired
8. Birth Date—Employee’s birthday
9. Gender—Female (F) or Male (M)
10. Clearance—N (none), C (confidential), S (secret), or TS (top secret)

4. For each task listed below, present your result in individual worksheet. Rename the worksheets used for the results with names that are meaningful, for example:

· Original Data(this would contain the information for the employee list)

· Average salary(this worksheet contains the result of a pivot table that summarizes each department’s average salary by gender

· Clearance(this worksheet would contain the count of employees for different types of clearance)…….

Task:

a. pivot table that shows average salary by gender within department.

b. pivot table that shows the number of employees by type of security clearance

c. Use conditional formatting to highlight those employees in the Engineering department.

d. Use conditional formatting to highlight those employees who have no clearance (none).

e. Use basic Filter to show only those employees who have top secret clearance (TS).

f. Use Custom Filter to show only those employees who earn more than $50,000

g. pivot table to show the highest and lowest salary of employees for each type of security clearance

5. When you are done with completing the assignment, you would have an Excel file with 8 worksheets (original list, and 7 result worksheets for tasks a through g). Rename this Excel file from employee.xls toyour name.xls. Submit this file using the assignment link provided.