Chapter 4 Running Case Portfolio
danevia
Chapter4RunningCasePortfolio_Instructions.docxGrader - Instructions Excel 2019 Project
Chapter 4 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. Since then, you copied the charts as pictures to replace the actual charts so changes you make to the dataset will not alter the charts. However, you have a backup of the original charts in case you need to change them later. Now, you will sort the main dataset for analysis and prepare it to be printed on two pages. You will apply conditional formatting to highlight transactions that took more than 9 days to close and display data bars for the amount billed to give a quick visual for transaction amounts. You will also filter a copy of the dataset to set filters to further analyze the transactions. Finally, you will convert the summary sections into tables.
Steps to Perform:
|
Step |
Instructions |
Points Possible |
|
1 |
Open e04r1NCCTS and save it as e04r1NCCTS_LastFirst. |
0 |
|
2 |
Freeze the fourth row and second column so that they do not scroll off the window on the March Hours worksheet |
4 |
|
3 |
Sort the data on the March Hours worksheet in alphabetic order by Call Type and within call type by Amount Billed from largest to smallest |
4 |
|
4 |
Apply conditional formatting that highlights over 9 days open in Light Red Fill with Dark Red Text. |
4 |
|
5 |
Apply solid fill Orange Data Bar conditional formatting to the Amount Billed values |
4 |
|
6 |
Display the March Filtered worksheet and set a filter to display transactions with an Opened Date in March. |
4 |
|
7 |
Set another filter to display transactions with an Amount Billed greater than or equal to $400 |
5 |
|
8 |
Display the Summary Statistics worksheet. Create a table for the Summary Statistics by Customer dataset. As you create the table, adjust the table range to start on row 2 |
7 |
|
9 |
Assign the name Customer_Stats to the table |
5 |
|
10 |
Apply Orange, Table Style Medium 3 |
5 |
|
11 |
Add a total row to display the sum of the Total Amount Billed column to the table. |
5 |
|
12 |
Select the range A1:D20 in the Summary Stats worksheet and set it as a print area |
5 |
|
13 |
Create a table for the Summary Statistics by Call Type dataset below the first dataset. As you create the table, adjust the table range to start on row 27 |
7 |
|
14 |
Assign the name CallType_Stats to the table |
5 |
|
15 |
Apply Orange, Table Style Medium 3 |
5 |
|
16 |
Add a total row to display the sum of the Total Amount Billed column to the table |
5 |
|
17 |
Click cell D27 and insert a table column to the left. Type Hours per Day in cell D27 |
7 |
|
18 |
Insert a structured reference in a formula in cell D28 that divides the Total Days Open by the Total Hours Logged |
7 |
|
19 |
Use Format Painter to copy the fill formatting from cell D26 to cell E26 |
5 |
|
20 |
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 of Summary Charts and Summary Statistics worksheets. Return to Normal view |
7 |
|
21 |
Save and close the workbook. Based on your instructor’s directions, submit e04r1NCCTS_LastFirst |
0 |
|
Total Points |
100 |
Created On: 10/22/2020 1 Chapter 4 Running Case Portfolio
