2. Security Analysis (30 pts)

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. SecureWorks competes for both private and U.S. government computer security contract work by submitting detailed bids outlining the work the company will perform if awarded the contracts. Because all of the work involves computer security, a highly sensitive area, almost all of SecureWorks tasks require access to classified material or company confidential documents. Consequently, all of the security engineers (simply known as engineers within the company) have U.S. government clearances of either secret or top secret. Some have even higher clearances for the 2 percent of SecureWorks work that involves so-called black-box security work. Most of the employees also hold clearances because they must handle classified documents.

Leslie Mamalis is SecureWorks human resources (HR) manager. She maintains all employee records and is responsible for semiannual review reports, payroll processing, personnel records, recruiting data, employee training, and pension option information. At the heart of an HR system are personnel records. Personnel record maintenance includes activities such as maintaining employee records, tracking cost center data, recording and maintaining pension information, and absence and sick leave record keeping. Although most of this information resides in sophisticated database systems, Leslie maintains a basic employee worksheet for quick calculations and ad hoc report generation. Because SecureWorks is a small company, Leslie can take advantage of Excels excellent list management capabilities to satisfy many of her personnel information management needs.

Leslie has asked you to assist with a number of functions (she has provided you with a copy of her trusted personnel data file, Security Analysis worksheet in the Data.xlsx):

3. Copy the data worksheet to a new worksheet called Sort. Sort the employee list in ascending order by department, then by last name, then by first name.
4. Copy the data worksheet to a new worksheet called Subtotal. Using the subtotal feature, create a sum of the salary for each department.
5. Copy the 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.

3. Paget Integrated Solutions (40 pts)

Constance Paget is the owner of Paget Integrated Solutions, a Web technology consulting firm based in Seattle, Washington. Paget is planning a seminar on Web applications with a popular guest speaker to take place in Houston, Texas, next spring. She wants your help in generating a budget for the seminar that will incorporate the fixed, variable, and mixed costs to determine under what conditions the seminar will be profitable for her company.

1. Use formulas to find total revenue, total fixed cost, variable cost, mixed costs and balance. (Hint: Vlookup function is required to find the mixed costs)
2. Use goal seek to determine what number of attendees will result in a balance of $0.
3. Constance know that the registration fee for the seminar increases, the number of attendees willing to pay decreases. Change the number of attendees from a constant value to the following formula:

4. Use Solver to determine the registration fee that will maximize the balance value with the constraint that the registration fee should be an integer.

Paget Integrated Solutions Seminar Budget Projections Revenue Number of Attendees Registration Fee Total Revenue 100 $120 Room Rental Persons Charge $400 50 $600 1001 $750 150 $900 200 $1,000 Variable Costs Training Material Cost (per person) Goodie Bag Cost (per person) Total Variable Costs $35 $15 Meal Catering Persons. Charge (per person) $25 50 100 $17 Fixed Costs Internet Hotspot Speaker Fee Speaker Travel Speaker Lodging Miscellaneous Total Fixed Costs $50 $900 $500 $140 $350 Support Staff Persons. Charge $60 $80 $100 $120 Mixed Costs Room Rental Meal Catering Support Staff Total Mixed Costs Balance Martin Resorts Name Total Question What is the best resorts sales? What is the worst resorts sales? What is the best selling product at the best resort? What is the worst selling product at the best resort? What is the best selling product at the worst resort? What is the worst selling product at the worst resort? What is the best sales month at the best resort? What is the worst sales month at the best resort? What is the best sales month at the worst resort? What is the worst sales month at the worst resort? What is the best selling product at the best resort during the best month? What is the worst selling product at the best resort during the best month? What is the best selling product at the best resort during the worst month? What is the worst selling product at the best resort during the worst month? **Ignore any values that are 0, unknown, all resorts, or blank Paget Integrated Solutions Seminar Budget Projections Revenue Number of Attendees Registration Fee Total Revenue 100 $120 Room Rental Persons Charge $400 50 $600 1001 $750 150 $900 200 $1,000 Variable Costs Training Material Cost (per person) Goodie Bag Cost (per person) Total Variable Costs $35 $15 Meal Catering Persons. Charge (per person) $25 50 100 $17 Fixed Costs Internet Hotspot Speaker Fee Speaker Travel Speaker Lodging Miscellaneous Total Fixed Costs $50 $900 $500 $140 $350 Support Staff Persons. Charge $60 $80 $100 $120 Mixed Costs Room Rental Meal Catering Support Staff Total Mixed Costs Balance Martin Resorts Name Total Question What is the best resorts sales? What is the worst resorts sales? What is the best selling product at the best resort? What is the worst selling product at the best resort? What is the best selling product at the worst resort? What is the worst selling product at the worst resort? What is the best sales month at the best resort? What is the worst sales month at the best resort? What is the best sales month at the worst resort? What is the worst sales month at the worst resort? What is the best selling product at the best resort during the best month? What is the worst selling product at the best resort during the best month? What is the best selling product at the best resort during the worst month? What is the worst selling product at the best resort during the worst month? **Ignore any values that are 0, unknown, all resorts, or blank