Reference no: EM13996980

In this project, you will complete a staff billing workbook similar to the one you worked on in Chapter 1. This worksheet is more complicated and uses a variety of formulas to calculate information about each staff member's weekly billing and to generate client bills from the staff hours. As you work on the Marshall Hours worksheet, you can use the Luz Hours or Stevens Hours worksheet as a guide. This is a long project. Be sure to save your work often!

Skills needed to complete this project:

• Naming Ranges of Cells
• Using CONCATENATE to Combine Text
• Creating Formulas Referencing Data from Other Worksheets
• Finding Data Using the VLOOKUP Function
• Working with Named Ranges
• Using the Function Arguments Dialog to Enter Function
• Creating Formulas Using Counting Functions
• Using Formula AutoComplete to Enter Functions
• Calculating Averages
• Finding Minimum and Maximum Values
• Using the Logical Function IF
• Displaying and Printing Formulas
• Using Date and Time Functions
• Checking Formulas for Errors
• Finding Errors Using Trace Precedents and Trace Dependents
• Updating Named Ranges with the Name Manager
• Editing and Deleting Names with the Name Manager
• Calculating Loan Payments Using the PMT Function

1. Open the start file EX2013-SkillReview-3-1.The file will be renamed automatically to include your name. Change the project file name if directed to do so by your instructor, and save it.

2. If the workbook opens in Protected View, click the Enable Editing button in the Message Bar at the top of the workbook.

3. The Luz Hours and Stevens Hours worksheets are completed, but they contain errors. You'll need to fix
the errors before working on the Marshall Hours worksheet.
a. Click the Luz Hours sheet tab.
b. Click cell C4.
c. Notice the #NAME? error. Move your mouse over the Smart Tag icon to display a tool tip describing the possible error-The formula contains unrecognized text.
d. The formula =VLOOKUP(C3,BillableRates,4,FALSE) references the named range
BillableRates. That name has not yet been defined. That's what is causing the error.

4. Billable rates are kept in the Rates worksheet. Create the name BillableRates to use in formulas throughout the workbook.
a. Click the Rates sheet tab.
b. Select cells A3:D5.
c. Type BillableRates in the Name box.
d. Press Enter.

5. Return to the Luz Hours worksheet. Notice all the errors have been fixed. Now you can move on to completing the Marshall Hours sheet.

6. Enter a formula in cell C2 to display Marshall's full name in the format Bob Smith. Staff names are kept in the Rates worksheet.
a. Click the Marshall Hours sheet tab, and click cell C2.
b. On the Formulas tab, in the Function Library group, click the Text button, and select CONCATENATE.
c. Click the Rates sheet tab. If necessary, position the Function Arguments dialog so you can click the sheet tabs.
d. Click cell C3 to enter the cell reference in the Text1 argument box.
e. Press TAB to move to the Text2 argument box.
f. Type " " to place a space between the first and last names.
g. Press TAB to move to the Text3 argument box.
h. Click the Rates tab again.
i. Click cell B3 to enter the text reference in the Text3 argument box.
j. Click OK. The completed formula should look like this: =CONCATENATE(Rates!C3," ",Rates!B3)

7. Enter a formula in cell C4 to look up Marshall's current billable rate. Use the employee number as the lookup value.
a. Click cell C4.
b. On the Formulas tab, in the Function Library group, click the Lookup & Reference button, and select VLOOKUP.
c. Click cell C3 to enter it in the Lookup_value argument box.
d. Type BillableRates in the Table_array argument box.
e. The rates are located in the fourth column of the lookup table. Type 4 in the Col_index_num argument box.
f. Ensure that the function will return only an exact match. Type false in the Range_lookup argument box.
g. Click OK. The completed formula should look like this: =VLOOKUP(C3,BillableRates,4,FALSE)

8. Enter formulas in cells B17:H17 to calculate the number of clients served each day.
a. Click cell B17.
b. Type =COU
c. Double-click COUNT in the Formula AutoComplete list.
d. Click cell B9 and drag to cell B12.
e. Press Enter. The completed formula should look like this: =COUNT(B9:B12)
f. Copy the formula in cell B17 to cells C17:H17. Use any method you want.

9. Enter a formula in cell H19 to calculate the average daily billable hours (B13:H13).
a. Click cell H19.
b. Type =AV and then double-click AVERAGE in the Formula AutoComplete list.
c. Click cell B13 and drag to cell H13.
d. Press Enter. The completed formula should look like this: =AVERAGE(B13:H13)

10. Enter a formula in cell H20 to calculate the total billable hours for the week (B13:H13).
a. Click cell H20.
b. Type =SU and then double-click SUM in the Formula AutoComplete list.
c. Click cell B13 and drag to cell H13.
d. Press Enter. The completed formula should look like this: =SUM(B13:H13)

