Visible in C10 a. 3. Lookups: (40 points) There are two lookup tables on this sheet: Retirement Costs and Employee Health Plan Costs. Employee Retirement Contribution: Create a formula in E18 to determine the employee's retirement contribution. i. Depending upon the employee's retirement plan, an employee's contribution is the percentage given in the Retirement Costs table times the employee's Salary ii. Copy your formula from E18 to the remainder of column E. b. Employee Health Care Contribution: Create a formula in H18 to determine the employee's health care contribution. i. There are four Plan Types. ii. Each Plan can cover Employee Only, Employee and Spouse, Employee and Dependents, or Employee, Spouse & Dependents. iii. The Coverage is coded as a 1, 2, 3, or 4. iv. Copy your formula from H18 to the remainder of column H. Insert Table Chart Text Doc Shape Media HAndor Data Table Lookup Macro Chart MultiSheet Point Possible: 40 March January 424 379 April February 253 385 385 312 283 268 May 473 455 June 499 405 Retirement Costs Employee Health Plan Cost Pian Retirement Plan Minimal Contribution Benefit Percent of Salan 1.42016 3.140% 4.970% Employee Type Only Code 1 Basic $ 5 Premiers 9505 Standards 550S HMO $ $ Employee Employee & Employee Spouse, a Spouse Dependents Dependents Code 2 Code 3 Code 4 1,000 $ 800S 1,600 1,8005 1,500 2.700 1,4005 1.100 2.100 8005 5 1,400 Employee Health Care Contribution Date Hired 3/1/06 1/2/98 1/8/09 6/2/03 5/1/04 2/1/06 Employee Abbot Anderson Baker Clausen Czechik Dawes Dearbom Ders Eagleton Evans Fawkes Freeborn Gregson Hadyn Holloway ley Jaggart Jankowski Jones Karts Code 1 2 3 3 1 2 2 4 1 2 1 4 1 2 4/1/10 4/1/10 5/2/07 12/1/04 8/1/98 8/1/00 7/1/02 9/1/00 5/7/10 10/1/08 11/2/99 5/7/07 1/1/03 6/1/00 44104 9/7/09 11/10 12/1/00 3/2/03 1/1/04 5/7/07 12/2/02 17/11/02 1/1/98 4/7/03 17/02 Type Retire sent Plan Health Contbution Plan Premier Standard Standard HIMO Standard Basic Premier Standard Standard Premier Premier HUO Premier Standard Standard Standard Premier Premier Standard Premier Standard HMO HMO Retirement Salary Plan 550.000 Minimal $65.000 Benefit $22,500 Minimal $41.000 Contribution $43.000 Benefit $21.500 Contribution $81.000 Minimal $39.000 Benefit $29.000 Contribution $41500 Benefit $49.500 Benefit $61.500 Contribution $65.000 Minimal $32.500 Benefit $38.000 Benefit 544, 500 Contribution $41500 Benefit $71,500 contribution $11.500 Contribution 555,500 Benefit $17.500 Contribution $31.500 Minimal $44,500 Benefit 542200 MM $29.500 Contribution 534,500 Benefit $39.000 Benefit $47.500 Minimal $61.500 contribution 532.500 Contribution 559.500 Minna 547.500 Benefit 569.500 Contribution 4 4 4 2 4 1 Mankowiki Mit Standard Orton P2 Ramirez 2 4 2 2 Standard Prender Premier 4 1 Sheiro Thomson WIA wit Back Standard 2 4