REINFORCE YOUR SKILLS: E8-R3

Calculate Company Matching Amount

In this exercise, you will look up the amount employees will receive into their retirement savings after Kids for Change decides to implement a tiered system to encourage employees to save more for retirement.

1. Open the E8-R3-Savings file from the Excel Chapter 8 folder and save it as E8-R3-SavingsMatched.

2. Select cell H8, if necessary.

3. Insert a VLOOKUP function.

4. For the Lookup_Value, select cell G8.

5. For the Table_Array, select the range $K$2:$M$6 and then press the F4 key to ensure it is an absolute reference for the entire range.

6. For the Col_Index_Num enter 3.

7. The Range_Lookup can be left blank.

8. Click OK to insert the function.

9. Apply the Percentage number format and then copy the formula down the column for all employees.

Now you will take the Annual Total for each employee and multiply it by the Company Amount.

10. In cell I8, enter the formula =G8*H8 and copy the formula all the way down.

Next you will sort the list and add subtotals by department.

11. Select cell B8.

12. Choose HomeEditingSort & Filter Sort A to Z.

13. Choose DataOutlineSubtotal .

14. Add a subtotal at each change in Department by using the Average function and add the subtotal to the Annual Total, Rate, and Company Amount columns.

15. Apply the Total cell style to the ranges G12:I12, G18:I18, and G23:I24.

16. Collapse the 3 groups so that only the 3 subtotal headings and the Grand Average heading are displayed.

17. Save your work and close Excel.

File Insert Draw Page Layout Formulas Data Review View Help Search Share Comments = = = 20 General A 12 D IX A - ab Wrap Text Merge & Center Paste Calibri - 11-AA BIU - - - A. Font | $ -% 82 Insert Delete Format AutoSum 42 Fill Sort & Find & Clear Filter Select Editing Conditional Format as Cell Formatting Table Styles Styles Ideas Clipboard Alignment Number Cells Ideas H8 B C D E F G H I J N O P Q R Kids for Change Employee Retirement Savings Contributions K L Contribution Range 0 to 249 250 to 499 500 to 749 750 to 999 1000 and up M Rate 10% 20% 30% 40% 50% esta Annual Rate Company Amt Employee Department 8 Craig McInnis Fundraising 9 James Piercey Admin 10 Janice Burke HR 11 Marsha Drew Fundraising 12 Ruth Bowers Admin 13 Shannon Woodhouse HR 14 Tiffany Spencer Fundraising 15 Carla Joyce HR 16 James Jones Fundraising 17 Ray Peters Admin 18 Charles Oakley Admin 19 Gaza Durr HR 20 Sheila Coffield Fundraising 21 Q1 Q2 Q3 100 2000 1000 0 300 250 175 200 200 200 0 0 0 0 300 250 350 250 200 150 180 180 180 3000 350 65 65 65 120 160 120 275 380 290 24 0 100 250 200 150 300 250 100 180 200 65 Total $300 $200 $975 $800 $150 $600 $1,100 $700 $720 $850 $260 $560 $1,370 250 160 425 2015