Grader – Instructions Excel 2019 Project
Chapter 6 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; sorted the main dataset for analysis; and prepared it to be printed on two pages. You 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. You filtered a copy of the dataset and converted the summary sections into tables. Finally, you created a PivotTable and PivotChart to analyze agent performance in resolving disaster recovery support calls. Next you will continue your analysis of disaster recovery services using one- and two-variable data tables as well as Goal Seek.
Steps to Perform:
| Step | Instructions | Points Possible |
| 1 | Open e06r1NCCTS and save it as e06r1NCCTS_LastFirst. | 0 |
| 2 | Ensure the Disaster Recovery Forecast sheet is active. Create range names based on the values in the range B15:B16 | 12 |
| 3 | Edit the name range Disaster_Recovery to the more appropriate name Disaster_Recovery_Rate | 10 |
| 4 | Use the formula Disaster_Recovery_Rate*Hours_Billed to calculate the Amount Billed in cell B17 | 7 |
| 5 | Create a series of substitution values in the range D4:D12 based on hours billed. The substitution values should range from five to nine hours in half hour increments | 10 |
| 6 | Enter a reference to the Amount Billed in the appropriate location (column) for the first data table. Complete the data table using the appropriate input cells. Apply a Custom number format (name it) to the reference to the formula cell. | 14 |
| 7 | Apply Comma Style format to the range E4:E12 | 5 |
| 8 | Copy the substitution values used in the first data table to the range G4:G12 | 5 |
| 9 | Create a series of substitution values in the range H3:N3 based on hourly rate. The substitution values should range from $65 to $95 in increments of $5.00. Apply Accounting Number format to the substitution values | 7 |
| 10 | Enter a reference to the Amount Billed in the appropriate location for the second data table. Complete the data table using the appropriate input cells. Apply Comma Style format to the results and create a Custom number format to the reference to the formula cell | 12 |
| 11 | Use Goal Seek to determine the optimal hourly rate (cell B15) to bill $500.00 for a 5.5-hour repair | 13 |
| 12 | 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 | 5 |
| 13 | Save and close the workbook. Based on your instructor’s directions, submit e06r1NCCTS_LastFirst | 0 |
| Total Points | 100 |
Created On: 10/22/2020 2 Chapter 6 Running Case Portfolio
