oyees.pdf - Adobe Acrobat Reader DC Help --Case Review ... x PRACTICE Data File needed for the Review Assignments: Employees.xlsx Vanessa wants you to analyze the MBHC employee data to determine each employee's work anniversary, available comp days, and bonus eligibility. She also wants you to calculate the bonus amount for eligible employees. Complete the following: 1. Open the Employees workbook located in the Excel8 > Review folder included with your Data Files, and then save the workbook as MBHC Employees in the location specified by your instructor 2. In the Documentation worksheet, enter your name and the date. 3. In the Employee Data worksheet, rename the Excel table as EmployeeTbl. 4. In the Work Anniversary column, enter an IF function. If the month in the employee's hire date matches the month in the Data Tables worksheet (cell B4), Yes should appear in the work anniversary column; otherwise, the cell should remain blank. (Hint: Remember to use an absolute cell reference to cell B4 because the formula will be copied to the rest of the column.) All employees receive a card on their work anniversary. 5. In the Eligible for Bonus column, enter a formula with IF and OR functions to display the text No if the employee's pay grade is D or the employee's job status is a part time (PT). Leave the cell blank if the employee is eligible for a bonus. 6. In the Comp Days column, enter a formula with nested IFs to display the number of comp days that an employee will receive based on their years of service. The table for Comp Days is on the Data Tables worksheet. (Hint: Remember to use absolute references to the cells in the Data Tables worksheet because the formula will be copied to the entire column.) 7. In the Bonus column, enter an IF function with a nested VLOOKUP function to calculate the bonus for each employee based on whether the employee is eligible for a bonus (column M) and his or her Pay Grade. The bonus information for qualifying employees is in a table named Bonus_Table in the Data Tables worksheet. Format the Bonus column with the Accounting format with no decimal places. 8. In the Years of Service column, modify the formula to include 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 E2 from 7/10/2014 to 17/10/2014. AutoFit the column. 9. In the Work Anniversary column, which also uses the Hire Date, modify the formula to include the IFERROR function and display the message Invalid hire date if an error value occurs. AutoFit the column 10. Edit the Duplicate Values conditional formatting rule applied to the Employee ID column so that the fill color of the duplicate value is formatted as light blue (the seventh color in the bottom rch 60 se Review ... 1 / 1 Danik me employee is engibre Toma onu 6. In the Comp Days column, enter a formula with nested IFs to display the number of comp days that an employee will receive based on their years of service. The table for Comp Days is on the Data Tables worksheet. (Hint: Remember to use absolute references to the cells in the Data Tables worksheet because the formula will be copied to the entire column.) 7. In the Bonus column, enter an IF function with a nested VLOOKUP function to calculate the bonus for each employee based on whether the employee is eligible for a bonus (column M) and his or her Pay Grade. The bonus information for qualifying employees is in a table named Bonus_Table in the Data Tables worksheet. Format the Bonus column with the Accounting format with no decimal places. 8. In the Years of Service column, modify the formula to include 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 E2 from 7/10/2014 to 17/10/2014. AutoFit the column. 9. In the Work Anniversary column, which also uses the Hire Date, modify the formula to include the IFERROR function and display the message Invalid hire date if an error value occurs. AutoFit the column. 10. Edit the Duplicate Values conditional formatting rule applied to the Employee 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 3226 in cell A101. 11. In the Employee Analysis worksheet, enter the COUNTIF function in cells B3 and 14 to count the number of part-time and full-time employees, respectively. (Hint: Job Status column in the EmployeeTbl table.) 12. In cells C3 and C4, enter the SUMIF function to calculate the total salaries of part-time employees and the total salaries of full-time employees, respectively. Format the Total Salary column with the Accounting format and no decimal places. 13. In cells D3 and D4, enter the AVERAGEIF function to calculate the average salary of part-time employees and the average salary of full-time employees, respectively. Format the Average Salary column with the Accounting format and no decimal places. 14. Save the workbook, and then close it. 150% 1 Copyright 2017 Cangage gi 60 hp oyees.pdf - Adobe Acrobat Reader DC Help --Case Review ... x PRACTICE Data File needed for the Review Assignments: Employees.xlsx Vanessa wants you to analyze the MBHC employee data to determine each employee's work anniversary, available comp days, and bonus eligibility. She also wants you to calculate the bonus amount for eligible employees. Complete the following: 1. Open the Employees workbook located in the Excel8 > Review folder included with your Data Files, and then save the workbook as MBHC Employees in the location specified by your instructor 2. In the Documentation worksheet, enter your name and the date. 3. In the Employee Data worksheet, rename the Excel table as EmployeeTbl. 4. In the Work Anniversary column, enter an IF function. If the month in the employee's hire date matches the month in the Data Tables worksheet (cell B4), Yes should appear in the work anniversary column; otherwise, the cell should remain blank. (Hint: Remember to use an absolute cell reference to cell B4 because the formula will be copied to the rest of the column.) All employees receive a card on their work anniversary. 5. In the Eligible for Bonus column, enter a formula with IF and OR functions to display the text No if the employee's pay grade is D or the employee's job status is a part time (PT). Leave the cell blank if the employee is eligible for a bonus. 6. In the Comp Days column, enter a formula with nested IFs to display the number of comp days that an employee will receive based on their years of service. The table for Comp Days is on the Data Tables worksheet. (Hint: Remember to use absolute references to the cells in the Data Tables worksheet because the formula will be copied to the entire column.) 7. In the Bonus column, enter an IF function with a nested VLOOKUP function to calculate the bonus for each employee based on whether the employee is eligible for a bonus (column M) and his or her Pay Grade. The bonus information for qualifying employees is in a table named Bonus_Table in the Data Tables worksheet. Format the Bonus column with the Accounting format with no decimal places. 8. In the Years of Service column, modify the formula to include 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 E2 from 7/10/2014 to 17/10/2014. AutoFit the column. 9. In the Work Anniversary column, which also uses the Hire Date, modify the formula to include the IFERROR function and display the message Invalid hire date if an error value occurs. AutoFit the column 10. Edit the Duplicate Values conditional formatting rule applied to the Employee ID column so that the fill color of the duplicate value is formatted as light blue (the seventh color in the bottom rch 60 se Review ... 1 / 1 Danik me employee is engibre Toma onu 6. In the Comp Days column, enter a formula with nested IFs to display the number of comp days that an employee will receive based on their years of service. The table for Comp Days is on the Data Tables worksheet. (Hint: Remember to use absolute references to the cells in the Data Tables worksheet because the formula will be copied to the entire column.) 7. In the Bonus column, enter an IF function with a nested VLOOKUP function to calculate the bonus for each employee based on whether the employee is eligible for a bonus (column M) and his or her Pay Grade. The bonus information for qualifying employees is in a table named Bonus_Table in the Data Tables worksheet. Format the Bonus column with the Accounting format with no decimal places. 8. In the Years of Service column, modify the formula to include 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 E2 from 7/10/2014 to 17/10/2014. AutoFit the column. 9. In the Work Anniversary column, which also uses the Hire Date, modify the formula to include the IFERROR function and display the message Invalid hire date if an error value occurs. AutoFit the column. 10. Edit the Duplicate Values conditional formatting rule applied to the Employee 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 3226 in cell A101. 11. In the Employee Analysis worksheet, enter the COUNTIF function in cells B3 and 14 to count the number of part-time and full-time employees, respectively. (Hint: Job Status column in the EmployeeTbl table.) 12. In cells C3 and C4, enter the SUMIF function to calculate the total salaries of part-time employees and the total salaries of full-time employees, respectively. Format the Total Salary column with the Accounting format and no decimal places. 13. In cells D3 and D4, enter the AVERAGEIF function to calculate the average salary of part-time employees and the average salary of full-time employees, respectively. Format the Average Salary column with the Accounting format and no decimal places. 14. Save the workbook, and then close it. 150% 1 Copyright 2017 Cangage gi 60 hp