New Perspectives Excel 2013 Tutorials 5-8: SAM Capstone Project 1a 1

Subject:MS ExcelPrice:19.87 Bought4

Share With

New Perspectives Excel 2013 Tutorials 5-8: SAM Capstone Project 1a

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 worksheets: 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:

a. Format cell A1 in the grouped worksheets using Times New Roman

font, with the 24 point font size and the bold font style.

b. Format the range B7:C10 in the grouped worksheets with the Comma

number style and 0 decimal places.

c. In the center Header section of the grouped worksheets, add a Header

and Footer Element that will display the Sheet Name of each worksheet.

Ungroup the worksheets.

 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

a. Open the file support_NP_E13_CS_T5-8_P1a_Iowa.xlsx available for

download from the SAM website.

b. In cell B7 of the Qtr4 worksheet, create a formula that is an external

reference to cell E6 in the Quarter 4 worksheet in the

support_NP_E13_CS_T5-8_P1a_Iowa.xlsx file.

c. Modify the formula in cell B7 of the Qtr4 worksheet to be a relative

reference to cell E6 in the support_NP_E13_CS_T5-8_P1a_Iowa.xlsx file.

d. Copy the formula from cell B7 in the Qtr4 worksheet to the range

B8:B9 in the Qtr4 worksheet.

e. Close the file support_NP_E13_CS_T5-8_P1a_Iowa.xlsx.

External reference to support_NP_E13_CS_T5-8_Nebraska.xlsx

a. Open the file support_NP_E13_CS_T5-8_P1a_Nebraska.xlsx available for

download from the SAM website.

b. In cell C7 of the Qtr4 worksheet, create a formula that is an external

reference to cell E6 in the Quarter 4 worksheet in the

support_NP_E13_CS_T5-8_P1a_Nebraska.xlsx file.

c. Modify the formula in cell C7 of the Qtr4 worksheet to be a relative

reference to cell E6 in the support_NP_E13_CS_T5-8_P1a_Nebraska.xlsx

file.

d. Copy the formula from cell C7 in the Qtr4 worksheet to the range

C8:C9 in the Qtr4 worksheet.

e. Close the file support_NP_E13_CS_T5-8_P1a_Nebraska.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:

a. Sort the table (in the range A1:G101) first in ascending order by the

Dept field values and then in ascending order by the Last Name field

values.

b. Convert the table into a range.

c. Insert subtotals into the range A1:G101 at each change in the Dept

field. The subtotals should use the SUM function to total the values in

the Current Salary column. The check boxes for the subtotals options

11. 

Switch to the EmployeeList worksheet and complete the following

actions:

a. 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.)

b. 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

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:

a. The hyperlink should link to cell N1 in the EmployeeBenefits

worksheet.

b. The hyperlink should have the display text Current Salary. (Hint: Do

not include the period.)

c. The hyperlink should include the ScreenTip Click to view additional

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 in the Assignment file:

a. Add the Pay Grade field and the Dept field (in that order) to the ROWS

area.

b. Add the Job Status field to the COLUMNS area.

c. Add the Current Salary field to the VALUES area. (Hint: This field’s

name will automatically be updated to display as Sum of Current Salary.)

d. Update the name of the Current Salary field (in the VALUES area of the

PivotTable) to display as Total Salaries. Update the number format of this

field to display in Accounting number format with 0 decimal places.

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:

a. In cell I102, use the AVERAGE function to determine the average of the

Current Salary column values.

b. In cell L102, use the AVERAGE function to determine the average of

the Age column values.

c. In cell M102, use the AVERAGE function to determine the average of

the Years Service column values.

d. In cell Q102, remove any calculation associated with the Vision Plan

Cost column.

Change the text in cell A102 to read Average.

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:

a. If the employee’s Job Status field value is equal to FT or an employee’s

Years Service field value is greater than 20, the formula should return

the value 1 (indicating 1 bonus week of vacation).

b. If neither of those conditions are true, the formula should return the

value 0 (indicating 0 bonus weeks of vacation).

The formula should automatically fill into the range N2:N101. (Hint: If the

formula does not fill into that range, copy the formula from cell N2 to the

range N3:N101.)

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:

a. Use a structured reference to the Medical Plan field as the

Lookup_value parameter value.

b. Use the defined name Medical_Premium (which represents the range

S8:T15) as the Table_array parameter value.

c. Use 2 as the Column_index_num parameter value.

d. Use FALSE as the Range_Lookup parameter value.

e. The entire VLOOKUP function should be multiplied by 12 (to convert

the monthly premium cost to a yearly premium cost).

The formula should automatically fill into the range P2:P101. (Hint: If the

formula does not fill into that range, copy the formula from cell P2 to the

range P3:P101.)

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:

a. Change the summary function for the field to Average.

b. Change the custom name of the field to Average Years of Service.

(Hint: If you select a summary function after defining the custom name

for this value, confirm that the custom name you defined did not change.

Do not include the period in the custom name.)

c. Change the number format of the column to display using the Number

format with 1 decimal place.

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:

a. 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.

b. Using the slicer, filter the table to only display records with a Gender

26. 

Switch to the RegistrationEntry worksheet and add a Data Validation rule

to cell B9 as described below:

a. The Data Validation rule should allow values from a List whose source

is the range G4:G6. The list should ignore blank and display as an in-cell

dropdown.

b. The Input Message title should be Select Dorm and the Input Message

should be Select a Dorm from the dropdown list. (including the period).

c. The Error Alert title should be Incorrect Dorm and the Error Message

should be Please select a valid Dorm. (including the period).

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.