## Create a series of substitution values in the range H3:N3 based on hourly rate.

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

### How does Nevada compare to other states in regard to illegal immigrants, legal immigrants, and temporary workers in the United States?

Comparative State Analysis HIS/311 Version 4 1 University of Phoenix Material Comparative State Analysis Perform an Internet search to complete the following charts and address the following topics, as it….

### Describe how people are involved in their ceremony (either as an individual, or as a group

Term Paper 2 Suggested questions to answer – not required questions to answer. There are also only suggested number of pages. The course is teaching American Indian religion and philosophy….

### Craft a list of questions beforehand to help guide you.

The Deadline Is Today 2:00 Pm (Wednesday 28-04-2021) ! Interview an elderly family member or friend about a major historical event, organization, or issue (For 2020 courses this may be….