Bus299 Assignment:

• West Coast Sales (WCS) Database

1. Each employee at WCS has an email address which is always their L..e@wcs.com. Using text functions, put each employee's email address in column "Email Address". For example, for employee # 1011, it should be G..l@wcs.com.

2. Calculate "Age" and "Years in Service" for each employee on Database worksheet using correct Excel formula. Format with one decimal place.

3. In column "Birthday Today?", your message should be "Happy Birthday!!" if today is an employee's birthday. Otherwise, message should be "Wait".

14. We want to find out the personnel who are in top 15% (indicated by Tops!) and lowest 15% salary range (to be indicated by "Lows", otherwise "Middle" in the column under Top/Low). (Use IF function with Percentile function).

• To be answered on the worksheet entitled "QUERIES" using appropriate Excel functions and/or formulas. There is no partial credit for incorrect usage.

4. Calculate the minimum salary of all employees. (Show the record of employee who is earning the minimum salary. This result should be in row A67 onwards in Database worksheet. Hint: Use Advanced filter)

5. How many employees are working at WCS (hint: how many records are there in this database)?

6. What is the average salary of 35 years or older employees and who are from either copier or printer division?

7. What is the maximum salary for employee over the age of 30 hired after 1/1/2000?

8. What percentage of the people are earning above $60,000? (Hint: Use a database function to find the count of personnel earning above $60000. Then calculate the percentage).

9. What is average salary of Copier division personnel who have been with company for at least 10 years but less than 15 years?

10. What is standard deviation of the salary of Copier division personnel who have been with company for at least 10 years but less than 15 years?

11. How many employees are in departments with department name starting with the letter A earning between $30,000 & $50,000?

12. How many employees are there with last name starting with letters Go?

13. Find the percent rank of the salary (use column F) for employee Ellen Smith? Describe what this percentile rank means for this company? HINT: Make use of DGET function.

15. On Worksheet "LOOKUP", do the following:

• Create a drop-down menu for the employee's last name referring to the Database worksheet.
• Based on an employee's last name, look up their first name, department, and data of birth. Hint: Use VLOOKUP functions.

16. On Worksheet Dashboard Template Creation:

1. Convert the Database worksheet to Table with the name "EmpSalary".
2. refer to the "Dashboard Template" sheet, create three pivot tables and charts (Salary Range by Department, Salary Average by Division, and Salary Average by Department).
3. Copy three pivot charts to a new sheet "Dashboard" and create two slicers as shown in the "Dashboard Template".

Note: You need to use the results of #14 question as salary range. For the slicers, you need to connect them to all three charts.

