Answered step by step
Verified Expert Solution
Question
1 Approved Answer
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
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
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access with AI-Powered Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started