SecureWorks, Inc., is a small computer security contractor that provides computer security analysis, design, and software implementation for the U.S. government and commercial clients. Leslie Mamalis is SecureWorks' human resources (HR) manager. Leslie has asked you to assist with a number of functions (she has provided you with a copy of her trusted personnel data file, AYK9_Data.xlsx): 1. Copy the original data worksheet to a new worksheet called Sort. Sort the employee list in ascending order by department, then by the last name, then by the first name. 2. Copy the original data worksheet to a new worksheet called Autofilter. Using the Autofilter feature, create a custom filter that will display employees whose birth date is greater than or equal to 1/1/1965 and less than or equal to 12/31/1975. 3. Copy the original data worksheet to a new worksheet called Subtotal. Using the subtotal feature, create a sum of the salary for each department. 4. Copy the original data worksheet to a new worksheet called Formatting. Using the salary column, change the font color to red if the cell value is greater than or equal to 55000. You must use the conditional formatting feature to complete this step. M N 0 P R T U Apply Your Knowledge Project 9 - Security Analysis Output boxes in yellow Glven data in blue Answers in red AYK 9 Security Analysis A B D E G H I J L M N N 0 Q RS S T U VW : 3 ID D. First Name Last Name Department Title Salary Hire Date Birth Date Gender Clearance 4 1008 Kevin Grundies Marketing CSR 538.900.00 13124788 03/04/61 M S C 5 1016 Oscar Gomez Marketing CSR S 43,500.00 02/16/90 S 04/29/67 M N 6 1032 Hiles Fintsteel Marketing Stall $ 34,500.00 03/21/90 08:22:53 F N 7 7 1042 Mare Andretti Marketing CER S $ 42,500.00 03/21/90 08:20/70 F N 8 1119 Sharad Maniu Eminering Security $ 45,800.00 10/13/90 02/04/89 M S I 9 1178 Rohorta Kurwall Management Manager Sli,n0n on 05/15,92 12122/58 F S 10 355 Barbara Grabowski Management Manager S $ 75,700.00 103192 04/10185 ) F C 11 1441 James Van Ham Management MANAGAR S 66.5on on 12/18.92|| S 66,500 0203153 M M. S 12 1614 Artie Lambros Marketing CSR $ 41,000.00 16.93 07/13/70 M N 13 1851 Melinda English Management Manager S 56.400.00 10/01/9 02/14158 F TS 14 2002 Edgar Rothrock Engineering Security Engror S 53,300.00 02/08/94 03/2017 M 15 2042 Hilary Qushner Stal S 92,000.00 08:28.94 05/08/61 F TS 16 2105 Luca Pasoll Marketing Stalt S42,300.00 OH/2010 //9 05/06/70 M S 17 2122 Yuan Chang IEritrain Security Engineer $ 46,640.00 $ 46,540.00 09/0519 03/04/14 M TS 18 2344 Nemanha Mehta Engineering Security Engineer S $ 65,000 on 10/01/9 02/12165 F S 19 2451 Francine Detweiler Engineering 10/10/95 04/29770 F SODE 856.703 03 20 2678 Koshi Yamamoto Engineering Security Engineer S 49,600.00 11/15/95 01124163 F S 21 2733 John Zumkowski Engineering Staff S $ 33,000.00 04/10/98 12/12/72 M TS 22 2788 PM Leonard Engineering Security Engineer S 59 HOD OD 12113/96 07102/70 F TS 23 2896 Samuel Gates Engineering Seoutty Engneer S 66,300.00 01/20/97 04/10174 M 24 2956 Michael Engineering Seely Emineer S 51,000.00 0411197 03/07163 M TS 25 3015 Patti Stanesiter Engineering Security Engineer S 14,300.00 07/06/98 03/10/66 F S 28 3043 Joseph Palermo Marketing Stall $ 32,000.00 07/08 08.98 03/10/68 M N 27 3117 Frederich Bednarczyk Engineering Security Engineer S 56,700.00 05/14/9 05127/70 M 20 3210 Carmen Ortega Maling Engineering Soy Engineer $ 46,000.00 OBYTU 0918.99 03/28152 F 29 3314 Svetlana Kartashy Himan Resources Stan $ 38,00n on 11/05/01 05/031821F N 30 4000 Alice Rovik Resor S 302.00 12/01.01 01/29758 M 31 32 GOR 34 35 361 37 SA 39 40 41 42 43 44 45 AYK 9 Security Analysis + SecureWorks, Inc., is a small computer security contractor that provides computer security analysis, design, and software implementation for the U.S. government and commercial clients. Leslie Mamalis is SecureWorks' human resources (HR) manager. Leslie has asked you to assist with a number of functions (she has provided you with a copy of her trusted personnel data file, AYK9_Data.xlsx): 1. Copy the original data worksheet to a new worksheet called Sort. Sort the employee list in ascending order by department, then by the last name, then by the first name. 2. Copy the original data worksheet to a new worksheet called Autofilter. Using the Autofilter feature, create a custom filter that will display employees whose birth date is greater than or equal to 1/1/1965 and less than or equal to 12/31/1975. 3. Copy the original data worksheet to a new worksheet called Subtotal. Using the subtotal feature, create a sum of the salary for each department. 4. Copy the original data worksheet to a new worksheet called Formatting. Using the salary column, change the font color to red if the cell value is greater than or equal to 55000. You must use the conditional formatting feature to complete this step. M N 0 P R T U Apply Your Knowledge Project 9 - Security Analysis Output boxes in yellow Glven data in blue Answers in red AYK 9 Security Analysis A B D E G H I J L M N N 0 Q RS S T U VW : 3 ID D. First Name Last Name Department Title Salary Hire Date Birth Date Gender Clearance 4 1008 Kevin Grundies Marketing CSR 538.900.00 13124788 03/04/61 M S C 5 1016 Oscar Gomez Marketing CSR S 43,500.00 02/16/90 S 04/29/67 M N 6 1032 Hiles Fintsteel Marketing Stall $ 34,500.00 03/21/90 08:22:53 F N 7 7 1042 Mare Andretti Marketing CER S $ 42,500.00 03/21/90 08:20/70 F N 8 1119 Sharad Maniu Eminering Security $ 45,800.00 10/13/90 02/04/89 M S I 9 1178 Rohorta Kurwall Management Manager Sli,n0n on 05/15,92 12122/58 F S 10 355 Barbara Grabowski Management Manager S $ 75,700.00 103192 04/10185 ) F C 11 1441 James Van Ham Management MANAGAR S 66.5on on 12/18.92|| S 66,500 0203153 M M. S 12 1614 Artie Lambros Marketing CSR $ 41,000.00 16.93 07/13/70 M N 13 1851 Melinda English Management Manager S 56.400.00 10/01/9 02/14158 F TS 14 2002 Edgar Rothrock Engineering Security Engror S 53,300.00 02/08/94 03/2017 M 15 2042 Hilary Qushner Stal S 92,000.00 08:28.94 05/08/61 F TS 16 2105 Luca Pasoll Marketing Stalt S42,300.00 OH/2010 //9 05/06/70 M S 17 2122 Yuan Chang IEritrain Security Engineer $ 46,640.00 $ 46,540.00 09/0519 03/04/14 M TS 18 2344 Nemanha Mehta Engineering Security Engineer S $ 65,000 on 10/01/9 02/12165 F S 19 2451 Francine Detweiler Engineering 10/10/95 04/29770 F SODE 856.703 03 20 2678 Koshi Yamamoto Engineering Security Engineer S 49,600.00 11/15/95 01124163 F S 21 2733 John Zumkowski Engineering Staff S $ 33,000.00 04/10/98 12/12/72 M TS 22 2788 PM Leonard Engineering Security Engineer S 59 HOD OD 12113/96 07102/70 F TS 23 2896 Samuel Gates Engineering Seoutty Engneer S 66,300.00 01/20/97 04/10174 M 24 2956 Michael Engineering Seely Emineer S 51,000.00 0411197 03/07163 M TS 25 3015 Patti Stanesiter Engineering Security Engineer S 14,300.00 07/06/98 03/10/66 F S 28 3043 Joseph Palermo Marketing Stall $ 32,000.00 07/08 08.98 03/10/68 M N 27 3117 Frederich Bednarczyk Engineering Security Engineer S 56,700.00 05/14/9 05127/70 M 20 3210 Carmen Ortega Maling Engineering Soy Engineer $ 46,000.00 OBYTU 0918.99 03/28152 F 29 3314 Svetlana Kartashy Himan Resources Stan $ 38,00n on 11/05/01 05/031821F N 30 4000 Alice Rovik Resor S 302.00 12/01.01 01/29758 M 31 32 GOR 34 35 361 37 SA 39 40 41 42 43 44 45 AYK 9 Security Analysis +