Chapter 5 Running Case Portfolio
danevia
Chapter5RunningCasePortfolio_Instructions.docxGrader - Instructions Excel 2019 Project
Chapter 5 Running Case Portfolio
Project Description:
New Castle County Technical Services (NCCTS) provides technical support services for a number of companies in New Castle County, Delaware. You previously created charts to depict summary data by service type, customer, and days open. You copied the charts as pictures to replace the actual charts so changes you make to the dataset will not alter the charts. Then you sorted the main dataset for analysis and prepared it to be printed on two pages. You also applied conditional formatting to highlight transactions that took more than nine days to close and displayed data bars for the amount billed to give a quick visual for transaction amounts. Finally, you filtered a copy of the dataset and converted the summary sections into tables. Next, you will create a PivotTable and PivotChart to analyze agent performance in resolving disaster recovery support calls.
Steps to Perform:
|
Step |
Instructions |
Points Possible |
|
1 |
Open e05r1NCCTS and save it as e05r1NCCTS_LastFirst |
0 |
|
2 |
Use the range A4:K39 on the March Hours worksheet and use the PivotChart command in the Charts group on the Insert tab to create a blank PivotChart and PivotTable on a new worksheet |
12 |
|
3 |
Name the worksheet Disaster Recovery Analysis |
5 |
|
4 |
Place the Call Type and Customer ID fields in Rows |
5 |
|
5 |
Place the Hours Logged, Days Open, and Amount Billed fields as Values. |
5 |
|
6 |
Set the Hours Logged and Days Open fields to Summarize by Average. Apply Number format with 2 decimal places |
8 |
|
7 |
Assign the name Summary Table to the PivotTable |
5 |
|
8 |
Apply Light Blue, Pivot Style Medium 20 to the PivotTable |
5 |
|
9 |
Insert a slicer based on Call Type. Place the top-left corner of the slicer in cell F3. Set a 3.5”height and 2.5”width for the slicer. Select the Disaster Recovery slice to filter the PivotTable |
15 |
|
10 |
Reposition the top-left corner of the PivotChart in cell A10 |
5 |
|
11 |
Change the chart type to Pie |
6 |
|
12 |
Apply Style 3 chart style |
5 |
|
13 |
Apply Accounting Number Format to the Amount Billed data. |
5 |
|
14 |
Display the March Hours worksheet and create GETPIVOTDATA functions by completing these steps: •Cell K1: Display the average Disaster Recovery hours using the Average of Hours Logged on row 4 in the PivotTable. •Cell K2: Display the total Disaster Recovery amount billed using the Sum of Amount Billed on row 4 in the PivotTable |
12 |
|
15 |
Insert a footer with your name on the left side, the sheet name in the center, and the file name code on the right side |
7 |
|
16 |
Save and close the workbook. Based on your instructor’s directions, submit e05r1NCCTS_LastFirst |
0 |
|
Total Points |
100 |
Created On: 10/22/2020 2 Chapter 5 Running Case Portfolio
