• 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