Paste B I U av Av Merge & Center $ v % ) J21 x fx B D E F F G H 1 ) K L M March April 4 5 6 7 8 January 424 379 February 253 385 283 385 312 May 473 455 June 499 405 268 Retirement Costs 9 Retirement Plan Plan Type 10 11 12 Minimal Contribution Benefit Percent of Salary 1.420% 3.140% 4.970% Employee Health Plan Cost Employee, Employee Employee & Employee & Spouse, & Only Spouse Dependents Dependents Code 1 Code 2 Code 3 Code 4 $ 1,000 $ 800 $ 1,600 950 $ 1,800 $ 1,500 $ 2,700 550 $ 1,400 $ 1,100 $ 2,100 $ 800 $ 650 $ 1,400 Basic $ Premier $ $ Standard $ HMO $ 13 14 15 16 Retirement Plan Contribution Employee Health Care Contribution Code 1 2 3 3 Type Health Plan Premier Standard Standard HMO Standard Basic Premier Standard Standard Premier Premier 1 17 Employee 18 Abbot 19 Anderson 20 Baker 21 Clausen 22 Czechiski 23 Dawes 24 Dearborn 25 Derks 26 Eagleton 27 Evans 28 Fawkes 29 Freeborn 30 Gregson 31 Hadyn 32 Holloway 33 Igu 2 2 4 1 2 1 Date Hired 3/1/06 1/2/98 1/8/09 6/2/03 5/1/04 2/1/06 5/1/93 4/1/10 4/1/10 5/2/07 12/1/04 8/1/98 8/1/00 7/1/02 9/1/09 5/1/10 10/1/08 11/2/99 5/2/07 1/1/03 6/1/06 4/1/04 9/1/09 8/1/08 12/1/09 3/2/03 3/1/04 5/2/07 12/2/03 7/1/02 1/1/98 4/2/03 8/2/07 34 Jaggart Salary Retirement Plan $50,000 Minimal $65,000 Benefit $22,500 Minimal $41,000 Contribution $43,000 Benefit $21,500 Contribution 210 $81,000 Minimal $39,000 Benefit $39,000 Contribution $41,500 Benefit $49,500 Benefit $61,500 Contribution $65,000 Minimal $52,500 Benefit $38,000 Benefit $44,500 Contribution $41,500 Benefit $71,500 Contribution $41,500 Contribution $55,500 Benefit $37,500 Contribution $31,500 Minimal $44,500 Benefit $42,500 Minimal $29,500 Contribution $34,500 Benefit $39,000 Benefit $42,500 Minimal $61,500 Contribution $52,500 Contribution $59,500 Minimal $42,500 Benefit $69,500 Contribution 4 1 2 3 4 4 4 35 Jankowski 36 Jones 37 Karls 38 Lee, H. 39 Lee, J. 40 Mankowski 41 Miller 3 4 1 1 3 HMO Premier Standard Standard Standard Premier Premier Standard Premier Standard HMO HMO Basic Standard Standard Standard Premier Premier Premier Basic Standard Premier 3 4 42 Nywatt 43 Orton 44 Pi 45 Ramirez 46 Sanchez 47 Shapiro 48 Thomson 49 Wu, A. 50 Wu, T. 2 2 3 4 1 1 3 4 3 There are two lookup tables on this sheet: Retirement Costs and Employee Health Plan Costs. a. 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