TA2: Preparation assignment for Decision Support
The goal of this assignment is to prepare you for completing the next tutorial assignment (#3) on pivot tables and decision support as assigned by your instructor.
This assignment itself is fairly easy. You are essentially going to do Exercise #3 (“Filtering SecureIT data”) on pages 14 & 15 of Plug-in module T3 (“Problem Solving
Using Excel 2013”) – HOWEVER you will complete this with some required enhancements to the assignment, as specified below.
You will complete this exercise with several adjustments:
As you do parts 1 through 5, you will label each worksheet as per the specifications listed below
Two additional parts on Pivot tables have been added as an additional challenge, also to be listed in labeled worksheets
Before submission, you will arrange the worksheets in a specific sequence that has been listed below.
In completing this problem, you will need to know to reorder and rename worksheets within your spreadsheet file and how to insert and delete new worksheets. If you
feel unskilled or rusty with these skills in MS Excel, we suggest that you review Plug-in T2 and/or your course materials for MIS 100 (or just look this up online or
in the University library resource of Lynda.com).
Obviously, you should read through this Plug-in T3 carefully PRIOR TO attempting this assignment and you should work through the examples provided. To assist you in
working through the examples in the Plug-in modules, here is a link to download T3_ProblemSolving_Data, the spreadsheet file that provides the examples used for
illustration on this module.
When you are done, you will submit the spreadsheet file (containing the appropriately labeled and ordered worksheets) by uploading it to the appropriate dropbox.
Instructions:
Open up the T3_Employee_Data spreadsheet file that I have posted here. Save this spreadsheet on your computer under the following name: firstname_lastname_TA2 (where
firstname is your first name & lastname is your last name).
Turn to problem #3 (“Filtering SecureIT data”) on pages 14 & 15 of plug-in module T3.
First do part 1. Do this by copying the data from the source worksheet (“employee data”) into a new worksheet that you will label “Sort”. Perform the required data
sorting operation on the list as specified in part 1 of this exercise (i.e. sorted by last name and hire date). After you have done this, rename the worksheet “Sort”.
Next do part 2. Do this by copying the data from the source worksheet (“employee data”) into a new worksheet that you will label “custom sort”. Perform the required
data sorting operation on the list as specified in part 2 of this exercise (i.e. using the custom list sorting parameter of Marketing, Human Resources, Management, and
Engineering). After you have done this, rename the worksheet “Custom Sort”.
Next do part 3. Do this by copying the data from the source worksheet (“employee data”) into a new worksheet that you will label “filter”. Perform the required
filtering operation on the list as specified in part 3 of this exercise (i.e. using a filter to display only those employees in the Engineering department with a
clearance of Top Secret). After you have done this, rename the worksheet “Filter”.
Next do part 4. Do this by copying the data from the source worksheet (“employee data”) into a new worksheet that you will label “custom filter”. Perform the required
filtering operation on the list as specified in part 3 of this exercise (i.e. using a custom display only those employees born between 1960 & 1969, inclusive [HINT:
Think of >= and <=], as well as when the dates when a year begins & ends] ). After you have done this, rename the worksheet “Custom Filter”.
Next do part 5. You will create a pivot table which totals salaries by department. When creating this Pivot table, choose the option to insert the pivot table into
into a new worksheet. After you have created an appropriate pivot table, name the worksheet “SumSalary”.
PART 6 (ASSIGNMENT ADDITION #1): Create a Pivot table that tabulates the number of employees by department & gender. Name the worksheet for this “Employees by
DeptGender”. HINT: What data element do you need to COUNT in order to generate this pivot table? It would obviously have to be a data element whose value was
different (unique, like a database table key) for every row in the table in order to insure an appropriate tabulation of employees by department). After you have
created this pivot table, name the worksheet “Employees by DeptGender”.
PART 7 (ASSIGNMENT ADDITION #2): Finally, go back to the worksheet named “Employees by DeptGender”. Find the cell that displays the number of female employees in the
management department. Click on this to reveal the audit trail of raw data (employee records) that was used in calculating this tally. Use conditional formatting to
display in red (or brown) the hire dates of those female employees in management who were hired after 1/1/1993. Rename the worksheet upon which this audit trail has
appeared “Data: Fs in Mgmt”.
Finally, you must re-order your worksheets in the following sequence and titles (and eliminate any extra work sheets that are not needed!):
Employee Data (i.e. the raw data you started with)
Sort
Custom Sort
Filter
Custom Filter
SumSalary
Employees by DeptGender
Data: Fs in Mgmt
Save your work (probably a good idea after completing each step)!
