EXCEL INDEPENDENT PROJECT B INSTRUCTIONS
This project is not found in your book. Use what you have learned so far in the course and apply
critical thinking skills to stretch the limits of your knowledge. Read the assignment instructions
carefully below or from Excel_Independent_Project_B_Instructions.pdf . Compare your results to
the grading rubric before submitting.
Open the IPB_Faculty_Data.xlsx file attached below. Save the file as
Lastname_Firstname_IPB_Faculty_Data.xlsx. You work in the Human Resources Department for Laurel College. You’ve just received some data about
Faculty members and have been given the task of analyzing this data. (Note: Because I have to see your
results for each question for grading purposes, the data has been duplicated on three separate sheets one for each question. In real life, you would only keep data in one list.)
Format and prepare all sheets for printing as you will be distributing copies to your management team.
Format the sheets using any theme, colors, font or table style you like as long as the results are
professional looking. Make sure all data is set to print in an easily readable format. Set print titles where
needed so all data on each page is clearly identified.
Manipulate the data on each sheet to show your results for the three questions below.
1. What is the average salary by Gender and Rank? Do males or females make more money? Is this
the same for all ranks? Summarize the answers to these questions below your results.
2. Prepare a report showing the records and total number of faculty in the Finance and Accounting
Departments who have worked here 10 years or more. Sort the list by Rank in order of seniority* (Full
Prof, Instructor, Associate, Assistant) and then by Year Hired in Ascending (smallest to largest) order.
Do not display (hide) the Salary column as this information is confidential.
*Use custom sort. See page 287. 3. Determine how much the employer will have to contribute to the retirement plan for each department.
Add conditional formatting to highlight the top 5 salaries. Highlight the lowest 5 salaries using a
different formatting style. (Hints: Add a field to the right in column i that calculates the Employers
Contribution Amount to the Retirement Fund. If employees said Yes to participation, multiply the
Salary times the Employers Contribution percentage shown. The contribution is zero for employees
who said No to participation. Once you have calculated the contribution amounts, then find the totals
for each department.) Grading Rubric:
Your work will be graded on the following scale:
Description Points All sheets are formatted professionally. Numbers are right aligned and formatted
with appropriate number style. Borders, colors, fonts etc. are used appropriately so
data is clear and easy to read. 5 All sheets are set to print. Headers with name, filename and sheet name are
included on all sheets. 5 Question 1:
Average Salary is shown by Gender and by Rank. Results will update if data is
sorted or edited. 20 Results are summarized to answer questions. 5 Question 2:
Only Finance and Accounting faculty are shown. 5 Records are sorted by seniority (Full Prof, Instructor, Associate, Assistant) and by
Year Hired. 10 Only employees who were hired 10 or more years are shown. 10 Salary column is hidden. 5 Total number of faculty is shown. 5 Question 3:
Column added to calculate employers required contribution to retirement plan. 10 Totals are shown by department for contributions. 10 Conditional formatting is added to show 5 highest salaries. 5 Conditional formatting is added to show 5 highest salaries. 5 Total Points 100 After completing the assignments, attach the Lastname_Firstname_IPB_Faculty_Data .xlsx and
submit for grading.