While most of the personnel information for SecureIT resides in database systems, a basic employee worksheet is maintained for quick calculations and ad hoc report generation

Practical Management Science
6th Edition
ISBN:9781337406659
Author:WINSTON, Wayne L.
Publisher:WINSTON, Wayne L.
Chapter2: Introduction To Spreadsheet Modeling
Section: Chapter Questions
Problem 20P: Julie James is opening a lemonade stand. She believes the fixed cost per week of running the stand...
icon
Related questions
Question
100%

 3. Filtering SecureIT Data
SecureIT, Inc., is a small computer security contractor that provides computer security
analysis, design, and software implementation for commercial clients. Almost all of SecureIT work requires access to classified material or confidential company documents. Consequently, all of the security personnel have clearances of either Secret or Top Secret. Some have even higher clearances for work that involves so-called black box security
work. While most of the personnel information for SecureIT resides in database systems, a
basic employee worksheet is maintained for quick calculations and ad hoc report generation. Because SecureIT is a small company, it can take advantage of Excel’s excellent list management facilities to satisfy many of its personnel information management needs. You have been provided with a sample worksheet, T3_Employee_Data.xls, to assist SecureIT with producing several worksheet summaries. Here is what is needed:

One worksheet that totals the salaries by department and the grand total of all department salaries. This worksheet should be sorted by department name first.

 

ID First Name Last Name
2956 Michael
4123 Steve
3117 Frederich
2122 Yuan
Department
Engineering
Goldstein
Ballmer
Bednarczyk Engineering
Engineering
Engineering
Engineering
2451 Francine
2896 Samuel
Chang
Detweiler
Gates
Leonard
Manispour
Mehta
2768 Phyllis
1119 Sharad
2344 Nemesha
Rothrock
Stonesifer
3210 Carmen
2002 Edgar
3015 Patti
2678 Koshi
2733 John
3314 Svetlana
Yamamoto Engineering
Zumkowski
Engineering
Kartashev
Rovik
4006 Alice
2042 Hillary
Cushner
1851 Melinda
English
1355 Barbara Grabowski
1173 Roberta
Kurzweil
1441 James
1042 Maria
1032 Hillary
1009 Kevin
1614 Artie
2105 Luca
1016 Oscar
Engineering
Engineering
Engineering
Engineering
Ortega-Molina Engineering
Engineering
Engineering
Van Horn
Andretti
Flintsteel
Grundies
Lambros
Pacioli
Gomez
Management
Management
Management
Management
Management
Title
Engineer
Engineer
Engineer
Engineer
Engineer
Engineer
Engineer
Marketing
Marketing
Marketing
Marketing
Marketing
Marketing
Manager
Engineer
Engineer
Engineer
Engineer
Engineer
Human Resources
Human Resources Manager
Staff
Staff
Staff
Manager
Manager
Manager
Manager
CSR
Staff
CSR
CSR
Staff
CSR
Salary
Hire Date
51,000
42,900
56,700
4/11/97
5/1/02
5/14/99
9/5/95
10/10/95
1/28/97
46,540
56,700
66,300
59,800 12/13/96
54,500 10/13/90
65,000
10/1/95
46,000
53,300
64,300
49,600
33,000
38,000
42,300
43,500
9/16/99
2/8/94
7/6/98
TS
M
M
F
Birth Date Gender Clearance
3/7/63 M
4/14/70 M
5/27/70
3/4/64
4/29/70
4/10/74 M
7/2/70 F
2/4/69 M
2/12/65 F
3/28/52
3/21/70
3/10/66
1/24/63
F
11/16/95
4/18/96 12/12/72
11/5/01
43,000
12/1/01
32,000
8/26/94
62,040
10/1/93
75,700 10/13/92
63,000
66,500
42,500
34,500
38,900
41,000
5/3/82
1/26/55
5/6/61
2/14/56
4/10/65
6/16/92 12/22/58
12/18/92
3/21/90
3/21/90
12/24/89
5/16/93
8/26/95
2/16/90
2/3/58
8/20/70
8/22/58
3/4/61
7/13/70
5/6/50
4/29/67
FFMFM
SASKSSKeses sisse PNSKROSSZZUNSN
FFMFFMMMM
M
F
F
N
TS
TS
S
S
S
TS
TS
TS
Transcribed Image Text:ID First Name Last Name 2956 Michael 4123 Steve 3117 Frederich 2122 Yuan Department Engineering Goldstein Ballmer Bednarczyk Engineering Engineering Engineering Engineering 2451 Francine 2896 Samuel Chang Detweiler Gates Leonard Manispour Mehta 2768 Phyllis 1119 Sharad 2344 Nemesha Rothrock Stonesifer 3210 Carmen 2002 Edgar 3015 Patti 2678 Koshi 2733 John 3314 Svetlana Yamamoto Engineering Zumkowski Engineering Kartashev Rovik 4006 Alice 2042 Hillary Cushner 1851 Melinda English 1355 Barbara Grabowski 1173 Roberta Kurzweil 1441 James 1042 Maria 1032 Hillary 1009 Kevin 1614 Artie 2105 Luca 1016 Oscar Engineering Engineering Engineering Engineering Ortega-Molina Engineering Engineering Engineering Van Horn Andretti Flintsteel Grundies Lambros Pacioli Gomez Management Management Management Management Management Title Engineer Engineer Engineer Engineer Engineer Engineer Engineer Marketing Marketing Marketing Marketing Marketing Marketing Manager Engineer Engineer Engineer Engineer Engineer Human Resources Human Resources Manager Staff Staff Staff Manager Manager Manager Manager CSR Staff CSR CSR Staff CSR Salary Hire Date 51,000 42,900 56,700 4/11/97 5/1/02 5/14/99 9/5/95 10/10/95 1/28/97 46,540 56,700 66,300 59,800 12/13/96 54,500 10/13/90 65,000 10/1/95 46,000 53,300 64,300 49,600 33,000 38,000 42,300 43,500 9/16/99 2/8/94 7/6/98 TS M M F Birth Date Gender Clearance 3/7/63 M 4/14/70 M 5/27/70 3/4/64 4/29/70 4/10/74 M 7/2/70 F 2/4/69 M 2/12/65 F 3/28/52 3/21/70 3/10/66 1/24/63 F 11/16/95 4/18/96 12/12/72 11/5/01 43,000 12/1/01 32,000 8/26/94 62,040 10/1/93 75,700 10/13/92 63,000 66,500 42,500 34,500 38,900 41,000 5/3/82 1/26/55 5/6/61 2/14/56 4/10/65 6/16/92 12/22/58 12/18/92 3/21/90 3/21/90 12/24/89 5/16/93 8/26/95 2/16/90 2/3/58 8/20/70 8/22/58 3/4/61 7/13/70 5/6/50 4/29/67 FFMFM SASKSSKeses sisse PNSKROSSZZUNSN FFMFFMMMM M F F N TS TS S S S TS TS TS
Expert Solution
trending now

