MIS 3003

Excel Project

Spring2013

You are a new employee at the JacobsCompany. Your job is analysis, in particular, assessment of employee data and creation of relevant reports. Management has just given you a set of Excel spreadsheets to be analyzed. The spreadsheets are as follows: Employment, Salaries, Minority Class, Performance Category, District, and State. Management would like a number of reports created, but before that can be done, you must first have a deep understanding of the data that has been entered in the spreadsheets.

Step 1: Data Validation – for each of the following on the “Employment” sheet, use a formula to validate that the data has been entered correctly. For example, according to the “Salaries” sheet, the only Job Classifications allowed are A1-I9. Use a formula to mark incorrect entries as “Invalid” in red font (using conditional formatting), and leave correct entries blank. Columns have already been setup on the sheet for your entry. (2 pts. each)

  • Note: When using Vlookup, ISNA must be used as well. Also, no entries are allowed to be blank; blanks should appear as “Invalid” until corrected in Step 2 below.

(1a)Male/Female (must be M or F)

(1b)Minority Code

(1c)State Code

(1d)Company District

(1e)Shares of Stock (must be between 0 and 250, inclusive)

(1f)Job Classification

(1g)Performance Rating

Step 2: Data Correction – for all invalid entries, correct the data. For example, if a job classification is incorrect, change it to any value that will make it valid. In the real world, you would return the incorrect data to the appropriate party for correction. Since we are dealing with a fictional company, just make the data correct. (1 pt.)

Step 3: Additional Calculations Needed – management has requested that the “Employment” sheet contain calculations to produce the following. Use conditional formatting (red font) for “Invalid” entries.When using Vlookup, ISNA must be used as well. (3 pts. each)

  • Note: Be sure to format data appropriately, for example, $ dollar signs.
  • Note: Salary ranges (on the “Salaries” sheet) have been provided based on the job classification; therefore, for each employee, management needs to know if the new salary (step 3a) is still within that range. Therefore, first determine the min and max salaries (steps 3b and 3c) for each employee given his/her job classification. Then, use a formula to determine if each employee’s new salary is out of its appropriate range (step 3d).

(3a)New Salary for each employee (this is a result of the old salary plus the employee raise based on that employee’s performance rating)

(3b)Minimum Salary (see note above)

(3c)Maximum Salary (see note above)

(3d)Out of Salary Range (show “Overpaid” in orange font and “Underpaid” in yellow font (using conditional formatting) for invalid salaries) (see note above)

(3e)Security Authorization Level 4. Produce a new security authorization code using the first initial in their last name as the first character, the fourthcharacter in their employee id as the second character, the first and second characters in their employee id as the thirdand fourth characters, and “TU” as the fifthand sixth characters.

Step 4. Reports – use pivot tables and graphs (were requested) to answer the following: (3 pts. each)

  • Note: Be sure to format data appropriately, for example, $ dollar signs.
  • Note: 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)Theaverage amount of money spent on new salariesby district, in table and graphic form. Name the new sheet – 4a-New Salaries District Report.

(4b)Thetotalshares of stock owned by gender and minority. Create a pivot table only. Show minority name, not the code. Name the new sheet – 4b-Stock SharesG-MReport.

(4c)Thenumber of employeesin eachdistrictby performance rating. Create a pivot table only. Show performance rating name, not the code. Name the new sheet – 4c-District PerfReport.

(4d)The number of employees and the average new salary for each minority. Create a pivot table only. Show minority name, not the code. Name the new sheet – 4d-Minority Report.

(4e)Create a report that will be useful to management (i.e., use your imagination). Create a pivot table only. Name the new sheet – 4e-Management Report.

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. You should cite numbers from your tables in your explanations. (1 pt. each)

Before turning in your project, make sure your worksheets are in the order indicated below and your Excel file is named as indicated below. Failure to do so will result in a five-point reduction in your overall project score.

  • Worksheet order: (1) Employment, (2) 4a-New SalariesDistrictReport, (3) 4b-Stock Shares G-MReport, (4) 4c-District PerfReport, (5) 4d-MinorityReport, (6) 4e-Management Report, and (7) all other sheets.
  • Excel file name:yourlastname_firstname_classtime.xlsx(If working in teams, list all student names in the file name.)