In H7:H26 enter a formula to put in the correct bonus amount. The amount is based on the employees locations, salary and the % in H2:H4. If the employee located in Atlanta they receive a bonus of H2*salary, Chicago H3*salary, otherwise H4*salary. The formula in H7:H26 must work if the salary, location or values in H2:H4 are changed.

1 Bonuses Percent 2 Last Day of Year 3 Chicago Other Locati 4 5 Transpagne Sales Rep Salare Information 6 Employee II Name Location Title Hire Date Salar 7 3824 Adams Atlanta 10/14/2007 $ 68,750 Manager Account Rep 4955 11/3/2018 $ 68,750 Akmataliev Chicago Barnes 9 2521 Boston Account Rep 6/14/2014 $ 68,750 10 4453 Crandell Boston Manager 3/9/2009 13 $ 68,750 11 2967 Deberard Chicago Account Rep 12/4/2014 7 $ 68,750 12 2645 Doering Atlanta 6/5/2014 Supervisor 8 $ 68,750 13 1268 Forgan Atlanta Account Rep 9/3/2015 6 $ 68,750 14 4458 Franklin Atlanta Account Rep 7/19/2014 8 $ 68,750 15 1370 Garbett Atlanta Account Rep 9/18/2014 7 $ 68,750 16 2848 Gomez Boston Account Rep 10/15/2016 5 $ 68,750 17 3996 Hartvigsen Boston Account Rep 2/18/2015 7 $ 68,750 18 4070 Keone Chicago Account Rep 4/3/2015 7 $ 68,750 19 3099 Laing Chicago $ 68,750 1/17/2011 4/15/2012 11 10 $ 68,750 20 2698 Lenz Atlanta 21 2611 Nitz Chicago Manager Account Rep Supervisor Account Rep Account Rep $ 68,750 4 6 22 1256 Peterson Atlanta $ 68,750 23 2009 Scholfield 4428 Selinger 6 $ 68,750 8/15/2017 11/4/2015 7/14/2016 11/8/2016 4/15/2012 Atlanta Boston Chicago 24 5 $ 68,750 Supervisor Account Rep 25 4545 Terriquez $ 68,750 26 1281 Unice Chicago Account Rep 1/31/2014 8 $ 68,750 $1,375,000 0 27 0 28 29 On this worksheet all functions/formulas must use absolute references where appropriate for full credit. 30 31 In range F7:F26 put a formula that will calculate the number of years an employee has worked. Use 364 days in a year when converting the number of days 32 to the years the value in F2 as the current date for the number of years employed calculation. 33 34 In H7:H26 enter a formula to put in the correct bonus amount. The amount is based on the employees locations, salary and the % in H2:H4. 35 If the employee located in Atlanta they receive a bonus of H2"salary, Chicago H3*salary, otherwise H4*salary. 36 The formula in H7:H26 must work if the salary, location or values in H2:H4 are changed. 37 12/31/2021 Atlanta Years 14 3 8 10 10.00% 7.00% 5.00% Bonus Raise Raises Manageri Account Percent 5% 3% 1 Bonuses Percent 2 Last Day of Year 3 Chicago Other Locati 4 5 Transpagne Sales Rep Salare Information 6 Employee II Name Location Title Hire Date Salar 7 3824 Adams Atlanta 10/14/2007 $ 68,750 Manager Account Rep 4955 11/3/2018 $ 68,750 Akmataliev Chicago Barnes 9 2521 Boston Account Rep 6/14/2014 $ 68,750 10 4453 Crandell Boston Manager 3/9/2009 13 $ 68,750 11 2967 Deberard Chicago Account Rep 12/4/2014 7 $ 68,750 12 2645 Doering Atlanta 6/5/2014 Supervisor 8 $ 68,750 13 1268 Forgan Atlanta Account Rep 9/3/2015 6 $ 68,750 14 4458 Franklin Atlanta Account Rep 7/19/2014 8 $ 68,750 15 1370 Garbett Atlanta Account Rep 9/18/2014 7 $ 68,750 16 2848 Gomez Boston Account Rep 10/15/2016 5 $ 68,750 17 3996 Hartvigsen Boston Account Rep 2/18/2015 7 $ 68,750 18 4070 Keone Chicago Account Rep 4/3/2015 7 $ 68,750 19 3099 Laing Chicago $ 68,750 1/17/2011 4/15/2012 11 10 $ 68,750 20 2698 Lenz Atlanta 21 2611 Nitz Chicago Manager Account Rep Supervisor Account Rep Account Rep $ 68,750 4 6 22 1256 Peterson Atlanta $ 68,750 23 2009 Scholfield 4428 Selinger 6 $ 68,750 8/15/2017 11/4/2015 7/14/2016 11/8/2016 4/15/2012 Atlanta Boston Chicago 24 5 $ 68,750 Supervisor Account Rep 25 4545 Terriquez $ 68,750 26 1281 Unice Chicago Account Rep 1/31/2014 8 $ 68,750 $1,375,000 0 27 0 28 29 On this worksheet all functions/formulas must use absolute references where appropriate for full credit. 30 31 In range F7:F26 put a formula that will calculate the number of years an employee has worked. Use 364 days in a year when converting the number of days 32 to the years the value in F2 as the current date for the number of years employed calculation. 33 34 In H7:H26 enter a formula to put in the correct bonus amount. The amount is based on the employees locations, salary and the % in H2:H4. 35 If the employee located in Atlanta they receive a bonus of H2"salary, Chicago H3*salary, otherwise H4*salary. 36 The formula in H7:H26 must work if the salary, location or values in H2:H4 are changed. 37 12/31/2021 Atlanta Years 14 3 8 10 10.00% 7.00% 5.00% Bonus Raise Raises Manageri Account Percent 5% 3%