Problem:
When employees at a company retire, they are given cash bonuses. These cash bonuses are based on the length of employment and the retiree's age. To receive a bonus, an employee must be at least 50 years of age and have worked for the company for 5 years. The following table summarizes criteria for determining bonuses:
Length of Employment............Bonus
<5 years..................................No bonus
5-10 years..............................20 percent of current annual salary
11-15 years............................30 percent of current annual salary
16-20 years............................40 percent of current annual salary
21-25 years............................50 percent of current annual salary
26 or more years...................100 percent of current annual salary
Using this information, build an Excel spreadsheet that calculates the correct bonus when given the required information.
-Set up spreadsheet so that users select the appropriate length of employment category from the above list.
-Set up spreadsheet so that the user cannot edit any values except the input fields.
Then use the "Scenario Manager" in Excel to set up the following What If Analyses:
Name........................Age .......Length of Service........Salary
John Smith................59...................12....................$120,000
G.I. Jane...................42...................20....................$40,000
Beth Goodfellow........ 51....................3.....................$97,000
Stephen Graham...... 58...................28....................$105,000
Kelsey Mattews......... 67....................6.....................$78,000
The spreadsheet should include, on a separate worksheet, the Scenario Summary for these scenarios.