. the Documentation sheet. (2 Points) 1. IfAndor and Lookup Sheet (up to 35 Points, 5 for a, 10 for b, and 8 for c, 5 for nested lookups in and 7 ford) (Covers Learning Objectives 2, 6, 7a and 7b) Name the variables and tables that you will be using on this worksheet. Be sure that these names do not conflict with other names in this workbook. b. Use information from the Employee Information table to input the employee's base hourly pay in Column C. Determine the employees' Gross pay in Column D. If an employee works 40 or less hours per week, their gross pay is "Hours***Wage". If they work more than 40 hours per week, their gross pay is 40*Wage plus 1.5*Wage* (Hours - 40). If they work more than 40 hours per week, and their base wage is greater than $17/hour, then their gross pay is 40*Wage plus 2.0*Wage"(Hours - 40). c. There are three levels of Health Plans available. Create formulas that will show the correct Health Plan costs in column D for each employee. If an employee were to change their Health Plan, your formula should automatically adjust to show the correct Health Insurance. There are a couple of ways to do this, including modifying the employee information table and (preferable, and for 5 extra points) nested lookups. d. Be sure that the formulas are copied to the bottom of the table. Format all cells correctly and appropriately. Show a weekly total (in Column F) for each individual and all individuals combined (Cell F28). The individual total will take the Gross Wage and subtract the Health Insurance cost. Protect your spreadsheet so that you can only enter Hours information in column B. 1 D Joe's Fish Shop Weekly Payroll Information 2 3 4 Today's Date S 6 7 1/6/21 Premier $165 Standard $11 Basic Employee Information Gross Monit Wage Insurance Total Plan Basile Basic Basic 8 Employee 9 Holloway 10 g 11 Jagat 12 Jones 13 Jankowski 14 Karls 15 Lee, 16 Lee H 17 Mankowski 18 Miller 19 Nywatt 20 Orton 21 PI 22 Ramirer 23 Sanchez 24 Shapiro 25 Thomson 26 Wu. A 27 Wu, 28 29 3.0 Hourly Hours Pay 44 39 45 42 32 40 44 42 39 39 43 39 43 40 41 40 38 40 40 Total: Employee Jankowski Nywatt Sanchez Orton Shapiro wu. I Langart Mankowski Karls Ramirez Thomson Lee, Lee, H. Wage $ 15.38 $ 15.80 $15.91 $ 16.15 $ 17.86 $ 13.91 $ 14.11 $ 1539 5 16:47 $ 17.54 $ 19.65 $ 13.05 $ 13.08 $ 13.14 $ 16.00 $ 17:37 $ 17.54 $ 18.87 $ 19.65 Basic Premier Premier Premier Premier Premier Premier Standard Standard Standard Standard Standard Standard Standard Standard - PI Wu, A Jones Miller Holloway . the Documentation sheet. (2 Points) 1. IfAndor and Lookup Sheet (up to 35 Points, 5 for a, 10 for b, and 8 for c, 5 for nested lookups in and 7 ford) (Covers Learning Objectives 2, 6, 7a and 7b) Name the variables and tables that you will be using on this worksheet. Be sure that these names do not conflict with other names in this workbook. b. Use information from the Employee Information table to input the employee's base hourly pay in Column C. Determine the employees' Gross pay in Column D. If an employee works 40 or less hours per week, their gross pay is "Hours***Wage". If they work more than 40 hours per week, their gross pay is 40*Wage plus 1.5*Wage* (Hours - 40). If they work more than 40 hours per week, and their base wage is greater than $17/hour, then their gross pay is 40*Wage plus 2.0*Wage"(Hours - 40). c. There are three levels of Health Plans available. Create formulas that will show the correct Health Plan costs in column D for each employee. If an employee were to change their Health Plan, your formula should automatically adjust to show the correct Health Insurance. There are a couple of ways to do this, including modifying the employee information table and (preferable, and for 5 extra points) nested lookups. d. Be sure that the formulas are copied to the bottom of the table. Format all cells correctly and appropriately. Show a weekly total (in Column F) for each individual and all individuals combined (Cell F28). The individual total will take the Gross Wage and subtract the Health Insurance cost. Protect your spreadsheet so that you can only enter Hours information in column B. 1 D Joe's Fish Shop Weekly Payroll Information 2 3 4 Today's Date S 6 7 1/6/21 Premier $165 Standard $11 Basic Employee Information Gross Monit Wage Insurance Total Plan Basile Basic Basic 8 Employee 9 Holloway 10 g 11 Jagat 12 Jones 13 Jankowski 14 Karls 15 Lee, 16 Lee H 17 Mankowski 18 Miller 19 Nywatt 20 Orton 21 PI 22 Ramirer 23 Sanchez 24 Shapiro 25 Thomson 26 Wu. A 27 Wu, 28 29 3.0 Hourly Hours Pay 44 39 45 42 32 40 44 42 39 39 43 39 43 40 41 40 38 40 40 Total: Employee Jankowski Nywatt Sanchez Orton Shapiro wu. I Langart Mankowski Karls Ramirez Thomson Lee, Lee, H. Wage $ 15.38 $ 15.80 $15.91 $ 16.15 $ 17.86 $ 13.91 $ 14.11 $ 1539 5 16:47 $ 17.54 $ 19.65 $ 13.05 $ 13.08 $ 13.14 $ 16.00 $ 17:37 $ 17.54 $ 18.87 $ 19.65 Basic Premier Premier Premier Premier Premier Premier Standard Standard Standard Standard Standard Standard Standard Standard - PI Wu, A Jones Miller Holloway
