Question

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 Home→Editing→Sort & Filter →Sort A to Z.

13. Choose Data→Outline→Subtotal .

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.

Answer 1

X Cut Copy - 11 - A B . 22 A Font -- FE BIU E Wrap Test Merge & Center - Alignment General $ % Number 9.9 Conditional Format Formatting as Table Styles Cell Styles Insert Clipboard A B C D E F G H I J K L M Contribution Range Rate O to 249 10% 250 to 499 500 to 749 30% 750 to 999 40% 1000 and up 50% Kids for Change Employee Retirement Savings Contributions 20% Company Amt Rate 02 03 04 100 2000 1000 0 300 250 175 200 200 200 0 0 0 300 Employee Department 01 Craig McInnis Fundraising James Piercey Admin P Janice Burke Marsha Drew Fundraising Ruth Bowers Admin Shannon Woodhouse HR Tiffany Spencer Fundraising Carla Joyce James Jones Fundraising Ray Peters Admin Charles Oakley Admin Gaza Durr þ Sheila Coffield Fundraising Annual Total 0 $300 100 $200 250 $975 200 $800 150 $150 300 $600 250 $1,100 100 $700 180 $720 200 $850 65 $260 160 $560 425 $1,370 250 250 HR 150 200 180 180 300 180 350 65 120 290 HR 65 160 380 120 275

In relation to above facts and figures the following working method has been provided in the question.

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.

Solution as per above method :-

Home Insert Page Layout Formulas Data Review 2 Connections g 21 Clear Reappy Advanced From From 31 Sort Filter From From Other Text Sources Get External Data Existing Connections Refresh All Edit tinkt Connections Test to Remove Data Consolidate What Columns Duplicates Validation Analysis Data Tools Group Ungroup Subtotal - Outline Sort & Filter C D E F G H N Kids for Change Employee Retirement Savines Contributions K L M Contribution Range Rate 249 10% 250 499 20% 500 749 30% 750 999 40% 1000 and up 50% Annual 02 03 Rate Company Amt 10% 10% 65 Employee Departme01 James Piercey Admin Ruth Bowers Admin lo Ray Peters Admin 1 Charles Oakley Admin 1.2 Craig Minnis Fundraising 13 Marsha Drew Fundraising 14 Tiffany Spencer Fundraising S James Jones Fundraising 6 Sheila Coffield Fundraising 17 Janice Burke 18 Shannon Woodhouse HR 19 Carla Joyce ko Gaza Durr Total 100 $200 150 $150 200 $850 65 $260 0 $300 200 $800 250 $1,100 65 200 200 04 0 0 350 65 0 200 350 180 290 175 300 150 120 200 20% 20% 40% 50% 30% 250 180 275 300 40% 4 25 2 50 300 100 160 0 200 160 $1,370 5975 $600 9700 $560 250 120 30% 30%

Next part of the Question :-

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.

Solution :-

Insert Page Layout Tomus osta Se connections 21 A properties om From ! Sort Filter Reapply Advanced From From Other Sources Get External Data - Existing Refresh Connections All Catalin Connections fo=G20"H20 SE Text to Remove Data Consolidate What Columns Duplicates Validation Analysis Data Tools Group Ungroup Subtot, Outline 120 N O Kids for Change Employee Retirement Savings Contributions K L M Contribution Range Rate 0 249 10% 250 499 20% 500 749 30% 750 999 40% 1000 and up 50% 02 03 Rate Company Amt 2016 $20 17 Employee Departme1 18 James Piercey Admin 19 Ruth Bowers Admin 10 Ray Peters Admin 11 Charles Oakley Admin 12 Craig McInnis Fundraising 113 Marsha Drew Fundraising 14 Tiffany Spencer Fundraising 15 James Jones Fundraising 16 Sheila Coffield Fundraising 17 Janice Burke 18 Shannon Woodhouse HR 19 Carla Joyce 20 Gaza Durr Annual 04 Total 0 100 $200 0 150 $150 350200 $850 65 $260 0 0 $300 200 200 $800 350 250 $1,100 180 $720 425 $1,370 175 250 $975 300 300 $600 150 100 $700 120 160 $500 0 3000 65 65 100 200 200 200 250 180 275 380 300 250 0 250 200 120 160 10% 40% 20% 20% 40% 50% $15 $340 $52 $60 $320 $550 $216 $685 $390 $180 $210 S168 180 180 30% 290 HR 50% 40% 30% 30% 30%

Next part of the Question :-

12. Choose Home→Editing→Sort & Filter  →Sort A to Z.

13. Choose Data→Outline→Subtotal  .

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.

Solution :-

BO - ME 11 FE From the a The g Connect Employee Retirement savings Core James Percy 0 00 200 31 Charles Oakley Fundraising . 15 Tiffany Spencer Fundraising Fundraising

Lastly,

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.

Solution :-

at Page Layout de Detal From Web e g Commen es 11 Sort Text to Remove and cons Cone Wh From Port G e From Sour Date Duta on A re to SED Contribution Range Rate 249 Eids for Change Retirement Servings Contributions 500 749 ON 1000 and up Company A 0 Total Rose Am G Deparn i 0 Admin Average Fundraising Average 5366 Grand Average AARRRRRRRR