PROJECT DESCRIPTION
Theresa Pratt works in the business office at Mills College, a technical college in Council Bluffs, Iowa. She has created a workbook to store and analyze registration and employee data. The workbook is composed of several individual worksheets. Theresa has asked you to help her work with table data, create advanced formulas, and build macros.
PROJECT STEPS
1. Switch to the Summary worksheet. Add a comment to the merged range B5:C5 that reads Use these figures in annual performance review. (including the period). Erase any other text that appears in the comment, including your name.
2. In cell B7, enter a formula using the SUM function that totals the value of cell B7 from each of the following workbooks: Qtr1, Qtr2, Qtr3, Qtr4. Copy the formula from cell B7 into the range B7:C9.
3. Add defined names to the Summary worksheet as described below:
a. Select the range A7:C9 and create names for the selection using the left column option.
b. Add the defined name Iowa_Total to cell B10.
c. Add the defined name Nebraska_Total to cell C10.
4. Group the Summary, Qtr1, Qtr2, Qtr3, and Qtr4 worksheets. Format the grouped worksheets as described below:
5. Make a copy of the Qtr4 worksheet and name it Qtr4-Revised. Move the Qtr4-Revised worksheet between the Qtr4 worksheet and the Calls worksheet. Switch to the Qtr4-Revised worksheet. Edit the content of the merged range B5:C5 to read Quarter 4 - Revised. (Hint: Do not use a period in the name of the new worksheet or in the range B5:C5.)
6. View the Qtr4 worksheet in Normal view and add external references to the worksheet as described below:
External reference to support_NP_E13_CS_T5-8_P1a_Iowa.xlsx
7. Switch to the Calls worksheet. Create a table based on the range A1:E48 that contains headers, and then update it as described below:
a. Change the name of the table to Qtr1Calls.
b. Apply the Table Style Medium 2 table style to the table.
8. Edit the record shown in row 43 of the Qtr1Calls table so that it has a State field value of Iowa.
9. Switch to the EmployeeSubtotal worksheet. Freeze the top row (row 1) of the worksheet.
10. Insert subtotals into the EmployeeSubtotal worksheet as described below:
11. Switch to the EmployeeList worksheet and complete the following actions:
. Apply a conditional formatting rule to the range A3:A102 that will highlight any Duplicate values using a conditional formatting color option of your choice. (Hint: Two values in the table will be highlighted.)
a. Change the Emp ID field value for the employee with the last name Ralston to 1011, so that the conditional formatting rule no longer highlights any value in column A. Do not clear the conditional formatting rule from the worksheet.
12. Sort the table by the Job Status field value in ascending order, then by the Pay Grade field values in ascending order, and finally by the Current Salary field value from Largest to Smallest.
13. Add a hyperlink to cell H2 in a section of the workbook as described below:
14. In cell K3, enter a formula using the COUNTIF function that will count the number of employees with a Job Status field value of FT. Use a structured reference to the Job Status column as the range parameter in your formula.
15. In cell K4, enter a formula using the AVERAGEIF function that will determine the average salary (based on the Current Salary field values) for the employees with a Job Status field value of FT. Use structured references to the Job Status column and Current Salary column in your formula.
16. Create a new PivotTable (not a recommended PivotTable) based on the EmployeeList table. The PivotTable should be placed in a new worksheet titled EmployeeListPivot between the EmployeeSubtotal and EmployeeList worksheets. Update the PivotTable as described in the following steps so that it matches Figure 3 below:
17. Switch to the EmployeeBenefits worksheet. Add a Total Row to the EmployeeBenefits table. Using the total row, add the following calculations to the table:
18. In cell N2, enter a formula using the IF and OR functions to determine whether an employee will earn a bonus vacation week. A bonus vacation week is awarded to employees based on their job status (shown in Column G) or years of service (shown in column M). Use structured references and the following parameters when creating this formula:
19. In cell O2, enter a formula using nested IF functions to calculate the maximum amount of 401(k) contributions that the company will match for an employee. The company's 401(k) matching plan is based on an employee's job status (shown in column G) and years of service (shown in column M). Use the following parameters and noted reference types when creating this formula:
a. If an employee's Job Status field value is equal to FT, the employee is eligible for the 401(k) matching program. Use a structured reference to the Job Status field in the formula.
b. If the FT employee also has a Years Service field value greater than or equal to 15, the Max 401(k) Company Match amount is calculated by multiplying the employee's Current Salary by the value in cell T3. Use structured references to the Years Service and Current Salary fields and an absolute reference to cell T3 in the formula.
c. If the FT employee does not have a Years Service value greater than or equal to 15, the Max 401(k) Company Match amount is calculated by multiplying the employee's Current Salary by the value in cell T4. Use structured references to the Years Service and Current Salary fields and an absolute reference to cell T4 in the formula.
d. If the employee's Job Status field value is not equal to FT, the formula should return a value of 0.
The formula should automatically fill into the range O2:O101. (Hint: If the formula does not fill into that range, copy the formula from cell O2 to the range O3:O101.)
20. In cell P2, enter a formula using the VLOOKUP function to determine the cost of each employee's medical plan as described below:
21. In cell Q2, nest the existing formula in the cell into an IFERROR function. The IFERROR formula should display the message Invalid Plan Code if the HLOOKUP function returns an error value.
The updated formula should automatically fill into the range Q2:Q101. (Hint: If the formula does not fill into that range, copy the formula from cell Q2 to the range Q3:Q101.)
22. Switch to the WorkforcePivot worksheet. Apply the Pivot Style Medium 2 PivotTable style to the PivotTable.
23. In the WorkforcePivot table, modify the Years Service field (displaying as Sum of Years Service) in the VALUES area as described below:
24. Add the Pay Grade field to the FILTERS area of the PivotTable. Filter the table to only display records where the Pay Grade field value equals B.
25. Add a slicer to the PivotTable based on the Gender field as described below:
. Resize and reposition the Gender slicer so that the top-left corner of the slicer appears in cell E3 and the bottom-right corner appears in cell F9.
a. Using the slicer, filter the table to only display records with a Gender field value equal to F.
26. Switch to the RegistrationEntry worksheet and add a Data Validation rule to cell B9 as described below:
27. Switch to the Visual Basic Editor and edit the RegistrationEntry macro so that the Range("B3:B10").Select command will select the range B3:B11. Switch to the RegistrationRecord worksheet and run the macro to confirm that the macro copied a record with a value in the Registration Date field value into the table. (Hint: The shortcut to run this macro is ctrl+k.)
28. Switch back to viewing the RegistrationEntry worksheet. Add a macro button to the worksheet in the range A12:B13 that is linked to the RegistrationEntry macro in the worksheet. Change the name of the button to Record Registration.
29. Protect the RegistrationEntry worksheet without a password. Do not change any of the default protection options for the worksheet.
Your workbook should look like the Final Figures on the following pages. Save your changes, close the workbook, and exit Excel. Follow the directions on the SAM website to submit your completed project.