MIS 3003: Excel Project

Spring 2012

Requirement: / Points Possible / Your Grade / Comments
Step 1: Data Validation – Use a formula to mark incorrect entries as “Invalid” in red font (using conditional formatting), and leave correct entries blank. (Note: When using Vlookup, ISNA must be used as well.)
(1a)Male/Female (must be M or F) / 2
(1b)Minority Code / 2
(1c)State Code / 2
(1d)Company District / 2
(1e) Shares of Stock (must be between 0 and 150, inclusive) / 2
(1f) Job Classification / 2
(1g)Performance Rating / 2
Step 2: Data Correction – for all invalid entries, correct the data. / 1
Step 3: Additional Calculations Needed – (Note: When using Vlookup, ISNA must be used as well; format data appropriately; use conditional formatting)
(3a)New Salary for each employee / 3
(3b)Minimum Salary / 3
(3c)Maximum Salary / 3
(3d)Out of Salary Range (show “Overpaid” in red font and “Underpaid” in orange font (using conditional formatting) for invalid salaries) / 3
(3e)Security Authorization Level 4. Produce a new security authorization code using “GO” as the first and second characters, the second and third characters in their employee id as the third and fourth characters, the fourth and fifth characters in their employee id as the fifth and sixth characters, and “TU” as the seventh and eighth characters. / 3
Step 4. Reports – (Note: format data appropriately, for example, $ dollar signs; Each report and graph is required to have a meaningful title, and graphs must have x and y axes labeled appropriately. Do not use the sheet name as either the report or graph title. Failure to use an appropriate title will result in a score of zero on the question.
(4a)The total amount of money spent on new salaries (a total of new salaries) by district, in table and graphic form. Show district name, not the code. Name the new sheet – 4a-New Salaries District Report. / 3
(4b)The total shares of stock owned by gender, in table and graphic form. Name the new sheet – 4b-Stock Shares Gender Report. / 3
(4c)The number of employees in each job classification by gender. Create a pivot table only. Name the new sheet – 4c-Job Gender Report. / 3
(4d)The number of employees in each job classification by minority. Create a pivot table only. Show minority name, not the code. Name the new sheet – 4d-Job Minority Report. / 3
(4e)Create a report that will be useful to management. Name the new sheet – 4e-Management Report. / 3
Step 5. Explanations – for each of the five reports in step 4, briefly explain below each (on the worksheet itself) what the report tells management and how management might use the report in decision making. / 5
Total / 50
Sheet order:(1) Employee, (2) 4a-New Salaries District Report, (3) 4b-Stock Shares Gender Report, (4) 4c-Job Gender Report, (5) 4d-Job Minority Report, (6) 4e-Management Report, and (7) all other sheets.
File name:yourlastname_firstname_classtime.xlsx