146 91 OMB MB 9MB Home Insert Draw Page Layout Formulas Data Review View Automate Help Editing Calibri 11 B 4 Av General 40 00 R20 3 XVfx A B C D E F G H I J K L M N 1 2 1. In R24:R26 and S24:S26 put functions that will calculate the correct values based row and column headings and from the data in C13:0113. 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 4 have Years Service greater than 3. Otherwise put a 0 in the cell. Use apprpriate absolute references. 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 6 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 values in column M are updated automatically. Use ablsolute references where appropriate. 7 8 9 10 4. The company has three options for health care coverage (listed in U24:U25). The employee selection is in column J. Based on the 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). Use absolute references as appropriate. 11 A sample of the output is shown to the right. + 12 13 Emp ID Last Name 14 1082 Steyerl 15 1087 Weaver 16 1097 Schwegler Hire Date Birth Date Sex Location 1/13/2012 5/12/1969 M 9/12/2011 9/24/1986 F 10/17/2006 3/20/1967 M Austin Austin New Orleans CN 17 1111 O'Donnell 12/29/2008 4/4/1953 F Nashville 2222 CN CN Job Pay Annual Status Grade Salary Plan 3 $ 23,500 PPOI 3 $ 66,000 None Health Years Heal Service Age 401(k) Bonus Cost 1 44 1 26 3 $ 90,000 PPOF 6 46 CN 3 $ 55,000 None 4 60 18 1033 Mccorkle 6/12/2006 1/30/1942 F Nashville FT 1 $ 78,500 PPOI 7 60 DI Instructions Problem 1 Problem 2 Problem 3 + Calculation Mode: Automatic Workbook Statistics OME