11. Enter a formula in cell H22 to calculate the lowest daily bill for the week (B15:H15).
a. Click cell H22.
b. Type =MIN( and then click cell B15 and drag to cell H15.
c. Press [Enter]. The completed formula should look like this: =MIN(B15:H15)

12. Enter a formula in cell H23 to calculate the highest daily bill for the week.
a. Click cell H23.
b. Type =MAX( and then click cell B15 and drag to cell H15.
c. Press Enter. The completed formula should look like this: =MAX(B15:H15)

13. Each staff member is required to log a minimum number of billable hours per week. Enter a formula in cell H3 using an IF statement to display "yes" if the total billable hours for the week (cell H20) is greater than or equal to the required hours (cell H2) and "no" if they are not.
a. Click cell H3.
b. On the Formulas tab, in the Function Library group, click Logical.
c. Click IF.
d. If necessary, move the Function Arguments dialog to the side so you can see the worksheet data.
e. In the Logical_test argument box, type: H20>=H2
f. In the Value_if_true argument box, type: yes
g. In the Value_if_false argument box, type: no
h. Click OK. The completed formula should look like this: =IF(H20>=H2,"yes","no")

14. Display your formulas temporarily to check for accuracy.
a. On the Formulas tab, in the Formula Auditing group, click the Show Formulas button.
b. When you are ready to continue, hide the formulas and display formula values by clicking the Show Formulas button again.

15. Now that the worksheet for Marshall is complete, you can generate a bill for the Smith client for the week. Click the Smith Bill sheet tab.

16. All bills are due thirty days from the date the bill was created. Enter a formula in cell C2 to calculate the due date using the TODAY function.
a. Double-click cell C2.
b. Type the formula: =TODAY()+30
c. Press Enter.

17. Enter formulas to reference the number of hours each staff member billed for Smith.
a. Click cell B6 and type = to begin the formula.
b. Click the Marshall Hours sheet, and click cell J12.
c. Press Enter. The completed formula should look like this: ='Marshall Hours'!J12
d. Type = to begin the next formula in cell B7.
e. Click the Stevens Hours sheet, and click cell J12.
f. Press Enter. The completed formula should look like this: ='Stevens Hours'!J12
g. Type = to begin the next formula in cell B8.
h. Click the Luz Hours sheet, and click cell J12.
i. Press Enter. The completed formula should look like this: ='Luz Hours'!J12

18. There are errors in the Rate and Bill Amount columns. Use your error checking skills to track down the cause of the error.
a. On the Formulas tab, in the Formula Auditing group, click the Error Checking button to open the
Error Checking dialog.
b. After you've reviewed the first error, click the Next button to go to the next error. Continue reviewing each error and clicking Next until you receive the message that the error check is complete for the entire sheet. Click OK.

19. Did you notice that every error in the worksheet is a "value not available" error? You probably need
to dig deeper to find the root cause of the problem.
a. Click cell D6 and look at the formula in the formula bar: =B6*C6
b. Display the Trace Precedent and Trace Dependent arrows for this cell. On the Formulas tab, in the Formula Auditing group, click both the Trace Precedents button and the Trace Dependents button.
c. You can see that the problem appears to start in the precedent cell C6. Hide the arrows for cell D6 by clicking the Remove Arrows button, and then click cell C6 and click the Trace Precedents button.
d. Notice that one of the precedent arrows for cell C6 refers to another worksheet. Double-click the dashed precedent arrow line.
e. In the Go To dialog, click the worksheet reference and then click the OK button.

20. The link takes you to the Rates sheet where cells A2:D5 are selected. Notice that the
Name box displays the name ClientRates. (Depending on your screen resolution, the name may be slightly cut-off.) The formula in cell C6 is a lookup formula that uses the named range ClientRates as the Table_array argument. There are two problems with the definition
of the named range: It includes the label row (A2:D2), and it includes the employee number data (A2:A5).
a. On the Formulas tab, in the Defined Names group, click the Name Manager button.
b. Click the ClientRates name and review the cell range in the Refers to box. The range is incorrect. The ClientRates name should refer to cells B2:D5 on the Rates sheet.
c. Edit the range listed in the Refers to box to: =Rates!$B$2:$D$5
d. Click the Close button to close the Name Manager.
e. When Excel asks if you want to save the changes to the name reference, click Yes.

21. Now that the total bill amount is computing correctly, you can enter a formula in cell D14 to give the client the option of a monthly payment plan. You are authorized to offer a 6-month payment plan at a 2% annual percentage rate. Use cell references in the formula.
a. If necessary, click the Smith Bill sheet.
b. Click cell D14.
c. On the Formulas tab, in the Function Library group, click the Financial button.
d. Scroll down the list, and click PMT.
e. In the Function Arguments dialog, enter the Rate argument: D13/12
f. Click in the Nper argument box, and then click cell D12 (the number of payments).
g. Click in the Pv argument box, and then click cell D9 (the present value of the loan).
h. In the Function Arguments dialog, click OK. The completed formula should look like this: =PMT(D13/12,D12,D9)
i. The monthly payment amount appears as a negative number. That might be confusing to the client. Modify the formula so the result appears as a positive number.
j. Double-click cell D14 and type - between = and PMT.
k. Press Enter. The final formula should look like this: = -PMT(D13/12,D12,D9)

22. Save and close the workbook.

23. Upload and save your project file.

24. Submit project for grading.

Attachment:- -ex2013-skillreview-3-1.rar