Use if or vlookup statements as needed.

A B D E F G H K K L M N 0 Q R S S T 35 Employee Name Location Health Insurance Status Health Insurance Provider Employee Employee Employer Monthly Annual Annual Deduction Deduction Cost Total Premium 36 37 #N/A FR F #N/A S S F | SR F 38 39 40 41 42 43 44 45 46 47 S #N/A S F Brown, Zac Cain, Rebekah Daane, Devin Daniels, Calista Dobberpuhl, Michelle Durst, Krystle El Hannaty, Mohammed Foss, Rachel Fries, Dolores Fuller, Holly Gilmore, Correna Gonzalez, Javiera Hajcak, Tami Hall, Lila Hanneman, Jason Helt, Judith Johnson, Ashlea Lopez, Joanna LaCrosse Madison Green Bay Milwaukee Madison Green Bay Milwaukee LaCrosse Green Bay Milwaukee LaCrosse Milwaukee LaCrosse Milwaukee Madison Green Bay Green Bay Milwaukee GHC GHC BC/BS DeanCare GHC BC/BS BC/BS UW Health BC/BS GHC UW Health DeanCare BC/BS DeanCare DeanCare GHC BC/BS UW Health F #N/A #N/A $38.00 #N/A #N/A #N/A #N/A $106.00 #N/A #N/A #N/A #N/A #N/A $38.00 #N/A #N/A FR 48 S F F SR 49 50 51 52 53 54 SR S SR N o Q R S T A B Formula Bar D E F G | H. K L M 12 Use Vlookup and IF statements, as needed, to complete the yellow section below. Add totals to appropriate columns. 13 14 15 Name: Daniels, Calista 16 17 18 19 20 Employee Monthly Health Insurance Premiums 21 Group BC/BS GHC DeanCare UW Health 22 F $106.00 $78.40 $71.50 $80.00 This chart represents the employee portion per month for 23 FR 75.00 55.00 52.00 health insurance. You will need to calculate employee and 60.50 employer annual amounts along with the total premium. 24 S 57.00 30.35 26.00 33.00 25 SR 38.00 22.00 20.00 24.00 26 27 Employee Deduction (EE%) 22% While you don't have the total premium amounts, you DO KNOW the company, on average, charges the employee 28 Employer Paid Benefit (ER%) 78% percentage shown 29 30 31 32 33 34 Insurance Budget Chart Pivot Table Data IT