## 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 have you used technology to help you as a student in the past, and why is it important to be open to learning new technology in the workplace?

The following question will give you a chance to self-evaluate, to think about what you’ve been learning in this course, and to draw your own conclusions about how you can….