AutoSave OFF buca SampleExam-2 Home Insert Draw Page Layout Formulas Data Review View Tell me Calibri (Body) 10 v LG ~ = = PIL! Date ab Wrap Text SIL IT Paste BIU Av V +1 V Merge & Center Insert $ % ) V V .00 >0 .00 Conditional Format Cell Formatting as Table Styles M11 X fx A B B D E F G H I J K L M N 0 P Q O R S T Q1 Q2 Q3 Q14 Q2 Years in Service Email Address Age Birthday today? Top/Low BUS 299 Sample Exam 2 3 West Coast Sales (WCS) Employee Database This database has records of all their employees 6 First 7 Emp ID Last Name Name Department Division Salary 8 1011 Gorton Hazel Accounting Copier $57,597.85 9 1012 Preston Liza Engineering Printer $63,394.15 10 1041 Tercan Roberta Rand D D Printer $68,043.68 11 1054 Smith Howard Art Copier $35,176.06 12 1055 Albert Maxine Marketing Copier $46,040.56 13 1075 Kane Sheryl Art Printer $23,239.44 14 1152 Henders Mark Accounting Printer $46,646.20 15 1153 Plant Allen Accounting Printer $48,043.68 16 1167 Berwick Sam Marketing Copier $51,913.88 17 1168 Asonte Toni Engineering Fax $93,035.88 18 1169 Dorfberg Jeremy Engineering Copier ########## 19 1290 Cooper Linda Admin. Fax $36,113.86 20 1299 Simpson Sandrae Engineering Copier $34,854.20 21 1300 Richards Phillip Accounting Printer $60,451.68 22 1301 Sindole Randy Marketing Fax $47,081.04 23 1310 Smith Ellen Engineering Fax $72,410.85 24 1311 Cane Nate Marketing Printer $43,486.95 25 1329 Guanuo Tuome Engineering Printer $85 410.85 26 1330 Selznick Anna Accounting Copier $31,539.24 27 1331 Cash Mary Engineering Printer $49,872.15 $ 28 1333 Szcznyck Tadeuz Engineering Copier $59,362.20 29 1334 Kaneko Midori Rand D Fax $78,043.68 30 1426 Lampstone Pete Marketing Fax $64,689.00 31 1516 Bell Tom Accounting Printer $28,549.50 32 151 Quan Karen Engineering Printer $75,589.60 33 1529 Kellerman Tommie Engineering Printer $65,146.68 34 1530 Stewart lain Admin. Printer $35,146.68 35 1531 Lempert Alexandra Rand D Copier $41,053.48 36 1556 Davison Karen Engineering Printer $79,362.20 37 1557 Bates Lisa Admin. Copier $37,081.04 38 1558 Sargent Evelyn Marketing Copier $41,987.40 39 1657 Wells Rose Accounting Printer $42,626.80 40 1674 Boughton Frank Accounting Fax $47,597.85 41 1675 Melendez Jaime Accounting Fax $59,045.24 42 1675 Miller Janet Marketing Copier $53,301.44 Start Date 11/4/19 1/26/16 4/12/12 4/16/09 4/8/09 8/7/06 11/18/11 1/13/09 4/18/11 4/10/09 11/4/18 1/3/18 12/21/05 12/13/13 8/6/08 10/4/16 9/26/02 2/22/11 4/12/09 5/11/09 4/16/15 4/8/08 9/8/15 3/6/04 2/26/09 1/28/07 1/20/11 5/11/16 11/26/06 8/18/09 10/16/05 12/6/02 3/25/06 3/17/16 10/26/08 Birth Date 11/4/94 12/2/84 9/21/65 8/9/87 8/20/67 8/28/79 10/23/71 2/20/83 9/28/70 7/8/70 7/12/78 10/1/96 9/16/85 9/24/65 6/25/61 11/4/85 11/18/54 5/6/64 9/23/68 5/21/64 10/7/65 10/18/65 9/23/58 6/26/63 7/7/63 9/10/71 1/15/67 1/19/67 9/23/87 3/22/95 9/26/62 10/10/83 1/17/80 1/28/64 9/1/691 Database Queries Lookup Dashboard Template + Ready AutoSave OFF buca SampleExam-2 Home Insert Draw Page Layout Formulas Data Review View Tell me MS Sans Serif v LG 10 ~ Wrap Text General PIL DAV = = = E Paste BI V Av 2 Merge & Center V $ % ) V .00 .00 >0 Conditional Format Formatting as Table S S54 - X fx I J K L M N 0 P P Q R A B D E 1329 Neeran Tome Engineering Pime 34 1530 Stewart lain Admin. Printer 35 1531 Lempert Alexandra Rand D Copier 36 1556 Davison Karen Engineering Printer 37 1557 Bates Lisa Admin. Copier 38 1558 Sargent Evelyn Marketing Copier 39 1657 Wells Rose Accounting Printer 40 1674 Boughton Frank Accounting Fax 41 1675 Melendez Jaime Accounting Fax 42 1675 Miller Janet Marketing Copier 43 1676 Wells Jason Admin Copier 44 1677 Levine Eric Rand D Fax 45 1723 Alstain Isolde Engineering Fax 46 124 Chu Steven Marketing Printer 47 1792 Barton Eileen Art Copier 48 1793 Able Aaron Admin. Fax 49 1794 Goldberg Malcolm Marketing Fax SO 1814 Al-Sabah Daoud Engineering Fax 51 1816 Lin Michael Engineering Fax 52 1931 Mueller Ursula Accounting Copier 53 1960 Fontaine Jean Accounting Copier 54 1961 Mueller Kris Admin. Fax 55 1966 Corwick Rob Art Fax 56 1967 Aruda Felice Admin Copier 57 1968 Martinez Sara Marketing Copier 58 1975 Franklin Larry Accounting Copier 59 1976 Petry Robin Engineering Printer 60 1977 Maguire Mollie Art Copier 61 1978 Silverberg Jay Engineering Fax 62 63 First 64 Emp ID Last Name Name Department Division 65 66 67 First 68 Emp ID Last Name Name Department Division 69 1075 Kane Sheryl Art Printer 70 71 72 73 74 75 F F 305,140.00 $35,146.68 $41,053.48 $79,362.20 $37,081.04 $41,987.40 $42,626.80 $47,597.85 $59,045.24 $53,301.44 $33,212.32 $87,895.52 $69,362.20 $48,043.68 $48,859.25 $44,179.50 $57 488.75 $51,303.60 $55,480.40 $36,101.44 $48,043.68 $37,081.04 $39,239.44 $33,212.32 $55,989.20 $41,887.95 $63,035.88 $30,013,62 $51,878.84 G G 17267UT 1/20/11 5/11/16 11/26/06 8/18/09 10/16/05 12/6/02 3/25/06 3/17/16 10/26/08 10/18/11 11/6/07 8/6/08 7/29/17 12/24/07 12/16/13 2/18/18 3/4/20 7/8/19 6/20/19 11/13/12 11/5/20 11/17/11 11/9/11 1/1/13 5/7/17 4/9/21 6/13/20 1/5/18 H 971073 1/15/67 1/19/67 9/23/87 3/22/95 9/26/62 10/10/83 1/17/80 1/28/64 9/1/69 9/12/82 9/16/89 9/23/65 5/21/65 10/3/68 10/14/76 10/18/86 9/28/69 9/1/89 5/28/86 3/22/65 4/2/95 2/18/69 5/15/69 5/19/69 9/28/70 10/9/99 9/21/81 9/26/87 Years in Service Start Date Birth Date Email Address Age Birthday today? Top/Low Salary $23.239 Years in Service Email Address Age Salary $23,239.44 Start Date 8/7/06 Birth Date 8/28/79 Birthday today? Top/Low Database Queries Lookup Dashboard Template + Ready A B B C D E F G I J K L M N 0 P Q R S U V W 1 Question # 2 3 94 = Minimum salary 4 5 Emp ID Last Name First Name Department Division Email Address Q5 Start Date Salary Company Name Birthday today? Birth Date Age Years In Service Top/Low New Salary Gor ... Domain Name 6 7 8 9 10 Q6 11 12 13 14 15 16 Q7 Emp ID Last Name First Name Department Division Salary Start Date Birth Date Address Age Years In Service today? Top/Low New Salary Gor Domain Name Name 17 18 19 20 21 22 23 Q8 24 25 26 27 28 29 30 31 32 33 34 35 36 09 37 Q10 38 Database Queries Lookup Dashboard Template + Ready 3 100% M8 fx A B D E F H I J K L M Q9 35 Q10 37 38 39 Q11 Q12 013 Database Queries Lookup Dashboard Template + Ready A B B C D D E F G H I J K L M N 0 P O R S 2 015: 3 4 5 Drop Down Menu in cell ES 6 Last Name of Employee First Name of Employee Department 7 7 Date of Birth Hint: Cells E6:E8 would require use of VLOOKUP function to look up various pieces of information for the employee whose last name is in cell E5. 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 Database Queries Lookup Dashboard Template + Ready A B C D D E E F G G H I J K L L M N O P Q R S 2 016: 3 4 5 6 7 8 Salary Range by Department Division Copier Rand D Engineering Rand D Fax Marketing Printer Middle Department Engineering Art Admin. Accounting Engineering Art Admin. Accounting Accounting Admin Art 8 10 12 Engineering Numbers of Employees Marketing Rand D 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 2 34 2 35 ac 36 37 38 39 40 Salary Average by Division Salary Average by Age Group $70,000.00 60-70 $52K $60,000.00 50-60 $56K $50,000.00 $ $40,000.00 $ a 40-50 $54K $30,000.00 1 30-40 $57K 20-30 $20,000.00 $10,000.00 $49K $0.00 $44K $46K $48K $50K $52K $54K $56K $58K $ $ Thousands Copier Fax Printer Database Queries Lookup Dashboard Template + Ready AutoSave OFF buca SampleExam-2 Home Insert Draw Page Layout Formulas Data Review View Tell me Calibri (Body) 10 v LG ~ = = PIL! Date ab Wrap Text SIL IT Paste BIU Av V +1 V Merge & Center Insert $ % ) V V .00 >0 .00 Conditional Format Cell Formatting as Table Styles M11 X fx A B B D E F G H I J K L M N 0 P Q O R S T Q1 Q2 Q3 Q14 Q2 Years in Service Email Address Age Birthday today? Top/Low BUS 299 Sample Exam 2 3 West Coast Sales (WCS) Employee Database This database has records of all their employees 6 First 7 Emp ID Last Name Name Department Division Salary 8 1011 Gorton Hazel Accounting Copier $57,597.85 9 1012 Preston Liza Engineering Printer $63,394.15 10 1041 Tercan Roberta Rand D D Printer $68,043.68 11 1054 Smith Howard Art Copier $35,176.06 12 1055 Albert Maxine Marketing Copier $46,040.56 13 1075 Kane Sheryl Art Printer $23,239.44 14 1152 Henders Mark Accounting Printer $46,646.20 15 1153 Plant Allen Accounting Printer $48,043.68 16 1167 Berwick Sam Marketing Copier $51,913.88 17 1168 Asonte Toni Engineering Fax $93,035.88 18 1169 Dorfberg Jeremy Engineering Copier ########## 19 1290 Cooper Linda Admin. Fax $36,113.86 20 1299 Simpson Sandrae Engineering Copier $34,854.20 21 1300 Richards Phillip Accounting Printer $60,451.68 22 1301 Sindole Randy Marketing Fax $47,081.04 23 1310 Smith Ellen Engineering Fax $72,410.85 24 1311 Cane Nate Marketing Printer $43,486.95 25 1329 Guanuo Tuome Engineering Printer $85 410.85 26 1330 Selznick Anna Accounting Copier $31,539.24 27 1331 Cash Mary Engineering Printer $49,872.15 $ 28 1333 Szcznyck Tadeuz Engineering Copier $59,362.20 29 1334 Kaneko Midori Rand D Fax $78,043.68 30 1426 Lampstone Pete Marketing Fax $64,689.00 31 1516 Bell Tom Accounting Printer $28,549.50 32 151 Quan Karen Engineering Printer $75,589.60 33 1529 Kellerman Tommie Engineering Printer $65,146.68 34 1530 Stewart lain Admin. Printer $35,146.68 35 1531 Lempert Alexandra Rand D Copier $41,053.48 36 1556 Davison Karen Engineering Printer $79,362.20 37 1557 Bates Lisa Admin. Copier $37,081.04 38 1558 Sargent Evelyn Marketing Copier $41,987.40 39 1657 Wells Rose Accounting Printer $42,626.80 40 1674 Boughton Frank Accounting Fax $47,597.85 41 1675 Melendez Jaime Accounting Fax $59,045.24 42 1675 Miller Janet Marketing Copier $53,301.44 Start Date 11/4/19 1/26/16 4/12/12 4/16/09 4/8/09 8/7/06 11/18/11 1/13/09 4/18/11 4/10/09 11/4/18 1/3/18 12/21/05 12/13/13 8/6/08 10/4/16 9/26/02 2/22/11 4/12/09 5/11/09 4/16/15 4/8/08 9/8/15 3/6/04 2/26/09 1/28/07 1/20/11 5/11/16 11/26/06 8/18/09 10/16/05 12/6/02 3/25/06 3/17/16 10/26/08 Birth Date 11/4/94 12/2/84 9/21/65 8/9/87 8/20/67 8/28/79 10/23/71 2/20/83 9/28/70 7/8/70 7/12/78 10/1/96 9/16/85 9/24/65 6/25/61 11/4/85 11/18/54 5/6/64 9/23/68 5/21/64 10/7/65 10/18/65 9/23/58 6/26/63 7/7/63 9/10/71 1/15/67 1/19/67 9/23/87 3/22/95 9/26/62 10/10/83 1/17/80 1/28/64 9/1/691 Database Queries Lookup Dashboard Template + Ready AutoSave OFF buca SampleExam-2 Home Insert Draw Page Layout Formulas Data Review View Tell me MS Sans Serif v LG 10 ~ Wrap Text General PIL DAV = = = E Paste BI V Av 2 Merge & Center V $ % ) V .00 .00 >0 Conditional Format Formatting as Table S S54 - X fx I J K L M N 0 P P Q R A B D E 1329 Neeran Tome Engineering Pime 34 1530 Stewart lain Admin. Printer 35 1531 Lempert Alexandra Rand D Copier 36 1556 Davison Karen Engineering Printer 37 1557 Bates Lisa Admin. Copier 38 1558 Sargent Evelyn Marketing Copier 39 1657 Wells Rose Accounting Printer 40 1674 Boughton Frank Accounting Fax 41 1675 Melendez Jaime Accounting Fax 42 1675 Miller Janet Marketing Copier 43 1676 Wells Jason Admin Copier 44 1677 Levine Eric Rand D Fax 45 1723 Alstain Isolde Engineering Fax 46 124 Chu Steven Marketing Printer 47 1792 Barton Eileen Art Copier 48 1793 Able Aaron Admin. Fax 49 1794 Goldberg Malcolm Marketing Fax SO 1814 Al-Sabah Daoud Engineering Fax 51 1816 Lin Michael Engineering Fax 52 1931 Mueller Ursula Accounting Copier 53 1960 Fontaine Jean Accounting Copier 54 1961 Mueller Kris Admin. Fax 55 1966 Corwick Rob Art Fax 56 1967 Aruda Felice Admin Copier 57 1968 Martinez Sara Marketing Copier 58 1975 Franklin Larry Accounting Copier 59 1976 Petry Robin Engineering Printer 60 1977 Maguire Mollie Art Copier 61 1978 Silverberg Jay Engineering Fax 62 63 First 64 Emp ID Last Name Name Department Division 65 66 67 First 68 Emp ID Last Name Name Department Division 69 1075 Kane Sheryl Art Printer 70 71 72 73 74 75 F F 305,140.00 $35,146.68 $41,053.48 $79,362.20 $37,081.04 $41,987.40 $42,626.80 $47,597.85 $59,045.24 $53,301.44 $33,212.32 $87,895.52 $69,362.20 $48,043.68 $48,859.25 $44,179.50 $57 488.75 $51,303.60 $55,480.40 $36,101.44 $48,043.68 $37,081.04 $39,239.44 $33,212.32 $55,989.20 $41,887.95 $63,035.88 $30,013,62 $51,878.84 G G 17267UT 1/20/11 5/11/16 11/26/06 8/18/09 10/16/05 12/6/02 3/25/06 3/17/16 10/26/08 10/18/11 11/6/07 8/6/08 7/29/17 12/24/07 12/16/13 2/18/18 3/4/20 7/8/19 6/20/19 11/13/12 11/5/20 11/17/11 11/9/11 1/1/13 5/7/17 4/9/21 6/13/20 1/5/18 H 971073 1/15/67 1/19/67 9/23/87 3/22/95 9/26/62 10/10/83 1/17/80 1/28/64 9/1/69 9/12/82 9/16/89 9/23/65 5/21/65 10/3/68 10/14/76 10/18/86 9/28/69 9/1/89 5/28/86 3/22/65 4/2/95 2/18/69 5/15/69 5/19/69 9/28/70 10/9/99 9/21/81 9/26/87 Years in Service Start Date Birth Date Email Address Age Birthday today? Top/Low Salary $23.239 Years in Service Email Address Age Salary $23,239.44 Start Date 8/7/06 Birth Date 8/28/79 Birthday today? Top/Low Database Queries Lookup Dashboard Template + Ready A B B C D E F G I J K L M N 0 P Q R S U V W 1 Question # 2 3 94 = Minimum salary 4 5 Emp ID Last Name First Name Department Division Email Address Q5 Start Date Salary Company Name Birthday today? Birth Date Age Years In Service Top/Low New Salary Gor ... Domain Name 6 7 8 9 10 Q6 11 12 13 14 15 16 Q7 Emp ID Last Name First Name Department Division Salary Start Date Birth Date Address Age Years In Service today? Top/Low New Salary Gor Domain Name Name 17 18 19 20 21 22 23 Q8 24 25 26 27 28 29 30 31 32 33 34 35 36 09 37 Q10 38 Database Queries Lookup Dashboard Template + Ready 3 100% M8 fx A B D E F H I J K L M Q9 35 Q10 37 38 39 Q11 Q12 013 Database Queries Lookup Dashboard Template + Ready A B B C D D E F G H I J K L M N 0 P O R S 2 015: 3 4 5 Drop Down Menu in cell ES 6 Last Name of Employee First Name of Employee Department 7 7 Date of Birth Hint: Cells E6:E8 would require use of VLOOKUP function to look up various pieces of information for the employee whose last name is in cell E5. 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 Database Queries Lookup Dashboard Template + Ready A B C D D E E F G G H I J K L L M N O P Q R S 2 016: 3 4 5 6 7 8 Salary Range by Department Division Copier Rand D Engineering Rand D Fax Marketing Printer Middle Department Engineering Art Admin. Accounting Engineering Art Admin. Accounting Accounting Admin Art 8 10 12 Engineering Numbers of Employees Marketing Rand D 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 2 34 2 35 ac 36 37 38 39 40 Salary Average by Division Salary Average by Age Group $70,000.00 60-70 $52K $60,000.00 50-60 $56K $50,000.00 $ $40,000.00 $ a 40-50 $54K $30,000.00 1 30-40 $57K 20-30 $20,000.00 $10,000.00 $49K $0.00 $44K $46K $48K $50K $52K $54K $56K $58K $ $ Thousands Copier Fax Printer Database Queries Lookup Dashboard Template + Ready