Chapter 6 Running Case Portfolio

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