80.2K

Verified Solution

Link Copied!

1) In cell 011, enter the current date using an appropriate date function. In cell G11, enter YOUR NAME. 2) In cell D14, use the YEARFRAC function to determine the total years of service for each employee. Using appropriate cell references, copy this formula down the column for all employees. Format as number, 2 decimals. 3) In cell F14, use an appropriate function to display the total overall bonus percentage based on the base bonus % data in cells B26:C29 AND an additional 2% if the employee meets the criteria outlined above (years of service of at least 3 years and a review score higher than 9). Using appropriate cell references, copy this formula for all employees. Format as a %, 0 decimals. 4) In cell 414, calculate the total bonus amount based on the bonus % in column Fand their annual salary in column G. Using appropriate cell references, copy formula for all rows. Format as accounting, o decimals. 5) In the bottom row of this table, calculate the average for each column. Ensure proper formatting for each column. Bonus Annual Review Score 18 19 Bonus % Amount 16 Employee Name Harry Ingram Gary Shouldice Henry Smith Tisha Abramson Laurie Jones Ben Jamieson Patty Guthrie Sam Batisson Hank Trenton Averages 20 Years of Service 5.91 6.79 3.70 2.65 13.60 2.50 3.09 6.95 4.36 Date Hired 2016-01-15 2015-02-28 2018-03-31 2019-04-20 2008-05-06 2019-06-12 2018-11-11 2015-01-01 2017-08-02 Annual Salary 43,000 $ 47,000 $ 42,000 $ 40,000 $ 55,000 $ 41,000 $ 42,500 $ 50,000 $ 48,000 $ $ $ $ $ $ $ $ $ $ 16 12 15 8 19 Review Score 0 10 Bonus % 0% 5% 10% 15% 16 19

Answer & Explanation Solved by verified expert

See Answer