Your friend Kimo is a server at a
restaurant. He downloaded data for his customers’ food and beverage purchases
for the week. You will complete the workbook by applying consistent formatting
across the worksheets and finalizing the weekly summary. The restaurant
requires tip sharing, so you will calculate how much he will share with the
beverage worker and the assistant.
Step |
Instructions |
Points Possible |
1 |
Start Excel. Download and open the file named Exp19_Excel_Ch09_Cap_Assessment_Tips.xlsx.
Grader has automatically added your last name to the beginning of the
filename. |
0 |
2 |
The Tip Left column in the
Friday worksheet contains a fill color and number formatting. You want to
fill these formats to the other daily worksheets. |
8 |
3 |
Now you want to insert column totals for the five
worksheets simultaneously. |
5 |
4 |
The Week worksheet is designed
to be a summary sheet. You want to insert a hyperlink to the Total heading in
the Monday worksheet. |
2 |
5 |
In cell A6 on the Week worksheet, insert a hyperlink to
cell A25 in the Tuesday worksheet with the ScreenTip text Tuesday’s
Totals. Test the hyperlink to ensure it works correctly. |
2 |
6 |
In cell A7, insert a hyperlink
to cell A25 in the Wednesday worksheet with the ScreenTip text Wednesday’s
Totals. Test the hyperlink to ensure it works correctly. |
2 |
7 |
In cell A8, insert a hyperlink to cell A25 in the Thursday
worksheet with the ScreenTip text Thursday’s Totals. Test the hyperlink to
ensure it works correctly. |
2 |
8 |
In cell A9, insert a hyperlink
to cell A25 in the Friday worksheet with the ScreenTip text Friday’s
Totals. Test the hyperlink to ensure it works correctly. |
2 |
9 |
Now, you are ready to insert references to cells in the
individual worksheets. First, you will insert a reference to Monday's Food
Total. |
2 |
10 |
The next formula will display
the totals for Tuesday. |
2 |
11 |
In cell B7, insert a formula with a 3-D reference to cell
B25 in the Wednesday worksheet. Copy the formula to the range C7:E7. |
2 |
12 |
In cell B8, insert a formula
with a 3-D reference to cell B25 in the Thursday worksheet. Copy the formula
to the range C8:E8. |
2 |
13 |
In cell B9, insert a formula with a 3-D reference to cell
B25 in the Friday worksheet. Copy the formula to the range C9:E9. |
2 |
14 |
Now you want to use a function
with a 3-D reference to calculate the totals. |
5 |
15 |
The servers are required to share a portion of their tips
with the Beverage Worker and Assistants. The rates are stored in another
file. |
5 |
16 |
Next, you will calculate the
tips for the assistant. |
5 |
17 |
You noticed a circular error when you first opened the
Tips workbook. Now you will find and correct it. |
5 |
18 |
You want to create a validation
rule to prevent the user from accidentally entering a negative value. For
now, you will create a validation in the Friday worksheet. |
10 |
19 |
Now you will copy the validation settings to the other
daily worksheets. |
10 |
20 |
You want to unlock data-entry
cells so that the user can change the tips in the daily worksheets. |
10 |
Get Free Quote!
289 Experts Online