Salaries for employees are determined by the number of years they have been employed. In cell G10 create a VLOOKUP that will enter a salary of $55,000 for those with a seniority of 20 or more $50,000 for those with at least 15 years but less than 20 years of seniority; $45,000 for those with seniority of 10 years but less than 15; and $35,000 for those with less than 10 years of seniority.

SPRING FINAL 2015 - ACCT/BCIS 3301 ? Page 4

Employee worksheet - continued (Be sure to save before continuing this section)

Start your lookup table in cell G1. Copy this down through row 31. Range name your table Employees.

Longevity bonus is given to certain employees and begins after an employee has completed 5 years of employment. Be careful reading this next question ? be sure to map it out correctly on your scratch paper. In cell H10 create a NESTED IF statement that will enter a longevity amount based on a percentage of the employee?s salary and number of years as an employee. An employee?s longevity percentage begins at 5 years with 4% of their salary and increases 4% every 5 years until an employee reaches 25 years ? then 20% longevity is given. No additional longevity increases are given after 25 years. Copy this IF down through row 31.

Starting in cell A40, create a One Variable Data Table that will show the average salaries, the total salaries, the maximum salary and the average longevity for each department. Remember what data table you use with more than one formula. Start the criteria in A36. (Three database functions/formulas are needed and may be repeated.) Format all salaries as accounting with 0 decimals, longevity as currency with 2 decimals and departments as general format.

Sort the table Dept (ascending) and Gender within Dept (ascending). Starting in cell A51, create a Two Variable Data Table that will show the total longevity for men in each of the five departments; the total longevity for the women in each of the five departments; and the total longevity for all employees in each of the five departments. Remember how many variables you are dealing with to determine what type of data table. Start the criteria in A47. (One database function is needed.) Format all longevity as currency with no decimals and departments as general.

I a FINAL-SPRING2015 (A ge Layout Formulas General H 2 O AutoSum r 2 A I Format Painter = = a merge & Center - $ % $ 4.0 .0g Conditional Format Cell Insert Delete Format Filtery Select Alignment A1 fe Christopher Phillips 9 Employee Gender Age Dept Trade Seniority Salaries Longevity 10 Jean, Maureen 11 Jordon, Jess 12 Snow, Ali 13 Jennings, Carl 14 Hill, Judy 15 BeatSharon 16 Pylersky, Alex 17 Lendow, Jodi 18 Peat, Jeff 19 Sanchez, Juan 20 Chun, Li 21 Sweet, Susie 22 Montie, Carol 23 Floures, Linda 24 Green, Pete 25 Brown, Clint 26 Martin, Mike 27 Smith, John 28 Jordon, Mary 29 Petts, Jeff 30 Sparks, Mike 31 Steep Stan 32 M M M F F M F M M M F F F M M M F F M M M 48 10 Operator 4810 Oiler 3510 Oiler 4220 Operator 22 20 Operator 4520 Operator 32 30 Oiler 32 30 Machinist 2530 Machinist 35 30 Oiler 2140 Oiler 3940 Operator 3040 Machinist 2140 Operator 2140 Operator 4740 Operator 2550 Operator 4650 Operator 4550 Oiler 2250 Machinist 4550 Machinist 7 3 15 12 1 23 13 5 7 15 10 10 7 2 1 25 6 21 19 2 15 II | | | | I I I I I I I I I I I I K , , ITBreak Even 7 Total Sales Tax Returns Chart Data vehicle 7 Scenario Summary Employees to D I I Ready ED W 100%