ComputerScienceExpert
$18/per page/
Excel Assignment. Straightforward, instructions and template are attached. Thank you.
Client Bill
Due Date
Client Name
Staff Members
Marshall
Stevens
Luz
Total Hours Billed Smith Rate Bill Amount
#N/A
#N/A
#N/A Payment Plan Option
# of Months
APR
Monthy Payment #N/A
#N/A
#N/A
#N/A 6
2% Legal Staff Billable Hours Log
Employee Name
Employee Number
Billable Rate Required billable hours per week:
Weekly requirement met? 265 Dates Worked Mon
1/14/2013 Tue
1/15/2013 Aturo
Hudson
Proctor
Smith
Total Billable Hours Per Day 2.00
3.50
2.25 2.50
3.50
2.25 7.75 8.25 Wed
1/16/2013 Thu
1/17/2013 Fri
1/18/2013 Sat
1/19/2013 3.50
0.50
2.25 1.00 2.00
2.25 Clients Daily Bill $
Bill Total for Week $
# of Clients Served Each Day - $ - 2.25
3.50
5.75
$ - 2.25
3.50
6.75 6.25
$ - $ - 4.25
$ - Average Daily Billable Hours
Total Billable Hours for Week
Lowest Daily Bill this Week
Highest Daily Bill this Week 35 Sun
1/20/2013 3.50
3.50
$ - Weekly total per client 11.00
9.75
11.25
10.50 Legal Staff Billable Hours Log
Employee Name
Employee Number
Billable Rate Maria Luz
7896
#NAME? Dates Worked Mon
1/14/2013 Aturo
Hudson
Proctor
Smith
Total Billable Hours Per Day 2.00
1.00
2.25 Required billable hours per week:
Weekly requirement met? Tue
1/15/2013 Wed
1/16/2013 Thu
1/17/2013 Fri
1/18/2013 Sat
1/19/2013 1.00
2.25
2.50
5.75 0.50
1.00
2.25
2.50
6.25 3.50
1.00
2.25
2.50
9.25 1.00
1.00
2.25
2.50
6.75 2.00
1.00
2.25
2.50
7.75 Clients Daily Bill
Bill Total for Week
# of Clients Served Each Day 5.25
#NAME?
#NAME?
3 #NAME?
3 #NAME?
4 #NAME?
4 #NAME?
4 #NAME?
4 Average Daily Billable Hours
Total Billable Hours for Week
Lowest Daily Bill this Week
Highest Daily Bill this Week 35
yes Sun
1/20/2013 Weekly total per client 9.00
6.00
13.50
12.50
#NAME?
0
5.86
41.00
#NAME?
#NAME? Legal Staff Billable Hours Log
Employee Name
Employee Number
Billable Rate William Stevens
1554
#NAME? Required billable hours per week:
Weekly requirement met? Dates Worked Mon
1/14/2013 Tue
1/15/2013 Wed
1/16/2013 Aturo
Hudson
Proctor
Smith
Total Billable Hours Per Day 2.00
3.50
2.25 1.00 0.50 Thu
1/17/2013 Fri
1/18/2013 Sat
1/19/2013 Clients Daily Bill
Bill Total for Week
# of Clients Served Each Day 2.25
2.50
5.75 7.75
#NAME?
#NAME?
3 #NAME?
3 2.25
2.75 5.75 #NAME?
2 1.00
3.50
2.25 #NAME?
2 2.25
4.25
7.50
#NAME?
3 #NAME?
0 Average Daily Billable Hours
Total Billable Hours for Week
Lowest Daily Bill this Week
Highest Daily Bill this Week 35
no Sun
1/20/2013 Weekly total per client 4.50
7.00
11.25
6.75
#NAME?
0
4.21
29.50
#NAME?
#NAME? Current Hourly Rates
Employee # Last Name
265 Marshall
1554 Stevens
7896 Luz
CONFIDENTIAL First NameRate
David
$
50
William
$
85
Maria
$
150
A Skills Approach: Excel 2013 Chapter 3: Using Formulas and Functions skill review 3.1
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
Step 1
Download
start file 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. 1 | Page skill review 3.1 Last Updated 4/3/15 A Skills Approach: Excel 2013 Chapter 3: Using Formulas and Functions 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. 2 | Page Copy the formula in cell B17 to cells C17:H17. Use any method you want. skill review 3.1 Last Updated 4/3/15 A Skills Approach: Excel 2013 Chapter 3: Using Formulas and Functions 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.
3 | Page skill review 3.1 Last Updated 4/3/15 A Skills Approach: Excel 2013 Chapter 3: Using Formulas and Functions 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. 4 | Page skill review 3.1 Last Updated 4/3/15 A Skills Approach: Excel 2013 Chapter 3: Using Formulas and Functions 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)
Step 2 22. Save and close the workbook. Upload &
Save 23. Upload and save your project file. Step 3 24. Submit project for grading. Grade my
Project 5 | Page skill review 3.1 Last Updated 4/3/15