Trending now

This is a popular solution!

steps

Step by step

Solved in 4 steps with 7 images

Blurred answer
Similar questions
Recommended textbooks for you
Practical Management Science
Practical Management Science
Operations Management
ISBN:
9781337406659
Author:
WINSTON, Wayne L.
Publisher:
Cengage,
Operations Management
Operations Management
Operations Management
ISBN:
9781259667473
Author:
William J Stevenson
Publisher:
McGraw-Hill Education
Operations and Supply Chain Management (Mcgraw-hi…
Operations and Supply Chain Management (Mcgraw-hi…
Operations Management
ISBN:
9781259666100
Author:
F. Robert Jacobs, Richard B Chase
Publisher:
McGraw-Hill Education
Business in Action
Business in Action
Operations Management
ISBN:
9780135198100
Author:
BOVEE
Publisher:
PEARSON CO
Purchasing and Supply Chain Management
Purchasing and Supply Chain Management
Operations Management
ISBN:
9781285869681
Author:
Robert M. Monczka, Robert B. Handfield, Larry C. Giunipero, James L. Patterson
Publisher:
Cengage Learning
Production and Operations Analysis, Seventh Editi…
Production and Operations Analysis, Seventh Editi…
Operations Management
ISBN:
9781478623069
Author:
Steven Nahmias, Tava Lennon Olsen
Publisher:
Waveland Press, Inc.