M 9 M OM OM File Home Insert Draw Page Layout Formulas Data Review View Automate Help Editing V 60 00 Calibri 11 v B HOA ab General R20 X fx A B ? D E F G H 1 J K L M 1 1. In R24:R26 and 524:526 put functions that will calculate the correct values based row and column headings and from the data in C13:0113. 2 2. In M14:M113 put a forumua that calcualtes a match by multiplying R19 and the employees annual salary if they are Job Status FT and 3 have Years Service greater than 3. Otherwise put a 0 in the cell. Use apprpriate absolute references. 4 3. In N14:N113 put a function that will assign a bonus to the employees based on the years of service in column K and the criteria 5 and amount of bonus in the range Q13:R17. For the output reference the values in range R14:R17 so that if the values change the 6 values in column M are updated automatically. Use ablsolute references where appropriate. 7 4. The company has three options for health care coverage (listed in U24:U25). The employee selection is in column). Based on the 8 monthly payment in V24:V25 put the annual Health Cost in 014:0113. (Hint: if they have None or PPOI the cost is V24, otherwise it is V25). 9 Use absolute references as appropriate. 10 + 11 A sample of the output is shown to the right. 12 Heal Cost Bonus Job Pay Annual Health Years 13 Emp ID Last Name Hire Date Birth Date Sex Location Status Grade Salary Plan Service Age 401(k) 14 1082 Steyerl 1/13/2012 5/12/1969 M Austin 3 $ 23,500 PPOI 1 44 15 1087 Weaver 9/12/2011 9/24/1986 F Austin 3 $ 66,000 None 1 26 16 1097 Schwegler 10/17/2006 3/20/1967 M New Orleans CN 3 $ 90,000 PPOF 6 46 17 1111 O'Donnell 12/29/2008 4/4/1953 F Nashville 3 $ 55,000 None 4 60 18 1033 Mccorkle 6/12/2006 1/30/1942 F Nashville FT 1 $ 78,500 PPOI 7 60 Instructions Problem 1 Problem 2 Problem 3 + 3 3 3 3 Calculation Mode: Automatic Workbook Statistics M 9 M OM OM File Home Insert Draw Page Layout Formulas Data Review View Automate Help Editing V 60 00 Calibri 11 v B HOA ab General R20 X fx A B ? D E F G H 1 J K L M 1 1. In R24:R26 and 524:526 put functions that will calculate the correct values based row and column headings and from the data in C13:0113. 2 2. In M14:M113 put a forumua that calcualtes a match by multiplying R19 and the employees annual salary if they are Job Status FT and 3 have Years Service greater than 3. Otherwise put a 0 in the cell. Use apprpriate absolute references. 4 3. In N14:N113 put a function that will assign a bonus to the employees based on the years of service in column K and the criteria 5 and amount of bonus in the range Q13:R17. For the output reference the values in range R14:R17 so that if the values change the 6 values in column M are updated automatically. Use ablsolute references where appropriate. 7 4. The company has three options for health care coverage (listed in U24:U25). The employee selection is in column). Based on the 8 monthly payment in V24:V25 put the annual Health Cost in 014:0113. (Hint: if they have None or PPOI the cost is V24, otherwise it is V25). 9 Use absolute references as appropriate. 10 + 11 A sample of the output is shown to the right. 12 Heal Cost Bonus Job Pay Annual Health Years 13 Emp ID Last Name Hire Date Birth Date Sex Location Status Grade Salary Plan Service Age 401(k) 14 1082 Steyerl 1/13/2012 5/12/1969 M Austin 3 $ 23,500 PPOI 1 44 15 1087 Weaver 9/12/2011 9/24/1986 F Austin 3 $ 66,000 None 1 26 16 1097 Schwegler 10/17/2006 3/20/1967 M New Orleans CN 3 $ 90,000 PPOF 6 46 17 1111 O'Donnell 12/29/2008 4/4/1953 F Nashville 3 $ 55,000 None 4 60 18 1033 Mccorkle 6/12/2006 1/30/1942 F Nashville FT 1 $ 78,500 PPOI 7 60 Instructions Problem 1 Problem 2 Problem 3 + 3 3 3 3 Calculation Mode: Automatic Workbook Statistics
