When employees at Gold Software Company retire, they are given cash bonuses. The

When employees at Gold Software Company retire, they are given cash bonuses. These cash bonuses are based on the length of employment and the retiree’s age. In order to receive a bonus, an employee must be at least 50 years of age and must have worked at the company for at least 5 years. The following table summarizes the criteria for determining bonuses.
Length or emplovment
0 – 5 years
6-10 years
11-15 years
16 -20 years
21-25 years
26 or more vears
Bonus
No bonus
20% of current annual salary
30% of current annual salary
40% of current annual salary
60% of current annual salary
100% of current annual salary
Using the information provided, build a simple system using Microsoft Excel that will assist in calculating the bonuses for following employees.
Name
John Smith
AJ Singh
Mike Rogers
Jane Smith
A Kumar
J Doe
Sarah Dorman
Age
48
56
62
55
52
56
65
Years of service
15
28
18
13
8
4
28
Current Salary
65000
72500
125000
95000
85500
110000
150000
Your project work should include the following (use separate sheets in Excel for the IF and VLOOKUP functions):
Use IF function to calculate the bonuses. (40 points)
Use VLOOKUP function to calculate the bonuses. (40 points)
Design your spreadsheet to facilitate sensitivity analysis to observe the impact of changes in bonus percentage, age, and years of service. (20 points)