You are the manager of an information technology (IT) team. Your employees go to training workshops and
Question:
You are the manager of an information technology (IT) team. Your employees go to training workshops and national conferences to keep up-to-date in the field. You created a list of expenses by category for each employee for the last six months. Now you want to subtotal the data to review total costs by employee and then create a PivotTable to look at the data from different perspectives.
Instructions:
For the purpose of grading the project you are required to perform the following tasks:
Step | Instructions | Points Possible |
---|---|---|
1 | Start Excel. Open the downloaded Excel file named exploring_e05_grader_a1_Expenses.xlsx. Save the workbook as exploring_e05_grader_a1_Expenses_LastFirst, replacing LastFirst with your own name. | 0.000 |
2 | On the Subtotals worksheet, sort the data by Employee and further sort by Category, both in alphabetical order. | 4.000 |
3 | Use the Subtotals feature to insert subtotal rows by Employee to calculate the total expense by employee. | 5.000 |
4 | Collapse the Donaldson and Hart sections to show only their totals. Leave the other employees’ individual rows displayed. | 5.000 |
5 | Use the Expenses worksheet to create a blank PivotTable on a new worksheet named Summary. Name the PivotTable Categories. | 8.000 |
6 | Use the Category and Expense fields, enabling Excel to determine where the fields go in the PivotTable. | 5.000 |
7 | Modify the Values field to determine the average expense by category. Change the custom name to Average Expense. | 4.000 |
8 | Format the Values field with Accounting number type. | 3.000 |
9 | Type Category in cell A3 and change the Grand Totals layout option to On for Rows Only. | 5.000 |
10 | Apply Pivot Style Dark 2 and display banded rows. | 5.000 |
11 | Insert a slicer for the Employee field, change the slicer height to 2 inches and apply the Slicer Style Dark 5. Move the slicer below the PivotTable. | 5.000 |
12 | Use the Expenses worksheet to create another blank PivotTable on a sheet named Totals. Add the Employee to the Rows and add the Expense field to the Values area. Sort the PivotTable from largest to smallest expense. | 10.000 |
13 | Change the name for the Expenses column to Totals and format the field with Accounting number format. | 4.000 |
14 | Insert a calculated field to subtract 2659.72 from the Expense field. Format the field with the custom name Above or Below Average and apply Accounting number format to the field. | 10.000 |
15 | Set 12.25 width for column B and column C, change the row height of row 3 to 30, and apply word wrap to cell C3. | 4.000 |
16 | Create a clustered column PivotChart from the PivotTable. Move the PivotChart to a new sheet named Chart. Hide all field buttons in the PivotChart, if necessary. | 10.000 |
17 | Add a chart title above the chart and type Expenses by Employee. Change the chart style to Style 14. | 6.000 |
18 | Apply 11 pt font size to the value axis and display vertical axis as Accounting with zero decimal places. | 4.000 |
19 | Create a footer on all worksheets with your name in the left section, the sheet name code in the center section, and the file name code in the right section. | 3.000 |
20 | Ensure that the worksheets are correctly named and placed in the following order in the workbook: Subtotals, Summary, Chart, Totals, Expenses. Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed. | 0.000 |
Total Points | 100.000 |
https://www.dropbox.com/s/m7falnl85tfazl9/Torres%20DaSilva_exploring_e05_grader_a1_Expenses.xlsx?dl=0
Basic Business Statistics
ISBN: 978-0321870025
13th edition
Authors: Mark L. Berenson, David M. Levine, Kathryn A. Szabat