Practice Review Assignments Data File needed for the Review Assignments: Compensation.xlsx Patrick wants you to try using some alternative calculations for bonuses and benefits for EVG employees. Complete the following: 1. 1. Open the Compensation workbook located in the Excel8 ? Review folder included with your Data Files, and then save the workbook as Bonus and Benefits. 2. 2. In the Documentation worksheet, enter your name and the date. 3. 3. In the Employee Data worksheet, rename the Excel table as EmpData. 4. 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. 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. 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. 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. 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. 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. 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. 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. 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. 13. Save the workbook, and then close it.

A 1 2 3 4 5 6 7 8 9 10 11 12 13 B C D Emp ID Last Name Hire Date Birth Date 1002 Lowe 1006 Forbes 1010 Speulda 1014 Hunt 1018 Hanson 1022 Philo 1026 Stolt 1030 Akhalaghi 1034 Vankeuren 1038 Mccorkle 1042 Mattis 1046 Baker 8/28/2014 5/24/2010 4/24/2015 7/18/2014 8/21/2015 3/5/2015 3/1/2013 12/8/2015 8/11/2011 6/12/2009 5/4/2012 12/18/2015 9/6/1966 12/15/1985 9/24/1968 8/9/1959 7/15/1950 5/2/1958 12/7/1977 12/4/1961 1/10/1959 1/30/1942 8/27/1989 1/6/1968 E F Gender Location F F M M F F M F F F M F NY SF SF NY NY SF SF NY NY AT AT DA G H Job Pay Grade Status FT FT FT FT FT FT FT FT PT FT FT FT A B B C A B C B C B B C I J K L M Current Salary Medical Plan Vision Plan Age Years Service SPOUSE2500 None FAMILY1000 SPOUSE1000 FAMILY1000 FAMILY1000 FAMILY2500 SPOUSE1000 FAMILY1000 FAMILY2500 SINGLE1000 FAMILY2500 EMP+Spouse None EMP+Family None EMP+Family EMP+Family None EMP+Spouse EMP+Family EMP+Family EMP EMP+Family $85,000 $40,000 $37,244 $80,000 $65,000 $125,000 $95,000 $36,000 $33,508 $21,840 $25,792 $32,011 ### ### ### ### ### ### ### ### ### ### ### ### N 2.3 6.6 1.7 2.5 1.4 1.8 3.8 1.1 5.4 7.6 4.7 1.0 O 401(k) Max 401(k) Contrib Company Match P Q R Bonus Amount Eligible Salary Increase Vision Plan Cost S T U V W Company match Bonus Pay Grade A Bonus Pay Grade B Bonus Pay Grade C X Y 3% $3,000 $6,000 $8,000 Z Years service as of AA 12/31/2016 AB AC Birthday this month AD 9 A 9 SINGLE2500 10 None 11 12 13 14 15 16 17 18 B C 425 0 D E F G H