1. Open the compensation workbook.
2. In the Documentation worksheet, enter your name and the date.
3. In the Employee Data worksheet, rename the Excel table as empData.
4. Employees who are age 50 or older are allowed to contribute up to $22,000 during the year to their 401(k) plan; all employees under age 50 can contribute a maximum of $17,000 during the year. In the 401(k) Max Contrib column, use an IF function to determine the maximum 401(k) contribution for each employee.
5. Only full-time (Job Status) employees over the age of 30 (Age) with more than one year of service are eligible for the 401(k) benefit. In the 401(k) Company Match column, enter the IF and AND functions to calculate the 401(k) company match as a percentage of the employee’s current salary; use a reference to cell X1 to obtain the 401(k) matching percent rate (3 percent). If the employee is not eligible, enter 0. 6. All full-time (FT) employees are eligible for a bonus. Pay Grade A employees receive $3,000 (cell X2), Pay Grade B employees receive $6,000 (cell X3), and Pay Grade C employees receive $8,000 (cell X4). In the Bonus Amount column, enter nested IF functions to calculate the bonus. For employees not eligible for a bonus, display the text Ne.
7. In the Eligible Salary Increase column, enter IF and OR functions to insert the text Not eligible if the employee’s pay grade is C or the employee’s job status is a consultant (CN). Leave the cell blank if the individual is eligible for a salary increase.
8. In the Vision Plan Cost column, enter the HLOOKUP function to do an exact match lookup to calculate the annual vision plan cost. Use the VisionRates data (the range $H$3:$K$4 in the Lookup Tables worksheet), which contains the monthly vision rates. (Hint: The HLOOKUP function provides the monthly rate, which you will need to multiply by 12 to determine the annual rate.)
9. In the Years Service column, modify the formula to incorporate the IFERROR function and display the message invalid hire date if an error value occurs. Test the modified formula by changing the date in cell C2 from 8/28/2014 to 18/28/2014. Increase the column width as needed to display the entire message. 10. Edit the Duplicate Values conditional formatting rule applied to the Emp ID column so that the fill color of the duplicate value is formatted as light blue (the seventh color in the bottom row of the Background Color palette). Test this change by typing 1002 in cell A101.
11. In the Employee Summary worksheet, enter the COUNTIF function in cells C3 and C4 to count the number of female and male employees, respectively. 12. In cells D3 and D4, enter the AVERAGEIF function to calculate the average salary of female employees and the average salary of male employees, respectively. Format the average salary column using the Accounting format with two decimal places. 13. Save the workbook, and then close it.
Â
Â
