Answered step by step
Verified Expert Solution
Question
1 Approved Answer
The Human Resources Director for Social Dynamics wants you to calculate annual bonuses based on length of service. She also recommends that the company pay
- The Human Resources Director for Social Dynamics wants you to calculate annual bonuses based on length of service. She also recommends that the company pay managers at least $80,000 per year and would like you to calculate which managers should be getting pay raises. In addition, at the top of the worksheet she wants to be able to enter an employ number, and a search criteria of one of the columns, and have it return the information into the Quick Search results box.
- Use a drop down list to select the search criteria
- Use nested functions to calculate bonuses and raise status.
- Use nested Index and Match functions for the search results box
- Use conditional formatting to highlight managers due for a raise.
Employee ID | Criteria: | Title | Quick Search Results: | Bonus Amount | Date | Percent | |||
Hired before | 1/1/10 | 8.0% | |||||||
Manager Salary Threshold | $ 80,000.00 | Hired on or before | 1/1/15 | 5.0% | |||||
Hired after | 1/1/20 | 1.5% | |||||||
Whatever Sales Rep Salary Information | |||||||||
Employee ID | Name | Location | Title | Hire Date | Salary | Bonus Amount | Raise Status | ||
3824 | Adams | Atlanta | Manager | 10/13/07 | $ 78,750 | ||||
4955 | Akmatalieva | Chicago | Account Rep | 11/2/21 | $ 49,575 | ||||
2521 | Barnes | Boston | Account Rep | 6/13/14 | $ 46,000 | ||||
4453 | Crandell | Boston | Manager | 3/8/09 | $ 75,800 | ||||
2967 | Deberard | Chicago | Account Rep | 12/3/14 | $ 46,795 | ||||
2645 | Doering | Atlanta | Account Rep | 6/4/14 | $ 43,750 | ||||
1268 | Forgan | Atlanta | Account Rep | 9/2/15 | $ 45,250 | ||||
4458 | Franklin | Atlanta | Account Rep | 7/18/14 | $ 47,240 | ||||
1370 | Garbett | Atlanta | Account Rep | 9/17/20 | $ 47,835 | ||||
2848 | Gomez | Boston | Account Rep | 10/13/16 | $ 46,725 | ||||
3996 | Hartvigsen | Boston | Account Rep | 2/17/15 | $ 45,000 | ||||
4070 | Keone | Chicago | Account Rep | 4/2/15 | $ 45,125 | ||||
3099 | Laing | Chicago | Manager | 1/16/11 | $ 75,500 | ||||
2698 | Lenz | Atlanta | Account Rep | 4/13/12 | $ 49,750 | ||||
2611 | Nitz | Chicago | Account Rep | 8/14/17 | $ 41,000 | ||||
1256 | Peterson | Atlanta | Account Rep | 11/3/15 | $ 45,100 | ||||
2009 | Scholfield | Atlanta | Account Rep | 7/12/16 | $ 39,750 | ||||
4428 | Selinger | Boston | Account Rep | 11/6/16 | $ 41,525 | ||||
4545 | Terriquez | Chicago | Account Rep | 4/13/12 | $ 49,750 | ||||
1281 | Unice | Chicago | Account Rep | 1/30/14 | $ 43,750 | ||||
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored 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